How to make unoconv auto calculate formulae before

Question

I created a .xlsx file using the python module Xlsxwriter.

Works well but any cells that contain formula will show up as empty or 0 when I run

unoconv -f pdf spreadsheet.xlsx

How do I force the unoconv autocalculate the values before converting to pdf?

Answer 1

As of this writing, apparently unoconv is purposely not doing any recalculation because it can cause errors. See Issue 97. The relevant part of unoconv's source code is commented out.

So one thing you could try is to go into your copy of unoconv and uncomment those lines (make them active again). You may get errors or you may be fine.

Another thing you could try, if your formulas are simple, is to calculate them using Python and write the results along with the formulas using XlsxWriter. Notice that the worksheet.write_formula() method has an optional value parameter. Quoting from the official docs:

XlsxWriter doesn’t calculate the result of a formula and instead 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 some 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. This is occasionally necessary when working with non-Excel applications that don’t calculate the result of the formula: [...]

Note that if the formulas are such that you can calculate them with Python, and you only care about the final PDF (no one is going to use the spreadsheet, and after you convert it to PDF you just throw it away anyway), then you could write the values directly, without the formulas at all.