Locale setting and custom number format

Aspose.Cells Product Family
, ,
1

I am generating a workbook using aspose cells. I am creating a named custom style using custom number format string. In case of default english, the style named “M1” has custom number formatting string set as “#,##0;-#,##0” with comma used as thousand separator. In case of es-ES locale, the string is set to “#.##0;-#.##0” with dot as thousand separator. However when the workbook is generated, I see the style’s number format string switches to “#,##0;-#,##0” and the number appearing in the cell becomes incorrectly formatted.
I am already setting locale on the workbook before saving it. e.g :

    Locale locale = new Locale("es", "ES");                  
    wb.getSettings().setLocale(locale);

What should be done to make formatting stick and get applied correctly?
thanks!

2

@redred,
Please share the code which you are using to create custom styles and settings based on locales, as well as the current output file and expected output file created using MS Excel for our analysis.

3

thanks for looking into this. attached is the generated workbook…
and screenshots showing the workbook opened. As you can see, the format string changed to using comma as thousand separator instead of dot (.)
MOD2.zip (7.4 KB)
format_switched.PNG (37.6 KB)
format_switched.PNG (37.6 KB)

You may rename .zip as xlsx and open it. Please look at the cell B5. It uses style MR_6.
Excel XML generated shows numFmtId #177 correct.

[numFmt numFmtId="177" formatCode="#.##0;-#.##0"]

However it is changing to comma when excel is opened.

4

@redred,
Please share your simplified runnable console application that can be compiled and executed here to generate this output Excel file for our reference.

5

aspose_cells_localeIssue.7z (754 Bytes)

Code snipped attached… Thanks!

6

@redred,
I have created attached output file and the format looks ok. Please share screenshots after opening it and let us know if you notice the issue. formatvalue.zip (5.8 KB)

7

Thanks for the response. I tried to open the excel workbook, but I see the same issue with this workbook as well. Screenshots attached.formatedVal_xlsx.PNG (35.1 KB)

spanish_options.PNG (30.9 KB)

expected format string shoudl be “#.##0” and the value should have shown as 5.127.399.
But when excel is opened, the value is shown as 5127399,0 and format switches to using comma as thousand separator.
thanks.

8

@redred,
We have analyzed the information and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSJAVA-43595 - Locale setting and custom number format does not display number correctly

9

@redred,

As the API reference denotes, Style.Custom is cultural-independent, so when setting this property, the provided pattern should always be the standard (corresponding to locales such as en-US). Correspondingly, the thousand group separator should be ‘,’ and decimal separator should be ‘.’.
If you need to use the cultural-dependent patterns, you may try Style.CultureCustom property. For your provided code segment, please change:

cellStyle.setCustom(formatStr);

to

cellStyle.setCultureCustom(formatStr);

However, because there are so many differences for different locales, CultureCustom property may not support for all locales. We still recommend users to use the standard patterns. MS Excel will automatically translate the pattern according to your environment and show the correct results.