Excel currency formatting

[domurtag]

Hi,

 

When I export my report to Excel, the following warning appears in each cell containing a monetary amount:

 

"The number in this cell is formatted as text or preceded by an apostrophe"

 

In an effort to get Excel to recognise the contents of these cells as currency amounts, I've added the pattern "$ #,##0.00" to the corresponding <reportElement> in the .jrxml file. I've also added the export parameter:

 

net.sf.jasperreports.engine.export.JRXlsExporterParameter.IS_DETECT_CELL_TYPE=true

 

Although the values look like monetary amounts when displayed in Excel, they are obviously stored as strings, which causes those annoying warnings to appear. Does anyone knows how to persuade Excel that these cells contain monetary amounts?

 

Thanks in advance,

domurtag

Post edited by: domurtag, at: 2008/07/04 12:44

[domurtag]

I found this post which describes:

• How to force Excel to store cell contents as numbers rather than strings

How to format these cells

[/ul]

Unfortunately, this approach is still not working for me. Here's an example of a report detail field that I would like to be stored as a currency-formatted number in Excel:

Code:

<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
  <reportElement style="currency" x="1034" y="0" width="71" height="17" forecolor="#000000" key="textField"/>
    <box/>					
    <textElement textAlignment="Right">
      <font size="12"/>
    </textElement>
    <textFieldExpression class="java.math.BigDecimal">$F{sale}</textFieldExpression>
</textField>

The 'currency' style referenced above is defined thus:

Code:

[code]<style name="currency" pattern="$ #,##0.00" isDefault="false"/>

 

I've also added the following exporter parameters:

Code:

[code]JExcelApiExporter exporter = new JExcelApiExporter(); exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.True); Map numberFormats = new HashMap(); numberFormats.put("$ #,##0.00", "$#,##0.00;-$#,##0.00"«»); exporter.setParameter(JRXlsExporterParameter.FORMAT_PATTERNS_MAP, numberFormats);

However, Excel is still storing these fields as strings, what am I doing wrong?

 

Thanks in advance,

domurtag

Post edited by: domurtag, at: 2008/07/04 18:53

[FlexTech]

Hi,

 

Do you still have the problem and did you find a solution for it ? Because I am in the same situation. Can you help me?

 

Many thanks in advance,

[developerdude]

Code:

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

Post Edited by Lauren Bish at 04/06/09 23:24

[walter_gillett]

Works!!! Thanks much

[cris_rufino]

Sorry,

I still have the same problem.

Was the solution set the "net.sf.jasperreports.export.xls.detect.cell.type" property in jrxml file?

I see this problem only in jasper 3.0 (poi-3.0). In jasper 1.3 (poi-2.0) works well.

In really, I would like to solve this problem in my java project, not in jrxml file.

Thanks for any response?

[]'s

Cris.

Post Edited by cris_rufino at 01/08/2010 19:07

[cris_rufino]

Solved problem ;-)

Setting IS_AUTO_DETECT_CELL_TYPE=true and IS_DETECT_CELL_TYPE=false make jasper 3.0.0 behavior equal jasper before 2.0.3, when the default value of IS_AUTO_DETECT_CELL_TYPE have changed, acording to chagelog file:

JasperReports 2.0.3 (2007-12-12)

- the default value of the deprecated JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE exporter parameter is now "false"; the newer IS_DETECT_CELL_TYPE exporter parameter should be used to control the cell types in the generated XLS documents.

[]'s Cris

Post Edited by cris_rufino at 01/11/2010 17:04

[kkalyan]

Tried it.. but still not working with 4.51

[hardik0537]

Hello,

I was facing similar issue and when i tried setting below property it works.

I hope this works for you too.

"net.sf.jasperreports.export.xls.detect.cell.type"  "false"