How do I set cell value to Date and apply default Excel date format?

Question

I've been using Apache POI for some time to read existing Excel 2003 files programmatically. Now I have a new requirement to create entire .xls files in-memory (still using Apache POI) and then write them to a file at the end. The only problem standing in my way is the handling of cells with dates.

Consider the following code:

Date myDate = new Date();
HSSFCell myCell;
// code that assigns a cell from an HSSFSheet to 'myCell' would go here...
myCell.setCellValue(myDate);

When I write the workbook containing this cell out to a file and open it with Excel, the cell is displayed as a number. Yes, I do realize that Excel stores its 'dates' as the number of days since January 1 1900 and that is what the number in the cell represents.

QUESTION: What API calls can I use in POI to tell it that I want a default date format applied to my date cell?

Ideally I want the spreadsheet cell to be displayed with the same default date format that Excel would have assigned it if a user had manually opened the spreadsheet in Excel and typed in a cell value that Excel recognized as being a date.

Answer 1

http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells

CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

Answer 2

To set to default Excel type Date (defaulted to OS level locale /-> i.e. xlsx will look different when opened by a German or British person/ and flagged with an asterisk if you choose it in Excel's cell format chooser) you should:

    CellStyle cellStyle = xssfWorkbook.createCellStyle();
    cellStyle.setDataFormat((short)14);
    cell.setCellStyle(cellStyle);

I did it with xlsx and it worked fine.

Answer 3

This example is for working with .xlsx file types. This example comes from a .jsp page used to create a .xslx spreadsheet.

import org.apache.poi.xssf.usermodel.*; //import needed

XSSFWorkbook  wb = new XSSFWorkbook ();  // Create workbook
XSSFSheet sheet = wb.createSheet();      // Create spreadsheet in workbook
XSSFRow row = sheet.createRow(rowIndex); // Create the row in the spreadsheet


//1. Create the date cell style
XSSFCreationHelper createHelper = wb.getCreationHelper();
XSSFCellStyle cellStyle         = wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("MMMM dd, yyyy")); 

//2. Apply the Date cell style to a cell

//This example sets the first cell in the row using the date cell style
cell = row.createCell(0);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

Answer 4

In addition to @BlondeCode answer, here are the list of all available formats that you can get with creationHelper.createDataFormat().getFormat((short) index)

0 = "General"
1 = "0"
2 = "0.00"
3 = "#,##0"
4 = "#,##0.00"
5 = ""$"#,##0_);("$"#,##0)"
6 = ""$"#,##0_);Red"
7 = ""$"#,##0.00_);("$"#,##0.00)"
8 = ""$"#,##0.00_);Red"
9 = "0%"
10 = "0.00%"
11 = "0.00E+00"
12 = "# ?/?"
13 = "# ??/??"
14 = "m/d/yy"
15 = "d-mmm-yy"
16 = "d-mmm"
17 = "mmm-yy"
18 = "h:mm AM/PM"
19 = "h:mm:ss AM/PM"
20 = "h:mm"
21 = "h:mm:ss"
22 = "m/d/yy h:mm"
23-36 = reserved
37 = "#,##0_);(#,##0)"
38 = "#,##0_);Red"
39 = "#,##0.00_);(#,##0.00)"
40 = "#,##0.00_);Red"
41 = "(* #,##0);(* (#,##0);(* "-");(@)"
42 = "("$"* #,##0_);("$"* (#,##0);("$"* "-");(@)"
43 = "(* #,##0.00_);(* (#,##0.00);(* "-"??);(@)"
44 = "("$"* #,##0.00_);("$"* (#,##0.00);("$"* "-"??);(@_)"
45 = "mm:ss"
46 = "[h]:mm:ss"
47 = "mm:ss.0"
48 = "##0.0E+0"
49 = "@"

And from index 164, there are your custom patterns

Answer 5

To know the format string used by Excel without having to guess it: create an excel file, write a date in cell A1 and format it as you want. Then run the following lines:

FileInputStream fileIn = new FileInputStream("test.xlsx");
Workbook workbook = WorkbookFactory.create(fileIn);
CellStyle cellStyle = workbook.getSheetAt(0).getRow(0).getCell(0).getCellStyle();
String styleString = cellStyle.getDataFormatString();
System.out.println(styleString);

Then copy-paste the resulting string, remove the backslashes (for example d/m/yy\ h\.mm;@ becomes d/m/yy h.mm;@) and use it in the http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells code:

CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy h.mm;@"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

Answer 6

I am writing my answer here because it may be helpful to other readers, who might have a slightly different requirement than the questioner here.

I prepare an .xlsx template; all the cells which will be populated with dates, are already formatted as date cells (using Excel).

I open the .xlsx template using Apache POI and then just write the date to the cell, and it works.

In the example below, cell A1 is already formatted from within Excel with the format [$-409]mmm yyyy, and the Java code is used only to populate the cell.

FileInputStream inputStream = new FileInputStream(new File("Path to .xlsx template"));
Workbook wb = new XSSFWorkbook(inputStream);
Date date1=new Date();
Sheet xlsMainTable = (Sheet) wb.getSheetAt(0);
Row myRow= CellUtil.getRow(0, xlsMainTable);
CellUtil.getCell(myRow, 0).setCellValue(date1);

WHen the Excel is opened, the date is formatted correctly.

Answer 7

This code sample can be used to change date format. Here I want to change from yyyy-MM-dd to dd-MM-yyyy. Here pos is position of column.

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class Test{ 
public static void main( String[] args )
{
String input="D:\\somefolder\\somefile.xlsx";
String output="D:\\somefolder\\someoutfile.xlsx"
FileInputStream file = new FileInputStream(new File(input));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
Cell cell = null;
Row row=null;
row=iterator.next();
int pos=5; // 5th column is date.
while(iterator.hasNext())
{
    row=iterator.next();

    cell=row.getCell(pos-1);
    //CellStyle cellStyle = wb.createCellStyle();
    XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    cellStyle.setDataFormat(
        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date d=null;
    try {
        d= sdf.parse(cell.getStringCellValue());
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        d=null;
        e.printStackTrace();
        continue;
    }
    cell.setCellValue(d);
    cell.setCellStyle(cellStyle);
   }

file.close();
FileOutputStream outFile =new FileOutputStream(new File(output));
workbook.write(outFile);
workbook.close();
outFile.close();
}}