Performance regression of Cells.clearContents since 24.6

Aspose.Cells Product Family
1

Hi,

Sample code:

System.out.println("CellsHelper.getVersion() = " + CellsHelper.getVersion());
Workbook inw = new Workbook("Case2.xlsx");
Worksheet worksheet = inw.getWorksheets().get("Sheet1");
ListObject table = worksheet.getListObjects().get(0);
System.out.println("Starting clearContents");
long startMillis = System.currentTimeMillis();

for (int i = 0; i <= 1000000; i++) {
    worksheet.getCells().clearContents(table.getStartRow(), table.getStartColumn(), table.getStartRow(), table.getStartColumn());
}

System.out.println("clearContents done. Time: " + (System.currentTimeMillis() - startMillis) + " ms");

Output when using Aspose Cells for Java, version 24.5:

CellsHelper.getVersion() = 24.5.0
Starting clearContents
clearContents done. Time: **132** ms

Output when using Aspose Cells for Java, version 24.6:

CellsHelper.getVersion() = 24.6.0
Starting clearContents
clearContents done. Time: **26673** ms

The performance has decreased by a factor of 200.

Kind regards,
Taras

Case2.zip (2.8 MB)

2

@TarasTielkes
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): CELLSJAVA-46103

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.

3

Hi @simon.zhao ,

We were able to implement a workaround.
Instead of calling com.aspose.cells.Cells#clearContents(int, int, int, int), we call com.aspose.cells.Cell#setValue(null). Can you confirm that this has the same effect?

Thanks in advance,
Taras

4

@TarasTielkes,

Thanks for sharing the workaround. This may be a temporary solution as we will surely address the performance issue in the Cells.clearContents API. We will provide an update once we figure it out or we have more information on it.

5

@TarasTielkes
When you clearing single cell by Cells.ClearContents, it has the same effect with Cell.PutValue(null)/SetValue(null). In fact it is much better to use Cell.PutValue() instead of Cells.ClearContents() when the processed range is a single cell. For range operations, there are more works need to be done, such as checking other objects that may be influenced.

For the performance issue, it is caused by the fact that there are large amount of formulas in the template file. When removing cells in a range, we need to check all formulas to make sure the operation will not cause any formula corrupted, otherwise invalid formulas and then corrupted excel file may be generated.

In old versions, we did not perform this check, in fact it was a bug and has caused issues for some cases so we fixed it by adding the required check in newer versions.

We will try to improve the performance of checking formulas in next version. However, for your case we still recomend you to use Cell.PutValue() instead because it will always give better performance.

6

@TarasTielkes,

This is to inform you that your issue (Ticket ID: “CELLSJAVA-46103”) has been resolved. We have improved performance for Cells.clearContents. The enhancement/fix will be included in our upcoming release, Aspose.Cells v24.9, which we plan to release in the first half of September 2024. You will be notified when the next version is released.

7

The issues you have found earlier (filed as CELLSJAVA-46103) have been fixed in Aspose.Cells for Java 24.9.