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.

Abri

You cannot accomplish the kind of dynamic behaviour you are after without a macro. Excel formulae/cells do always the same (are static), if you want to account for variable behaviours, you would have to rely on a macro (or on other type of application). – Antonetteantoni

@varocarbas can you provide a full answer detailing how to achieve this? – Abri

Sure. But if you want the macro in the same file, you would have to convert it into xlsm; and also the prompt ("do you want to enable the macros?") would appear if the Excel security setting in the target computer tell so. Do you want it anyway? – Antonetteantoni

What do you mean be <v> and <f>? What process do you use to "generate" xlsx files? – Kookaburra

@LS_dev Open an xlsx with 7zip and see for yourself. I'm programmatically creating the file format directly. – Abri

Any answer that involves using Excel to edit the file, is not an answer to this question. – Abri

Ok, but you should replace excel tag by openxml! – Kookaburra

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.

Fyn

The reference to Python nearly put me off appreciating the value of this answer. I solved this by using a PHP preg_replace to set the existing Tag Calcpr to fullCalcOnLoad="1" – Desjardins

Tested this with Excel Online and LibreOffice but only worked in the former. – Sandhurst

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.

Calva

Following the linked instructions, files saved in automatic vs. manual mode are identical. Just in case, I changed the value of computed cells in the auto-mode file, and they did not recalculate when opened. – Abri

"I changed the value of computed cells in the auto-mode file"? You have to generate workbooks with calculation mode set to Automatic. Is a workbook property, not cell! – Kookaburra

I changed the value of the cell to be not the result of the formula. – Abri

It would appear that this is a property of xls files, not xlsx. – Abri

xls files are not Office Open XML. Whouldn't it be msdn.microsoft.com/en-us/library/… ? – Kookaburra

I know that. Your answer does not work with xlsx. Perhaps it works with xls (as described in the link), but I'm not in a position to check. – Abri

It worked for me! I added <calcPr calcMode="auto"/> to xl/workbook.xml and cells were correctly calculated when I opened workbook. – Kookaburra

Yes, that works. Why didn't you just put that as your answer? – Abri

Because I just wanted to show you one way and I didn't know Office Open XML in deep. I had to study it, and that should be your job! I will add it to answer. – Kookaburra

You should also mention the caveats if someone has Excel already open and set to manual, rather than relying on the link. – Abri

LS_dev: I agree with OrangeDog, your first answer did not respect not looking for answers that involve using Office programs, while your last one does. – Wicked

Yes, it did! I was already saying in first answer "generate your files with calculation mode set to Automatic". And for this you don't need Office. – Kookaburra

Answer 3

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.

Wicked

This looked to me a good idea. But I tested it and if calculation mode isn't set to automatic, B2 is shown empty when opening workbook. – Kookaburra

Also, Google docs used to reject Excel files that didn't have a <v> value set. So, it general it is best to set some value, even if it is 0. – Fyn

Answer 4

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

Underhanded

This "worked" only because you made the formula Volatile (Indirect forces cells to be considered volatile). As a result, your formula is recomputed anytime anythying in the workbook changes. Note that a simpler solution would have been to change your formula to SUM(A3:A999)+0*RAND() This also makes the formula volatile, but doesn't require a hidden sheet. – Venlo