XLS number format problem

[2002 JI Open Discussion]

By: G᢯r M󣺡r - moczar

XLS number format problem

2003-08-04 05:52

Hi!

 

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!

 

Regards,

 

Gabor

 

 

 

 

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).

 

???

 

Thanks!

 

Gabor

 

 

 

 

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://sourceforge.net/forum/message.php?msg_id=2011270</a> 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.