I have an XSLX report that uses a formula in the template to total up a column. The resultant report file contains the formula correctly and if opened in Excel for example, the total is displayed correctly. However if opened in LibreOffice Calc for example the cell displays as zero but the cell contains the correct formula - hitting Ctrl+Shift+F9 (Recalculate Hard) produces the correct cell results. This is an inconvenience and could be lived with but I also use the Scheduled reports Add-on to email the report out and in many email clients only an XLSX viewer opens the report and does not have the ability to update the formula cells, so the report doesn’t look good
I could probably update the report to add another band and use SUM() in a version of my previous SQL to produce the totals that way but that seems an overhead and also there maybe other cases where only a formula will do(?).
I believe the “issue” is that the Reports Add-on uses docx4j/xlsx4j which as far as I can tell does not support the evaluation of formulas unlike Apache’s POI.
So, is there a way that the Reports Add-on could either call POI before saving a report containing formula? or hack into docx4j/xlsx4j to do it? or …???
As I said, I am using add-ons from the market place so I have no code to change. I’ve had to create my own “scheduled-report-service” to add in POI code like you suggested and I can now get clean reports in Excel, LibreOffice and IOS.
I hope one day the reports add-on can support formulas OOTB.