Excel Number Formatting

At the moment we have come up with a work around using java to replicate an accounting format in excel, where the contents in the cell are untouched and it is formatted to no decimal places with negatives wrapped in brackets.

 

JRXlsExporter exporter = new JRXlsExporter();

exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, print);

exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, outputStream);

exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);

exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);

Map numberFormats = new HashMap();

numberFormats.put("#,##0.0000000000", "_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)");

exporter.setParameter(JRXlsExporterParameter.FORMAT_PATTERNS_MAP, numberFormats);

exporter.exportReport();

render(response, outputStream);

 

As you can see we would need to add a new mapping for each format where we would like the contents of the cell unchanged.

Hi,

 

Can you try the latest JR version and see if the precision is there?

We recently made some modifications so that we preserve the actual value of the text field. So although we format the value when we show it, behind the scenes, when you want to edit the cell in Excel, you can see it has more decimals than it shows.

 

Thanks,

Teodor

try following attribute in reportElement tag of jrxml.

 

pattern="$#,##0.00"

Hi,

 

Also note that there is a new property available at element level, to specify the pattern to be used in Excel, and thus avoid using the format patterns map parameter:

http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.pattern

 

Let me know if you can verify if recent JR versions preserve number precision for you.

 

Thanks,

Teodor

Changed Status from Resolved to Closed

---