Set xlsx to recalculate formulae on open

Question

I am generating xlsx files and would like to not have to compute the values of all formulae during this process. That is, I would like to set <v> to 0 (or omit it) for cells with an <f>, and have Excel fill in the values when it is opened.

One suggestion was to have a macro run Calculate on startup, but have been unable to find a complete guide on how to do this with signed macros to avoid prompting the user. A flag you can set somewhere within the xlsx would be far better.

Edit: I'm not looking for answers that involve using Office programs to make changes. I am looking for file format details.

Answer 1

The Python module XlsxWriter sets the formula <v> value to 0 (unless the actual value is known) and the <calcPr> fullCalcOnLoad attribute to true in the xl/workbook.xml file:

<calcPr fullCalcOnLoad="1"/>

This works for all Excel and OpenOffice, LibreOffice, Google Docs and Gnumeric versions that I have tested.

The place it won't work is for non-spreadsheet applications that cannot re-calculate the formula value such as file viewers.

Answer 2

If calculation mode is set to automatic, Excel always (re)calculates workbooks on open.

So, just generate your files with calculation mode set to "Automatic".

In xl/workbook.xml, add following node to workbook node:

<calcPr calcMode="auto"/>

Also check Description of how Excel determines the current mode of calculation.

You can use macros as suggested, however you will create a less secure and less compatible workbook without avoiding user interaction to force calculation.

If you opt by using VBA, you may Application.Calculate in Workbook_Open event.