OpenXML - refresh Excel sheet after cell update

3
votes

I have a class that reads & writes Excel (xlsx) cells using OpenXML SDK. The class is based of the most voted answer from here: Open XML SDK 2.0 - how to update a cell in a spreadsheet?

I need to update a cell and then get a value of another cell, which contains a calculation formula. The update works fine, but when I read the formula cell after the update I get the old value, that existed in the doc before editing. However, when I open my xlsx manually after running the program I can see the correct value.

So it's seems like the old value for cell is cached somewhere... Which is weird, because I open/close my doc each time before I read/write cells.

EDIT: Vincent's answer made me update my sample code. I added a Refresh method that opens, saves and closes the document in the Excel application ran in background. This recalculates my formulas. For more details and C# code sample see: http://fczaja.blogspot.com/2013/05/how-to-read-and-write-excel-cells-with.html

1
excelexcel-formulaopenxmlopenxml-sdk
stackoverflow.com/questions/2668643/… – Chris
yes, I've seen that. If you take a look at my UpdateCell & ReadCell methods you will see that I already tried applying solution from the top-rated answer - no luck – filip
You should also read other answers. I am trying to find where I have read that the only way to make sure Excel recalculate formula is to delete the cached value of the formula cell – Chris
In another duplicate ;-) stackoverflow.com/questions/7012545/… – Chris

1 Answers

8
votes

Open XML SDK doesn't refresh the calculation results of formulas. Only Excel does that (or other spreadsheet software). So even if you set ForceFullCalculation and FullCalculationOnLoad to true, you only tell Excel to force a full calculation and do it when loading the spreadsheet file.

This is why you always get the "old" value. Because there's no "new" value to speak of.

To test this, you can set the CellValue (of the Cell class) to say "3.14159" with the CellFormula intact. Excel will calculate and display the correct value of your formula, even though you specifically set the cell value as "3.14159". [Unless of course, your formula evaluates to PI...]

To solve this, you either have a calculation engine to read in the formula and calculate the result for you. Or save the spreadsheet and run Excel (in Interop mode) to calculate all the formulas, but that kind of defeat the purpose of using Open XML SDK in the first place.