Problem with Custom Date formats cell of XLSX in Java

Hi Team


Its urgent. Please revert if there is any solution to this issue.

Hi Vikram,

Thanks for your posting and using Aspose.Cells.

Please make sure you are using the latest version: Aspose.Cells for Java 8.3.1 because it works fine.

I have tested this issue with the source Excel file attached with this post and after setting the Control Panel Short Date to dd/MM/yyyy and running this code and did not notice the issue. You can see the Console Output of this code for your reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Cell cell = workbook.getWorksheets().get(0).getCells().get(“A1”);


String str = cell.getStringValue();


System.out.println(str);

Console Output:
23/12/2014

Hi Shakeel Faiz.


Thanks for your response.
I tried using the latest Aspose API but still i am getting the same issue. Attached the sample code and screenshots of the console.
Please check and help me in correcting the same.

Note: In order to replicate this issue, the date in the sample xlsx sheet should be something like “02/03/2013” where the day and month should be b/w 01 to 09.

Thanks & Regards,
Vikram

Hi Vikram,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue as you mentioned with the latest version: Aspose.Cells
for Java v8.3.1.1
. The date is in dd-mm-yyyy format so it looks in Excel like 02-05-2014 but Aspose.Cells prints it like 5/2/2014.


We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41147 - Problem with Custom Date formats of Aspose.Cells

Hi Shakeel Faiz,

Thanks for your response.

Request to inform if there is any update on the above mentioned issue.
Request you to prioritize this issue.

Thanks & Regards,
Vikram

Hi Vikram,

Thanks for using Aspose.Cells.

The cell’s numberformat is builtin number 14 which would give different
formatting string according to different locale settings. There is a
“BUG” for JDK1.7/1.8 for getting default locale: http://bugs.java.com/bugdatabase/view_bug.do?bug_id=7073906. For such kind of issue, please see our reply to another similar requirement: https://forum.aspose.com/t/65961


Please use jdk1.5 or 1.6 to test this issue again, the expected result should be got according to your machine's region setting.

Hi Shakeel Faiz,


I tried using JDK 1.5 but still i am facing the same issue.
Please find below the example code which i am using read xlsx cell

String filePath = “D:\test.xlsx”;
Workbook workbook = new Workbook(filePath);
workbook.getSettings().setRegion(CountryCode.INDIA);
Cell cell = workbook.getWorksheets().get(0).getCells().get(“A1”);
String str=cell.getStringValue();
System.out.println("The Output value is "+str);


Scenario: 1) Regional setting date format is dd/MM/yyyy, and xlsx cell date is 02/03/1947
2) Regional setting date format is MM/dd/yyyy, and xlsx cell date is 03/02/1947

Attached sample xlsx file.

Please suggest if there is any customization needs to be done in order to fix this issue or correct me if anything wrong. Please check this on priority.


Thanks & Regards,
Vikram

Hi Vikram,

Thanks for your posting and using Aspose.Cells.

We have looked into this issue further. Please check the attached screenshots.

  • GB_JDK15.jpg: pdf was created by JDK1.5 with region setting of windows "en_GB"
  • GB_JDK17.jpg: pdf was created by JDK1.7 with region setting of windows "en_GB"
  • US_JDK15.jpg: pdf was created by JDK1.5 with region setting of windows "en_US"
  • US_JDK17.jpg: pdf was created by JDK1.7 with region setting of windows "en_US"

Please see the screenshots for different region settings and Java
version. The generated pdf is exactly same with what
shown in ms excel. For JDK17, it always uses the format according to
machine’s default language(here for us is zh_CN) and ignore the region
set in control panel.

Hi Shakeel Faiz,


Thanks for your respose.

I have tried using the solution provided but still i am facing the same issue.
Please find the attached document contains the screenshot with description.

My requirement is read the XLSX document as it is how the user upload with the same cell formats.


Regards,
Vikram


Hi Vikram,

