Hi Des,
Yes, there is a more elegant solution ;)
When you know the cell contains a formula, you can check the value returned
by cell.getCachedFormulaResultType() and go into a switch again to get the
result properly.
Pierre
On Wed, Dec 10, 2008 at 1:38 PM, Des Hartman <[EMAIL PROTECTED]> wrote:
> 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
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
