How to properly format a date cell and populate content with Apache POI 3.7

Question

Background:

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

I am currently customizing the style of the workbook as follows:

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

and inserting data into the cell / setting the cell format as follows:

 ... if (Date.class.isAssignableFrom(o.getClass())) { Calendar cal = Calendar.getInstance(); cal.setTime((Date) o); cell.setCellStyle(dateTimeStyle); cell.setCellValue(cal); } ... 

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

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

Also, VLOOKUP operations do not work on a column (which I admittedly do wrong):

I have a feeling that this is due to a misunderstanding of how Excel stores and formats content, and would be grateful for the help provided.

Question:

So, how to format / populate a cell correctly so that Microsoft Excel treats it as the date and work of VLOOKUP, etc.?

Update:. If I open the resulting file in Open Office Calc and select "Cell Format" ... the format displays correctly as Date. Afraid if this is a problem with the POI library and Excel 2007 ...

Many thanks.

Answer 1

If you want the dates in your Excel to be correct (including VLOOKUP, etc.), you should write them as numeric, not calendars.

Also, when performing a Date → Excel Double conversion, be careful with setting the correct time zone for your Calendar object, otherwise the time zone offset to UTC will be added automatically, and you will end up with datetimes in Excel other than the date you thought you had on Java

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

See this small example that Excel generates, in which the search works very well:

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