sum fields in Excel report
Zoi Moravec
When generating a report in Excel format, fields with the CURRENCY type are inserted as text (.display_text) - "123,45". In this situation, it is no longer possible to work with these sums in Excel as sums (use it in formulas, etc...). The only thing I was able to do was insert amounts separated by "." (for example, "123.45") and after that, the user in Excel replaces "." with "," (123,45) in these columns. After this operation, the text amounts become real amounts. Is this the only way? How does anyone solve this "issue" (feature)?
Dean D. Babic
Never got around it, sorry. That is LO limit. It is not ".", it is ` (single quote), inserted as per above thread.
Remove the singe quote and it works, providing using values, not display_text.
LO headless conversion is fiddly. Unfortunately, with any new LO release, just more issues.
You would need to construct xml for float (this is Demo invoices.ods, 536524 is hardcoded in ods, $0.99 is not, xml saved before headless conversion):
<table:table-cell table:style-name="ce26" office:value-type="float" office:value="536524" calcext:value-type="float"><text:p>536524</text:p></table:table-cell>
this is different to:
<table:table-cell table:style-name="ce21" office:value-type="string" calcext:value-type="string"><text:p>$0.99</text:p></table:table-cell>
Jam is only doing <text:p> in report.py. Easier to just remove a single quote.
Zoi Moravec
Hi Dean,
Thank you for the clarification in the thread. In my case, the leading single quote (') is definitely not present in the template cells.
I encountered the core issue you mentioned: even after setting the cell format to "Number" (2 decimal places, thousands separator) in the LibreOffice Calc 25.2.6.2 GUI, the generated XML in content.xml stubbornly remains office:value-type="string" for cells containing placeholders like %(sum_field)s. Manually editing the ODS file at the XML level for every report is not a practical solution for our workflow.
My Implemented Solution:
I modified the print_band method in report.py. The logic now detects numeric fields (by name prefix, e.g., sum_, count_) and during report generation, it directly replaces the cell's opening XML tag. It changes office:value-type="string" to office:value-type="float", adds the office:value attribute with the numeric value, and updates calcext:value-type accordingly. All original cell styling (table:style-name) is preserved.
This approach works reliably in production, ensuring numbers are recognized as such in the final ODS/Excel file while keeping the template formatting intact.
Attached is a patch file for report.py in case you're interested