Dear Aspose Support team,
I noticed that locale-dependent date formatting is not always preserved when converting from “XML Spreadsheet 2003” format to XLSX format using Aspose.Cells 8.6.2.
This is likely to be because I am not using Aspose correctly. I have searched the forums and found interesting related topics like :
Free Support Forum - aspose.comMy original issue was that dates that were in Japanese format yyyy/mm/dd would appear as US format mm/dd/yyyy after conversion. I tried several combination of settings on the default locale, the workbook opening options, and the workbook options. I ended up with code like follows :
Date and number format mismatch for non-English locale
Locale.setDefault(Locale.JAPAN);
LoadOptions loadOptions = new LoadOptions();
loadOptions.setRegion(CountryCode.JAPAN);
loadOptions.setLocale(Locale.JAPAN);
Workbook wb = new Workbook(is,loadOptions);
wb.getSettings().setRegion(CountryCode.JAPAN);
wb.save(outputFile, SaveFormat.XLSX);
With this, the files generated by Aspose correctly show the dates in yyyy/mm/dd format.
But, I then noticed that my files are actually using a locale-dependent date format just identified as “Short Date” in the “XML Spreadsheet 2003” file. And I found that :
- before conversion, the date is indeed locale-dependent : shows yyyy/mm/dd on a PC setup to a Japanese locale, and shows dd/mm/yyyy on a PC with a UK locale.
- after conversion, the date loses its locale-dependent property : it always shows as Japanese yyyy/mm/dd, even with a UK locale. And surprisingly, when opening on a JP locale, Excel reports that the date is locale-dependant (format shown with a star in the formatting dialog), but when opening the same file on a UK locale, the format is identified as a fixed custom date format to always show yyyy/mm/dd (I will attach screenshots).
I used Aspose.Cells for Java 8.6.2, Excel 2010, and I ran the conversion on windows 7 with Java 1.8.0. I also observed the same with files converted on a Unix server (that is using the C locale).
I attached my sample file as well as screenshots of the behavior I observe.
Did I use Aspose incorrectly ? Is there a way to preserve the locale-dependent behavior of a date format after saving with Aspose.Cells ?
Also, are you able to observe the same behavior by converting the sample and opening with a locale that does not use yyyy/mm/dd ? For me, the converted file shows yyyy/mm/dd anyway, while the original xml adapts to my current locale automatically.
Thanks and regards,
Aspose.Cells User.