如何使用apache poi从公式单元格中读取单元格值 [英] How to read cell value from formula cell using apache poi

问题描述

我试图从一个 excel 文件中读取所有数据，该文件也有一些公式单元格，但我不知道哪个单元格是公式单元格.无论单元格的类型如何，我如何从单元格中读取所有值.

I am trying to read all the data from a excel file, which also have some formula cell, but i have no idea that which cell is formula cell. how can i read all the values from the cells irrespective of the type of the cell.

我的代码是这样的

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
while (rows.hasNext()) {
     row = (HSSFRow) rows.next();
     Iterator cells = row.cellIterator();
     while (cells.hasNext()) {
             cell = (HSSFCell) cells.next();
             if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                  ar.add(cell.getStringCellValue());
             } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                 ar.add(cell.getNumericCellValue());
             }else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                 ar.add(evaluator.evaluateFormulaCell(cell));
             } else {
                 ar.add("");
             }
     }
}

我得到的公式单元格值为 0

I am getting the formula cell value as 0

推荐答案

您正在寻找的方法是 Cell.getCachedFormulaResultType - 用于告诉您公式结果类型的公式单元格

The method you're looking for is Cell.getCachedFormulaResultType - for a formula cell that'll tell you the type of the formula result

你的代码可以是这样的:

You code can then be something like:

private void handleCell(int type, Cell cell) {
         if (type == HSSFCell.CELL_TYPE_STRING) {
              ar.add(cell.getStringCellValue());
         } else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
             ar.add(cell.getNumericCellValue());
         } else if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
             ar.add(cell.getBooleanCellValue());
         } else if (type == HSSFCell.CELL_TYPE_FORMULA) {
             // Re-run based on the formula type
             handleCell(cell.getCachedFormulaResultType(), cell);
         } else {
             ar.add("");
         }
}

public void handleSheet(Sheet sheet) {
    for (Row row : sheet) {
       for (Cell cell : row) {
           handleCell(cell.getCellType(), cell);
       }
    }
}

请注意，迭代器仅给出文件中定义的单元格，因此如果从未使用过这些单元格，则会出现间隙.如果您需要获取每个单元格，包括丢失的单元格，请参阅 迭代与获取文档

Note that the iterators only give the cells that are defined in the file, so there will be gaps if the cells have never been used. If you need to get every cell including missing ones, see the Iterating vs Fetching docs

这篇关于如何使用apache poi从公式单元格中读取单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！