按poi解析Excel，目标单元格为空有时解析ok有时抛出nullpointexception [英] Parse Excel by poi and target cell is empty sometimes parse ok sometimes throw nullpointexception

问题描述

发现poi解析excel的奇怪现象.看代码

I found a strange phenomenon of parse excel by poi. See code

goods.subtitle = row.getCell(headerNameIndexMap.get("subtitle")).getStringCellValue();

解析字幕列并将值设置为对象.有两个excel，字幕列都是空的，但是解析其中一个没问题，只是subtitle属性为null，但是解析另一个excel时，抛出异常:

parse subtitle column and set value to object. There are two excel, both subtitle column is empty, but parse one of them is ok, just subtitle property is null, but when parse another excel, it throws exception:

java.lang.NullPointerException

从success excel 复制一些同名列到fail excel 后，在parse failed 列之前可以解析ok，但是突然fail excel 中的一个非空列突然解析失败，抛出NullPointException.然后将success excel的同名列复制到fail excel，fail excel现在可以解析成功了.

After copying some same name column from success excel to fail excel, before parse failed column could parse ok, but suddenly a non empty column in fail excel suddenly parse failed, throw NullPointException. Then copying the same name column from success excel to fail excel, the fail excel now can parse successfully.

如果我将解析的失败列从失败 excel 复制到成功 excel，它仍然解析失败.但是当复制成功excel中的另一列并粘贴特殊并且只检查格式到失败列时，它会解析.

And if I copy the parsed fail column from fail excel to success excel, it still parse failed. But when copy another column in success excel and paste special and only check Formats to the fail column, it will parse ok.

是什么原因?

推荐答案

根据 Apache POI JavaDocs，Row.getCell(int) 可能返回 Null.空单元格是没有值和样式的单元格，因此不会记录在文件中

As per the Apache POI JavaDocs, Row.getCell(int) may return Null. A null cell is one that has no value and no styling, and hence is not recorded in the file

因此，您的代码对于空单元格、空白单元格(以前持有一个值但不再存在)和数字单元格将失败.所以，坦率地说，大多数情况...

Your code will therefore fail for empty cells, and for blank cells (previously held a value but no longer), and for numeric cells. So, frankly, most cases...

您可能应该将代码更改为更像这样:

You should probably change your code to more like this:

DataFormatter formatter = new DataFormatter();

Cell cell = row.getCell(headerNameIndexMap.get("subtitle"), Row.    RETURN_BLANK_AS_NULL);
if (cell == null) {
   // No value here, handle
} else {
   String subtitle = formatter.formatCellValue(cell);
}

这将处理空单元格、空单元格，并为您提供格式化数字单元格的字符串

That will handle empty cells, null cells, and will give you a string for formatted numeric cells

这篇关于按poi解析Excel，目标单元格为空有时解析ok有时抛出nullpointexception的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！