Does Aspose.Cells support auto calculation of Formulas (just like Excel does) - e.g. I have:
A1 : 10
A2 : =A1 *2
When I query A2 Im getting a blank value returned until I manually run workbook.calculateFormulas().
Does this mean that for complex worksheets with lots of formulas and summations, every single time I update any cell on the worksheeet (i.e. any call to setValue() ) I have to invoke calculateFormulas() ? Is there a way for Aspose.Cells to know what cells are referenced via the formula graphs and then update the formulas affected automatically?
(
To clarify, I set my Workbook up with:
WorkbookSettings settings = wb.getSettings();
settings.setCalcMode( CalcModeType.AUTOMATIC );
)
I expected this to auto calc formulas as entered and as values in the sheet were updated.
Thanks for your posting and using Aspose.Cells for Java.
Aspose.Cells does not support automatic calculation. You need to call calculateFormula() to trigger formula calculation using Aspose.Cells. It might be a complex feature or may slow down Aspose.Cells processing.
However, I have logged your feature request in our database. We will look into it and update you. Once, there is some fix or news for you, we will let you know asap.
This actually sets the calculation mode of MS-Excel for the output xls/xlsx file to automatic or manual. It does not make Aspose.Cells to automatically calculate the formula.
We cannot do such kind of operation automatically because it may cause performance issue for most users. For your requirement, we think you should enable the formula calculation chain by setting Workbook.Settings.CreateCalcChain as true. Then you may call Workbook.CalculateFormula() anywhere and anytime before getting one cell’s value which may need to be re-calculated. With the calculation chain, we only calculate those formulas that need to be re-calculated according to their precedents that have been changed.