Hi,
you need to call:
evaluator.setCurrentRow(row);
before :
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(cell);
Also, check your formula, you are referring to Cell
"A0" which does not exist, since excel numbering
starts from 1 :)
So I think the formula that you intended to use should
be "B1+B2" instead of "A1+A0" since:
row=0 & col=1 => B1
row=1 & col=1 => B2
row=2 & col=1 => B3
I tried your program with the above changes and it
works fine.
On a related note, you shouldnt have to call
cell.setCellType(..), it is handled internally
depending on the type of argument to setCellValue(..).
(Although, calling setCellType(..) will not cause a
problem if called with the correct arg value.)
Regards,
~ amol
--- Glauco Vinicius Scheffel
<[EMAIL PROTECTED]> wrote:
> Hi,
>
> I�m a subscriber of the POI list, to follow the
> product evolution since we use it in our GED tool. A
> friend o mine ask for assistance in using formulas
> but I never did this if POI (I just use to extract
> data). Do you know how can send his help request for
> the POI developers?
>
>
> TIA
>
>
>
> H E L P R E Q U E S T I S
> B E L O W
>
>
>
> Hi !
>
> I need a little assistence. I'm just trying to
> evaluate a simple formula using HSSF component. Take
> a look
> in the code below:
>
> import org.apache.poi.hssf.usermodel.HSSFCell;
> import
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
> import org.apache.poi.hssf.usermodel.HSSFRow;
> import org.apache.poi.hssf.usermodel.HSSFSheet;
> import org.apache.poi.hssf.usermodel.HSSFWorkbook;
> import org.apache.poi.hssf.util.CellReference;
>
> /**
> * Simple test class.
> * @version 1.0
> */
> public class Formula {
>
> private HSSFWorkbook wb;
> private HSSFSheet sheet;
>
> public Formula(){
> this.wb = new HSSFWorkbook();
> this.createSheet();
> }
>
> private void createSheet(){
> this.sheet = this.wb.createSheet();
> this.createCells();
> }
>
> private void createCells(){
> HSSFRow r = null;
> HSSFCell c = null;
>
> HSSFCell formula = null;
>
> this.sheet.createRow(0);
> this.sheet.createRow(1);
> this.sheet.createRow(2);
>
> c =
> this.sheet.getRow(0).createCell((short)1);
> c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
>
>
this.sheet.getRow(0).getCell((short)1).setCellValue(10);
> c =
> this.sheet.getRow(1).createCell((short)1);
> c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
>
>
this.sheet.getRow(1).getCell((short)1).setCellValue(10);
> c =
> this.sheet.getRow(2).createCell((short)1);
> c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
> c.setCellFormula("A0+A1");
>
> HSSFFormulaEvaluator evaluator = new
> HSSFFormulaEvaluator(this.sheet,this.wb);
>
> CellReference cellReference = new
> CellReference(2,1);
> HSSFRow row =
> sheet.getRow(cellReference.getRow());
> HSSFCell cell =
> row.getCell(cellReference.getCol());
>
> if (cell == null){
> System.out.println("its null");
> } else if (cell.getCellType() ==
> HSSFCell.CELL_TYPE_FORMULA) {
> System.out.println("Formula : "+
> cell.getCellFormula());
> }
>
> HSSFFormulaEvaluator.CellValue cellValue =
> evaluator.evaluate(cell);
>
>
> System.out.println(cellValue.getNumberValue());
> }
>
> public static void main(String args[]){
> Formula f = new Formula();
> }
>
> }
>
>
> My program aborts with a NullPointerException:
> Formula : A0+A1
> java.lang.NullPointerException
> at
>
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:281)
> at
>
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:181)
> at
>
com.datasul.dcl.formula.test.Formula.createCells(Formula.java:67)
> at
>
com.datasul.dcl.formula.test.Formula.createSheet(Formula.java:30)
> at
>
com.datasul.dcl.formula.test.Formula.<init>(Formula.java:25)
> at
>
com.datasul.dcl.formula.test.Formula.main(Formula.java:73)
> Exception in thread "main"
>
> Did I make any mistake ? Can somebody help me ?
>
> Thanks in advance !
>
>
>
>
> -----Mensagem original-----
> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Enviada em: ter�a-feira, 24 de maio de 2005 11:23
> Para: [EMAIL PROTECTED]
> Assunto: cvs commit:
> jakarta-poi/src/documentation/content/xdocs/hssf
> eval.xml
>
> avik 2005/05/24 07:23:03
>
> Modified: src/documentation/content/xdocs/hssf
> eval.xml
> Log:
> small update
>
> Revision Changes Path
> 1.3 +4 -5
>
jakarta-poi/src/documentation/content/xdocs/hssf/eval.xml
>
> Index: eval.xml
>
>
===================================================================
> RCS file:
>
/home/cvs/jakarta-poi/src/documentation/content/xdocs/hssf/eval.xml,v
> retrieving revision 1.2
> retrieving revision 1.3
> diff -u -r1.2 -r1.3
> --- eval.xml 19 May 2005 10:36:06 -0000 1.2
> +++ eval.xml 24 May 2005 14:23:03 -0000 1.3
> @@ -15,11 +15,10 @@
> formulas in Excels sheets read-in, or created
> in POI. This document explains
> how to use the API to evaluate your formulas.
>
> </p>
> - <warning> This code currently lives in
> Bugzilla as
> - <link
>
href="http://issues.apache.org/bugzilla/show_bug.cgi?id=34828";>
>
> - bug 34828 </link>. It is expected to land in
> POI CVS in the scratchpad
> - area soon.
> - </warning>
> + <note> This code currently lives the
> scratchpad area of the POI CVS repository.
> + Ensure that you have the scratchpad jar or
> the scratchpad build area in your
> + classpath before experimenting with this
> code.
> + </note>
> </section>
> <section><title>Status</title>
> <p> The code currently provides
> implementations for all the arithmatic operators.
>
>
>
>
>
---------------------------------------------------------------------
> 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/
>
>
=== message truncated ===
---------------------------------------------------------------------
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/
