Hide all unused rows/columns

Aspose.Cells Product Family
1

I have looked at the following thread on the same topic:

But our spreadsheets are much more complicated, and they cannot be formatted with the simple SetRowHeight() and SetColumnWidth(). Is there any other way to hide all unused rows/columns?

Thanks a lot.
Louis

2

@sanshi

Could you please provide more details about the specific complications you are facing with hiding unused rows and columns in your spreadsheets?

3

The spreadsheets we are creating have at least 30+ columns and hundred or thousands of rows. Each cell may have different formatting requirements.

4

@sanshi

Could you explain what are unused rows/columns?
It’s better that you can share a template file and excepted result file. We will check them soon.

5

Please see shakeel.faiz’s screenshot in the aforementioned thread. All the rows greater than 3 and all the columns greater than C are hidden/blank.

6

@sanshi
If blank cells have no settings at all and are considered unused, you can do it as follows.

const int MAX_EXCEL_ROWS = 1048576;
const int MAX_EXCEL_COLUMNS = 16384;
Workbook workbook = new Workbook("test.xlsx");
Worksheet sheet = workbook.Worksheets[0];
// Get the last used row and column indexes
int lastRow = sheet.Cells.MaxDataRow;      
int lastColumn = sheet.Cells.MaxDataColumn;  
Cells cells =  workbook.Worksheets[0].Cells;
 cells.IsDefaultRowHidden = true;

cells.StandardWidth = 0;

hope helps a bit!

7

@ xinya.zhu Thanks for the reply. I’m already doing the loops to hide rows and columns. Just like to see if there are any other ways.

By setting cells.StandardWidth to 0, is it the same way as the aforementioned thread? Then I have to go through data cells to set width and height accordingly.

8

@sanshi
Please set
cells.IsDefaultRowHidden = true;
cells.StandardWidth = 0;
to hide rows and columns.

It’s better to hide rows and columns when creating empty file.

If Column A ,Column C contain data, but column B does not contain data, do you want to hide column B ?

9

Ok I’ll try.

For your question, I don’t hide any rows or columns in the middle of the data area so Column B will be always shown.

10

@sanshi,

Kindly try using the suggested APIs to complete your task. If you encounter any issues, please provide more details along with a sample Excel file (zipped before attaching) and the expected output Excel file for reference. Additionally, sharing your current code would be helpful. This information will allow us to better understand your situation and potentially offer an improved code snippet.

11

@sanshi

Please try the following codes to reduce row and column object:


int lastRow = cells.MaxDataRow;
int lastColumn = cells.MaxDataColumn;

for (int i = 0; i <= lastRow; i++)
{
    Row row = cells.Rows[i];
}
cells.IsDefaultRowHidden = true;
for (IEnumerator ie = cells.Rows.GetEnumerator(true, false);ie.MoveNext();)
{
    Row row = ie.Current as Row;
    if (row.Index <= lastRow)
        break;
    row.IsHidden = true;
}
for (int i = 0; i <= lastColumn; i++)
{
    Column column = cells.Columns[i];
}
//  cells.IsDefaultColumnHidden = true;//  we will try to support this property in the next version
cells.StandardWidth = 0;
for (IEnumerator ie = cells.Columns.GetEnumerator(); ie.MoveNext();)
{
    Column column = ie.Current as Column;
    if (column.Index <= lastColumn)
        continue;
    column.IsHidden = true;
}