JExcelAPI - writing date to Excel sheet ignores day, month and year.

Question

I try to generate some Excel sheets in Java application using JExcelAPI (v. 2.6.3) and can't generate date cells properly. 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();
 }

The output (on console) is:
Date1 is Mon Jun 08 11:14:45 GMT+01:00 2009
My birthday is on Wed Oct 17 08:15:45 GMT+01:00 2007

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

The time part in Excel is corrected to UTC and the date part is discarded. While the reference mentions time zone problem, it says nothing about discarding dates. What am I doing wrong?

Answer 1

http://stackoverflow.com/questions/861877/reading-date-values-from-excel-cell-using-poi-hssf-api

have a look at the above related question too. I'd the same problem with POI library and that was the solutions suggested at this forum.

Answer 2

OK. I figured it. Creating DateFormat

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

and passing it to DateTime constructor

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

fixes it. It seems that by default only time part is taken. Sorry for my dumbness.

Answer 3

POI is not the answer I'd recommend. JExcel can manage it. I don't see where you've set the type on that cell. Have a look at DateFormats.

The problem is the same if you're using Excel. If you enter a Date into a cell that doesn't have that format set you'll have unexpected behavior.