Hidden columns and rows

Aspose.Cells Product Family
1

I need to do some preliminary checking on worksheets before certain formatting is applied, one of them being makin gsure there are no hidden columns or rows.

How do I check to see if a column/row is hidden or not?

2

You can use Cells.GetRowHeight and Cells.GetColumnWidth method to get row height and column width values. The value will be 0 if the row/column is hidden.

3

Thanks, but now I have another problem.

I’ve attached the test file I’m using, with one hidden column. However, as I will not know the exact number of columns before a user submits the file, I’m using the following to get the number of columns:

For col = 0 To oSheet.Cells.Columns.Count - 1
If oSheet.Cells.GetColumnWidth(col) = 0.0 Then Return True
Next

As you can see the tets file has 5 columns, but oSheet.Cells.Columns.Count is returning a value of 1 (2 columns, not 5).

Any ideas?

4

Columns class only contains columns with settings different with default setting.

Please try:

For col = 0 To oSheet.Cells.MaxColumn
If oSheet.Cells.GetColumnWidth(col) = 0.0 Then Return True
Next

5

Thanks, but now I have another problem.

Using the attached test file, I applied some formatting to the first sheet and left the second sheet with all defaults. Now my code is not picking up any rows with a height of 0.0. --> all rows are showing a height as if not hidden.

Any ideas?

6

Which version are you using? Please try this latest fix.

7

@mecED,
We have discontinued Aspose.Excel and introduced a new product Aspose.Cells that is a much advanced and feature-rich product. It contains a lot of functions that can be used to check the look and feel of the Excel file. Now you can set/un-set and check the visibility of a column or row as demonstrated in the following sample code.

Workbook workbook = new Workbook();
workbook.Worksheets[0].Cells.HideColumn(2);
Console.WriteLine(workbook.Worksheets[0].Cells.IsColumnHidden(2));

workbook.Worksheets[0].Cells.HideColumns(4, 4);
for(int c = 4; c< 8;c++)
    Console.WriteLine(workbook.Worksheets[0].Cells.IsColumnHidden(c));

//A random check. It should display FALSE
Console.WriteLine(workbook.Worksheets[0].Cells.IsColumnHidden(10));

Here we can see that Cells class contains functions HideColumn(), HideColumns() and IsColumnHidden(). You can hide a single column using the HideColumn() by providing (0 index based) column number as argument.

Similarly if multiple columns are to be hidden, use HideColumns(), where the first argument is the starting column number and the second argument is the number of columns to be hidden.

You can use IsColumnHidden() function to check if a column is hidden or not.

The same set of functions is available for rows like HideRow, HideRows, and IsRowHidden.

Test the product features by downloading a free trial version here:
Aspose.Cells for .NET (Latest Version)

A comprehensive solution is available here that can be used to test different features of this product.