16

I want to set date in date format in an Excel file with Apache POI. The value will set in such a manner so that in Address Bar it will show as mm/dd/YYYY and in cell it will show as dd-mmm (numeric day and month abbreviation: 01-Jan).

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
sairajgemini
  • 353
  • 1
  • 3
  • 11

1 Answers1

23

You can apply a CellStyle to the cell you need to fill. Here some code snippets from my past work, it's not intact but shows the basic idea:

Row row = sheet.createRow(0);
Cell cell = row.createCell((short) 0);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);

SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd");
Date cellValue = datetemp.parse("1994-01-01 12:00");
cell.setCellValue(cellValue);

//binds the style you need to the cell.
CellStyle dateCellStyle = wb.createCellStyle();
short df = wb.createDataFormat().getFormat("dd-mmm");
dateCellStyle.setDataFormat(df);
cell.setCellStyle(dateCellStyle);
    

More information about date format in JDK, you should read this: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
Gavin Xiong
  • 957
  • 6
  • 9
  • Thanks @Gavin. It helped me a lot. But I want to remove the timestamp from the date. Can you help me in this regard? – sairajgemini Jan 30 '13 at 12:44
  • What do you mean by "remove the timestamp from the date" ? @SaikatGupta – Gavin Xiong Jan 31 '13 at 01:59
  • Hi @Gavin: In the excel cell Date should show like "30-JAN" but if I select the cell, in address bar date should show like "1/30/2013" rather than "1/30/2013 12:01:00 AM" (without the timestamp) – sairajgemini Jan 31 '13 at 07:07
  • I changed the date format of the cell value to "yyyy-MM-dd", You can change the format according to your requirement, the format string is quite flexible. @SaikatGupta – Gavin Xiong Jan 31 '13 at 07:41
  • How do I tell it that I want it in UTC timezone? And where's the documentation for describing which characters I can use in the "getFormat" method? – Ring Mar 08 '21 at 18:26
  • @Ring Excel date formatting is covered [here](https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e) or within Excel's Help. – OrangeDog May 13 '21 at 11:17