Dear Sirs or Ladies.
I have several workbooks where there is one worksheet for input/output of values and one worksheet for doing complex calculations. I made a very simple template for you to verify: Test.zip (6.6 KB)
When I open the workbook and check Workbook.Settings.CalcMode
it is CalcModeType.Automatic
. After clearing the range A1:C20 with Worksheet.Cells.ClearContents(CellArea.CreateCellArea(“A1”, “C20”)) the range A1:C20 is empty but the values in the range A21:A40 are still the old values from before clearing the range A1:C20. See the saved workbook: Result.zip (8.1 KB)
When I force calculation either with Workbook.CalculateFormula(New CalculationOptions() With {.Recursive = True})
or with Workbook.Worksheets(0).CalculateFormula(New CalculationOptions() With {.Recursive = True}, True)
everything is calculated properly.
Why does the calculation doesn’t happen automatically. Is there anything to set that I’m missing to make all calculations automatically?
With kind regards
Michael
@casswarry0,
It looks like your MS Excel’s Calculation Options are set to “Manual”, that’s why you are still seeing older values (see the screenshot (attached) for your reference). Please set to “Automatic” and then open the output file, so you will see correct or updated values.
In case, you do not want to set Calculation Options to “Automatic”, then you must call CalculateFormula() method before saving the file. This way the calculated results will be saved to the file, so when you will open into MS Excel, you will see updated results.
sc_shot1.png (23.0 KB)
No, MS Excel Calculation mode is set to Automatic: Excel.png (138.0 KB)
The point is we have an .NET application and want to migrate from Microsoft.Office.Interop.Excel to Aspose.Cells. I migrated the .NET code and when I open our .xlsx files in Aspose.Cells and check Workbook.Settings.CalcMode
it is CalcModeType.Automatic
. But the calculation is not done automatically. You can verify this with the attached .xlsx files. When I read the values of the range A21:A40 with Aspose.Cells Worksheet.Cells("A21").Value
after Worksheet.Cells.ClearContents(CellArea.CreateCellArea("A1", "C20"))
the old values are still read. There is no Excel involved at all.
I saved the file only to show the problem.
@casswarry0,
We have noted your feedback and will share our comments after detailed analysis.
@casswarry0,
Please note when you update or change values in the source cells, you have to call CalculateFormula to get updated results. In short, if you need to read those cells, you must call CalculateFormula method first. Moreover, CalcMode attribute is for MS Excel settings and not for Aspose.Cells.