1

I am using POI-3.10.1 and Java 1.5 to generate excel files which contains date fields whose formats are
dd-mmm-yy.

Eg: 24-Nov-15

This is the sample code :

Workbook wb = new SXSSFWorkbook();
SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);

// Create date formatter
CellStyle dateCellStyle = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
short format = dataFormat.getFormat("dd-mmm-yy");
dateCellStyle.setDataFormat(format);

Row row = sheet.createRow(rowIndex);

Cell dateCell = row.createCell(columnIndex);
dateCell.setCellValue(dataValue);
dateCell.setCellStyle(dateCellStyle);

The problem is this, when I open and select a date field in the generated excel file in Windows, the format of the Cell is shown as Custom.

Following is the steps to recreate/get the issue in Windows:

  1. Select a date cell

enter image description here

  1. Right click on the cell and select Format Cells... option

enter image description here

  1. The opened window, the type of the cell is selected as Custom, even though there is a matching type under the Date formats enter image description here

Really appreciate if any one can give/show directions or a solution to show the this dd-mmm-yy formatted dates under Date field styles.

Shantha Kumara
  • 3,272
  • 4
  • 40
  • 52
  • See if this help you : http://stackoverflow.com/questions/5794659/poi-how-do-i-set-cell-value-to-date-and-apply-default-excel-date-format – Helping Hands Nov 24 '15 at 09:32
  • @HelpingHands thank you for the reply, but it didn't help. – Shantha Kumara Nov 24 '15 at 10:29
  • `dd-mmm-yy` is a custom date format though - the default date format changes the date/month order depending on locale, while yours always looks the same – Gagravarr Nov 24 '15 at 10:59

2 Answers2

2

Try:

short format = dataFormat.getFormat(DateFormatConverter.convert(Locale.ENGLISH, "dd-MMM-yy"));
agad
  • 2,192
  • 1
  • 20
  • 32
0

Just looking at my code for something similar I did a while back I'm creating date cells as numeric type. From what I recall this is how Excel represents dates internally (there is a date format, but no date "type").

From the documentation for createCell:

The cell that is returned is a Cell.CELL_TYPE_BLANK. The type can be changed either through calling setCellValue or setCellType.

You can also create a numeric cell using:

Cell dateCell = row.createCell(columnIndex, Cell.CELL_TYPE_NUMERIC);
robert
  • 4,612
  • 2
  • 29
  • 39