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

The following code should be able reading any kind of Excel cell contents. I hope I have not forgotten something.

主要是对忙碌开发者指南:获取单元格内容的改动> 是:

使用具有 FormulaEvaluator 的 DataFormatter.

如果 cell.getCellTypeEnum() 是 CellType.FORMULA 然后再次检查 cell.getCachedFormulaResultTypeEnum() 并根据这是什么做出反应喜欢.

If cell.getCellTypeEnum() is CellType.FORMULA then check again the cell.getCachedFormulaResultTypeEnum() and react dependent of what this is like.

使用最新的稳定版本Apache POI 3.16.不使用较低版本的作品.

Works using latest stable release Apache POI 3.16. Works not using lower versions.

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

 }
}