XLS number format problem

By: G᢯r M󣺡r - moczar
XLS number format problem
2003-08-04 05:52

I have a java.lang.Double field in my report. I use the '###,###,##0.00' formatting pattern.
When I export the report to xls I got the proper result (e.g. '45,22'), but the decimal symbol is "," instead of ".". My regional setting includes "." too, so Excel only recognize ".". So, my number fields are exported as strings and I can not find a way to transform these fields to number type.
I have also noticed that using '###,###,##0' formatting pattern results the same '32,56' style number (string) format in xls (pdf is good!), instead of '33'.

Any help is greatly appriciated!



By: G᢯r M󣺡r - moczar
RE: XLS number format problem
2003-08-05 02:34
What does jasperreports do with the pattern in the text fields? How jasperreports evalute it?

I run through my registry and the decimal symbol is set to '.'. BUT Jasperreports always generate ',' for patterns like 0.00 (where the '.' is the decimal symbol and Java docs says, that '.' is always the local decimal symbol).




By: G᢯r M󣺡r - moczar
RE: XLS number format problem
2003-08-05 09:13
Now I figured it out ...
Jasper only uses pattern through DecimalFormat.
DecimalFormat creates for itself an instance of DecimalFormatSymbols from its locale data.
In my Win2k it means that Java gets locale from regional options. It is set to Hungarian for me.
You also have the possibility to set e.g. decimal separator to something different in regional options, but Java does not care about it. So only default locale settings are used.
Of course Java doc does not mention it! :-(

I thought that Hungarian locale uses '.' for decimal separator, but it uses ','. The problem is that Excel only recognizes '.' as decimal separator. So I have 2 choices:
- reset locale to e.g. English(US), I do not want it
- set a formatter varibale as described in <a href=https://sourceforge.net/forum/message.php?msg_id=2011270>https://sourcef... for every field

I hope it will help for other people.

By: G᢯r M󣺡r - moczar
RE: XLS number format problem
2003-08-05 09:38
The most efficient way is to modify JRFillTextField.java:
I have replaced "this.format = new DecimalFormat(pattern);" to "this.format = new DecimalFormat(pattern, new java.text.DecimalFormatSymbols(Locale.US));"
and I have added "import java.util.Locale".

So I do not have to format each field.
2002 JI Open Discussion's picture
Joined: Aug 10 2006 - 3:28am
Last seen: 16 years 10 months ago

0 Answers:

No answers yet