Problem with Custom Date formats cell of XLSX in Java

shakeel.faiz · January 5, 2015, 11:06am

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.

shakeel.faiz · January 6, 2015, 8:35pm

Hi Vikram,

Thanks for your using Aspose.Cells.

We are afraid we cannot find the way to modify the default formatting
rules of one specific Locale with Java’s formatting APIs. You have to
choose the Locale which can format datetime as what you expect.

VikramRaj · June 3, 2015, 12:22am

Hi Shakeel Faiz,

Is there any update for the issue?

I didn’t find any work around to fix this issue for all the possible scenarios.

Please let me know in case the issue is resolved,

shakeel.faiz · June 3, 2015, 8:09pm

Hi Vikram,

Thanks for your using Aspose.Cells.

You should change the Built-In date format to Custom format. Please see the following code, it changes the built-in date formats to custom formats.

Workbook wb = new Workbook(“example.xlsx”);

for(int i=wb.getCountOfStylesInPool()-1; i>-1; i–)

{

Style s = wb.getStyleInPool(i);

if(s != null && s.getNumber() == 14)

{

s.setCustom(“dd/MM/yy”);

}

wb.save(“res.pdf”);

Please understand the difference between Built-in formats and Custom formats.

Built-in
formats are represented by numbers and their display depends on the
User Date and Language settings. While custom formats are represented by
format string and they are independent of User Date and Language.

To explain the difference between them in detail, I have attached the MS-Word document with screenshots. Please download it and read it.

I have also attached the source excel file used in these screenshots for your reference.

VikramRaj · June 4, 2015, 5:10am

Hi Shakeel Faiz,

Thanks a lot for your response.

Yes your right, as you said the Built-in formats are depend on the User Date and Language setting,

In order to resolve this we need convert this to custom format in our code.

There are several built-in formats available, basis on what criteria we will have to convert ??,If it basis on Style.number then can you please provide me the list of numbers with corresponding date formats?

Thanks & Regards,

Vikram

babar.raza · June 4, 2015, 9:07am

Hi Vikram,

You can find the list of supported built-in number & date formats on the below linked article.

http://www.aspose.com/docs/display/cellsjava/Setting+Display+Formats+of+Numbers+and+Dates