Hidden Rows and Columns
The API of the workbook model allows you to set the hidden state of each row or column. The following sections describe how to get, set, and clear the hidden state:
Get, Set and Clear the Hidden State
To set the hidden state of the rows or columns, create a RowSelection or a ColumnSelection instance from the rows or columns you want to manipulate. This instance exposes the GetHidden(), SetHidden(), and ClearHidden() methods that you can use 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,
IsIndeterminateis set tofalse. However, if the hidden state varies, theIsIndeterminateproperty is set totrueand theValueproperty of theRangePropertyValueclass returns the default value of the hidden state, which isfalse. -
Value: Holds the actual hidden state. If the
IsIndeterminateproperty is set tofalse,Valuecontains the hidden state shared by the entire region. If theIsIndeterminateproperty is set totrue, this indicates that the state is not the same for all rows or columns in the selection and theValueproperty is set to its default value.
The SetHidden() method changes the hidden state of the rows and columns. It takes an argument of type bool which specifies the new state. The ClearHidden() method resets the hidden state of the selected rows or columns to the default.
Example 1 shows how to get 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 makes 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 the height or width properties. The opposite is also true. If the height or width of a row or column is set to zero, this does not change its hidden state in the model.
Example 2 shows how you can set the width of a group of columns without affecting 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 does not affect the hidden state of the rows or columns. It does, 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;