Hi,
Am trying to "evaluate in cell" and keep getting the following exception:
java.lang.IllegalStateException: Cannot get a text value from a numeric
formula cell
at
org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:623)
at
org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:628)
at
org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:707)
at
org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue(HSSFCell.java:685)
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:354)
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:277)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setCellType(HSSFFormulaEvaluator.java:187)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:175)
the evaluation works for few formula cells while not for rest of the cells,
and i cant seem to find what is the difference between the cells that are
working and those that throw the type mismatch exception.
This is what I am trying to do in this application:
1 . Open a workbook
2 . Fill values in a worksheet from the application (SHEET1)
3 . All other worksheets in the workbook simply refer to the worksheet
filled in step 2 using simple reference formulas i.e. '=SHEET1!B5' ....
4 . Once I have written to the worksheet in step 2, I go through the rest of
the worksheets and try to replace the formula by their evaluated values.
Problem is most of cells bomb, while few cells go through fine, I can see
the formulas evaluated and values replaced in those cells.
With some sout code below,
if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
try {
evaluator.evaluateInCell(cell);
System.out.println("Success:" + row.getRowNum() + ":" +
cell.getCellNum() + ":" + cell.getCellType());
} catch (Exception e) {
System.out.println("Failed:" + row.getRowNum() + ":" +
cell.getCellNum() + ":" + cell.getCellType() + ":" +e.getMessage());
}
}
am getting the following output:
Success:9:5:1
Failed:10:1:2:Cannot get a text value from a numeric formula cell
Failed:10:2:2:Cannot get a text value from a numeric formula cell
Failed:10:3:2:Cannot get a text value from a numeric formula cell
Failed:10:4:2:Cannot get a text value from a numeric formula cell
Success:10:5:1
Success:11:1:0
Success:11:2:0
Success:11:3:0
Success:11:4:0
Success:11:5:1
Success:12:1:0
Success:12:2:0
Success:12:3:0
Even though I cannot see any difference between Cell[9][5] and Cell[10][1]
in the excel, it seems that the setCellType of evaluated cell fails for
[10][1].
Also from the logs, for all failures, the cell.getCellType is 2, while for
the "success" cells the cell.getCellType is 0, 1 or 5.
What is the very obvious mistake that I am making here
Thanks for any help on this!
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/TypeMismatch-error-with-HSSFFormulaEvaluator-evaluateInCell-tp3353681p3353681.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
