Updating value in Cell to DateTime does not change Celltype

Aspose.Cells Product Family
1

Hi,


I am trying to update a cell value to DateTime value. I am using the following code:

Workbook workbook = new Workbook(“test.xlsx”);
Worksheet ws = workbook.getWorksheets().get(0);
Cells cells = ws.getCells();
Cell cell = cells.get(“A1”);
System.out.println("Cell content: " + cell.getStringValue() + ", Cell type is: " + cell.getType());
cell = cells.get(“A2”);
cell.setValue(“01/01/09”);
System.out.println("Cell content: " + cell.getStringValue() + ", Cell type is: " + cell.getType());
cell = cells.get(“B1”);
cell.setValue(new DateTime(2009,1,1));
System.out.println("Cell content: " + cell.getStringValue() + ", Cell type is: " + cell.getType());

Output is:
Cell content: 01/01/09, Cell type is: 1
Cell content: 01/01/09, Cell type is: 5
Cell content: 39814, Cell type is: 4

According to http://www.aspose.com/docs/display/cellsjava/Adding+Data+to+Cells, cell.setValue(new DateTime(2009,1,1)) must change CellType to DateTime but it is not changed.

Can you please provide a possible solution to add DateTime data to cell.

Thanks,



2

Hi Tarun,

Thanks for your posting and using Aspose.Cells.

Dates are stored as numbers inside the Excel file. To make them appear as Dates, you will have to set the Cell Style.setNumber to number 14 and then type of cell will change to CellValueType.IS_DATE_TIME

Please see the following sample code and its console output for your reference.

Java


Workbook workbook = new Workbook();


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

Cells cells = ws.getCells();

Cell cell = cells.get(“A1”);

System.out.println("Cell content: " + cell.getStringValue() + ", Cell type is: " + cell.getType());


cell = cells.get(“A2”);


//Pass true as a parameter, so that this string is converted to date

cell.putValue(“01/01/09”, true);


//Dates are stored as numbers, to make it date time, set its style

//Number 14 means, it is Date Time

Style style = cell.getStyle();

style.setNumber(14);

cell.setStyle(style);


//Now print its contents

System.out.println("Cell content: " + cell.getStringValue() + ", Cell type is: " + cell.getType());


cell = cells.get(“B1”);

cell.putValue(new DateTime(2009, 1, 1));


style = cell.getStyle();

style.setNumber(14);

cell.setStyle(style);


System.out.println("Cell content: " + cell.getStringValue() + ", Cell type is: " + cell.getType());

Console Output:
Cell content: , Cell type is: 3
Cell content: 1/1/2009, Cell type is: 1
Cell content: 1/1/2009, Cell type is: 1