POI - Problems converting between Excel & Java date & vice-versa

Question

I'm trying to save a time in a cell in an Excel workbook using Apache POI (4.0.1).

I'm converting the time to a java.util.Date and then attempting to use the org.apache.poi.ss.usermodel.DateUtil.getExcelDate method to then save that in the cell.

What I'm finding is that as the time is in the same year as the epoch (1899), there is a hardcoded check in getExcelDate that prohibits conversion of dates with years before 1900, preventing me from doing what I was after.

Example application:

import java.util.Date;
import java.util.stream.DoubleStream;
import org.apache.poi.ss.usermodel.DateUtil;

public class POIQuery {
    public static void main(final String[] args) {
    final String format = "%-20s%-35s%-20s%n"; //$NON-NLS-1$

    System.out.format(format, "Input Excel Date", "Java Date", "Converted Excel Date");

    DoubleStream.of(0, 0.5d, 1.5d).forEach(excelDate -> {
        final Date date = DateUtil.getJavaDate(excelDate);
        System.out.format(format, excelDate, date, DateUtil.getExcelDate(date));
    });
    }
}

Output (note, as per the documentation - a "Converted Excel Date" of "-1" indicates an error).

Input Excel Date    Java Date                          Converted Excel Date
0.0                 Sun Dec 31 00:00:00 GMT 1899       -1.0                
0.5                 Sun Dec 31 12:00:00 GMT 1899       -1.0                
1.5                 Mon Jan 01 12:00:00 GMT 1900       1.5

Here you can see I'm asking for the Java Date representing the Excel date "0.5". I'm then taking the Java date provided, but POI says that there is no Excel date corresponding to that - even though I've generated it from that.

Am I missing something...?

Axel Richter Axel Richter · Accepted Answer · 2020-03-06T17:13:54

Excel date values in 1900 date system start with value 0 = day 0 in January 1900. There are no Excel date values before that.

If you have following cells in Excel:

     A                  B
1  Value      Excel date
2      0      01/00/1900 00:00:00
3    0.5      01/00/1900 12:00:00
4      1      01/01/1900 00:00:00
5    1.5      01/01/1900 12:00:00

where the values in column B are the same values as in column A but formatted as dates using number format MM/DD/YYYY hh:mm:ss, you see that.

So there is no Excel date December 31 1899. That's why DateUtil.getExcelDate(date) fails when date is the Java date Sun Dec 31 00:00:00 GMT 1899 or Sun Dec 31 12:00:00 GMT 1899.

But DateUtil.getJavaDate(excelDate) returns December 31 1899 when excelDate is a double value from 0.0 to 0.999.... That is because there is no Java date January 0 1900 possible. But there are Excel date values possible from 0.0 to 0.999....

In other words, the Excel date values from 0.0 to 0.999... are in reality time values before the first possible whole day in Excel, which is date January 01 1900. They not really are in a day but are in day 0 instead.

Since date values from 0.0 to 0.999... and converted Java dates of December 31 1899 are special cases, one could handling them as such.

By extending your code:

import java.util.Date;
import java.util.Calendar;
import java.util.stream.DoubleStream;
import org.apache.poi.ss.usermodel.DateUtil;

public class POIQuery {
    public static void main(final String[] args) {
    final String format = "%-20s%-35s%-20s%n"; //$NON-NLS-1$

    System.out.format(format, "Input Excel Date", "Java Date", "Converted Excel Date");

    DoubleStream.of(0, 0.5d, 1d, 1.5d, 44000d, 44000.5d).forEach(excelDate -> {
        final Date date = DateUtil.getJavaDate(excelDate);
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        double excelDateFromDate = -1d;
        if (cal.get(Calendar.YEAR) == 1899 && cal.get(Calendar.MONTH) == 11 && cal.get(Calendar.DATE) == 31) {
         cal.add(Calendar.DATE, 1);
         excelDateFromDate = DateUtil.getExcelDate(cal.getTime());
         excelDateFromDate -= 1d; 
        } else {
         excelDateFromDate = DateUtil.getExcelDate(date);
        }
        System.out.format(format, excelDate, date, excelDateFromDate);
    });
    }
}

If the converted Java date is December 31 1899 and we know that this was converted from Excel, then while reconverting it to Excel date value, first add one day before using DateUtil.getExcelDate. So DateUtil.getExcelDate converts from January 01 1900. Then subtract 1 from the result of DateUtil.getExcelDate.

POI - Problems converting between Excel & Java date & vice-versa

1 Answers