Our project has recently upgraded aspose. cells for net, from version 8. x to 23.8. After the upgrade, we found that the code “cell. Formula=formula;” has become very slow. Because there are a large number of formula calculations in the Excel we generate, the resulting time for generating a file becomes very long. May I ask what the reason is and is there any way to optimize it? For example, when assigning values to formulas, calculations are not performed.
The same amount of data, 23.8 is more than 5 times longer than 8. x.
There is another method that significantly reduces performance: WorkSheet. AutoFitRows (startRow, endRow)
Thank you.
@llw168
Could you share a sample project about settting formulas? We will check it soon.
In order to make AutoFitRows work same as Excel ,we have to check merged area, condtional formatting ,table and pivot table style, so it works slower, And you can share a template file, we will try to improve it.
I’m sorry I can’t share the complete code.
Our usage scenarios for Aspose include a large number of cells and formulas in Excel, with nearly 100000 formulas. Most of them are cross sheet formulas @simon.zhao
image.png (9.8 KB)
Thanks for the screenshot.
When setting up a large number of formulas that reference cells in different worksheets, the process can be relatively slow. To accurately evaluate the problem, we require a final example Excel file containing these recursive formulas since we will need to test it to find the root cause of the slowness issue. If you share an Excel file that contains formulas for confidential data, please use dummy data for the formulas in your spreadsheet and share the file with us. We will check your issue as soon as possible.
Additionally, Aspose.Cells provides a lightweight mode that includes the LightCells API, mainly used to handle cell data one by one without building the complete data model into memory using the Cell collection, etc. This mode works in an event-driven manner whereby a Cell object is processed and then discarded, and the Workbook object does not hold the collection. In this mode, the time cost is less for large data sets, and memory usage is saved. Refer to the documentation containing sample code for more information.
https://docs.aspose.com/cells/net/using-lightcells-api/
We tested the process you described and found a performance gap. We need to do further research on performance issue when setting formulas with sheet reference for large amount of cells. For this purpose we have created a ticket as following in our internal issue tracking system:
Issue ID(s): CELLSNET-54269
We will notify you here when there is new news on it.
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.10) that we plan to release in the next week or so. You will be notified when the new version is published.
The issues you have found earlier (filed as CELLSNET-54269) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi