JExcelAPI - date of writing to Excel worksheet ignores day, month and year

Question

I am trying to generate some Excel worksheets in a Java application using JExcelAPI (version 2.6.3) and cannot correctly create date cells. For example, for code:

WritableWorkbook workbook = null; workbook = Workbook.createWorkbook(new File("C:\\tmp\\tests.xls")); try { Date date = new Date(); final WritableSheet sheet = workbook.createSheet("Sheet", 0); DateTime dateTime = new DateTime(0, 0, date); sheet.addCell(dateTime); System.out.println("Date1 is " + date); final Calendar cal = Calendar.getInstance(); cal.set(Calendar.YEAR, 2007); cal.set(Calendar.MONTH, Calendar.OCTOBER); cal.set(Calendar.DAY_OF_MONTH, 17); cal.set(Calendar.HOUR_OF_DAY, 8); cal.set(Calendar.MINUTE, 15); date = cal.getTime(); dateTime = new DateTime(0, 1, date); sheet.addCell(dateTime); System.out.println("My birthday is on " + date); } finally { workbook.write(); workbook.close(); } 

Output (on the console):
Date1 - Mon Jun 08 11:14:45 GMT + 01: 00 2009
My birthday is Wed October 17, 08:15:45 GMT + 01: 00 2007

And in the Excel file the cells are 1900-01-00 10:14:46
1900-01-00 07:15:46

The time part in Excel is corrected in UTC, and the date part is discarded. Although the link mentions a problem with the time zone, it does not say anything about dropping dates. What am I doing wrong?

Answer 1

OK I get it. Creating a DateFormat

 DateFormat customDateFormat = new DateFormat ("dd MMM yyyy hh:mm:ss"); WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 

and pass it to the DateTime constructor

 DateTime dateTime = new DateTime(0, 0, date, dateFormat); 

corrects this. It seems that by default only part of the time is taken. Sorry for my stupidity.

Answer 2

POI is not the answer I would recommend. JExcel can manage it. I do not see where you set the type in this cell. Check out DateFormats .

The problem is the same if you are using Excel. If you enter a date in a cell that does not have this format, you will have unexpected behavior.