Return-Path: Delivered-To: apmail-poi-user-archive@www.apache.org Received: (qmail 33679 invoked from network); 10 Dec 2008 21:28:28 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 10 Dec 2008 21:28:28 -0000 Received: (qmail 36348 invoked by uid 500); 10 Dec 2008 21:28:39 -0000 Delivered-To: apmail-poi-user-archive@poi.apache.org Received: (qmail 36338 invoked by uid 500); 10 Dec 2008 21:28:39 -0000 Mailing-List: contact user-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: "POI Users List" Delivered-To: mailing list user@poi.apache.org Received: (qmail 36327 invoked by uid 99); 10 Dec 2008 21:28:39 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 Dec 2008 13:28:39 -0800 X-ASF-Spam-Status: No, hits=3.7 required=10.0 tests=HTML_MESSAGE,SPF_NEUTRAL,URIBL_GREY X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [209.85.198.231] (HELO rv-out-0506.google.com) (209.85.198.231) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 Dec 2008 21:28:23 +0000 Received: by rv-out-0506.google.com with SMTP id b25so575375rvf.45 for ; Wed, 10 Dec 2008 13:27:49 -0800 (PST) Received: by 10.140.172.19 with SMTP id u19mr875618rve.87.1228944469690; Wed, 10 Dec 2008 13:27:49 -0800 (PST) Received: by 10.141.77.10 with HTTP; Wed, 10 Dec 2008 13:27:49 -0800 (PST) Message-ID: <8773d42e0812101327u12ebecdcp2c6a8ebd486ad4cb@mail.gmail.com> Date: Thu, 11 Dec 2008 08:27:49 +1100 From: "Des Hartman" To: "POI Users List" Subject: Re: CELL_TYPE_FORMULA - String vs. Numeric In-Reply-To: <40e09acb0812100513l5e31343ej2a476cc7fb610cf5@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_67249_30789548.1228944469674" References: <8773d42e0812100438l208041fby4aee4e2f5bb384fa@mail.gmail.com> <40e09acb0812100513l5e31343ej2a476cc7fb610cf5@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_67249_30789548.1228944469674 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 !=3D null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: // Get the type of Formula switch (cell.getCachedFormulaResultType()){ case HSSFCell.CELL_TYPE_STRING: value =3D cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: value =3D cell.getNumericCellValue()+""; break; default: } break; case HSSFCell.CELL_TYPE_NUMERIC: Double dval =3D cell.getNumericCellValue(); value =3D dval + ""; break; case HSSFCell.CELL_TYPE_STRING: value =3D cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: value =3D ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value =3D cell.getStringCellValue(); break; default: } } else { // Padd for blank cell value =3D ""; } 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 > Hi Des, > > Yes, there is a more elegant solution ;) > > When you know the cell contains a formula, you can check the value return= ed > by cell.getCachedFormulaResultType() and go into a switch again to get th= e > result properly. > > Pierre > > On Wed, Dec 10, 2008 at 1:38 PM, Des Hartman 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 =3D > cell.getRichStringCellValue(); > > value =3D stringValue.getString(); > > } catch (Exception e) { > > //System.out.println("Numeric formula"); > > value =3D 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.............. > > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > > > for (int c =3D 0; c < cells; c++) { > > HSSFCell cell =3D row.getCell(c); > > String value =3D null; > > > > if (cell !=3D null) { > > > > switch (cell.getCellType()) { > > > > case HSSFCell.CELL_TYPE_FORMULA: > > try { > > HSSFRichTextString stringValue =3D > > cell.getRichStringCellValue(); > > value =3D stringValue.getString(); > > } catch (Exception e) { > > //System.out.println("Numeric formula"); > > value =3D cell.getNumericCellValue()+""; > > // TODO: handle exception > > } > > break; > > > > case HSSFCell.CELL_TYPE_NUMERIC: > > Double dval =3D cell.getNumericCellValue(); > > value =3D dval + ""; > > break; > > > > case HSSFCell.CELL_TYPE_STRING: > > value =3D cell.getStringCellValue(); > > break; > > > > case HSSFCell.CELL_TYPE_BLANK: > > value =3D ""; > > break; > > > > case HSSFCell.CELL_TYPE_BOOLEAN: > > value =3D cell.getStringCellValue(); > > break; > > > > default: > > } > > } else { > > // Padd for blank cell > > value =3D ""; > > } > > > > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > > > -- > > Thanks > > Des Hartman > > > > > > -- > Cordialement, > Pierre Lavignotte > Ing=E9nieur Conception & D=E9veloppement > http://pierre.lavignotte.googlepages.com > ------=_Part_67249_30789548.1228944469674--