CODEWITHSUNDEEP
.netexcelopenxmlopenxml-sdk
chiccodoro
chiccodoro

Reputation: 14716

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 containing 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:

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.

Upvotes: 14

Views: 24390

Answers (6)

Thomas
Thomas

Reputation: 3358

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.

Upvotes: 0

Andrii Kolesnikov
Andrii Kolesnikov

Reputation: 1

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

Upvotes: 0

robin
robin

Reputation: 21

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

Upvotes: 2

Nafi
Nafi

Reputation: 572

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

Works for me!

Upvotes: 54

Elmer
Elmer

Reputation: 9437

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

Upvotes: 2

chiccodoro
chiccodoro

Reputation: 14716

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

Upvotes: 3

Related Questions