poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nancy Espinoza <nespreyes2...@yahoo.com>
Subject RE: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Date Wed, 18 Jan 2006 18:25:05 GMT
Thanks guys for the answers
  I resolve my problem 
  Anyway thanks for your help :)
  Have a nice day
   
  regards,
  NANCY
   
   

"Donahue, Michael" <michael.donahue@pearson.com> escribió:
  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/

  


__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis! 
Regístrate ya - http://correo.espanol.yahoo.com/ 
Mime
  • Unnamed multipart/alternative (inline, 8-Bit, 0 bytes)
View raw message