15

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.

Lalit Chattar
  • 1,914
  • 8
  • 27
  • 49

1 Answers1

23

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)

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • 2
    You should use org.apache.poi.ss.util.DateFormatConverter to convert the formatter in Java style into a formatter in Excel style by calling DateFormatConverter.convert(Locale.getDefault(), "d-MMM-yy"): https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/DateFormatConverter.html – gouessej Jan 15 '19 at 13:14