Hi,
I’ve experienced an issue with numeric formats using Aspose.Cell.
My target is to read a workbook that contains numbers with a particural format: “,” as decimal symbol and “.” as thousands separator. This workbook is saved in a file with no extension.
As you can see in the attached example, when I try to read values from this workbook, the “DisplayStringValue” function returns wrong formatted value: “.” as decimal symbol and “,” as thousands separator.
When I save this file as “.xlsx” and than read values again, number are right formatted. This not happened if I save the file as “.xls”.
What is the reason of this difference? How can I correcly read values keeping the right format?
Hi Andrea,
Thank you for contacting Aspose support.
I have evaluated your presented scenario while using the latest version of
Aspose.Cells for .NET (Latest Version) , and I am getting comma (,) as thousand separator & dot (.) as decimal separator for both XLSX & XLS files. Moreover, when I checked the result in Excel manually, they are the same as read by Aspose.Cells API, that is: comma (,) as thousand separator & dot (.) as decimal separator. Based on these facts, I believe the problem could be related to the locale.
Please try the following piece of code with both XLS & XLSX files. It will produce similar & desired results.
C#
Workbook wbk =
new Workbook(input);
wbk.Settings.CultureInfo =
new CultureInfo(“it-IT”);
for (
int i = 0; i < wbk.Worksheets[0].Cells.MaxDataRow; i++)
{
Console.WriteLine(wbk.Worksheets[0].Cells[i, 0].DisplayStringValue);
}
So, if I understand correcly, formats depends on the CultureInfo settings and this settings cannot be read from a workbook but they can be set.
Now, my code need to be execute in different OS with different settings, so I can’t
set this information in that way.
My OS use Italian format settings, if I change this to English (USA) I get the same result that you’ve obtained: comma (,) as thousand separator and dot (.) as decimal separator.
My expectation is that, using Italian OS setting, I will get comma (,) as decimal separator and dot (.) as thousand separator. But this not happened.
Probably you can replicate my issue by setting your OS region format to “Italian”.
Hi Andrea,
Please note that the built-in formats depend on the locale/regional settings of the machine. This is the same behavior as of Excel application. You may have noticed when you changed the locale of your machine to US-EN, the number format for the given spreadsheet changed when loaded in Excel.
Let me try the case by changing the locale of my machine to Italian, and get back to you with updates in this regard.
Hi,
Thanks for using Aspose.Cells.
Ms excel does not record any region related information in the file data when saving an xlsx file, so aspose.cells always uses the default regional settings of the environment to format cells after loading workbook from xlsx templae file.
However, commonly ms excel will record regional information into the file data when saving an xls file. For such kind of template file, aspose.cells will use the regional settings recorded in the template file to format cells by default.
To let aspose.cells use the OS(environment) locale to format cells always for all template files with different file formats(xls, xlsx…), we think you can add one line of code after loading template files:
Java
Workbook wbk = new Workbook(input);
wb.getSettings().setRegion(CountryCode.DEFAULT);
…
Hi Andrea,
As promised, I have performed tests by changing the locale/language of my machine to Italy/Italian. I have noticed that XLSX file type returns the thousand separator as dot (.) and decimal separator as comma (,) without any additional settings, Whereas XLS file type returns the opposite, that is; comma (,) as thousand separator & dot (.) as decimal separator. Shakeel has already explained the reason for this behavior in his last response so the solution is to set the WorkbookSettings.Region to CountryCode.Default in order to force the API to format the numbers (regardless of the spreadsheet type) according to the locale of the machine.
C#
Workbook wbk = new Workbook(“D:/Test_Table.xlsx”);
wbk.Settings.Region = CountryCode.Default;
for (int i = 0; i < wbk.Worksheets[0].Cells.MaxDataRow; i++)
{
Console.WriteLine(wbk.Worksheets[0].Cells[i, 0].DisplayStringValue);
}