CODEWITHSUNDEEP
openxmlxlsx
OrangeDog
OrangeDog

Reputation: 38749

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.

Upvotes: 14

Views: 8735

Answers (4)

Andrii Kolesnikov
Andrii Kolesnikov

Reputation: 1

Faced the same problem when exporting xlsx'es via openxml (with fastest SAX + template file approach w/o zip stream rewinds).

Despite Calculation option=Automatic, no recalculation on opening the file. Furthermore no recalculation via Calculate Now and Calculate Sheet buttons. Only upon selecting the cell and pressing enter ;(

Original formula: SUM(A3:A999)

Solution:

  1. Create an internal hidden sheet
  2. Place end row number (999 in my case) into any cell in hidden sheet (P1 in my case)
  3. Reference row number in the cell via INDIRECT operator

Final formula: SUM(A3:INDIRECT("A"&Internal!P1))

Please refer to the attached gifs

before.gif

after.gif

P.S.

Theoretically, in P1 you can implement dynamic row number calculation via smth like =LOOKUP(2;1/(Sheet1!A:A<>"");ROW(Sheet1!A:A)), but my customers were satisfied with hardcoded row number solution

Upvotes: 0

jmcnamara
jmcnamara

Reputation: 41534

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.

Upvotes: 14

Skrol29
Skrol29

Reputation: 5552

In your XML contents, simply omit the <v> entity in each cell that have a formula, this will force Ms Excel to actualize the formula whatever the Excel options are.

Instead of: 

  <c r="B2" s="1">
    <f>SUM(A1:C1)</f>
    <v>6</v>
  </c>

Have: 

  <c r="B2" s="1">
    <f>SUM(A1:C1)</f>
  </c>

If you have to actualize formula in an already given XML contents, then you can code easily a small parser that search for each <c> entities. If the <c> entity has a <f> entity, then delete its <v> entity.

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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.

Upvotes: 3

Related Questions