Friday, January 07, 2011

Export JasperRerpots to Excel, Number Formats and Patterns Mapping for JasperServer and JaperReports

After a complaint I got from one of the client on some numerical formating are not applied when the reports are exported to excel, I had to look for a solution for this.
For Example,

When you apply a pattern such as "#,##0.0 %" in iReports and deploy the report on JasperServer and then export the report to excel from JS you would expect the values to be something like, "12.0%". But instead, what you find on the excel is "0.12". The reason for this is because, Java patterns in iReports should be explicitly mapped to Excel patterns or Masks in order to have them exactly the way they are on the JRXML.

How to do this in two simple Steps,

Step1,
You need to add the mappings in the applicatonContext.xml file which can be found in the following folder,

"tomcat_folder"\webapps\jasperserver\WEB-INF\

Search for the following,

<util:map id="formatPatternsMap">
<!-- entry key="¤ #,##0.00" value="$ #,##0.00"/-->
</util:map>

Add a mapping as below,

<util:map id="formatPatternsMap">
<!-- entry key="¤ #,##0.00" value="$ #,##0.00"/-->
<entry key="#,##0.0 %" value="0.00%"/>
</util:map>

Key should be the Java pattern, and the value should be the pertaining excel pattern/mask

Step2

Do Not forget to disable "detectCellType" property. To do this search for the following bean,

"xlsExportParameters"

and change the following property to false,

<property name="detectCellType" value="false"/>

Restart Tomcat for changes to take effect. After that you should be able to see your desired formatting on excel too.

Cheers!

4 comments:

Guru said...

It is really great. Thanks for sharing. Regards, Selvaguru

Jatin Bidaye said...

Thnaks. However, could it be done @ report level by setting a property?

Shankar Panda said...

Hi sir, I have report dat contains image.But when i am exporting that image to xls format,,image is not displaying in that file.

Raju said...

Thanks for the sharing. It worked really great.