Hidden Rows and Columns
The API of the workbook model allows you to set the hidden state of each row or column. This article briefly describes how this can be achieved. It contains the following sections:
Get, Set and Clear the Hidden State
In order to set the hidden state of the rows or columns, you need to create a RowSelection or a ColumnSelection instance from the rows or columns you would like to manipulate. This instance exposes the methods GetHidden(), SetHidden() and ClearHidden() which can be used to change the hidden state of the selection.
The GetHidden() method returns a RangePropertyValue instance which summarizes the information about the hidden state of all selected rows or columns. The object exposes two properties:
IsIndeterminate: Indicates whether the hidden state is consistent among all rows or columns in the selection. If all rows or columns have one and the same hidden state, IsIndeterminate is set to false. However, if the hidden state varies, the IsIndeterminate property is set to true and the Value property of the RangePropertyValue class returns the default value of the hidden state, which is false.
Value: Holds the actual hidden state. If the IsIndeterminate property is set to false, Value contains the hidden state shared by the entire region. If the IsIndeterminate property is set to true, this indicates that the state is not the same for all rows or columns in the selection and the Value property is set to its default value.
The SetHidden() method is used to change the hidden state of the rows and columns. It takes an argument of type bool which specifies the new state. The ClearHidden() method is used to reset the hidden state of the selected rows or columns to the default.
Example 1 shows how to retrieve and change the hidden state of several rows using the RowSelection class. The code checks if all the rows in the selection are visible and only then hides them. If the selection contains any hidden rows, the hidden state is cleared which will make all rows visible.
Example 1: Change row hidden state
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
RowSelection rowSelection = worksheet.Rows[5, 7];
RangePropertyValue<bool> rowsHiddenState = rowSelection.GetHidden();
if (!rowsHiddenState.IsIndeterminate && !rowsHiddenState.Value)
{
rowSelection.SetHidden(true);
}
else
{
rowSelection.ClearHidden();
}
Relationship with the Height and Width Properties
When the hidden state property of a RowSelection or a ColumnSelection is set, this does not affect its height or width properties. The opposite is also true. If the height or width of a row or column is set to zero, this will not change its hidden state in the model.
Example 2 shows how you can set the width of a group of columns and it would not affect the hidden state.
Example 2: Change column width
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ColumnSelection columnSelection = worksheet.Columns[5, 7];
columnSelection.SetWidth(new ColumnWidth(0, true));
bool hiddenState = columnSelection.GetHidden().Value;
Relationship with the AutoFit Method
Like setting the height or width through the SetHeight() and SetWidth() methods, using the Auto Fit methods will not affect the hidden state of the rows or columns. It will, however, affect the underlying height and width.
Example 3 demonstrates this by hiding a row, autofitting it and then checking its hidden state.
Example 3: Auto fit on hidden rows
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
RowSelection rowSelection = worksheet.Rows[3];
rowSelection.SetHidden(true);
rowSelection.AutoFitHeight();
bool hiddenState = rowSelection.GetHidden().Value;