14

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.

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
  • 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). – varocarbas Aug 21 '13 at 11:33
  • @varocarbas can you provide a full answer detailing how to achieve this? – OrangeDog Aug 21 '13 at 11:42
  • 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? – varocarbas Aug 21 '13 at 11:46
  • 1
    What do you mean be `` and ``? What process do you use to "generate" xlsx files? – LS_ᴅᴇᴠ Aug 21 '13 at 12:26
  • @LS_dev Open an xlsx with 7zip and see for yourself. I'm programmatically creating the file format directly. – OrangeDog Aug 21 '13 at 12:43
  • Any answer that involves using Excel to edit the file, is not an answer to this question. – OrangeDog Aug 21 '13 at 12:48
  • Ok, but you should replace `excel` tag by `openxml`! – LS_ᴅᴇᴠ Aug 21 '13 at 13:07

4 Answers4

14

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.

Timmmm
  • 88,195
  • 71
  • 364
  • 509
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 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" – zzapper Oct 09 '15 at 13:58
  • 1
    Tested this with Excel Online and LibreOffice but only worked in the former. – Přemysl J. Mar 19 '20 at 23:12
4

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.

LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
  • 2
    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. – OrangeDog Aug 21 '13 at 13:13
  • "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! – LS_ᴅᴇᴠ Aug 21 '13 at 13:16
  • I changed the value of the cell to be not the result of the formula. – OrangeDog Aug 21 '13 at 13:18
  • It would appear that this is a property of xls files, not xlsx. – OrangeDog Aug 21 '13 at 13:18
  • xls files are not Office Open XML. Whouldn't it be http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.calculationproperties.aspx ? – LS_ᴅᴇᴠ Aug 21 '13 at 13:21
  • 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. – OrangeDog Aug 21 '13 at 13:23
  • It worked for me! I added `` to `xl/workbook.xml` and cells were correctly calculated when I opened workbook. – LS_ᴅᴇᴠ Aug 21 '13 at 13:38
  • Yes, that works. Why didn't you just put that as your answer? – OrangeDog Aug 21 '13 at 13:43
  • 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. – LS_ᴅᴇᴠ Aug 21 '13 at 13:45
  • You should also mention the caveats if someone has Excel already open and set to manual, rather than relying on the link. – OrangeDog Aug 21 '13 at 13:54
  • 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. – Skrol29 Aug 21 '13 at 16:33
  • 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. – LS_ᴅᴇᴠ Aug 22 '13 at 07:53
0

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.

Skrol29
  • 5,402
  • 1
  • 20
  • 25
  • 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. – LS_ᴅᴇᴠ Aug 22 '13 at 08:40
  • Also, Google docs used to reject Excel files that didn't have a `` value set. So, it general it is best to set some value, even if it is 0. – jmcnamara Aug 26 '13 at 14:46
0

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

Andrii Kolesnikov
  • 1
  • 1
  • 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. – Alain Apr 28 '22 at 15:40