OpenXML SDK: Make Excel recalculate formula

I update some cells of an Excel spreadsheet through the Microsoft Office OpenXML SDK 2.0. Changing the values makes all cells contain开发者_如何转开发ing formula that depend on the changed cells invalid. However, due to the cached values Excel does not recalculate the formular, even if the user clicks on "Calculate now".

What is the best way to invalidate all dependent cells of the whole workbook through the SDK? So far, I've found the following code snippet at http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm:

public static void ClearAllValuesInSheet
      (SpreadsheetDocument spreadSheet, string sheetName)
{
    WorksheetPart worksheetPart =
        GetWorksheetPartByName(spreadSheet, sheetName);

    foreach (Row row in
       worksheetPart.Worksheet.
          GetFirstChild().Elements())
    {
        foreach (Cell cell in row.Elements())
        {
            if (cell.CellFormula != null &&
                  cell.CellValue != null)
            {
                cell.CellValue.Remove();
            }
        }

    }

    worksheetPart.Worksheet.Save();
}

Besides the fact that this snippet does not compile for me, it has two limitations:

• It only invalidates a single sheet, although other sheets might contain dependent formula
• It does not take into account any dependencies.

I am looking for a way that is efficient (in particular, only invalidates cells that depend on a certain cell's value), and takes all sheets into account.

Update:

In the meantime I have managed to make the code compile & run, and to remove the cached values on all sheets of the workbook. (See answers.) Still I am interested in better/alternative solutions, in particular how to only delete cached values of the cells that actually depend on the updated cell.

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

Works for me!

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();
        }
    }
}

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

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();
}

Alternatively, you can change the formulas to use INDIRECT operator. Especially useful if you are using SAX + template files approach. Since this solution does not require changing your code, only template excel files. Please refer to my solution here - Set xlsx to recalculate formulae on open

Wanted to note another issue I ran into which appeared to be a problem with recalculating. I'd blindly followed some code to populate cells and it showed a shared string. After a long while, I discovered that I needed to use a CellValues.Number value for the DataType. Once I did that, the cells recalculate on opening.