apache poi reading date from excel date() function gives wrong date

Question

Im using an xlsx file that contains a DATE(year, month, day) formula within a cell. This cell is formatted as date, so Excel/OpenOffice shows the proper date. e.g. the cell content is '=DATE(2013;1;1)' which produces : '01.01.2013' So far - so good.

When reading this file with the poi library I do:

XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File("/home/detlef/temp/test.xlsx")));
XSSFSheet sheet = workbook.getSheet("Sheet A");
XSSFCell cell = sheet.getRow(0).getCell(0);
System.out.println(cell.getDateCellValue());

This will print out:

Sun Dec 31 00:00:00 CET 1899

I am using POI 3.9.

Can anybody tell me why this happens?

Found a way to do it:

if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
   FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
   CellValue evaluate = evaluator.evaluate(cell);
   Date date = DateUtil.getJavaDate(evaluate.getNumberValue());
   System.out.println(date);
}

That produces:

Tue Jan 01 00:00:00 CET 2013

Thanks anyway.

score 5 · Accepted Answer · answered Oct 17 '13 at 09:55

Everything you have described is entirely correct and expected

When Excel stores a formula in a cell, not only does it store the formula itself (either a string in .xlsx, or in parsed token form for .xls), it also stores the last evaluated answer of that formula. This means that when Excel loads, it doesn't have to grind away for ages calculating the formula results to display, it can just render them with the last value like any other cell.

This is why when you make changes to your excel file with Apache POI, you then need to run an evaluation to update all those cached formula values, so it looks right in Excel before you go to that cell

However, there are a few special formula functions in Excel, which are defined as volatile. These are functions which always return a different value every time, for example DATE, and for these Excel just writes a dummy value (eg 0 or -1) to the cell, and re-evaluates it on load

When you read the numeric value of a formula cell in POI, it gives you the cached value back. If you want POI to evaluate the formula, you need to ask it to, as you're doing

Dates in Excel are stored as fractional days since 1/1/1900 or 1/1/1904 (depending on a flag). A value of -1 is the 31st of December 1899, so that's what you see when Excel writes -1 and you request it as a date

apache poi reading date from excel date() function gives wrong date

1 Answers1

Linked