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:[EMAIL PROTECTED] 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" <[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/ **************************************************************************** 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 The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
