如何从公式单元格使用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.

我的code看起来像这样

My code looks like this

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

推荐答案

您正在寻找的方法是<一个href=\"http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#getCachedFormulaResultType%28%29\">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

您code，则可以是这样的：

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

请注意，该迭代只给该文件中定义的细胞，因此，如果细胞从未使用会有间隙。如果你需要让每一个细胞都包括失踪的人，请参见迭代VS抓取文档

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屋！