XML SDK recalculate values

Question

I have an excel file and I have a piece of code that updates some cells with a formula.

Before saving the file I set these two boolean properties:

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
// Save the worksheet.
worksheetPart.Worksheet.Save();

After saving the file, I open it again using the Open XML SDK, but I'm still getting the old values. When I open the excel file using Excel and I save it, then I can see the new values.

Do you know how I can make the values recalculate? If I save the file, I was expecting that if I open it, the values should be recalculated.

Thanks.Jose.

amurra amurra · Accepted Answer · 2012-11-01T10:57:30

If you have cached values then those values will be used and the values will not be recalculated. Try flushing the cached values like in this snippet:

public static void FlushCachedValues(SpreadsheetDocument doc)
{
   doc.WorkbookPart.WorksheetParts
      .SelectMany(part => part.Worksheet.Elements<SheetData>())
      .SelectMany(data => data.Elements<Row>())
      .SelectMany(row => row.Elements<Cell>())
      .Where(cell => cell.CellFormula != null && cell.CellValue != null)
      .ToList()
      .ForEach(cell => cell.CellValue.Remove());
 }

Just make sure you call this method before you save the document.

XML SDK recalculate values

1 Answers