formatting pattern of xls.formula


having this JRXML:

<textField pattern="€ #,##0.00" isBlankWhenNull="true">
<reportElement uuid="7daa13fd-4faf-4926-940c-3547fcb0e89e" x="346" y="0" width="456" height="20">
<property name="net.sf.jasperreports.export.xls.pattern" value="€ #,##0.00"/>
<property name="net.sf.jasperreports.export.xls.formula" value="OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)*OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2)"/>
<textElement textAlignment="Right" markup="none"/>
it seems that xls.pattern has no effect on how number is displayed in XLS in my setup - I want currency, but I googled a lot and  tried also many other patterns and configuration changes, but no change of how number is displayed (still as Standard Number with variable count of decimal places and not Currency in LibreOffice Having JasperServer 5.1.0.
What am I doing wrong? Thank you in advance!
P.S. 24.3.2014 10:30: I have this property set: <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
ostry's picture
Joined: Jul 6 2012 - 7:20am
Last seen: 6 years 3 weeks ago

2 Answers:

Try adding the following property to your report:

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

If you're using Studio, you can add properties to a report by clicking your report from the Outline view. Going to Properties view and selecting "Advanced" tab and select "Misc" -> "Properties"

hozawa's picture
Joined: Apr 24 2010 - 4:31pm
Last seen: 3 years 7 months ago

I already had <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/> there (using iReport)... thank you for hint anyway, I googled this earlier

ostry - 8 years 10 months ago


by exporting to XLSX instead of XLS and everything is OK (tested in Excel 2007 and LibreOffice Calc

with legacy XLS, there were not only problems with currency formatting, but also with some weird circular references (this second and more serious problem was present only in Excel, not in LibreOffice)

ostry's picture
Joined: Jul 6 2012 - 7:20am
Last seen: 6 years 3 weeks ago