poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ichy <ichyli...@gmail.com>
Subject Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Date Wed, 18 Jan 2006 17:05:52 GMT
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/


Mime
View raw message