How to correctly format a date cell and populate content using Apache POI 3.7

Question

Background:

I need to export a spreadsheet document with one column containing date formatted data.

I'm currently setting up the workbook style like so:

...
dateTimeStyle = workbook.createCellStyle();
//dateTimeStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
dateTimeStyle.setDataFormat((short)0x16);
...

and inserting the data into the cell/setting the format of the cell like so:

...    
if (Date.class.isAssignableFrom(o.getClass())) {

    Calendar cal = Calendar.getInstance();
    cal.setTime((Date) o);
    cell.setCellStyle(dateTimeStyle);
    cell.setCellValue(cal);
}
...

Note: According to BuiltinFormats documentation (http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html) 0x16 refers to the date format I'm trying to achieve.

The problem I have is that when I open the exported document in Microsoft Office Excel 2007, when I right-click the cell and choose Format cells... it shows the selected cell as having a custom format of dd/mm/yyyy hh:mm

Also, VLOOKUP operations do not work on the column (which I may, admittedly, be doing wrong):

I have a feeling this is due to a misunderstanding of how Excel stores and formats content, and would appreciate any help provided.

Question:

So, how do I correctly format/populate the cell so that Microsoft Excel treats it as a date and VLOOKUPs work etc?

Update: If I open the resulting file in Open Office Calc and choose Format Cells... the format shows up correctly as being Date. Starting to wonder, then, if this is an issue with the POI library and Excel 2007...

Many thanks.

Answer 1

If you want the dates in your excel to "behave" properly (including VLOOKUP, etc), you should write them as numeric and not as calendars.

Also when doing the Date -> Excel Double conversion be careful about setting the correct timezone to your Calendar object, otherwise the timezone offset to UTC will be added automatically and you will end up with datetimes in Excel different from the date times you thought you had in Java.

Finally note that your setDataFormat() uses 0x16 format id, when I think for standard date format it should be just plain 16 (decimal). See this tutorial for a list of valid formats.

See this small example which generates an Excel in which the lookup works just fine:

package test;

import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.TimeZone;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Main {

    // note: 2014/6/11 -> 41801 excel
    public static double date2double(Date date)
    {
        return date.getTime() / 1000.0 / 60.0 / 60.0 / 24.0 + 25568.0;
    }

    public static void main(String[] args) 
    {
        try 
        {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();

            CellStyle csDate = wb.createCellStyle();
            csDate.setDataFormat((short)16);

            Calendar cal = new GregorianCalendar(TimeZone.getTimeZone("UTC"));
            cal.set(2014, 6 - 1, 12, 0, 0, 0);
            cal.set(Calendar.MILLISECOND, 0);

            for(int i = 0; i < 10; ++i)
            {
                Row row = sheet.createRow(i);

                double d = date2double(cal.getTime());
                Cell cell = row.createCell(0);
                cell.setCellValue((int)d);
                cell.setCellStyle(csDate);

                cell = row.createCell(1);
                cell.setCellValue(i);

                cal.add(Calendar.DATE, 1);
            }

            FileOutputStream out = new FileOutputStream("/Users/enicolas/Downloads/test2.xls");
            wb.write(out);
            out.close();
        }
        catch (Throwable e) 
        {
            e.printStackTrace();
        }
    }
}