After updating a cell and saving excel, formulas based on this cell are not recalculated

Question

Hi,

Im using the XML SDK to update some cells in the excel file.

This is the code that im uses to update the cell for a given text (and its working fine).

WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sheetname); spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; if (worksheetPart != null) { Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex); cell.CellValue = new CellValue(text); cell.DataType = new EnumValue<CellValues>(CellValues.Number); // Save the worksheet. worksheetPart.Worksheet.Save(); } 

On the other hand, now I want to open the excel file again and get updated values ​​from other cells whose formulas are based on the previous cell, but even with the following lines im not able to do this:

 spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; 

Do you know why I am not getting updated values?

thanks

Sincerely.

Jose

Answer 1

Perform the following function for each cell containing the formula.

 public void ReCalcuateFormula(Cell cell) { if (cell.CellFormula != null) cell.CellFormula.CalculateCell = new BooleanValue(true); } 

Answer 2

See Vincent's answer here: OpenXML - refresh Excel sheet after cell refresh

The Open XML SDK does not update formula calculation results. Only Excel does this (or other spreadsheet software). Therefore, even if you set ForceFullCalculation and FullCalculationOnLoad to true, you only tell Excel to perform a complete calculation and do this when loading a spreadsheet file.

That is why you always get the "old" value. Because there is no "new" meaning that can be spoken of.

To test this, you can set CellValue (class Cell) to say "3.14159" with intact CellFormula. Excel will calculate and display the correct value for your formula, even if you specifically set the cell value to "3.14159". [Unless, of course, the formula evaluates to PI ...]

To solve this problem, you have a calculation mechanism to read into the formula and calculate the result for you. Or save the spreadsheet and run Excel (in Interop mode) to calculate all the formulas, but this is partly a defeat for the purpose of using the Open XML SDK in the first place.