poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Des Hartman" <...@deshartman.com>
Subject Re: CELL_TYPE_FORMULA - String vs. Numeric
Date Wed, 10 Dec 2008 21:27:49 GMT
Pierre, Nick

Thanks for the reply. Yep that worked and is a lot better, since I do not
have to catch an exception to branch (bad coding :-(   )

Relevant code is now:

                    if (cell != null) {

                        switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_FORMULA:
                            // Get the type of Formula
                            switch (cell.getCachedFormulaResultType()){
                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                value = cell.getNumericCellValue()+"";
                                break;
                                default:
                            }
                            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 = "";
                    }


Thanks for the help. I have to finish off a few more parts of this and then
I'll post the UserAPI based XLS2CSV.

2008/12/11 Pierre Lavignotte <pierre.lavignotte@gmail.com>

> 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 <des@deshartman.com> 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
>

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