Apache POI Date format partially working

Question

I am using poi 3.8 to create excel sheet

I have some date columns which I am formitting with the below code

CreationHelper ch = wb.getCreationHelper();
short df = ch.createDataFormat().getFormat("MM/dd/yyyy");
System.out.println(df);
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(df);

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

    SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
    cell.setCellValue( sdf.parse("04/30/2013") );
    sheet.setColumnWidth(0, 6000);
    cell.setCellStyle(cs);

but the problem is that poi is not formating some of the date cells, instead of showing date in "MM/dd/yyyy" format it is showing date as number value like 451235

One more thing to mention here is that I am using pre created styles with setDataFormat(df) method call for date and default format for non-date columns

Have to check: Did you call setCellStyle on the Cell that needs to be formatted as a date? — rgettman
If you read the formatting rules for the cell back using POI, does POI see the correct formatting? — Gagravarr
One thing that I have observed so far is that poi does not apply format if Date cell is aligned e.g if cellStyle.setAlignment is called on cell cellStyle.setDataFormat is ignored — Prince Charming

IcedDante IcedDante · Accepted Answer · 2020-08-11T03:27:00

Calls to .setCellStyle(...) are not additive. That is, if you apply a Date format style:

cell.setCellStyle(cs);

and then apply some other style:

cell.setCellStyle(thickBorderStyle)

you will be removing your date format

Apache POI Date format partially working

1 Answers