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

enter image description here

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

enter image description here

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.

For your vlookup to work, the cells need to contain a time of 00:00, which you can do by rounding down the date (=ROUNDDOWN(CELL,0)). Changing their format (in Excel) to dd-mm-yy will not change their values (i.e. the time will be hidden but still there), not sure about POI. — assylias
So is there no way to do a VLOOKUP on a datetime? Also, if I replace the first parameter of the VLOOKUP with a cell reference on that column, it still didn't match? — ndtreviv
There is. But VLOOKUP will look for an exact match (if the last parameter is set to false) and your argument "2012/01/31" is not equal to "31/01/2012 14:34" so you need to adjust one of them. If the last parameter of VLOOKUP is true, then it will look for the closest match which might or might not be what you need. — assylias
Thank for your help! I sense this is the tip of the VLOOKUP iceberg! Thing is, when I do this: =VLOOKUP("31/01/2012 14:34", A2:G20, 7, FALSE) with either FALSE or TRUE at the end I get the #N/A error. Is that because the value it's looking for must match the cell format? Why wouldn't this work? — ndtreviv
You can for example use =VLOOKUP(DATE(2012,1,31)+TIME(14,34,0),A2:G20,7,FALSE) (assuming seconds are 0) or =VLOOKUP(H1,xxxx) assuming H1 contains the date+time you are looking for — assylias

Eric Nicolas Eric Nicolas · Accepted Answer · 2014-06-11T22:30:59

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();
        }
    }
}

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

1 Answers