Is there a way in SpreadsheetML to force Excel to recalculate formulas (and other dependencies) when document is opened?

Question

As far as I understand cells with formulas in Microsoft Office Excel can contain calculated values when serialized and saved in Office Open XML formats (specifically SpreadsheetML). This most likely applies to other types of dependencies and functions of values from other cells (like charts, pivot tables, etc.). I most likely do something wrong, but when processing this XML documents (SpreadsheetML) by external tools, that do not use any .Net components or similar APIs provided by MS, but just directly manipulating XML, I get into a problem that when I modify some content of one of worksheets Excel will still use last generated values in cells containing formulas. So when user opens generated spreadsheet he sees modified data but all the calculated fields are outdated. Now the only thing that I could find (these days) on internet was this:

http://openxmldeveloper.org/discussions/formats/f/14/p/1561/4164.aspx

This is really not a preferable solution especially if it applies to any kind of calculated cells and objects (charts, etc.) as it means partially reimplementing some SpreadsheetML processor when you do not know exactly the structure of all worksheets.

I would hope there would certainly be either an option in Excel or a configuration in one of the SpreadsheetML parts to force recalculations or to mark cells dirty, but I couldn't find one yet.

There is an assumption that scripting would help, but my lack of knowledge of that area didn't brought me to any successful results yet as I'm not sure how to include scripts into SpreadsheetML worksheet. Though I found quite some examples how to trigger recalculation and how to add open event listeners.

score 1 · Answer 1 · answered Mar 29 '12 at 18:50

The easiest way is to remove the calculated value from the cell (as also noted in the link you provided).

You do not have to know the exact structure of worksheets. Just remove all occurrences of <v>#VALUE!</v> in worksheets/sheet1.xml (so that other functions will not be affected).

score 0 · Answer 2 · answered Mar 16 '12 at 11:20

0

press F9 to recalculate all open workbooks Excel Recalculation

answered Mar 16 '12 at 11:20

gogogirl

1

I need it to be done automatically without user interaction (except that he opens). – Bohdan Tsymbala Mar 16 '12 at 13:22

score 0 · Answer 3 · answered Mar 16 '12 at 14:01

Perhaps your Calculation Mode for the workbook is getting set to manual. Force this mode to Automatic when you open the workbook by setting it to null in the code with the following:

    public  void SetAutomaticCalculationMode(WorkbookPart workbookPart1)
    {
        Workbook workbook1 = workbookPart1.Workbook;

        CalculationProperties calculationProperties1=workbook1.GetFirstChild<CalculationProperties>();
        calculationProperties1.CalculationMode = null;
    }

This will correspond to Automatic calculation mode as seen in the Options of Excel 2007 client: enter image description here

Is there a way in SpreadsheetML to force Excel to recalculate formulas (and other dependencies) when document is opened?

3 Answers3