CODEWITHSUNDEEP
javaexceldatetimejexcelapi
Tadeusz Kopec for Ukraine
Tadeusz Kopec for Ukraine

Reputation: 12413

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

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?

Upvotes: 4

Views: 6719

Answers (2)

duffymo
duffymo

Reputation: 308948

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.

Upvotes: 0

Tadeusz Kopec for Ukraine
Tadeusz Kopec for Ukraine

Reputation: 12413

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.

Upvotes: 6

Related Questions