Formulas in Excel spreadsheet do not calculate until "Enable Editing" is clicked

user_12038063 0
Viewed 45

I am currently generating a excel file with XLSXWRITER and uploaded the generated file to blob storage. when I download the excel file with SAS URL. When I open downloaded excel file the cells that contain a formula display "0" instead of the formula output number. The top of the Excel 2010 window states "Protected View This file originated from an Internet location and might be unsafe. Click for more details. Enable Editing. Once enable editing is clicked the cells that displayed "0" now display the correct formula output numbers.

So, My questions:

  1. Is there a way to display formula output numbers without having to click Enable Editing?
xlsxwriter excel python
1 Answers

This is explained, in part, in the XlsxWriter FAQ on Why do my formulas show a zero result in some, non-Excel applications?

Due to a wide range of possible formulas and the interdependencies between them XlsxWriter doesn’t, and realistically cannot, calculate the result of a formula when it is written to an XLSX file. Instead, it stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas, such as Excel Viewer, or several mobile applications, will only display the 0 results.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter in write_formula():

worksheet.write_formula('A1', '=2+2', None, 4)

See also Formula Results.

The effect you are seeing is similar. The calculation of the cell values doesn't happen until you click on Enable Editing.

However the workaround of adding the formula value isn't always practical.

user_102380
Related