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列都是string类型，因此我想不到如何获取字符串值
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中一样，无论类型如何，那么您要查找的类都是
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屋！