Thanks for your explanation, screenshots and using Aspose.Cells.

You are right. The date 02-04-2013 should show like 04/02/2013 but Aspose.Cells shows it 4/2/2013. I have logged your comments and my findings in our database against this issue.

We will look into it and fix this issue or let you know the workaround. Please spare us some time. Once, we will have any fix or some advice for you, we will let you know asap.

Hi Shakeel Faiz,


Thanks for your respose.

The issue which you have mentioned in the previous post is not main issue.
The main issue is that the expected result is not got according to the machine setting. For example:
Scenario-1
As mentioned in the previous post if we set the format to any region using WorkbookSettings.setRegion method.
The console output format is d/M/yyyy irrespective of the machine setting when the xlsx date cell format is same as the machine setting format,

Example: if the machine setting is: MM/dd/yyyy and xlsx cell value is 02/03/2014(MM/dd/yyy)
The output of console is: 3/2/2014(d/M/yyyy)

Note: Without setting the format for any specific region also the same issue exists. The console output returns the M/d/yyyy format irrespective of the machine setting when the xlsx date cell is same as machine setting format.


Thanks & Regards,
Vikram

Hi Vikram,

Thanks for your posting and using Aspose.Cells.

For your problem, we think maybe it is because the your region settings is not en_IN (which is the default locale we set for CountryCode.INDIA). There are many different languages for INDIA country, so you can try to set specific locale instead of setting the region for workbook:

workbook.getSettings().setLocale(new Locale(“hi_IN”)); //or other language for INDIA country.

Also you can try to format datetime object with SimpleDateFormat without reference to Aspose.Cells to check whether SimpleDateFormat can give you the expected result with specific locale.

Hi,


Thanks for your response.

Tried using both the ways which you have mentioned in your previous post but still i am not getting the Expected result.
Please find below the sample code used
---------------------------------------------------------------------------------------------------------
public class LightCellsTest1
{
public static void main(String[] args)throws Exception
{
String str="";
try {
//To get the date format using SimpleDateFormat
Locale.setDefault(new Locale(“en_IN”));
SimpleDateFormat format=new SimpleDateFormat();
System.out.println("Format is "+format.format(new Date()));
//Reading the Xlsx Cell
String filePath = “D:\test.xlsx”;
Workbook workbook = new Workbook(filePath);
//Setting the Specific Locale to Workbook
workbook.getSettings().setLocale(new Locale(“en_IN”));
Cell cell = workbook.getWorksheets().get(0).getCells().get(“A1”);
str=cell.getStringValue();
System.out.println("The Output value is "+str);
} catch (Exception e) {
e.printStackTrace();
}
}
------------------------------------------------------------------------------------------------------------
Note: Machine Local format is:dd/MM/yyyy
Output is:M/d/yyyy
Expected result:dd/MM/yyyy

Thanks & Regards,
Vikram

Hi Vikram,

Thanks for your posting and using Aspose.Cells.

Actually, the pattern returned by your Locale is not dd/MM/yyyy, but it is M/d/yyyy.

Kindly execute the following code and let us know its output. I have also shown the output of my machine for your reference.

Java


//3-Feb-2013

Date aDate= new Date(113, 1,3);


//Print with default localized pattern

System.out.println(“With Default Date Pattern”);

SimpleDateFormat format=new SimpleDateFormat();

System.out.println("Date Format: "+format.format(aDate));

System.out.println("Localized Pattern: "+format.toLocalizedPattern());

System.out.println(“Pattern: “+format.toPattern());



System.out.println(”\nWith specific Date Pattern”);

format=new SimpleDateFormat(“yyyy/MMM/dd”);

System.out.println("Date Format: "+format.format(aDate));

System.out.println("Localized Pattern: "+format.toLocalizedPattern());

System.out.println("Pattern: "+format.toPattern());


Console Output:
With Default Date Pattern
Date Format: 2/3/13 12:00 AM
Localized Pattern: M/d/yy h:mm a
Pattern: M/d/yy h:mm a

With specific Date Pattern
Date Format: 2013/Feb/03
Localized Pattern: yyyy/MMM/dd
Pattern: yyyy/MMM/dd

Hi Shakeel Faiz,


Thanks for your respose.

Please find below the output of my machine.

----------------------------------------------------------------------------------------------------------------------

With Default Date Pattern
Date Format: 2/3/13 12:00 AM
Localized Pattern: M/d/yy h:mm a
Pattern: M/d/yy h:mm a

With specific Date Pattern
Date Format: 2013/Feb/03
Localized Pattern: yyyy/MMM/dd
Pattern: yyyy/MMM/dd

---------------------------------------------------------------------------------------------------------------------

Thanks & Regards,
Vikram

Hi Vikram,

Thanks for your posting and using Aspose.Cells.

Please also let us know the output of the following code. I have used the test.xlsx file which was provided by you inside the code. I have also shown the output got on my machine for a reference.

Please note, here I am setting the default locale to Italy.

Java


Locale.setDefault(Locale.ITALY);


String filePath = “F:\Shak-Data-RW\Downloads\test.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“A1”);


