Java POI：如何读取Excel单元格的值而不是计算它的公式？

Question

Java POI：如何读取Excel单元格的值而不是计算它的公式？

javaexcelapache-poinotimplementedexception

73

我正在使用Apache POI API从Excel文件中获取值。除了包含公式的单元格外，一切都很顺利。事实上，cell.getStringCellValue()返回的是单元格中使用的公式而不是单元格的值。

我尝试使用evaluateFormulaCell()方法，但它不起作用，因为我正在使用GETPIVOTDATA Excel公式，而该公式没有在API中实现：

Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Landscape!K11
    at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)
    at fromExcelToJava.ExcelSheetReader.unAutreTest(ExcelSheetReader.java:193)
    at fromExcelToJava.ExcelSheetReader.main(ExcelSheetReader.java:224)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: GETPIVOTDATA
    at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)

- Aminoss

6个回答

12

之前发布的解决方案对我没用。 cell.getRawValue() 返回的是与单元格中显示的公式相同的公式。以下函数对我起作用：

public void readFormula() throws IOException {
    FileInputStream fis = new FileInputStream("Path of your file");
    Workbook wb = new XSSFWorkbook(fis);
    Sheet sheet = wb.getSheetAt(0);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    CellReference cellReference = new CellReference("C2"); // pass the cell which contains the formula
    Row row = sheet.getRow(cellReference.getRow());
    Cell cell = row.getCell(cellReference.getCol());

    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cellValue.getBooleanValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cellValue.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cellValue.getStringValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            break;

        // CELL_TYPE_FORMULA will never happen
        case Cell.CELL_TYPE_FORMULA:
            break;
    }

}

- SelThroughJava

对我没用，evaluator.evaluate(cell) 返回了 null。 - aboger

3

看起来是从这个回答复制过来的，提供了更多信息。 - aboger

3

如果需要从Excel表格中读取值并将其作为字符串呈现在某个地方或在文本文件格式中使用，则使用DataFormatter是最好的选择。 DataFormatter可以从每个单元格值获取一个字符串，无论单元格值本身是字符串、布尔值、数字、错误还是日期。这个字符串看起来与Excel在其GUI中显示的单元格中一样。

唯一的问题是公式单元格。在使用DataFormatter时，需要FormulaEvaluator来计算公式。当apache poi无法计算公式时，这会失败。从5.2.0开始，DataFormatter可以设置为对公式单元格使用缓存值。如果Excel已经计算过公式，则不需要进行公式评估。

完整示例：

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;

class ReadExcel {

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

  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelExample.xlsx"));
  
  // up to apache poi 5.1.0 a FormulaEvaluator is needed to evaluate the formulas while using DataFormatter
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
  
  DataFormatter dataFormatter = new DataFormatter(new java.util.Locale("en", "US"));
  // from 5.2.0 on the DataFormatter can set to use cached values for formula cells
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  
  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    //String value = dataFormatter.formatCellValue(cell, evaluator); // up to apache poi 5.1.0
    String value = dataFormatter.formatCellValue(cell); // from apache poi 5.2.0 on 
    System.out.println(value);
   }
  }
  workbook.close();
 }
}

- Axel Richter

这一行代码"dataFormatter.setUseCachedValuesForFormulaCells(true);"对我很有帮助！ - Alan Z.

2

有一种替代命令，可以获取放置公式的单元格的原始值。它返回的类型是字符串。使用以下命令：

cell.getRawValue();

- Sanjay Singh

1

这个方法在POI javadocs中没有显示出来，你确定你的答案适用于这个库吗？ - Gagravarr

请看这里：https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html - Sanjay Singh

3

啊，那只有XSSF才有。你应该明确说明，因为Common SS模型没有它，HSSF也没有。 - Gagravarr

这里的解决方法是关于从 Excel 表格单元格获取值的。它将支持 Excel 支持的所有文件格式。 - Sanjay Singh

1

不会的，HSSF中不存在那个方法，因此对于 .xls 文件不可用 - 它仅适用于 .xlsx 文件。 - Gagravarr

1

如果您想从HSSF单元格中提取一个较原始的值，可以使用类似以下代码片段的方法：

CellBase base = (CellBase) cell;
CellType cellType = cell.getCellType();
base.setCellType(CellType.STRING);
String result = cell.getStringCellValue();
base.setCellType(cellType);

对于完全由数字组成的字符串（并被Excel自动转换为数字），此方法返回原始字符串（例如"12345"），而不是分数值（例如"12345.0"）。请注意，setCellType在接口Cell中可用（从v. 4.1开始），但已弃用，并宣布将在v 5.x中被淘汰，而此方法仍在类CellBase中可用。显然，要么在Cell接口中有getRawValue，要么至少能够在非STRING单元格类型上使用getStringCellValue会更好。不幸的是，描述中提到的所有setCellType替换都无法涵盖此用例（也许POI开发团队的成员会阅读此答案）。

- Remigius Stalder

0

SelThroughJava的回答非常有帮助，我不得不对我的代码进行一些修改才能正常工作。我使用了https://mvnrepository.com/artifact/org.apache.poi/poi和https://mvnrepository.com/artifact/org.testng/testng作为依赖项。下面是完整的代码，包括准确的导入。

 import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.util.CellReference;
    import org.apache.poi.sl.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.CellValue;
    import org.apache.poi.ss.usermodel.FormulaEvaluator;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;


    public class ReadExcelFormulaValue {

        private static final CellType NUMERIC = null;
        public static void main(String[] args) {
            try {
                readFormula();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        public static void readFormula() throws IOException {
            FileInputStream fis = new FileInputStream("C:eclipse-workspace\\sam-webdbriver-diaries\\resources\\tUser_WS.xls");
            org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            CellReference cellReference = new CellReference("G2"); // pass the cell which contains the formula
            Row row = sheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());

            CellValue cellValue = evaluator.evaluate(cell);
            System.out.println("Cell type month  is "+cellValue.getCellTypeEnum());
            System.out.println("getNumberValue month is  "+cellValue.getNumberValue());     
          //  System.out.println("getStringValue "+cellValue.getStringValue());


            cellReference = new CellReference("H2"); // pass the cell which contains the formula
             row = sheet.getRow(cellReference.getRow());
             cell = row.getCell(cellReference.getCol());

            cellValue = evaluator.evaluate(cell);
            System.out.println("getNumberValue DAY is  "+cellValue.getNumberValue());    


        }

    }

- Sameera De Silva

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gagravarr · Accepted Answer

对于公式单元格，Excel存储两个东西。一个是公式本身，另一个是“缓存”的值（公式上次计算出的值）。

如果您想获取最后一个缓存值（可能不再正确，但只要Excel保存了文件并且您没有更改它，就应该是），您需要类似以下内容：

 for(Cell cell : row) {
     if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        System.out.println("Formula is " + cell.getCellFormula());
        switch(cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                System.out.println("Last evaluated as: " + cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                break;
        }
     }
 }