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

问题描述

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

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.

代码:

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

我不知道为什么使用公式TODAY() + 1

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

类似于另一个函数TODAY() + 15，返回22-04-2018.

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

推荐答案

如中所述公式评估:

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

"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!"

因此，所有公式都将从上一次对其进行评估以来存储已缓存的结果.这是最后一次在Excel中打开工作簿，重新计算并保存该工作簿，或者这是最后一次在Excel之外进行评估.

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.

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

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

或者您正在使用 DataFormatter 以及 FormulaEvaluator :

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从Excel中检索日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！