System.out.println(cell.getStringValue());


Console Output:
02/12/1947

Hi Shakeel Faiz,


Thanks for your respose and support.

Please find below the sample code, console output and my observations.

Java Code

System.out.println("The Defalut locale before is "+Locale.getDefault());


Locale.setDefault(Locale.UK);System.out.println("The Defalut locale after is "+Locale.getDefault());

String filePath = “D:\test.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.getWorksheets().get(0);

Cell cell = worksheet.getCells().get(“A1”);

System.out.println("The Format is "+cell.getStyle().getCultureCustom());


XLSX cell input value is
12/02/1947

Console output

The Defalut locale before is en_US
The Defalut locale after is en_GB
The Format is dd/MM/yyyy
02/12/1947

Expected result

The Defalut locale before is en_US
The Defalut locale after is en_GB
The Format is MM/dd/yyyy
12/02/1947

Observations

1) Each locale will have there own default date formats ex: UK is dd/MM/yyyy, US is M/d/yyyy.
2) The code will give expected result in the following scenario.
a) Lets set the default locale to UK(Locale.setDefault(Locale.UK)), default format is dd/MM/yyyy.
b) The system Region setting short date is dd/MM/yyyy
c) The XLSX custom date format is dd/MM/yyyy
3) But the code returns wrong output in the above scenario if the XLSX custom cell and System region setting date is MM/dd/yyyy.
Means the code returns wrong output if the Locale default date format is different from the combination of the System region Short date and user XLSX date cell format.

Note: The code formats the XLSX cell using the Locale default format irrespective of the format that the user sets in the XLSX cell if the System Region date format is same as the XLSX cell date format which the user sets.


Thanks & Regards,
Vikram

Hi Virkam,

Thanks for your posting and using Aspose.Cells.

It is not the issue of Aspose.Cells but it is the issue of Locale. You can see the same date is shown in UK and US Locale differently. It is because UK Locale is dd/MM/yyyy and US Locale date format is M/d/yyyy.

Here are the outputs of UK and US Locales

UK Locale: 02/12/1947
US Locale: 12/2/1947

Hi Shakeel Faiz,


Thanks for your response.

Yes your right it is problem with the locale.
Please provide an solution for this or let me know if there is any work around needs to be done to fix the issue so that it should support all the User custom date formats.


Thanks & Regards,
Vikram



Hi Vikram,

Thanks for your posting and using Aspose.Cells.

You will have to select a per-defined Locale. For example, if you want to support dd/MM/yyyy date format, you will have to select UK Locale or similar.

Also, you should search on internet if you can customize your Locale settings and specify your own custom date format.

I have also logged your comment in our database against this issue and we will let you know if such a thing is possible or not.