从Excel日期的Apache POI阅读date（）函数给出错误的日期 [英] apache poi reading date from excel date() function gives wrong date

问题描述

即时通讯使用一个包含日期（年，月，日）公式的单元格内XLSX文件。此单元格的格式日期，所以的Excel / OpenOffice的显示正确的日期。
例如单元格内容是 = DATE（2013; 1; 1）产生： 01 .01.2013
迄今为止 - 那么好

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.

在阅读本文件的POI库我做的：

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());

这会打印出：

Sun Dec 31 00:00:00 CET 1899

我使用POI 3.9。

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);
}

这会产生：

Tue Jan 01 00:00:00 CET 2013

不管怎样，谢谢。

Thanks anyway.

推荐答案

您所描述的一切是完全正确的和预期的

Everything you have described is entirely correct and expected

当Excel存储在细胞中的公式，它不仅存储公式本身（在.XLSX字符串，或在解析令牌形式的.xls），它还存储式的最后评价答案。这意味着，当Excel负荷，它不必磨去的年龄计算公式的结果显示，它可以只使它们中包含的任何其他小区中的最后一个值。

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.

这就是为什么当您更改您的Excel与Apache POI文件，然后你需要运行进行评估，以更新所有这些高速缓存的配方值，所以看起来就在Excel中去那个小区之前

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

不过，也有在Excel中一些特殊配方的功能，它被定义为波动。这些函数总是返回每一次不同的值，例如日期，并为这些Excel中只是写了一个虚拟的值（如0或-1）的单元格，重新评估它负载

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

当您阅读POI公式单元格的数值，它给你的缓存值回。如果你想POI评估公式，你需要把它问，因为你正在做

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

在Excel中的日期是因为1/1/1900或1904年1月1日（取决于标志）存储为小数天。值-1是1899年12月31日，所以这就是你所看到的当Excel写入-1，你要求它作为一个日期

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

这篇关于从Excel日期的Apache POI阅读date（）函数给出错误的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！