读取Apache POI xlsx、带#值的单元格——错误的意外单元格类型(5)

Could you hepl me with such issue.

你能帮我解决这个问题吗?

I need to read each cell as String value. In this case I am using appache poi lib. and such method for normilizing each cell:

我需要将每个单元格读取为字符串值。在本例中，我使用的是appache poi lib.以及这样的方法来规范化每个单元格:

String getNormilizedCell(Cell cell){
return new DataFormatter().formatCellValue(cell);}

But when In .xlsx file I faced with such value:

但在xlsx文件中，我遇到了这样的值:

|#N/A|#N/A|...|...|...

| # N / | # N / A |……| |…

I am getting error [Unexpected Cell type (5)] and I don't know how to handle this. In google I can't find necessary information.

我得到错误[意外单元类型(5)]，我不知道如何处理它。在谷歌中我找不到必要的信息。

3 个解决方案

#1

The DataFormatter class only handles CELL_TYPE_FORMULA, CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN, and CELL_TYPE_BLANK. It doesn't handle CELL_TYPE_ERROR, which is 5.

DataFormatter类只处理CELL_TYPE_FORMULA、CELL_TYPE_NUMERIC、CELL_TYPE_STRING、CELL_TYPE_BOOLEAN和CELL_TYPE_BLANK。它不处理CELL_TYPE_ERROR，也就是5。

You'll have to work around this by first detecting the error cell type and then handling it specially, referring to the error cell value codes:

要解决这个问题，首先要检测错误单元类型，然后特别处理它，参考错误单元值代码:

if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
    byte errorValue = cell.getErrorCellValue();
    switch(errorValue) {
    case ERROR_DIV_0:
        return "#DIV/0!";
    case ERROR_NA:
        return "#N/A";
    case ERROR_NAME:
        return "#NAME?";
    case ERROR_NULL:
        return "#NULL!";
    case ERROR_NUM:
        return "#NUM!";
    case ERROR_REF:
        return "#REF!";
    case ERROR_VALUE:
        return "#VALUE!";
    default:
        return "Unknown error value: " + errorValue + "!";
    }
} else {
    return new DataFormatter().formatCellValue(cell);
}

#2

You need to upgrade your copy of Apache POI!

您需要升级您的Apache POI副本!

As of r1537552 DataFormatter is now happy to format Error cells for you. It'll give you back the error string that Excel displays, using the FormulaError constants

从r1537552开始，DataFormatter可以为您格式化错误单元格。它将使用公式错误常量返回Excel显示的错误字符串

#3

As said by @Gagravarr, DataFormatter handles most errors now (I use poi-3.11-beta2). But, as said in my comment, some formula errors can still throw exceptions.

正如@Gagravarr所说，DataFormatter现在处理了大多数错误(我使用了poi-3.11-beta2)。但是，正如我在评论中所说，一些公式错误仍然可以抛出异常。

For example, when evaluating a formula like =xxx() when xxx isn't a real function, Excel displays #NAME? but we get a "Don't know how to evaluate name 'xxx'" runtime exception.

例如，当评估一个公式如=xxx()时，当xxx不是一个真正的函数时，Excel显示#NAME?但是我们得到一个“不知道如何计算名称‘xxx’”运行时异常。

Fortunately, it is simple to handle:

幸运的是，它很容易处理:

public String readCellValue(Cell cell)
{
    switch (cell.getCellType())
    {
    case Cell.CELL_TYPE_BLANK:
        return "(blank)";
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_ERROR:
        return String.valueOf(cell.getErrorCellValue());
    case Cell.CELL_TYPE_FORMULA:
        return readFormattedCellValue(cell);
    case Cell.CELL_TYPE_NUMERIC:
        return String.valueOf(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default:
        return "Unknown type!";
    }
}

public String readFormattedCellValue(Cell cell)
{
    try
    {
        return formatter.formatCellValue(cell, evaluator);
    }
    catch (RuntimeException e)
    {
        return e.getMessage(); // Error from evaluator, for example "Don't know how to evaluate name 'xxx'" if we have =xxx() in cell
    }
}

For the record, formatter and evaluator are created like in the convert to CSV example:

对于记录，格式化程序和求值程序被创建，就像转换到CSV示例中的那样:

    try (FileInputStream fis = new FileInputStream(file))
    {
        // Open the workbook and then create the FormulaEvaluator and
        // DataFormatter instances that will be needed to, respectively,
        // force evaluation of formulae found in cells and create a
        // formatted String encapsulating the cells contents.
        workbook = WorkbookFactory.create(fis);
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formatter = new DataFormatter(true);
    }

For some unknown reason, the WorkbookFactory lives only in the poi-ooml-3.11-beta2.jar, not in the poi-3.11-beta2.jar one.

不知什么原因，WorkbookFactory只在poi-ooml-3.11-beta2中运行。jar，不是poi-3.11-beta2。jar。

#1