You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Nancy Espinoza <ne...@yahoo.com> on 2006/01/17 01:24:50 UTC
HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Hello people
I have an archive in Excel who have formulas that shows values
I need to save this values
With POI I use the method getCellFormula()
so it reads the formula but I need to save the value of the formula
For example if the formula is sum(A1:A2) and the value in A1 and A2 is 1
the result of the formula is 2
I need to read the result 2 no sum(A1:A2)
how can I do this?
Please help me
Thanks a lot
Nancy
__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/
Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Posted by ichy <ic...@gmail.com>.
hi Nancy.
this may be your problem.
http://java2.5341.com/msg/106624.html
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/
Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Posted by ichy <ic...@gmail.com>.
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/
Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Posted by Nancy Espinoza <ne...@yahoo.com>.
Hi ichy
Well in fact I need to read a date for example
if in cell a1 is 17/07/1980 and the next cell I have a formula that is =a1+1
who shows 18/07/1980 in the Excel I need to read this values
Reading the first cell its easy I use getDateCellValue().toString()
but when I try to read the another cell using getNumericCellValue() I didn´t obtain the value of the cell and when I use getCellFormula() I obtain the formula
how can I read the value? please help me
thanks
regards,
NANCY
ichy <ic...@gmail.com> escribió:
hi Nancy.
did you try getNumericCellValue() to get the value of
the formula?
regards
ichy
2006/1/17, Nancy Espinoza :
> Hello people
>
> I have an archive in Excel who have formulas that shows values
> I need to save this values
> With POI I use the method getCellFormula()
> so it reads the formula but I need to save the value of the formula
> For example if the formula is sum(A1:A2) and the value in A1 and A2 is 1
> the result of the formula is 2
> I need to read the result 2 no sum(A1:A2)
> how can I do this?
> Please help me
> Thanks a lot
>
> Nancy
>
> __________________________________________________
> Correo Yahoo!
> Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
> Regístrate ya - http://correo.espanol.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/
__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/
Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE
Posted by ichy <ic...@gmail.com>.
hi Nancy.
did you try getNumericCellValue() to get the value of
the formula?
regards
ichy
2006/1/17, Nancy Espinoza <ne...@yahoo.com>:
> Hello people
>
> I have an archive in Excel who have formulas that shows values
> I need to save this values
> With POI I use the method getCellFormula()
> so it reads the formula but I need to save the value of the formula
> For example if the formula is sum(A1:A2) and the value in A1 and A2 is 1
> the result of the formula is 2
> I need to read the result 2 no sum(A1:A2)
> how can I do this?
> Please help me
> Thanks a lot
>
> Nancy
>
> __________________________________________________
> Correo Yahoo!
> Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
> Regístrate ya - http://correo.espanol.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/