apache poi 从 excel date() 函数读取日期给出了错误的日期 [英] apache poi reading date from excel date() function gives wrong date

问题描述

我正在使用一个 xlsx 文件，该文件在一个单元格中包含一个 DATE(year, month, day) 公式.此单元格的格式为日期，因此 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?

找到了一种方法:

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

还是谢谢.

推荐答案

您所描述的一切都完全正确且符合预期

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.

这就是为什么当您使用 Apache POI 更改 Excel 文件时，您需要运行评估以更新所有这些缓存的公式值，以便在您转到该单元格之前在 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 中有一些特殊的公式函数，它们被定义为 volatile.这些函数每次总是返回不同的值，例如 DATE，对于这些 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 或 1/1/1904(取决于标志)以来的小数天数.-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

这篇关于apache poi 从 excel date() 函数读取日期给出了错误的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！