Date value not being effected by Region and Language settings

Aspose.Cells Product Family
1

We can encountered a problem where it appears that Aspose.Cells is not using the computer’s Region and Language settings (at least as it pertains to the Short Date format).

I have attached two files “ExcelTest_NADateFormat.xls” and “ExcelTest_UKDateFormat.xls”. The “NA” file was created with the computer’s Region and Language setting to "English (United States) which causes the short date format to be “MM/DD/YYYY”. The “UK” file was created with the computer’s Region and Language setting to “English (United Kingdom)” which causes the short date format to be “DD/MM/YYYY”. The formatting of these cells in both files is set to a Date whose format has an “*” - which should mean that the way the date is displayed should change with the operating system setting’s chosen Region and Language.

If you run the following code using Aspose.Cells version 7.0.2.0, the values in the resulting DataTables are the same. If you have your Region and Language settings to US, all the dates in both DataTables appear as “MM/DD/YYYY” formatted. If you are set to be UK, all the dates in both DataTables appear as “DD/MM/YYYY” formatted.

However, if you use Aspose.Cells version 7.3 or 7.4, the values do not match. The values in the DataTable created from the “US” file are always “MM/DD/YYYY” formatted and the values in the DataTable created from the “UK” file are always “DD/MM/YYYY” formatted. And this is regardless of how the computer’s Region and Language settings are configured.

Aspose.Cells.LoadOptions loadOptions = new Aspose.Cells.LoadOptions();

//This file was create with the PC’s Region and Language Date Format to be English (United States) so that the short datetime format is ‘MM/DD/YYYY’

//The format on the excel was set “*” so that it’s date format would switch with the operating system settings

string excelFileNameAndPathNA = "c:\\ExcelTest_NADateFormat.xls";

Aspose.Cells.Workbook excelFileNA = new Aspose.Cells.Workbook(excelFileNameAndPathNA, loadOptions);

//This file was create with the PC’s Region and Language Date Format to be English (United Kingdom) so that the short datetime format is ‘DD/MM/YYYY’

//The format on the excel was set “*” so that it’s date format would switch with the operating system settings

string excelFileNameAndPathUK = "c:\\ExcelTest_UKDateFormat.xls";

Aspose.Cells.Workbook excelFileUK = new Aspose.Cells.Workbook(excelFileNameAndPathUK, loadOptions);

//Export the worksheet as a datatable of strings

Aspose.Cells.Worksheet currentWorksheetNA = excelFileNA.Worksheets["Sheet1"];

System.Data.DataTable cultureTableNA1 = currentWorksheetNA.Cells.ExportDataTableAsString(0, 0, currentWorksheetNA.Cells.MaxDataRow + 1, currentWorksheetNA.Cells.MaxDataColumn + 1);

Aspose.Cells.Worksheet currentWorksheetUK = excelFileUK.Worksheets["Sheet1"];

System.Data.DataTable cultureTableUK1 = currentWorksheetUK.Cells.ExportDataTableAsString(0, 0, currentWorksheetUK.Cells.MaxDataRow + 1, currentWorksheetUK.Cells.MaxDataColumn + 1);

This change from the way it was being handled is causing us some problems in our handling of data from non-US clients. Is this a bug in the later versions of Aspose? Is there some other way we should be handling the files in order for the dates to appear consistently based upon the region settings?

2

Hi,


Thanks for providing details and sample files with code snippet.

Well, I tested your scenario a bit and after an initial test, I found the exported data tables from two files are not the same. But, we need to investigate it in details as I am not entirely sure if this is an expected behavior (i.e. We may have changed its way accordingly now which is the right behavior and which was not there there in the previous or older versions, e.g v7.0.2. or older versions) or an issue with our recent versions (v7.4.x etc.).

For the sake of investigation, I have logged a ticket with an id “CELLSNET-41452” for your issue. Our concerned developer will look into it and once we have any update on it, we will let you know here.

Thank you.
3

Hi,


After further investigation, we come to know that It is not a bug rather a new feature for our recent versions/fixes: For your information, for XLS files, there is a setting (Workbook.Settings.Region) saved. We will format cells with this setting when there is a custom value for it. For your given two files, they have different region settings, i.e…, one is USA based and another one is United Kingdom based. So, we will format cells with corresponding locale setting. If you want to keep all excel files to be formatted with USA locale, you can reset the region to USA after opening the Excel template file. e.g
Workbook wb = new Workbook(…);
wb.Settings.Region = CountryCode.USA;
…

Or, if you want to keep all Excel files to be formatted with the default locale of the environment of the running application, code can be changed to:
e.g
Workbook wb = new Workbook(…);
wb.Settings.Region = CountryCode.Default


Also, please download and try our latest fix/version: Aspose.Cells for .NET (Latest Version)

Thank you.

4

I have a situation where when I download an excel file from our server where the region is set to US, the Spanish date is converted to a US format.


If I export the same file from my local box where my region is set to Spanish (Argentina), it stays in the Spanish format.

The excel workbook is created from HTML and I’m using HtmlLoadOptions and setting the workbook.settings.region and languagecode to the LatinAmerica country code.

It seems as though the format is based on the server control panel->region setting.

Is that the case? If so, how can I keep the date format that’s in the HTML I’m loading in the workbook?
5

Hi Steve,


Thank you for contacting Aspose support.

We will be in a far more better position to assist you with this inquiry if you can provide us your input HTML document as well as your complete code for investigation. Moreover, it would be appropriate that you should create a new thread while providing the above requested. This way, you will be the owner of the thread and will be able to receive all notifications. Thank you for your understanding.