poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Donahue, Michael" <michael.dona...@pearson.com>
Subject RE: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Date Thu, 19 Jan 2006 15:57:20 GMT
Igor -

I haven't had an opportunity to use HSSFDateUtil on a cell of type
CELL_TYPE_FORMULA, but I believe all cells have an assigned cell style and
all cell styles have an assigned data format.  So it would seem that you
could just copy the nested if (HSSFDateUtil.isCellDateFormatted(cell)) from
my example to check the data format of any cell type.  If it's a date data
format, then process it as a date, else handle it as a formula.

Still keep in mind that this only appears to work for the built-in data
formats supported by the HSSFDataFormat class.

The only other option I can think of is to parse the formula and see what
the data formats are for the cells referenced in the formula, but that's not
a fun solution.

One other thought, I think there's a Java based workbook viewer in one of
the POI HSSF sub-projects.  I haven't used it myself, but you might see how
they handled the issue.

Good luck,

- MJD

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

Micheal this code works for cells with normal data
types. Ofcourse if we deal with cells that has actual
dataa dn are formated as Excel data types that is ok.
But if cell containe FORMULA then:

cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA

and if you eveluate formula you can use
cell.getCellFormula();
to get actual FORMULA as string. But unless you know
what
result this formula returns ahead of time; number,
string or date forexample there is no way to tell. I
cannot find any way to find the data type of the
formula result.

The only code I was able to come up with is to default
to numeric DOUBLE and then test if that DOUBLE result
from formula is NAN - NOT NUMBER like this:

double icl = cell.getNumericCellValue();
// check if value is a NaN - NOT NUMBER
if (!Double.isNaN(icl))
    Double.toString(icl));
else
    cl = cell.getStringCellValue();
    
This code works in case when Formula returns number or
string. But if it will return say boolean or date type
there is no way to tell programatically what to
convert FORMULA cell to.
This is really the problem I am talknig about.

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

> 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
> 
=== message truncated ===


__________________________________________________
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/


Mime
View raw message