How can I set aspose cells to take the locale for date format from OS?

Aspose.Cells Product Family
1

I am creating an excel with Aspose. I would like that the date will be formatted according to the OS's Locale, meaning that if the excel will be open in US or Europe the dates will appear in a different format, like if you select the format with asterisk in excel (see attached file).

This is my code:

style.setCustom(????);
cell.setStyle(style);

String date =“2/23/2015”;

DateTime myDate = new DateTime(new Date(date));

cell.setValue(myDate);

How shall I set the cell style?

thanks :slight_smile:

2

Hi Daniel,

Thank you for contacting Aspose support.

In order to make the format adaptable to the operating system’s locale, you have to use the built-in number formats. Please review the detailed article on setting date & number formats using Aspose.Cells for Java APIs and check the built-in number formats from 14 to 17 for date literals.

Java

Workbook book = new Workbook();
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
Style style = book.createStyle();
//style.setCustom(“m/d/yyyy”);
style.setNumber(14);
Cell cell = cells.get(“A1”);
cell.setStyle(style);

String date = “2/23/2015”;
DateTime myDate = new DateTime(new Date(date));
cell.setValue(myDate);
book.save(dir + “output.xlsx”);
3

Hi thank you.

For dates type is works great but I also have dateTime columns.
Do you have a solution for them?
thanks,
Shirly
4

Hi Shirly,


Thank you for the confirmation on previously mentioned solution. Regarding your recent concerns, if you check the article referenced in my previous response, you will notice there aren’t many built-in formats to handle Data as well as Time. However, you may try the same code (provided previously) with number format 22 for columns containing DateTime values.
5

Hi,

I tried to use with number format 22 but I when I check the cell format in excel, the format was set to custom and not as I expected, here is the code I used:
Style style = cell.getStyle();
style.setNumber(22);
cell.setStyle(style);

LocalDateTime local = ((DateTimeValue) value).getLocalDateTime();
ZonedDateTime zdt = local.atZone(DateTimeUtils.utcZoneId);
Date output = Date.from(zdt.toInstant());
cell.setValue(output);

Do you have any idea why the style is set for custom and not for Time format as expected?

thanks,
Shirly
6

Hi Shirly,


Thank you for writing back.

You are correct, setting the number format to 22 shows the format in custom section of Format Cell dialog, however, the format is locale driven, that means; it will adapt according to the regional formats. You can test this by opening the attached spreadsheet in Excel by first changing the region format of your machine (Control Panel\Clock, Language, and Region\Change date, time, or number formats).

Please note, I was not able to figure out the packages for DateTimeValue & DateTimeUtils classes from your code therefore I have used an existing spreadsheet with DateTime value to test the scenario.

Java

Workbook book = new Workbook(dir + “book1.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
Style style = book.createStyle();
style.setNumber(22);
Cell cell = cells.get(“A1”);
cell.setStyle(style);
book.save(dir + “output.xlsx”);
7

Thanks for your help, it works

8

Hi Shirly,

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.