如何使用 Apache POI 的公式从 Excel 中检索日期 [英] How to retrieve the Date from Excel with Formula by Apache POI

问题描述

我有一个 Excel 工作表，其中的日期单元格在 Excel TODAY() + 1 中指定了日期公式.所以基本上今天它默认显示为 03/10/2018.我创建了一个代码来从 Excel 中读取数据，其中包含公式，但是当我得到日期时，它会有所不同.

代码:

 Cell c = CellUtil.getCell(r, columnIndex);CellType 类型 = c.getCellType();如果(类型== CellType.FORMULA){开关 (c.getCachedFormulaResultType()) {案例数字:如果 (DateUtil.isCellDateFormatted(c)) {value = (new SimpleDateFormat("dd-MM-yyyy").format(c.getDateCellValue()));数据添加(值)；//日期应该显示 03-10-2018 但它显示的是 23-01-2018} 别的 {值 = (c.getNumericCellValue()) + "";数据添加(值)；}休息;案例字符串:value = c.getStringCellValue();data = new LinkedList (Arrays.asList(value.split(";")));休息;}}

我不知道为什么它会显示从一月开始的日期并应用了TODAY() + 1

与此类似的另一个函数 TODAY() + 15 返回 22-04-2018.

解决方案

如中所述公式评估:

<块引用>

Excel 文件格式(.xls 和 .xlsx)存储缓存"结果对于每个公式以及公式本身.这意味着当文件打开后，可以快速显示，无需花很长时间计算所有的公式结果.它也是意味着当通过Apache POI读取文件时，结果是您也可以快速使用！"

因此，所有公式都将缓存上次评估的结果.这是上次在 Excel 中打开、重新计算和保存工作簿的时间，或者是上次在 Excel 之外进行评估的时间.

因此，如果具有公式 =TODAY() 的单元格存储了 22-01-2018 的缓存结果，则工作簿将在 2018 年 1 月 22 日进行评估最后一次.

要始终获得最新的公式结果，您需要先首先评估公式读.最简单的方法:

<预><代码>...workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();...

或者您正在使用 DataFormatter 连同 FormulaEvaluator:><预><代码>...DataFormatter formatter = new DataFormatter();FormulaEvaluator 评估器 = workbook.getCreationHelper().createFormulaEvaluator();...细胞单元格 = CellUtil.getCell(...);...String value = formatter.formatCellValue(cell, evaluator);...

I have an Excel Sheet where the Date Cell is assigned with the Date Formula in Excel TODAY() + 1. So basically today it's showing as 03/10/2018 by default. I've created a code to read the data from Excel which has the formula in it but when I'm getting the date it's coming differently.

Code :

  Cell c = CellUtil.getCell(r, columnIndex);
  CellType type = c.getCellType();
      if (type == CellType.FORMULA) {
          switch (c.getCachedFormulaResultType()) {
                 case NUMERIC:
                      if (DateUtil.isCellDateFormatted(c)) {
                          value = (new SimpleDateFormat("dd-MM-yyyy").format(c.getDateCellValue()));
                          data.add(value); // Date should display 03-10-2018 but it's showing 23-01-2018
                      } else {
                          value = (c.getNumericCellValue()) + "";
                          data.add(value);
                      }
                  break;
                  case STRING:
                           value = c.getStringCellValue();
                           data = new LinkedList <String>(Arrays.asList(value.split(";")));
                   break;
              }
      }

I don't know why it's showing date from January with the formula applied TODAY() + 1

Similar to this another function TODAY() + 15 returning the 22-04-2018.

解决方案

As stated in Formula Evaluation:

"The Excel file format (both .xls and .xlsx) stores a "cached" result for every formula along with the formula itself. This means that when the file is opened, it can be quickly displayed, without needing to spend a long time calculating all of the formula results. It also means that when reading a file through Apache POI, the result is quickly available to you too!"

So all formulas will have cached results stored from the last time they were evaluated. This is either the last time the workbook was opened in Excel, recalculated and saved or from the last time an evaluation was be done outside of Excel.

So if a cell having the formula =TODAY() has a cached result of 22-01-2018 stored, then the workbook was evaluated on January 22, 2018 the last time.

To get always current formula results you need evaluating the formulas first before reading. Simplest way:

...
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
...

Or you are using a DataFormatter together with a FormulaEvaluator:

...
DataFormatter formatter = new DataFormatter(); 
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
...
Cell cell = CellUtil.getCell(...);
...
String value = formatter.formatCellValue(cell, evaluator);
...

这篇关于如何使用 Apache POI 的公式从 Excel 中检索日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！