Trying to display numbers using custom number format.
Setting the number format as “# ##0,00;-# ##0,00”
space is the thousand separator, comma is decimal separator, and there are 2 digits after decimal.
When I open excel file, the number format string for those numbers gets replaced automatically to some other incorrect format string, and the numbers do not appear correct.
When I look at styles.xml file in the generated xlsx file, style appears correct. Why is the number format changing?
setCustom() / setCultureCustom() makes no difference. Excel advanced formatting options also made no difference.
Please see attachments showing the problem and the code used to create style and apply the numberformat.
For your example code, we just replace the line:
cellStyle.setCustom(formatStr);
with
cellStyle.setCultureCustom(formatStr);
then the cells can be formatted correctly in the generated xlsx file. Would you please try it?
For the formatting pattern, generally you don’t need to use culture-depended separators for it. You should just use the standard separators(same with en-US locale, ‘,’ as group separator, ‘.’ as decimal separator, …), set it to the Style object by Style.setCustom(). Then ms excel and our component both can format cells with the correct separators according to current locale.
I have already tried cellStyle.setCultureCustom(formatStr) and nothing chnages. If you look at the styles.xml - format string specified there is correct as I expect it to be. However once Excel is opened, the format string changes to incorrect.
Original (and expected) format string is # ##0,00;-# ##0,00
Excel changes it to : #\ ## 000;-#\ ## 000
Number (61641584,41) is expected to show up as 61 641 584,41
However it shows up as : 61 6 41 584 when opened in excel.
Please try the latest version: Aspose.Cells for Java v22.7 (Download | Maven)
If you still find the issue with latest version, kindly share your current output Excel file and your expected output Excel file. You may create your expected output file in MS Excel manually. We will check it soon.
Thanks Amjad.
This seems to work in 22.07. i was using 21.3 where setCultureCustom() did not do anything. Could you please confirm that 21.3 indeed has this bug, and if there is any workaround possible? - in case updating to 22.7 takes long time.
We have tested the same code with 21.3 and found the resultant file can make the cell formatted correctly too. Of course, if you are using Style.setCustom() instead of Style.setCultureCustom(), you will get incorrect output because the formatStr is using special separators of specific locale.
Please make sure the old version you are using is 21.3, and you are using the same code for both 22.7 and 21.3. Anyways, you may try the solution in my previous post:
"You should just use the standard separators(same with en-US locale, ‘,’ as group separator, ‘.’ as decimal separator, …), set it to the Style object by Style.setCustom(). "
We do not know how did you create/re-save those files with apis of Aspose.Cells. Here we provide the resultant files and screenshots generated by your code(in your first post):
“code.txt” is just completely copied from the samplecode.java in your “ForAspose.zip” except the replacement of “Style.setCustom” with “Style.setCultureCustom”.
After running the code with Aspose.Cells for Java 21.3, we got “res.xlsx”. We opened it in ms excel, with different locales, please see the screenshots: en_US.png is what shown in ms excel with en_US locale, and fr_FR.png is what shown in ms excel with fr_FR locale. We cannot find the issue. Those numbers can be formatted with the proper separators corresponding the locale.
Please confirm whether you can get the same result with us by the code. If your current case is not the same one, please provide us the runnable project to reproduce the issue, we will look into it.