Apache POI 的 getCachedFormulaResultType() 返回错误类型 [英] getCachedFormulaResultType() of Apache POI returns incorrect type
问题描述
我正在阅读 Excel 表格.最初(直到 rowid 30)我得到了公式单元格的正确 (CELL_TYPE_STRING) 结果类型，但从第 31 行开始，我收到了不正确的 (CELL_TYPE_NUMERIC) 结果类型.
I am reading an Excel sheet. Initially (till rowid 30) I am getting the correct (CELL_TYPE_STRING) result type for the formula cell, but from row id 31 onwards I receive the incorrect (CELL_TYPE_NUMERIC) result type.
请查看下面的日志:我使用 ==== 作为分隔符来分隔每一行的日志.列号 1 是重点，对于 rowID=30 我得到 type=String 而对于 rowId=31 我得到 type=Numeric，但是每行的第 1 列是字符串类型，因此我缺少如何获取字符串值.
Please see the logs below: I have used ==== as delimiter for separating logs for each row. Column number 1 is the point to focus, where for rowID=30 I get type=String whereas for rowId=31 I get type=Numeric, but column 1 for each row is of type string therefore I am missing how to get a string value.
日志对应行请查看Excel表格内容.
Please see the Excel sheet content for the row corresponding to the log.
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
cellWrapper.setCellValueFarmula(true);
switch (cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
logged.append("CELL_TYPE_FORMULA->CELL_TYPE_NUMERIC");
break;
case Cell.CELL_TYPE_STRING:
cellValue = "" + cell.getRichStringCellValue();
logged.append("CELL_TYPE_FORMULA->CELL_TYPE_STRING");
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = "" + cell.getBooleanCellValue();
logged.append("CELL_TYPE_FORMULA->CELL_TYPE_BOOLEAN");
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "error! ";
logged.append("CELL_TYPE_FORMULA->CELL_TYPE_ERROR");
break;
default:
cellValue = null;
logged.append("case not captured" + " type fetched = " + cell.getCellType());
break;
}
break;
case Cell.CELL_TYPE_BLANK:
logged.append("CELL_TYPE_BLANK");
cellValue = null;
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "error!";
logged.append("CELL_TYPE_ERROR");
break;
... some for cases below
Excel 内容
Col1 Col2 Col3 Col4
[ACTIVE] - 21 - 1A - CALDRA - 305 . {Chitra Subramaniam - 9538924280}, {deepakag@yahoo.com} 12 Rs. 1,000
[ACTIVE] - 22 - 1A - CALDRA - 306 . {Parasad Parigi - 9538924280}, {deepakag@yahoo.com} 25 Rs. 1,000
[SEALED] - 23 - 1A - CALDRA - 401 . {Vivek - 9538924280}, {deepakag@yahoo.com} <empty cell> Rs. 1,075
日志
==================================================================================================
Cell @ row=30col=1 type = CELL_TYPE_FORMULA->CELL_TYPE_STRING Value=[ACTIVE] - 21 - 1A - CALDRA - 305 .
Cell @ row=30col=2 type = CELL_TYPE_FORMULA->CELL_TYPE_STRING Value= {Chitra Subramaniam - 9538924280}, {deepakag@yahoo.com}
Cell @ row=30col=3 type = CELL_TYPE_NUMERIC Value=12.0
Cell @ row=30col=4 type = CELL_TYPE_STRING Value=Rs. 1,000
==================================================================================================
Cell @ row=31col=1 type = CELL_TYPE_FORMULA->CELL_TYPE_NUMERIC Value=0.0
Cell @ row=31col=2 type = CELL_TYPE_FORMULA->CELL_TYPE_STRING Value= {Parasad Parigi - 9538924280}, {deepakag@yahoo.com}
Cell @ row=31col=3 type = CELL_TYPE_NUMERIC Value=25.0
Cell @ row=31col=4 type = CELL_TYPE_STRING Value=Rs. 1,000
==================================================================================================
Cell @ row=32col=1 type = CELL_TYPE_FORMULA->CELL_TYPE_NUMERIC Value=0.0
Cell @ row=32col=2 type = CELL_TYPE_FORMULA->CELL_TYPE_STRING Value= {Vivek - 9538924280}, {deepakag@yahoo.com}
Cell @ row=32col=3 type = CELL_TYPE_BLANK Value=null
Cell @ row=32col=4 type = CELL_TYPE_STRING Value=Rs. 1,075
==================================================================================================
那么如何检索单元格结果值，即使该值是数字?是否有一种通用方法可以以字符串形式给出结果值，而不管单元格是什么类型?
So how can I retrieve the cell result value, even if the value is numeric? Is there a generic method that will give the result value in string form irrespective of what type of cell is?
推荐答案
Excel 中的单元格可以有多种不同的类型.POI 会在 Excel 存储时为您提供单元格值，这很可能不是 Excel 显示它的方式.(Excel 可能有点神秘！).
Cells in Excel can have a number of different types. POI gives you back the cell value as Excel stored it, which may well not be how Excel displays it. (Excel can be a bit mysterious that way!).
如果您希望 POI 尝试将单元格的格式设置为与 Excel 中的一样，无论是哪种类型，那么您要查找的类是 DataFormatter.您的代码将类似于:
If you want POI to try to format the cell to look like it does in Excel, no matter the type, then the class you're looking for is DataFormatter. Your code would be something like:
DataFormatter fmt = new DataFormatter();
for (Row r : sheet) {
for (Cell c : r) {
CellReference cr = new CellRefence(c);
System.out.println("Cell " + cr.formatAsString() + " is " +
fmt.formatCellValue(c) );
}
}
这篇关于Apache POI 的 getCachedFormulaResultType() 返回错误类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
