poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Des Hartman" <...@deshartman.com>
Subject CELL_TYPE_FORMULA - String vs. Numeric
Date Wed, 10 Dec 2008 12:38:35 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message