Why does xls file gives incorrect data whereas xlsx gives proper formatted date data

Aspose.Cells Product Family
1

Hi Team,

I am using aspose cells 19.1 version to parse Excel in my Java code. I am using below to get values from Excel

cell.getStringValue();

Excel contains below formatted data: (Saturday, 10 October 2020)
Screen Shot 2021-01-13 at 5.53.07 PM.jpg (321.8 KB)

When I use xls I get 10 October 2020 as cell.getStringValue();
When I use xlsx I get proper and correct formatted data Saturday, October 10, 2020 as cell.getStringValue();

So Why does xls file gives incorrect data whereas xlsx gives proper formatted date data?

DateCustomXLS and XLSX.zip (12.6 KB)

2

@nemish.doshi,

As we told you in other thread, for XLS file, there might be some regional settings data in the template file so the formatted result will be determined by that data. For XLSX file format, there is no such kind of data so the formatted result totally depends on the default region of the current thread. To get the expected formatted results, please just specify the expected region/Locale for the workbook before getting cell’s string values, see the following line of code for your reference:
wb.getSettings().setLocale(…);//please set your locale accordingly first and then use cell.getStringValue() to retrieve the value

In case you still find the issue, kindly share your sample code (after embedding suggested (above) recommendations) to show the issue, we will check it soon.

3

@Amjad_Sahi,

I have created xls file and xlsx file from scratch in my Excel and have not applied any regional settings data in the template file. Attached both files in topic

4

@nemish.doshi,

Maybe regional settings is embedded due to your Excel or environment where the file is created. Anyways, please try the following sample code (see the line in bold), it should work fine now:
e.g.
Sample code:

Workbook workbook = new Workbook(“f:\files\DateCustomXLS.xls”);
Worksheet worksheet = workbook.getWorksheets().get(0);
workbook.getSettings().setLocale(Locale.getDefault());
System.out.println(worksheet.getCells().get(“A2”).getStringValue());

Let us know if you still find any issue.

5

Thanks. Let me try and get back to you if any issues

6

@Amjad_Sahi
Thanks it worked for me.

7

@nemish.doshi,

Thanks for your feedback.

Good to know that by specifying your regional settings/locale to your default(expected) locale works for your needs. In the event of further queries or issue, feel free to write us back, we will be happy to assist you soon.

1 Like