Hi,
We would like to set the numeric format of a cell by specifying the number of decimals and if a 1000 separator should be applied, without using a custom format. This way the generated file would be adjusted to the correct decimal and thousand separator for the locale of the different end users. When using a custom format the same file will not display numbers with the settings of the users viewing the file.
Is this possible?
Thanks,
Ulf Ekström
Hi,
Thank you for considering Aspose,
Well, Custom format does not depend upon user local settings. You can set a custom format for you number and it will work fine on any local settings from the users. Only the currency formats in the default number formats get affected by the local settings but that can also be overcome by using the custom currency format.
For your requirement, either you can use style.setNumber(4) or you can create your custom format as style.setCustom("#,##0.00”) and it will work fine with different local settings. Please see the following sample code in this regard,
Sample Code:
//Instantiating a Workbook object
Workbook workbook = **new** Workbook();
//Accessing the added worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().getSheet(0);
Cells cells = worksheet.getCells();
Cell cell = cells.getCell("A1");
cell.setValue(32987);
//Setting the display format
Style style = cell.getStyle();
style.setNumber(4);
cell.setStyle(style);
//Adding a numeric value to "A3" cell
cell = cells.getCell("A2");
cell.setValue(32987);
//Setting the display format
style = cell.getStyle();
style.setCustom("#,##0.00");
cell.setStyle(style);
//Saving the modified Excel file in default format
workbook.save("C:\\output.xls",FileFormatType.*DEFAULT* );
Please see the following link for more supported Display formats,
Thank You & Best Regards,
Hi,
That is not what I mean. We need it the other way around. We want the swedish users to get a ‘,’ (comma) as the decimal separator and american users to get a ‘.’ (dot) as the decimal separator, etc. without having to create different files for them. This is not possible with custom formats. The custom format you suggested will always give a dot as the decimal separator.
With Excel it is possible to select the number of decimals to use without specifying a custom format. The numbers will then be formatted according to the end users computer settings. This is what we are trying to achieve using Aspose Cells. Is this possible?
Thanks and best regards,
Ulf
Hi,
Thank you for considering Aspose.
Well, in this case you can try the Style.setNumber(4) to achieve your desired results. This default Number format is used for displaying the thousand separators as per user local settings and shows the value up to 2 decimal places. This way the number format will be according to the user’s local settings. Please see the following sample code,
Sample Code:
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Accessing the added worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().getSheet(0);
Cells cells = worksheet.getCells();
Cell cell = cells.getCell("A1");
cell.setValue(32987);
//Setting the display format
Style style = cell.getStyle();
style.setNumber(4);
cell.setStyle(style);
//Saving the modified Excel file in default format
workbook.save("C:\\output.xls",FileFormatType.DEFAULT);
Also, as I mentioned earlier that there are certain other default display formats supported by Aspose.Cell. Please see the following link in this regard,
Thank You & Best Regards,
Thanks for your quick reply. However, we need to be able set a different number of decimals for different cells. (Without using custom formats) Sometimes 2, sometimes 4, etc. I guess from your reply that this isn’t possible in the current version of Aspose Cells.
I’m thinking about methods like:
Style.setDecimals(int decimals)
Style.useThousandSeparator(boolean useThousandSeparator)
and they should accomplish this without resorting to custom formats.
Is this something you might consider to implement?
Thanks,
Ulf
Hi,
Thank you for considering Aspose.
Well, I am afraid; currently your requirement is not supported in Aspose.Cells. We will look into this requirement in our future release.
Thank You & Best Regards,
Thank you. It would be extremely valuable for us.
Best regards,
Ulf
Hi,
Thank you for considering Aspose.
After further exploring your issue, we think that you can use custom format to get the desired result. Please see the following sample code:
Sample Code:
Cell cell1 = worksheet.getCells().getCell("A1");
Cell cell2 = worksheet.getCells().getCell("A2");
cell1.setValue(123456789.1234);
Style style1 = cell1.getStyle();
style1.setCustom("#.00");
cell1.setStyle(style1);
cell2.setValue(123456789.1234);
Style style2 = cell2.getStyle();
style2.setCustom("###,###.0000");
cell2.setStyle(style2);
For the above mentioned code, the generated workbook shows different value format for different locale. When the locale is Swedish, value of cell1 is “123456789,12” and value of cell2 is: “123 456 789,1234”. When the locale is US, value of cell1 is “123456789.12” and value of cell2 is “123,456,789.1234”. This way you can set any desired style format as per your requirements.
Please check it and let us know if this can fulfill your requirements.
Thank You & Best Regards,
Hi again,
Your suggested solution fulfilled our requirements. This is really good news.
Thanks,
Ulf
Hi I’ve a similar issue.
<<<
When the locale is Swedish, value of cell1 is “123456789,12” and value of cell2 is: “123 456 789,1234”. When the locale is US, value of cell1 is “123456789.12” and value of cell2 is “123,456,789.1234”. This way you can set any desired style format as per your requirements.
<<<
How to change the locale?
Thanks
Hi,
Thank you for considering Aspose.
Well, we are not very clear about your requirement. If you mean you want to change the locale for opening the excel files in MS Excel, I am afraid, it will not be possible as MS Excel uses the same local settings as the locale settings of the system.
Thank You & Best Regards,