OpenXML SDK: Make Excel recalculate formula

Solution 1:

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

Works for me!

Solution 2:

Since it partially solves my problem and there seems to be no better solution so far, moved that codeblock out from the question to an answer... This is how the new code looks like:

foreach (WorksheetPart worksheetPart in spreadSheet.WorkbookPart.WorksheetParts)
{
    foreach (Row row in
            worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements())
    {
        foreach (Cell cell in row.Elements())
        {
            if (cell.CellFormula != null && cell.CellValue != null)
                cell.CellValue.Remove();
        }
    }
}

Solution 3:

I use this

    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)
            .Where(cell => cell.CellValue != null)
            .ToList()
            .ForEach(cell => cell.CellValue.Remove())
            ;
    }

This flushes the cached values

greets

Solution 4:

You need to save the worksheet at the end, This worked for me.

foreach (WorksheetPart worksheetPart in spreadSheet.WorkbookPart.WorksheetParts) {
    foreach (Row row in
            worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements()) {
        foreach (Cell cell in row.Elements()) {
            if (cell.CellFormula != null && cell.CellValue != null)
                cell.CellValue.Remove();
        }
    }
    worksheetPart.Worksheet.Save();
}