Search code examples
openxmlxlsx

Set xlsx to recalculate formulae on open

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.

Solution

  • 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.