Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <34B80AFC74D7914EAF5F70B69CC8B1D2110C0031@iowacexch1.ic.ncs.com>
From: "Donahue, Michael" <michael.donahue@pearson.com>
To: 'POI Users List' <poi-user@jakarta.apache.org>
Subject: RE: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Date: Wed, 18 Jan 2006 17:58:48 -0600
MIME-Version: 1.0
Content-Type: text/plain

Igor -

Here is and example of some of the code I use.  Excel has more formats than
the class HSSFDateUtil supports, so it doesn't always work, but you can see
if it works in your case.

Hope this helps...

- MJD

if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) {  // does not always work.
        Date dateValue = cell.getDateCellValue();
    } else {
        double dblValue = cell.getNumericCellValue();
} // end if


-----Original Message-----
From: Igor Androsov [mailto:iandrosov@yahoo.com] 
Sent: Wednesday, January 18, 2006 5:47 PM
To: POI Users List
Subject: RE: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE

Michael

What is the cell data type is a Formula that may
return some number or may be string or bolean based on
condition formulas?
Is it possible to finddata type of result from
formula?

Igor

--- "Donahue, Michael" <michael.donahue@pearson.com>
wrote:

> If the real question is how do you determine if you
> have a data or a number
> here is how you do that.
> 
> Check the Cell's format it will tell you if the
> format is a Date format or a
> number format.  Then you can use that to determine
> when to convert the
> double to a Date object.
> 
> This should work unless you use a non-standard
> format.
> 
> Good Luck,
> 
> - MJD
> 
> -----Original Message-----
> From: ichy [mailto:ichylinux@gmail.com] 
> Sent: Wednesday, January 18, 2006 11:06 AM
> To: POI Users List
> Subject: Re: HSSF POI READING FORMULAS IN AN EXCEL
> ARCHIVE
> 
> Hi Nancy.
> 
> well, this is a little code that i tested to get
> date value.
> 
>
---------------------------------------------------------------------------
> import java.io.FileInputStream;
> import org.apache.poi.hssf.usermodel.HSSFCell;
> import org.apache.poi.hssf.usermodel.HSSFRow;
> import org.apache.poi.hssf.usermodel.HSSFSheet;
> import org.apache.poi.hssf.usermodel.HSSFWorkbook;
> 
> public class Main {
> 
>     public static void main( String[] args ) {
>         String filename = "date-formula.xls";
> 
>         HSSFWorkbook hWorkbook;
>         try {
>             hWorkbook = new HSSFWorkbook( new
> FileInputStream( filename ) );
>             HSSFSheet hSheet = hWorkbook.getSheetAt(
> 0 );
>             HSSFRow hRow = hSheet.getRow( 0 );
>             HSSFCell hCell = hRow.getCell( (short)0
> );
>             HSSFCell hCell2 = hRow.getCell( (short)1
> );
> 
>             System.out.println("type of A1=" +
> getCellTypeString(
> hCell.getCellType() ) );
>             System.out.println(
> hCell.getNumericCellValue() );
>             System.out.println(
> hCell.getDateCellValue() );
> 
>             System.out.println("type of B1=" +
> getCellTypeString(
> hCell2.getCellType() ) );
>             System.out.println(
> hCell2.getNumericCellValue() );
>             System.out.println(
> hCell2.getDateCellValue() );
>         } catch ( Exception e ) {
>             e.printStackTrace();
>         }
>     }
> 
>     private static String getCellTypeString( int
> cellType ) {
>         switch ( cellType ) {
>             case HSSFCell.CELL_TYPE_BLANK :
>                 return "BLANK";
>             case HSSFCell.CELL_TYPE_BOOLEAN :
>                 return "BOOLEAN";
>             case HSSFCell.CELL_TYPE_ERROR :
>                 return "ERROR";
>             case HSSFCell.CELL_TYPE_FORMULA :
>                 return "FORMULA";
>             case HSSFCell.CELL_TYPE_NUMERIC :
>                 return "NUMERIC";
>             case HSSFCell.CELL_TYPE_STRING :
>                 return "STRING";
>             default :
>                 return "UNKNOWN";
>         }
>     }
> }
>
---------------------------------------------------------------------------
> 
> if i run the code above with a excel file
> "date-formula.xls" which
> has date "2006/1/20" on cell A1 and formula "A1+1"
> on cell B1,
> 
> i get a result as:
> 
> type of A1=NUMERIC
> 38737.0
> Fri Jan 20 00:00:00 JST 2006
> type of B1=FORMULA
> 38738.0
> Sat Jan 21 00:00:00 JST 2006
> 
> so, if you know that a1 has a number, you can use
> getNumericCellValue()
> and if you know that a1 has a date, you can use
> getDateCellValue().
> but you may not be able to know which cell type a1
> has by the formula
> "A1+1".
> 
> and the number 38737 and 38738 are the way excel
> handles date values
> if i remember correctly.
> 
> i hope this will help you a bit.
> 
> regards
> ichy
> 
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> Mailing List:    
> http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: 
> http://jakarta.apache.org/poi/
> 
>
****************************************************************************
> 
> This email may contain confidential material. 
> If you were not an intended recipient, 
> Please notify the sender and delete all copies. 
> We may monitor email to and from our network. 
>
****************************************************************************
> 
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> Mailing List:    
> http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: 
> http://jakarta.apache.org/poi/
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/

**************************************************************************** 
This email may contain confidential material. 
If you were not an intended recipient, 
Please notify the sender and delete all copies. 
We may monitor email to and from our network. 
****************************************************************************

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/