Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set Date format using Apache POI

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).

like image 641
sairajgemini Avatar asked Jan 14 '13 07:01

sairajgemini


People also ask

How do I find cell type date in POI?

setREPO_DATE(row. getCell(16). getDateCellValue()); it works fine if cell is formatted as date in excel.

How do you format a date?

Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.

How does Apache POI read date value in Excel?

If you know which cell i.e. column position say 0 in each row is going to be a date, you can go for row. getCell(0). getDateCellValue() directly. UPDATE: Here is an example - you can apply this in your switch case code above.

How use DataFormatter Apache POI?

DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale Cell cell = sheet. getRow(i). getCell(0); String j_username = formatter. formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell type.


1 Answers

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

like image 115
Gavin Xiong Avatar answered Sep 27 '22 17:09

Gavin Xiong