Issue in setConvertDateTimeData

Aspose.Cells Product Family
1

Hello,

I use txtLoadOptions.setConvertDateTimeData(true) for converting any string into date. It converts some date formats to date and some of the date formats remains same as string or some times as numbers.

Kindly refer the attached document, It contains 20 date columns. In that, the columns Date18, Date19, Date20 is in date type. other columns(Date1- Date17) is not in date type.

Is there a way to set these type of date formats as date type in xls which is converted from csv.

Thanks in advance.

Regards,
Farhana Y

2

Hi Farhana,

Thanks for your posting and using Aspose.Cells.

As a workaround, you can apply built-in Date format to a range of cells to make the strings as date values using Rang.applyStyle() method. Please see the following code. It applies Date format to range A2:T11.

I have attached the output xls file generated by the code for your reference.

Java


Workbook workbook = new Workbook(“date_with_time.xls”);


Worksheet worksheet = workbook.getWorksheets().get(0);


//Create style with Date format

//14 is Built-in Date Format

Style style = workbook.createStyle();

style.setNumber(14);


//Create style flag, we only want to set number format

StyleFlag sf = new StyleFlag();

sf.setNumberFormat(true);


//Create your desired range and apply style to it

Range range = worksheet.getCells().createRange(“A2:T11”);

range.applyStyle(style, sf);


workbook.save(“output.xls”);

3

Hello Shakeel,

Thank you for your quick response,

I referred the file output.xls

In that, the columns Date18, Date19, Date20 has changed to the build in date format (31/12/1999).
and they are displayed as the given build in date format (31/12/1999). Where as, the columns (Date1 to Date17) is not displayed in the build in date format (31/12/1999). It remains same as my sample file “date_with_time.xls”.

But when i see the format cells option of those cells(columns Date1 to Date17). It is in the category as “Date” and the format as “31/12/1999”. why they are not displayed in this “31/12/1999” format?

Thanks in advance

Regards,
Farhana Y




4

Hi Farhana,

Thanks for your posting and using Aspose.Cells.

Not all strings could be properly converted to date time. Could you provide us your csv file and the sample code you are using to convert your csv into xls? We will investigate your issue further.

Kindly also download and try the latest version: Aspose.Cells for Java 8.1.0 and see if it makes any difference.