Hiding Rows Slow, Results in Huge File Size

Aspose.Cells Product Family
1

Hello,

I am trying to hide all rows after a certain row in a worksheet. I.e. from row 200, to the maximum row, I want to hide all rows. This is how I am doing it:

public static void HideAllRowsAfter(Worksheet worksheet, int rowIndex)
{
    const int MaxExcelRows = 1048576;

    worksheet.Cells.HideRows(rowIndex, MaxExcelRows - rowIndex);
}

This is really slow, makes the workbook incredibly slow, and turns my workbook size from ~250kb to ~3000kb. When I manually select this range in Excel, right-click, and press on “hide” it doesn’t bloat the file and it’s not slow. How do I copy this functionality in Aspose?

Please advise. Thank you!

2

@danilolekovic,

I tested your scenario/case using a sample Excel file(attached in the zipped archive) with latest version of Aspose.Cells for .NET 23.10 using the following sample code and it works ok, the process is not slow so the code executed efficiently.
e.g.
Sample code:

Workbook workbook = new Workbook("g:\\test2\\Bk_hiderows1.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
int rowIndex = 200;
const int MaxExcelRows = 1048576;
worksheet.Cells.HideRows(200, MaxExcelRows - rowIndex);
workbook.Save("g:\\test2\\out1.xlsx");

Please also find attached the output Excel in the zipped archive.
files1.zip (224.9 KB)

However, I noticed the size of the output file is extended which can be investigated. Do you need to log a ticket and investigate on the output size issue? We can do it for your needs.

3

Hi Amjad, thank you. Yes, the size of the output is my primary concern. I’d appreciate any possible solution for that, if one may exist. Thank you so much!

4

@danilolekovic,

Since we have already noticed that the size of the output file is extended even in a simple scenario, it needs to be investigated. We will check if there is any way to minimize the size of the output file, particularly. Additionally, if possible, we will enhance the relatively slower process (when comparing and performing the similar task in MS Excel, which is more efficient) for hiding so many rows.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-54397

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
5

@danilolekovic

Because by default the rows in the sheet in your template file are visible, so when you set most rows as hidden, all those rows must be instantiated and saved to the resultant file with “hidden” property. Because the data of so many rows has been saved to the generated file, so the file size is large.

To decrease the file size, we think you may set the default visibility as hidden so most of rows in the worksheet need not to be instantiated. After that, instead of setting most of rows as hidden, please set those fewer rows as visible explicitly. Code example:

...
worksheet.Cells.IsDefaultRowHidden = true;
for (int i = 0; i < rowIndex; i++)
{
    worksheet.Cells.Rows[i].IsHidden = false;
}
...
1 Like