formatting pattern of xls.formula

[ostry]

Hello,

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)"/>

</reportElement>

<textElement textAlignment="Right" markup="none"/>

<textFieldExpression><![CDATA[]]></textFieldExpression>

</textField>

 

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 4.0.0.3). Having JasperServer 5.1.0.

 

What am I doing wrong? Thank you in advance!

 

Jan

 

P.S. 24.3.2014 10:30: I have this property set: <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

[hozawa]

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"

[ostry]

I already had there (using iReport)... thank you for hint anyway, I googled this earlier

[ostry]

PROBLEM SOLVED:

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

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)