I have a particular Excel workbook with one worksheet that has a large used range due to a formula in cell XFD1. When iterating over the used range on this sheet with Aspose, the total size of the workbook noticeably increases, and we have determined that this is because Aspose is creating new XML data for the many empty cells in this used range. The following is the utility program I ran in C# using Aspose.Cells 24.7.0:
using Aspose.Cells;
License license = new();
license.SetLicense("Aspose.Cells.lic");
Workbook workbook = new(@"C:\Swift\IRE\GP Hourly Pension Plan_Baseline 2023.xlsm");
var stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes on open: {stream.Length}");
stream.Close();
var inputCells = workbook.Worksheets["Input"].Cells;
inputCells.ExportArray(0, 0, inputCells.MaxDataRow + 1, inputCells.MaxDataColumn + 1);
stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes after exporting used range data as array: {stream.Length}");
stream.Close();
for (int r = 0; r <= inputCells.MaxDataRow; r++)
{
for (int c = 0; c <= inputCells.MaxDataColumn; c++)
{
_ = inputCells[r, c].Value;
}
}
stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes after iterating all cells in used range: {stream.Length}");
stream.Close();
Using the file previously mentioned, this is the console output from this utility program:
Number of bytes on open: 10501120
Number of bytes after exporting used range data as array: 10501120
Number of bytes after iterating all cells in used range: 10531328
GP Hourly Pension Plan_Baseline 2023.zip (2.4 MB)
I will be very curious to know what the root cause of this is. Thank you!
@bytefyre
When you use row and column indexes to retrieve a cell, if the cell does not exist, a new cell will be created. You can use the Cells.CheckCell method to check cells and iterate through their values. Please refer to the following example code.
Console.WriteLine("version: " + CellsHelper.GetVersion());
Workbook workbook = new Workbook(filePath + @"GP Hourly Pension Plan_Baseline 2023.xlsm");
var stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes on open: {stream.Length}");
stream.Close();
var inputCells = workbook.Worksheets["Input"].Cells;
int maxDataRow = inputCells.MaxDataRow;
int maxDataColumn = inputCells.MaxDataColumn;
inputCells.ExportArray(0, 0, maxDataRow + 1, maxDataColumn + 1);
stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes after exporting used range data as array: {stream.Length}");
stream.Close();
for (int r = 0; r <= maxDataRow; r++)
{
for (int c = 0; c <= maxDataColumn; c++)
{
Cell temp = inputCells.CheckCell(r, c);
if (temp != null)
{
_ = temp.Value;
}
}
}
stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes after iterating all cells in used range: {stream.Length}");
stream.Close();
The output:
version: 25.5
Number of bytes on open: 10503680
Number of bytes after exporting used range data as array: 10504192
Number of bytes after iterating all cells in used range: 10504192
Hope helps a bit.
Hey John! Thanks for your reply! I tried out your suggestion using my utility program and saw a similar result. I say similar because in my case, using Aspose.Cells. 24.7.0, there was actually no change in the number of bytes at all, so I’m curious as to why that would be different in 25.5.0.
@bytefyre,
Please try using the code snippet shared in the reply of @John.He. In case you still find the issue, kindly try latest version/fix: Aspose.Cells v25.5.
Amjad,
I think there is a miscommunication over my last response. I was pointing out that when using the code changes John suggested with Aspose.Cells 24.7.0, there was no change whatsoever in the number of bytes, which is what I would expect. However, the output he provided using 25.5.0 had an increase in the number of bytes simply from calling ExportArray, which is why I’m questioning what would have changed between 24.7.0 and 25.5.0. Frankly, I wouldn’t feel comfortable upgrading without a proper explanation and remedy to that issue.
I decided to upgrade to 25.5.0 to see how this would affect the output and saw the following:
Number of bytes on open: 10503168
Number of bytes after exporting used range data as array: 10503168
Number of bytes after iterating all cells in used range: 10502656
The number of bytes being read after opening the workbook is different between 24.7.0 and 25.5.0, and I’m not sure to what that should be attributed. Also, and perhaps more importantly, the number of bytes is different between ExportArray and iterating using CheckCell, which was not the case in 24.7.0. Therefore, we would like this to be treated as a bug since this behavior is not only inexplicably different between versions but doesn’t line up with Excel Interop.
@bytefyre,
Thanks for providing further details.
I did test your scenario/case using Aspose.Cells for .NET v25.5 with the following sample code using your template Excel file.
Console.WriteLine("version: " + CellsHelper.GetVersion());
Workbook workbook = new Workbook("e:\\test2\\GP Hourly Pension Plan_Baseline 2023.xlsm");
var stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes on open: {stream.Length}");
stream.Close();
var inputCells = workbook.Worksheets["Input"].Cells;
int maxDataRow = inputCells.MaxDataRow;
int maxDataColumn = inputCells.MaxDataColumn;
inputCells.ExportArray(0, 0, maxDataRow + 1, maxDataColumn + 1);
stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes after exporting used range data as array: {stream.Length}");
stream.Close();
for (int r = 0; r <= maxDataRow; r++)
{
for (int c = 0; c <= maxDataColumn; c++)
{
Aspose.Cells.Cell temp = inputCells.CheckCell(r, c);
if (temp != null)
{
_ = temp.Value;
}
}
}
stream = workbook.SaveToStream();
Console.WriteLine($"Number of bytes after iterating all cells in used range: {stream.Length}");
stream.Close();
console output:
version: 25.5
Number of bytes on open: 10503168
Number of bytes after exporting used range data as array: 10503168
Number of bytes after iterating all cells in used range: 10502656
You are right, the number of bytes being read after opening the workbook is different between 24.7.0 and 25.5.0, the number of bytes is different between ExportArray and iterating using CheckCell.
We require thorough evaluation of the issue to confirm if this is a bug or something else. We have opened the following new ticket(s) in our internal issue tracking system and will either provide complete details on it or deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58455
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.
@bytefyre
workbook.SaveToStream() (Workbook.SaveToStream | Aspose.Cells for .NET API Reference) will save the workbook as xls file, so we have to remove redundant data since Excel 2007. There was an issue about removing data validation. We have fixed this issue and will include the fix into next release 25.6 in the next month. Here are results by the fix.
Number of bytes on open: 10502656
Number of bytes after exporting used range data as array: 10502656
Number of bytes after iterating all cells in used range: 10502656
If you want to keep the xlsm file format and save to MemoryStream , please try the following codes:
using (MemoryStream stream = new MemoryStream())
{
workbook.Save(stream, SaveFormat.Xlsm);
Console.WriteLine($"Number of bytes after exporting used range data as array: {stream.Length}");
}
Hi Simon,
Thanks for following up. I do see consistent results now with the new code you provided, and I appreciate the bug report regarding SaveToStream.
@bytefyre,
Thank you for the confirmation on the results. Aspose.Cells v25.6 is planned for release in the first half of June 2025, and you will be notified as soon as the new version is published.