Difference for DateTime while extracting datatable in XLS and XLSX in .NET

Aspose.Cells Product Family
1

i’m seeing inconsistent results for items that should produce the same result depending upon the file format (extension) i use.

based on our understanding, these files should extract to string identically. as you can see, we are setting the workbook setting region to default, and setting the current thread’s date format to be “d/M/yyyy”.

i have attached the complete unit test class (.txt file), and two of the files i’m using for testing. these are files we are receiving from clients so we are now seeing this in a production environment.

please investigate and let me know.

thanks!

2

for clarity, how we got here was from this post:

Date value not being effected by Region and Language settings - Free Support Forum - aspose.com

3

forgot to mention that our expectation is that the .XLS file extraction is correct, but the .XLSX file extraction is not.

4

Hi,


I have tested your code a bit with your template file, it works fine with v7.4.2.3 (please try it).

Workbook workbook = new Workbook(“e:\test2\Clean+EU+Quest±+DE+Date%2c+DE+Num+XLSX.xlsx”);

CultureInfo tempCultureInfo = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone();
tempCultureInfo.DateTimeFormat.ShortDatePattern = “d/M/yyyy”;
System.Threading.Thread.CurrentThread.CurrentCulture = tempCultureInfo;

workbook.Settings.Region = CountryCode.Default; // forces aspose to use the thread settings instead of the last saved file settings for string extraction

Worksheet worksheet = workbook.Worksheets[“Incumbent Data”];

int numberOfRowsToExport = worksheet.Cells.MaxDataRow + 1;
int numberOfColumnsToExport = worksheet.Cells.MaxDataColumn + 1;

System.Data.DataTable dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, numberOfRowsToExport, numberOfColumnsToExport);

// note that the referenced cell extracted into the datatable uses a “* date format” so it should obey culture settings

object objectValue = dataTable.Rows[5][40];
string actualStringValue = dataTable.Rows[5][40].ToString();

System.Diagnostics.Debug.WriteLine(string.Format(“cell [{0}]”, objectValue)); //“19/2/2012” -OK
System.Diagnostics.Debug.WriteLine(string.Format(“cell [{0}]”, actualStringValue));//“19/2/2012” -OK

For your information, my regional settings is US (english).

If you still find the issue, kindly give us your locale and regional settings, we will further investigate your issue on our end.

Thank you.

5

which regional (and locale) settings do you need? the executing thread? the workbook.Settings.CultureInfo? which one?

6

