How to set date field in excel using java apache poi library?

0
votes

I have to set the date field in excel file (.xlsx or .xls) using java apache poi library.

I have tried setting the cellStyle and dataFormat of the cell but it does not set it as date field and instead stores it as string or numeric field.

Here is my code:

XSSFWorkBook wb = new XSSFWorkBook();
CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-mm-yyyy")); // I have tried different formats here but none working
cell = row.createCell(1);
cell.setCellValue(new Date()); // does not store anything
//cell.setCellValue(new Date().getTime()); // stores the time in milliseconds
//cell.setCellValue(new SimpleDateFormat("dd-MM-yyyy").format(new Date()));
cell.setCellStyle(cellStyle);

I want to set the field as "dd-MM-yyyy" format in excel but it might not be defined in Apache POI Library BuiltinFormats in DataFormat.

I may be doing something wrong here, please suggest.

3
javaexceldateapache-poixssf
Did you forget to save the file afterwards? Only there's no save code in your snippet... – Gagravarr
I did save the file using the code FileOutputStream fileOut = new FileOutputStream("ooxml_dataFormat.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); – agrawalsp

3 Answers

1
votes

Dates in Excel are numeric, with a date formatting. So you need to format your cell accordingly:

Date date = ...
cellStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("dd-mm-yyyy"));
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(date);
cell.setCellStyle(cellStyle);
0
votes

you can try this approach

XSSFFont yourFont = (XSSFFont)workbook.CreateFont();
yourFont.FontHeightInPoints = (short)10;

XSSFCellStyle dateCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat dateDataFormat = (XSSFDataFormat)workbook.CreateDataFormat();
dateCellStyle.SetDataFormat(dateDataFormat.GetFormat(("dd-mm-yyyy"));
dateCellStyle.SetFont(yourFont);
sheet.SetDefaultColumnStyle(col, dateCellStyle);
0
votes

Old question but as I've just had a similar case, I can tell you, that your code snippet here should work.

I guess that your problem lies outside your displayed code... Do you use an excel template with existing column format or something that could overwrite your CellStyle?

My working code with a german date format:

CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();    
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd.MM.yyyy"));

... application of style to each cell with a java.util.Date object:

cell.setCellValue(date);  //java.util.Date date
cell.setCellStyle(cellStyle);