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!
Any comments or statements made in this email are not necessarily those of
Tavant Technologies.
The information transmitted is intended only for the person or entity to which
it is addressed and may
contain confidential and/or privileged material. If you have received this in
error, please contact the
sender and delete the material from any computer. All e-mails sent from or to
Tavant Technologies
may be subject to our monitoring procedures.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
