XLSX Report Formulas are not evaluated

Support
#1

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 :frowning_face:

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 …???

#2

Hi there,
It’s been a while since I posted this. Just wondering if anyone has any ideas on how to fix this.
Thanks.
Andrew

#3

Hi,

There is an issue about this problem, but as I see, no fast solution was found.

I think you can do the following:

  • Run the report using the com.haulmont.reports.app.service.ReportService
  • Get the file content from the result: com.haulmont.yarg.reporting.ReportOutputDocument#getContent
  • Recalculate all formulas in the result file using POI, like it is described in POI documentation.
  • Send the new file with recalculated formulas to your clients
#4

I’ve added the piece of code that demonstrates the workaround to Jmix issue about this problem. Maybe it may be useful for you.

#5

Thank you.

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.

Thanks again.

Andrew.