Hi Team
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);
23/12/2014
Hi Shakeel Faiz.
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,
workbook.getSettings().setRegion(CountryCode.INDIA);
Cell cell = workbook.getWorksheets().get(0).getCells().get(“A1”);
String str=cell.getStringValue();
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,
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,
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,
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());
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,
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());
02/12/1947
Hi Shakeel Faiz,
String filePath = “D:\test.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
Cell cell = worksheet.getCells().get(“A1”);
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,
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.