i have added an updated test (AsposeTests - B.txt which is a C# file).

i have used the latest version of Aspose.Cells you provided and I still see the error.

i have added more debug information to the output window and am copying what i am seeing below:

.xlsm: before setting region to CountryCode.Default : workbook Culture Settings [Name:en-US] [ShortDatePattern:M/d/yyyy]
.xlsm: after setting region to CountryCode.Default : workbook Culture Settings [Name:en-US] [ShortDatePattern:M/d/yyyy]
cell [2/19/2012]
cell [2/19/2012]
Expected [19/2/2012] Actual [2/19/2012]: .xlsm date to string conversion not obeying temp culture short date pattern.


.xls: before setting region to CountryCode.Default : workbook Culture Settings [Name:de-DE] [ShortDatePattern:dd.MM.yyyy]
.xls: after setting region to CountryCode.Default : workbook Culture Settings [Name:en-US] [ShortDatePattern:d/M/yyyy]
cell [19/2/2012]
cell [19/2/2012]
Expected [19/2/2012] Actual [19/2/2012]: .xls date to string conversion OK.


.xlsx: before setting region to CountryCode.Default : workbook Culture Settings [Name:en-US] [ShortDatePattern:M/d/yyyy]
.xlsx: after setting region to CountryCode.Default : workbook Culture Settings [Name:en-US] [ShortDatePattern:M/d/yyyy]
cell [2/19/2012]
cell [2/19/2012]
Expected [19/2/2012] Actual [2/19/2012]: .xlsx date to string conversion not obeying temp culture short date pattern.

edit: the bolded items showcase the difference between XLS and XLSX/XLSM format behavior. perhaps this is the root problem and i am only seeing a side-effect of that?

7
Hi,

Thanks for your posting and using Aspose.Cells.

For xls file, the region settings may be saved in the xls file data. So when reading xls template files, if there is specified region settings saved in the file, we will read it and take it as the default region of the workbook. So when creating one Workbook from xls template file, the workbook’s region settings may not be the default region of the environment.
However, for xlsx file, there is no such kind of region settings saved anymore. So when creating one Workbook from xlsx file, the default region is always the default one of the environment.
So, when comparing the output of workbooks from xls and xlsx template file, the formatted values may be different because the workbooks are using different region settings. To get definite output for formatted values, one way is to use custom formatting string instead of built-in formats for cell style. Another way is to reset the region settings of workbook to specified one after creating the workbook from template file.
8

here is our dilemma.

we send an excel file out globally to be populated with information from clients. they send it back in, populated with data. we do not know what region or environment settings were last used to save the file. we expect that the user has entered the date correctly depending upon their local computer’s settings. We are using the native Excel date formats so that the date will look correct when opened in Excel, no matter what the user’s individual computer settings may be. however, to get them to a known date format, we want the dates formatted as ‘d/M/yyyy’, so we can have all the data extracted into a database with a known format for further processing.

when we open the file, we process it on a server using a windows service. the default thread setting would be english with M/d/yyyy date format. but as you can see, in our code we are trying to work around this by setting the thread to have a specific date format (d/M/yyyy). Our code now gives the desired result for XLS files, but for XLSX/M files Aspose.Cells is not returning the date value formatted as a string in the same way when we call ExportDataTableAsString.

so, what is the API specification for what is returned as the string value in ExportDataTableAsString? is our understanding wrong? does that logic depend upon the file format so that we will get different things and there’s no way to get predictable and consistent results using the same code?




9
Hi,

For performance issue, we check whether user set region value is same with current one, if true then we do not update formatting related information. Because the default region after openning xlsx file is CountryCode.Default, so when you set it as Default again the new format that you set for current thread will take no effect. In fact you need not reset the cultureinfo for current thread. Instead you can reset the cultureinfo directly for the workbook. Please change you code:
System.Threading.Thread.CurrentThread.CurrentCulture = tempCultureInfo;
workbook.Settings.Region = CountryCode.Default;
To:
workbook.Settings.CultureInfo = tempCultureInfo;
Then the new format should take effect.

10

thanks again for your response.

i have made the suggested modifications to my source code and am still seeing the same issue. please see the updated text file containing the code i am using. (specifically starting at the method at line 55.)

are you not able to reproduce with your code using these same files? if not, if you re-download the included excel files and don’t open them in excel (going straight to aspose.cells), then do you see the issue?

11

ALL APOLOGIES!

i re-downloaded and re-integrated the previously noted Aspose Cells version 7.4.2.3 (in place of the version 7.4.0.0 i was using) and this passes my test now.

so, for final clarification, the last response from you is the officially supported method? i want to make certain before we move forward and deploy our hotfix. i’d hate to find out we did something wrong and then re-introduce a bug because we weren’t using Aspose correctly.

Also, i notice this appears to be fixed in version 7.4.2.0, which is the last officially announced build. Is that the preferred one to deploy to a production environment, or is 7.4.2.3 considered equally stable and regression tested?

12

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Yes, it is offically supported version. You can use it in your production environment.They are stable releases, If you find any problem, please feel free to post on our forums. We will be glad to help you further.

13

thanks for the response.

one final question and then i will go away:

the last response from you is the
officially supported method of getting dates consistently formatted as strings? (not changing the thread at all, just setting the workbook.Settings.CultureInfo directly.) i want to make certain before we move
forward and deploy our hotfix. i’d hate to find out we did something
wrong and then re-introduce a bug because we weren’t using Aspose
correctly in the first place - which has already happened more than once.

14

Hi,

Thanks for your posting.

Yes, it is the officially supported method for getting definite formatted result by setting cultureinfo or region for the workbook