Hi,
This problem arises from POI-3.2-alpha1. I'm using the alpha since
I had previously ran into this bug:
https://issues.apache.org/bugzilla/show_bug.cgi?id=45798
I want to extract the values of my spreadsheet's cells as strings.
This worked fine:
if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
short i = cell.getCellStyle().getDataFormat();
String format = cellFormat.getFormat(i);
if(HSSFDateUtil.isADateFormat((int)i,format)) {
//Some date stuff
}
}
else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
val = cell.getRichStringCellValue().toString()
}
else {
//etc....
}
Until... testing. I threw a bunch of spreadshets at it and
received:
Exception in thread "main" java.lang.IllegalStateException:
Cannot get a error value from a text formula cell
at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)
at org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:630)
at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:709)
Ok, so I change the CELL_TYPE_FORMULA branch to:
else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA
&& cell.getCellType()!=HSSFCell.CELL_TYPE_ERROR)
This results in the same exception.
Looking at the source I see that the cell type is assigned
once at initialization (i.e. instances of FormulaRecordAggregate are
deemed CELL_TYPE_FORMULA), though when one attempts to get that cell's
value as a Rich String, the cell's FormulaRecord.getCachedResultType is
called, revealing that the cell is of CELL_TYPE_ERROR.
This seems wrong, though further examination of the code shows
that an effort is made to keep a clear distinction between formula and
error (non-formula error) cells. Plus, I see that there's a
getCachedFormulaResultType() method.
I then try:
else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA &&
cell.getCachedFormulaResultType()!=HSSFCell.CELL_TYPE_ERROR
But, once I hit a formula that returns a numeric value, I receive:
Exception in thread "main" java.lang.IllegalStateException: Cannot get a text
value from a numeric formula cell at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)
at org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:630)
at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:709)
How is one supposed to get a string value from a formula cell,
numeric or otherwise?
-Skye
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
|