How to convert General cell format to Date format

Aspose.Cells Product Family
1

Hi Aspose Team,

I am trying to apply style on date column, but not getting as expected result.

	Workbook workbook = new Workbook();
	Worksheet sheet = workbook.getWorksheets().get(0);
	sheet.getCells().get("A1").putValue("12-10-2014");
	Style style;
	StyleFlag flag = new StyleFlag();
	style = sheet.getCells().getColumns().get(0).getStyle();
	style.setCustom("mm/dd/yyyy");
	flag.setNumberFormat(true);
	sheet.getCells().getColumns().get(0).applyStyle(style, flag);
	
	workbook.save("d:\\ASPOSE\\colnumberformat.xlsx");

Here I apply style “mm/dd/yyyy” on A1 cell but not getting result in that format.

Could you please update me ASAP.

thanks

2

Hi,


Thank you for contacting Aspose support.

You are not able to see the value in A1 (12-10-2014) in your desired format because the value is being considered as a text/string. Please use the following statement to insert the value in DateTime format and then you will be able to receive the expected results as attached.

Java

sheet.getCells().get(“A1”).putValue(“12-10-2014”, true);

Please feel free to write back in case you have any concerns or questions.
3

Thank you Raza for quick reply.

Actually I am importing csv file to excel by calling importCSV() method
destinationCells.importCSV( in, delimiter, false, destinationRange.getRow(), destinationRange.getColumn());

Here convertNumericData set to false as I need some column which is actually numeric but expected as text.

If my CSV file have date column which have format like mm/dd/yyyy, but if I import it using above statement inot excel its format get changed to dd-mm-yyyy.

If I apply manually format on that date column as like “mm/dd/yyyy”, its not get changed.

If I importCSV() usinf convertNumericData as true and then apply style on date column as “mm/dd/yyyyy” then it get expected output of date format like “mm/dd/yyyy”

Faced issue : importCSV(,false,) and CSV file have Date column then aplly style on date column “mm/dd/yyyy” not working as date column coming as text. No style get changed

4

Hi again,


Thank you for elaborating your scenario further.

In case of importCSV method, you have to set the convertNumericData to true in order to apply the required styling for the date columns. Alternatively, you can use the TxtLoadOptions class because it has the separate properties for numeric & date type data so you can set the both options separately. Please check the following piece of code that outputs the desired date format.

Java

TxtLoadOptions loadOptions = new TxtLoadOptions();
loadOptions.setConvertNumericData(false);
loadOptions.setConvertDateTimeData(true);

Workbook workbook = new Workbook(“D:/temp/sample.csv”, loadOptions);
Worksheet sheet = workbook.getWorksheets().get(0);

Style style;// = workbook.createStyle();
StyleFlag flag = new StyleFlag();
style = sheet.getCells().getColumns().get(0).getStyle();
style.setCustom(“mm/dd/yyyy”);
flag.setNumberFormat(true);
sheet.getCells().getColumns().get(0).applyStyle(style, flag);

workbook.save(“D:/temp/output.xlsx”, SaveFormat.XLSX);

Please feel free to write back in case you have any concerns.
5

Hi Raza,

Really thank you for guiding me.

Thanks,
Gauri

6

Hi Gauri,


You are most welcome. Please feel free to contact us back if you need our further assistance with Aspose APIs.