Suggestions on how to change excel format

0
Hi, I'm successfully exporting reports in XLS format using either the jexcelapi or poi exporters. I have numbers like the following 10876.87435345 which are actually currency. Ideally, I want the final numbers in the spreadsheet to appear as €10,876.87 But for the excel cell to still contain the raw number. So I'm looking at exporting the value as a number but getting the excel file to store the number using a custom format. I understand I can using a style pattern to export the data as €10,876.87 but I lose the raw number. Does anyone have any suggestions on how to post-process the exported XLS to apply this sort of transformation? I've seen some reference to export filters which may or may not do what I want to do - but I've come across no documentation of them. Similarly, could working with scriptlets achieve what I want here? At worst, I'll have to load the exported XLS back into PoI and massage it myself - just wondering how others are approaching this and whether I can do it through a hook in jasper or whether I need to do it as an entirely separate processing step outside of jasper afterwards. Comments welcome. -stephen
smul's picture
11
Joined: Oct 3 2007 - 6:42pm
Last seen: 6 years 11 months ago

7 Answers:

0
Take a look at [url=http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/exp..., which can be used to map a XLS format to a report (Java) format. Regards, Lucian
lucianc's picture
5281
Joined: Jul 17 2006 - 1:10am
Last seen: 3 weeks 2 days ago
0
lucianc wrote:
Take a look at [url=http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/exp..., which can be used to map a XLS format to a report (Java) format. Regards, Lucian
Hi Lucian, Thanks for your response. I came across some references to this in other posts but no documentation other than the Javadoc you referenced (is there other documentation for this?). I'm still not entirely clear on how FORMAT_PATTERNS_MAP works. If I add some entries to this - instead of jasper reformatting the data, will it pass the formatting field through to Excel? Thus preserving the underlying number (e.g. 1098776.6453) but displaying it using the formatting pattern I pass in the map? Thanks for any clarification, -stephen
smul's picture
11
Joined: Oct 3 2007 - 6:42pm
Last seen: 6 years 11 months ago
0
If your text field expression has class="java.lang.Number" and you set the IS_DETECT_CELL_TYPE flag when exporting to XLS (or if you use the deprecated IS_AUTO_DETECT_CELL_TYPE flag), the text field will produce a numerical cell and the number will be preserved. The format from FORMAT_PATTERNS_MAP will be used by excel to display the number in the cell. Regards, Lucian
lucianc's picture
5281
Joined: Jul 17 2006 - 1:10am
Last seen: 3 weeks 2 days ago
0
Hi Lucian, Thanks for the clarification - I nearly understand now :) So the Java formatting expression is the key in the Map and the Excel formatting expression is the value, right? In my case, as mentioned, I have a number like 10.1234567891 (10 decimal places) in my query. My text field expression has class = "java.math.BigDecimal" and I'm setting the IS_DETECT_CELL_TYPE. In my case, I want to pass the value through to excel without any changes to it - am I right in my understanding that I will still need to provide a Java number formatting pattern (in the jrxml and in the Map) even though I don't want the number to be modified? If so, I'm using a format like "#,##0.0000000000" to ensure nothing is lost. Is this the right approach or am I making this more complicated than it needs to be? The result is certainly what I was looking for, just wondering if I can streamline my implementation a little. Heres a sample of my code for reference,
Code:
JExcelApiExporter exporter = new JExcelApiExporter();
 
exporter.setParameter(JExcelApiExporter.IS_DETECT_CELL_TYPE, Boolean.True);
 
Map<String,String> numberFormats = new HashMap<String, String>();
 
numberFormats.put("#,##0.0000000000; (#,##0.0000000000)","#,##0.00;[RED]-#,##0.00"«»);
 
exporter.setParameter(JExcelApiExporterParameter.FORMAT_PATTERNS_MAP,numberFormats);
 
</td></tr></tbody></table>
 
 
 
Thanks again for your response - I'm continually finding new functionality in Jasper that I hadn't anticipated.
 
 
 
-stephen
smul's picture
11
Joined: Oct 3 2007 - 6:42pm
Last seen: 6 years 11 months ago
0
smul wrote:
So the Java formatting expression is the key in the Map and the Excel formatting expression is the value, right?
Yes.
Is this the right approach or am I making this more complicated than it needs to be?
I don't know a simpler way to do it. You need to specify a format in the JRXML, or otherwise FORMAT_PATTERNS_MAP would not apply. Regards, Lucian
lucianc's picture
5281
Joined: Jul 17 2006 - 1:10am
Last seen: 3 weeks 2 days ago
0
Hi, A final posting on this topic to note some things I've discovered along the way and to present a quick HOWTO for others interested in using this approach. Firstly, things look like this on the Java side now,
Code:
        JExcelApiExporter exporter = new JExcelApiExporter();
 
 
 
 exporter.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
 
 
 
       Map<String, String> numberFormats = new HashMap<String, String>();
 
        numberFormats.put("#,##0.0000000000", "#,##0.00;[RED]-#,##0.00"«»);  
 
      exporter.setParameter(JExcelApiExporterParameter.FORMAT_PATTERNS_MAP, numberFormats );
 
</td></tr></tbody></table>
 
 
 
Note that I've simplified the Java expression in the Map (no need to specify the negative format as well as the normal format, since we want them to be the same and I seemed to experience some problems in the output when using both).
 
 
 
The following is an excerpt from the report that shows how the textField should be setup for this to work,
 
 
 
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>
 
				<textField isStretchWithOverflow="false" pattern="#,##0.0000000000" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
 
					<reportElement
 
						style="VerySmall"
 
						x="0"
 
						y="14"
 
						width="50"
 
						height="14"
 
						key="textField"/>
 
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
 
					<textElement textAlignment="Right">
 
						<font/>
 
					</textElement>
 
				<textFieldExpression   class="java.math.BigDecimal"><![CDATA[$F{myField}]]></textFieldExpression>
 
 
 
</td></tr></tbody></table>
 
 
 
<b>Note</b> that the pattern in the report has to exactly match the numberFormat in the java code for this to work.
 
 
 
I experienced problems when trying to use <i>###0.0000000000</i> in place of <i>#,##0.0000000000</i>. Not sure why so just went with <i>#,##0.0000000000</i> in the end.
 
 
 
Lucien, as a possible feature enhancement would it be possible to do one of the following,
 
 
 
1. Add an attribute to text-fields that marks a particular value as something that should be formatted in the output rather than in jasper? This would allow us to do what I'm achieving here without using the Map.
 
2. Allow "magic" tokens to be specified in the pattern attribute which tell Jasper to do do any formatting on the value but simply pass it through to the report (one could then create mappings which explicitly mapped those magic tokens to various formats without having to ensure they use a) a valid java format and b) a format that always returns data in the right precision for their application).
 
 
 
Just an idea.
 
 
 
Thanks again for your support,
 
 
 
-stephen
smul's picture
11
Joined: Oct 3 2007 - 6:42pm
Last seen: 6 years 11 months ago
0
Please log the feature requests [url=http://jasperforge.org/sf/tracker/do/listArtifacts/projects.jasperreport... so that we keep track of them. Regards, Lucian
lucianc's picture
5281
Joined: Jul 17 2006 - 1:10am
Last seen: 3 weeks 2 days ago
Feedback