Hi, I’m converting xlsx files to pdf with the command below.
libreoffice --headless --convert-to pdf:“calc_pdf_Export:ReduceImageResolution=True” …/my_tmp/19809172761433941497.xlsx --outdir …/my_tmp/
However the cells with formulas are producing zeroes. I would like them to have the result of their formula instead.
Is there any way i can achieve this?
here is the link to the file i’m trying to convert
A nitpick: as described at help page, the correct syntax for --outdir is immediately after related --convert-to parameter (which it is in fact a part of). Our arguments parser allows some freedom, like allowing out-of-order --outdir appearance, but this is strictly speaking wrong - the file name should appear after the --outdir and its parameter.
In fact, all the command line here is just a red herring - because simply opening the file downloaded from google docs in LibreOffice (without any conversion command lines) also shows zeroes - until doing a hard recalc.
Maybe @erAck knows an answer, but it might well be something to do with google producing some strange result (it shows a “maximum number of columns” warning for me on 7.2.0.2 btw).
Thank you for correcting my overlook on the shell syntax. I will correct it.
You are right about simply opening it doesn’t show result. I am using MS Office in my local and it also shows zeroes. I didn’t notice since i was only using libreoffice in remote server for conversion purposes and nothing else.
I got the maximum number of columns warning as well, but it doesn’t really make sense since all the sheet (you can unhide the others) are within libreoffice limits (<AMJ).
The hard recalculate works for me on the gui, is there any way i can do that with conversion?
Yes, you may set the Always recalculate option under Options|Calc|Formula|Recalculation on file load|Excel 2007 and newer. You may have that setting set in the profile used on the remote server (e.g., making related changes manually in profile’s registrymodifications.xcu, after you see how it’s set when you configure the option locally).
Tested, and this works for batch conversion for me.
Interesting - I tried with MS Office 2016, and it seems to calculate the
formulas here; maybe it has something to do with versions…
Google writes in the .xlsx structure (app.xml, workbook.xml) that the file was created in the Excel 2007 version.
Excel recalculates all formulas when the file is opened if the version of Excel is different from the version in which the workbook was created.
It’s just another broken Google Sheets export. Here with .xlsx they don’t write text results of formulas but instead falsely declare them to be numeric 0 values. The best to get around that in this case is probably to download as .ods instead, which seems to be correct on first sight. But Google is also known to fail to export some .ods details correctly. You’ll always have to check what actually works. Recalculation may be your best bet.
The maximum number of columns warning may be due to that several sheets define a
<selection activeCell="$A1" sqref="$A1:$XFD1"/>
of which sqref AFAIK is somehow related to conditional formatting, or there’s also one sheet with
<dimension ref="A1:WWE112"/>
that’s clearly out of 1024 columns bounds. Note there are 23 hidden sheets, and sheets may have excess formatting applied to far right empty columns as well that may result in this.
Sorry, i meant i was using MS Office (2016) to create the file, and opening it in LibreOffice shows zeroes as well as the warnings. It works totally fine in MS Office.
I uploaded the file to googledrive since i am new to the forum and couldn’t upload directly. Google drive seem to reformat the file into google sheet.
Could you upload the original .xlsx file?
The upload icon is in the middle of toolbar for creating / editing a message and looks like a wide up arrow.
Hi, the solution to my main concern has been addressed by @mikekaganski
However feel free if you’d like to address other issue such as the warning message regarding max column count.
Hopefully onedrive treats the xlsx file as original
I couldn’t upload file to this site because this site configured such that new users cannot upload files, so it’s a technical thing, not that i cannot locate where or how to do so 
Thanks a lot!
The last * .xlsx file has an attribute forceFullCalc = “1” (workbook.xml), which requires recalculating formulas.
The results of calculating the formulas of interest to us are indicated in the file as empty values.
In my Calc (7.1.5.2) all formulas were automatically recalculated when the file was opened.
As for the “max column count”, then in the file structure in the description of the hidden sheet “PQ-raw” (Sheet1.xml) there is a tag
<selection sqref = "A1: XFD1" />
XFD corresponds to column number 16384, which is greater than the current maximum value in Calc.
Fwiw, I downloaded a copy of that onedrive file and (with recalculation for Excel files set to never) ran it through --convert-to pdf and I don’t see 0 results except on sheets WQ-summary and WQ-result, because they reference empty or deleted data on sheet WQ-raw.