Incorrect date format when exporting to Excel via Aspose.Cells for Java

Aspose.Cells Product Family
1
Hi,

We have a problem when export csv file to xls/xlsx file, it is viewing the date as mm-dd-yyyy if the first two digits are 12 or less, but otherwise viewing them as dd-mm-yyyy. This stuffs up any sorting.

Here is our code to convert:
//Createing and TAB delimited LoadOptions object
LoadOptions loadOptions = new LoadOptions(FileFormatType.TAB_DELIMITED);
//Instantiating a Workbook object
Workbook workbook = new Workbook(myTemporaryFile,loadOptions);
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
//Auto row fit
worksheet.autoFitRows();
//Auto column fit
worksheet.autoFitColumns();
workbook.save(myFileName, SaveFormat.XLSX);
It works on JDK 1.7 for windows, but it seems it doesn't work on JDK1.7 for Ubuntu 12.04 LTS.

I've attached input and output files.

Kind regards,
Leo.

2

Hi,

Thanks for your posting and using Aspose.Cells for Java.

The date field sequence will be determined by the local settings when the actual value is ambiguous. Such as, for US locale, the default sequence is Month-Day-Year. For UK the sequence is Day-Month-Year. So you should set the specific locale to get the date string be parsed as desired, code like:

TxtLoadOptions opt = new TxtLoadOptions();
opt.setLocale(Locale.UK);

3

Hi,

Testing, we’ve found that the problem happens on both JDK1.6 and JDK1.7 when using Ubuntu 12.04 LTS.

The good news are: using the following code works for us:

//Createing and TAB delimited LoadOptions object
LoadOptions loadOptions = new LoadOptions(FileFormatType.TAB_DELIMITED);
// sets locale date
loadOptions.setLocale(Locale.UK);
loadOptions.setConvertNumericData(true);
//Instantiating a Workbook object
Workbook workbook = new Workbook(myFileName,loadOptions);

Thank you,
JP

4

Hi,


Thanks for your feedback.

Good to know that your issue is resolved. Feel free to contact us any time if you need further help or have some other issue, we will be happy to assist you soon.

Thank you.