Applying Custom Date Format in Excel worksheet using Aspose.Cells for Java

[sivasankaris]

Hi Aspose Team,

I am using aspose cells version 8.1.2. I tried to apply custom date format “MM/DD/YYYY” with below code. In open office I am getting correct format. But in excel I am getting this format

MM-DD-YYYY. Can you please help in this as it is urgent.

Workbook wb = new Workbook(“C:\siva\input\format checking.xlsx”);

Worksheet ws = wb.getWorksheets().get(2);

int styleIdx = wb.getStyles().add();

Style style = wb.getStyles().get(styleIdx);

Range r = ws.getCells().createRange(6, 5, ws.getCells().getMaxDataRow()+1, 1);

style.setCustom(“MM/DD/YYYY”);

r.setStyle(style);

Thanks,

Sivasankari.

[babar.raza]

Hi Sivasankari,

Thank you for contacting Aspose support.

As you are using a quite old revision of the API, and you are able to see the correct results in Open Office but not in Excel application, it could be due to some bug in the API. Please try the case against the latest version of Aspose.Cells for Java 17.02.0 and following piece of code.

Java

Workbook wb = new Workbook(dir + “book1.xlsx”);
Worksheet ws = wb.getWorksheets().get(0);
Style style = wb.createStyle();
Range r = ws.getCells().createRange(0, 0, 2, 2);
style.setCustom(“MM/DD/YYYY”);
r.setStyle(style);
wb.save(dir + “output.xlsx”);

[sivasankaris]

Hi Babar Raza,

Thanks for your support. I downloaded latest version and pointed to latest aspose cells version 17.02.0 and modified the code as below

Workbook wb = new Workbook(“C:\siva\input\format checking.xlsx”);

Worksheet ws = wb.getWorksheets().get(2);

Style style = wb.createStyle();

Range r = ws.getCells().createRange(6, 5, ws.getCells().getMaxDataRow()+1, 1);

style.setCustom(“MM/DD/YY”);

r.setStyle(style);

For testing purpose I tried this code with custom formats “MM/DD/YY” and “YYYY/MM/DD”. Still I am having the same issue. For example

Open office displaying as expected format. Excel displaying like MM-DD-YY and YYYY-MM-DD.

Can you please help.

Thanks and Regards,

Sivasankari

[babar.raza]

Hi Sivasankari,

Thank you for your feedback.

Please share the input spreadsheet “format checking.xlsx” and the locale/region of your machine. I have attached a snapshot showing the region of my machine.

[sivasankaris]

Hi Babar Raza,

Please find the below details

Default Locale : en_IN

With Default Date Pattern

Date Format: 28/2/17 6:25 PM

Localized Pattern: d/M/yy h:mm a

Pattern: d/M/yy h:mm a

With specific Date Pattern

Date Format: 2017/Feb/28

Localized Pattern: yyyy/MMM/dd

Pattern: yyyy/MMM/dd

We tried that with below code.

System.out.println(Locale.getDefault().toString());

Date aDate= new Date();

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());

Please find the attached input and java code

Thanks and Regards

Sivasankari

[babar.raza]

Hi Sivasankari,

Thank you for sharing the samples.

I have reevaluated the presented scenario while using the latest version of Aspose.Cells for Java 17.02.3 (attached) and the code from the recently shared archive. I am afraid, I am not able to replicate the said issue even though I have changed the locale of my machine to English - India. As you can see from the attached spreadsheets that the custom patterns such as MM/DD/YYYY & YYYY/MMM/DD have been correctly applied to the contents in column B. Please note, the aforementioned release has been published recently so I believe you haven’t yet checked the scenario against 17.02.3. Could you please give a try to the latest version on your end as well? In case the problem persists, please share the resultant spreadsheet along with more details about your environment. There could be some settings that we haven’t mimicked so far and therefore getting different results.

[sivasankaris]

Hi Babar Raza,

Thanks for your reply. Let me explain more. In your attached screen itself I able to see hyphen ‘-’ (‘MM-DD-YYYY’) instead of slash ‘/’ (In the code I applied format with slash ‘MM/DD/YYYY’ not with hyphen ‘MM-DD-YYYY’). When I opened your output in openoffice I able to see correct output with slash. Please find the attached screenshot taken by opening open office which is showing slash which is our expectation.

Can you please help how to display Slash instead of Hyphen in Excel with aspose cells API.

Thanks,

Sivasankari

[babar.raza]

Hi Sivasankari,

Please accept my apology for missing out that detail. Please note, this is the behaviour of Excel application that it changes the custom date separator to the default separator according to the locale of the machine (slash is replaced by hyphen in this case). You can avoid this situation by escaping the slash character while setting the custom format. Please check the following piece of code and its resultant spreadsheets as attached.

Java

Workbook wb = new Workbook(dir + “format checking with aspose.xls”);
Worksheet ws = wb.getWorksheets().get(0);
Style style = wb.createStyle();
Range r = ws.getCells().createRange(0, 1, ws.getCells().getMaxDataRow()+1, 1);
///style.setCustom(“yyyy\/mm\/dd”);
style.setCustom(“mm\/dd\/yyyy”);
r.setStyle(style);
///wb.save(dir + “yyyymmdd.xls”);
wb.save(dir + “mmddyyyy.xls”);

[sivasankaris]

Hi Bapar Raza,

Thank you very much. We are getting expected format MM/DD/YYYY in excel also.

Thanks and Regards,

Sivasankari.

[babar.raza]

Hi Sivasankari,

Thank you for the confirmation on proposed solution. It is good to know that you are up & running again. Please feel free to contact us back in case you need our further assistance with Aspose APIs.