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?

score 6 · Accepted Answer · answered Jun 08 '09 at 09:53

6

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.

answered Jun 08 '09 at 09:53

Tadeusz Kopec for Ukraine

12,283
6
56
83

Don't add a new answer. Edit your original. – duffymo Jun 08 '09 at 09:56
1

or accept this answer to make it clear you figured out the problem – matt b Jun 08 '09 at 13:01
Solved my problem to :-) Only thing I was doing different was I created the DateTime like 'new DateTime(0, 0, date)' so without the format. I afterwards added the format using the setCellFormat(..) function on the DateTime object. But that didn't work, passing it in via the constructor did. Thanks for this very helpful tip! :-) – dirkvranckaert Mar 08 '15 at 06:47

score 0 · Answer 2 · answered Jun 08 '09 at 09:55

0

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.

answered Jun 08 '09 at 09:55

duffymo

305,152
44
369
561

I created a WritableCell of type jxl.write.DateTime. I expected it to set the proper type (as jxl.write.Number sets type to number). But it seems that a DateFormat is also needed. I thought it only controlled display but it also affects which part of java.util.Date is written. – Tadeusz Kopec for Ukraine Jun 08 '09 at 10:47

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

2 Answers2