How to set date as type date in excel with apache poi?

Question

I am using apache poi 3.8 to create an excel file. This excel file needs to contain some dates.

I am trying to write a date to the excel file with as format the excel type "date". But i always get a type "custom". I need to use the the type "date", so it will be localized based on the users settings.

I have tried the following:

Apache poi date format

Apache POI localized Date into Excel cell

But it doens't work.

This is the code that I have:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");

XSSFDataFormat df = wb.createDataFormat();
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(df.getFormat("d-mmm-yy"));

XSSFCell cell = sheet.createRow(0).createCell(0);

Calendar c = Calendar.getInstance();
c.set(2012,3-1,18);
cell.setCellValue( c.getTime() );

cell.setCellStyle(cs);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("c:\\temp\\dates-sworkbook.xlsx");
wb.write(fileOut);
fileOut.close();

Which format should I use?

Thanks

Answer 1

Date is one of the special, built in formats. If you want to use that one explicitly, then you need to pick it explicitly. (Almost all formats used in Excel are custom ones, that render how you specify, but there are a few special ones)

POI has the full list of special, built in formats (which is much smaller than the list of formats in the dropdowns in recent copies of Excel!) in BuiltinFormats. If you make sure you set one of those, and not your own custom format string, it should behave exactly as you expect

Answer 2

Don't really know if you solved this already or not but here's a code that would solve it. It actually solved my problem.

CreationHelper createHelper = wb.getCreationHelper();
...
cs.setDataFormat(createHelper.createDataFormat().getFormat("yourformat"));

Taken from http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells