java - Apache poi date format

Question

Hi i am reading csv file and getting date in 01-01-2011 but i want it in 01-Jan-2011 format when i write .xlsx file using apache poi library. my code is

XSSFDataFormat df = workBook.createDataFormat();
cs.setDataFormat(df.getFormat("dd-MMM-yy"));

but it is not working for me. where am i doing mistake.

Answer 1

Not only do you need to create a cell format, but you also need to apply it to the cell!

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

// Get / Create our cell
XSSFRow row = sheet.createRow(2);
XSSFCell cell = row.createCell(3);

// Set it to be a date
Calendar c = Calendar.getInstance();
c.set(2012,3-1,18); // Don't forget months are 0 based on Calendar
cell.setCellValue( c.getTime() );

// Style it as a date
cell.setCellStyle(cs);

Secondly, you need to be aware that Java and Excel differ slightly in how they express Date formatting rules. You should open up a copy of Excel, format a sample cell how you want, then take a note of the formatting rules needed. In your case, you'd gone for a Java style upper case M, while in Excel it's lower case (see above)