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" <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
> 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" <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]
> > 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:
> > [EMAIL PROTECTED]
> > 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:
> > [EMAIL PROTECTED]
> > 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: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
