OpenXML SDK: Make Excel recalculate formula

Question

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:

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.

After deleting the cached values, how do you read the value of the cell?. Is it necessary to close the object?. In my case, I get the value and returns nothing — mggSoft, Oct 21 '13 at 16:46

score 53 · Accepted Answer · edited Feb 24 '11 at 19:43

53

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

Works for me!

edited Feb 24 '11 at 19:43

Fls'Zen

4,594
1
29
37

answered Nov 24 '10 at 03:44

Nafi

554
5
3

Cool, looks much simpler! To make it more readable, can you edit your answer and remove the spaces in front of your source code? (Down to 4 spaces which are required in order to format the text as source code) – chiccodoro Nov 24 '10 at 07:56
It doesnt works for me in this form and previous message. What I can try? – mggSoft Oct 21 '13 at 16:21
1

A little piece of information, you need to add the above two lines to your code after you've updated the cell values. – Vipul bhojwani May 16 '17 at 07:35
A little piece of information. `document.save()` after the above doesn't calculate the value for you. You have to open in excel application or excel interop and then save to have the fields updated for you. – Jins Peter Jan 24 '19 at 12:00
This saved me 2 months of headache.. I wrote my own helper class over OpenSDK v2.5 which does extremely well and even beats EPPlus in speed in certain instances but i had this issue. Thanks a lot. – OneGhana Dec 19 '20 at 18:36

score 3 · Answer 2 · answered Apr 21 '10 at 15:24

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

Elmer · Answer 3 · 2010-07-13T17:25:42.553

2

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

edited Jul 13 '10 at 17:25

answered Jul 09 '10 at 10:37

Elmer

9,147
2
48
38

I have tried this, and after deleting the cached values, I get the value and returns nothing – mggSoft Oct 21 '13 at 16:49
same here this approach does not work for me. I posted similar question on SO. – dotnet-practitioner Jan 24 '14 at 01:59

score 1 · Answer 4 · edited Mar 31 '15 at 12:54

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

score 0 · Answer 5 · answered Aug 10 '20 at 10:54

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

score 0 · Answer 6 · answered Oct 28 '21 at 21:13

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.

OpenXML SDK: Make Excel recalculate formula

6 Answers6

Linked