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

score 10 · Answer 1 · answered Jun 29 '12 at 10:54

10

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

answered Jun 29 '12 at 10:54

Gagravarr

47,320
10
111
156

3

I thought "d-mmm-yy" was a built in format. But for some reason this format doesn't work. When I use "m/d/yy" it works as expected. – cremersstijn Jun 29 '12 at 11:20
1

Nope, d-mmm-yy is a European style format. That's what things like the built in (American style) ones looks like when you display it in Europe! – Gagravarr Jun 29 '12 at 12:17
Actually, it's not true. I specified format 22 (0x16, "m/d/yy h:mm"). But it's shown as "Custom". – Alexey Oct 16 '15 at 11:19

score 4 · Answer 2 · answered Apr 15 '13 at 11:37

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

score 0 · Answer 3 · answered Nov 15 '17 at 09:19

0

I understand your problem. I resolved using DateUtil of Apache Poi.

Cell cell = yourRow.createCell(yourIndex);
cell.setCellValue(DateUtil.getExcelDate(yourDate);

Enjoy !!

answered Nov 15 '17 at 09:19

Marcus

80
6

score 0 · Answer 4 · answered Nov 30 '17 at 21:11

It is resolved using below:

DataFormatter fmt = new DataFormatter();
Object Excel1data=Excel.readExcel(Excel1, "Sheet3", rownumber, columnnumber1);
            String valueAsInExcel1 = fmt.formatCellValue((Cell) Excel1data);

            Object Excel2Data=Excel.readExcel(Excel2, "Sheet2", getrownumber, columnnmumber2);
            String valueAsInExcel2 = fmt.formatCellValue((Cell) Excel2Data);

            org.testng.Assert.assertEquals(valueAsInExcel1, valueAsInExcel2,"Incorrect data in RowNumber:"+getrownumber+" ColumnNumber:"+columnnmumber2);
            columnnumber1++;

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

4 Answers4