poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Igor Androsov <iandro...@yahoo.com>
Subject RE: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Date Wed, 18 Jan 2006 23:47:29 GMT
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/


Mime
View raw message