java - 无法从使用 Apache POI 通过公式计算的 Excel 工作表中读取日期值作为字符串，缺少什么？

Question

所以我的Java代码就像这样读取Excel工作表单元格值，但我无法将日期值作为字符串获取，而是自动更正为数字。另请注意，Excel 的所有这些值都是通过各自单元格中的公式计算的。缺少什么？

public class DatasheetReader {
public static void main(String args[]) throws Exception {
    String path = "D:\\Workspace\\Analytics\\TestDataSheets\\IRPTestData.xlsx";
    String sheet = "CompleteSimulationData";
    getCellData(path, sheet);
}

public static Object[][] getCellData(String datSheetPath, String sheetName)throws Exception {

    FileInputStream fis = new FileInputStream(new java.io.File(datSheetPath));
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheet(sheetName);

    int rowCount = sheet.getPhysicalNumberOfRows();
    int columnCount = sheet.getRow(0).getPhysicalNumberOfCells();

    // System.out.println(rowCount);
    // System.out.println(columnCount);

    Object[][] cellData = new Object[rowCount][columnCount];
    Object[][] dataProviderArray = new Object[rowCount - 1][columnCount];

    for (int i = 0; i < rowCount; i++) {
        System.out.println(" ");
        for (int j = 0; j < columnCount; j++) {
            // IF - for blanks in excel
            if (i != 0) {
                // IF - for not null values
                if (sheet.getRow(i).getCell(j) != null) {
                    Cell cell = sheet.getRow(i).getCell(j);
                    int cellType = cell.getCachedFormulaResultType();
                    if(cellType==Cell.CELL_TYPE_NUMERIC){
                         System.out.println(cell.getNumericCellValue()+" "+cell.getCachedFormulaResultType());
                    }else if(cellType==Cell.CELL_TYPE_STRING){
                         System.out.println(cell.getRichStringCellValue()+" "+cell.getCachedFormulaResultType());
                    }
                } else {
                    cellData[i][j] = "";
                }

            } else {
                continue;
            }
            //System.out.println(cellData[i][j]);
        }
    }

    for (int i = 1; i < rowCount; i++) {
        for (int j = 0; j < columnCount; j++) {
            //System.out.print(cellData[i][j] + " ");
            dataProviderArray[i - 1][j] = cellData[i][j];
        }
    }
    workbook.close();
    return dataProviderArray;
}}

所有这些值都是根据公式计算的缓存值。
除日期外，所有值均按预期获取，它为 42887.0 而不是 Jun-2017 基本上所有值都不是 Excel 单元格中的单独值，而是从公式，对于日期 POI 返回其类型为 CELL_TYPE_NUMERIC，不明白如何处理此问题，因为无法使用 DataFormat POI 的，因为那么我要读取的所有值都将作为公式获取？

Answer 1

以下代码应该能够读取任何类型的 Excel 单元格内容。我希望我没有忘记什么。

主要修改为Busy Developers' Guide: Getting the cell contents是:

使用带有FormulaEvaluator的DataFormatter。

如果 cell.getCellTypeEnum() 是 CellType.FORMULA，则再次检查 cell.getCachedFormulaResultTypeEnum() 并根据其内容使用react喜欢。

使用最新稳定版本Apache POI 3.16工作。不使用较低版本即可工作。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.CellType.*;

import java.io.FileInputStream;

class ReadExcelExample {

 public static void main(String[] args) throws Exception {

  Workbook wb  = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter formatter = new DataFormatter();
  FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
    System.out.print(cellRef.formatAsString());
    System.out.print(" - ");

    // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
    String text = "";
    try {
     text = formatter.formatCellValue(cell, evaluator);
    } catch (org.apache.poi.ss.formula.eval.NotImplementedException ex) {
     text = "Formula not implemented";
    }
    System.out.println(text);

    // Alternatively, get the value and format it yourself
    switch (cell.getCellTypeEnum()) {
     case STRING:
      System.out.println(cell.getRichStringCellValue().getString());
      break;
     case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)) {
       System.out.println(cell.getDateCellValue());
      } else {
       System.out.println(cell.getNumericCellValue());
      }
      break;
     case BOOLEAN:
      System.out.println(cell.getBooleanCellValue());
      break;
     case FORMULA:
      System.out.println(cell.getCellFormula());
      switch (cell.getCachedFormulaResultTypeEnum()) {
       case STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        break;
       case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
         System.out.println(cell.getDateCellValue());
        } else {
         System.out.println(cell.getNumericCellValue());
        }
        break;
       case BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        break;
       case ERROR:
       System.out.println(cell.getErrorCellValue());
        break;
       default:
        System.out.println("default formula cell"); //should never occur
      }
      break;
     case ERROR:
      System.out.println(cell.getErrorCellValue());
      break;
     case BLANK:
      System.out.println("blank");
      break;
     default:
      System.out.println("default cell"); //should never occur
    }
   }
  }

  wb.close();

 }
}