Been struggling with this all day and I think I finally have a good answer.
Hope this helps someone else :-)
Problem is that if you iterate through XLS cells and the cell type is
CELL_TYPE_FORMULA, the formula itself can evaluate to String or double.
there is no real way of knowing.
What I did to solve this issue is to try and read it as a String using
getRichStringCellValue(). this throws an exception if it is Numeric and I
can then catch the exception and change my evaluation.
The relevant part is:
case HSSFCell.CELL_TYPE_FORMULA:
try {
HSSFRichTextString stringValue = cell.getRichStringCellValue();
value = stringValue.getString();
} catch (Exception e) {
//System.out.println("Numeric formula");
value = cell.getNumericCellValue()+"";
}
break;
The more complete code is below to handle the types. Like I said, hope it
helps and if anybody has a more elegant solution, I would love to hear from
you..............
==================================
<snip>
for (int c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(c);
String value = null;
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
try {
HSSFRichTextString stringValue =
cell.getRichStringCellValue();
value = stringValue.getString();
} catch (Exception e) {
//System.out.println("Numeric formula");
value = cell.getNumericCellValue()+"";
// TODO: handle exception
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
Double dval = cell.getNumericCellValue();
value = dval + "";
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getStringCellValue();
break;
default:
}
} else {
// Padd for blank cell
value = "";
}
<snip>
==================================
--
Thanks
Des Hartman
