poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Amol Deshmukh <amol...@yahoo.com>
Subject Re: RES: cvs commit: jakarta-poi/src/documentation/content/xdocs/hssf eval.xml
Date Mon, 30 May 2005 19:55:02 GMT
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
<glauco.scheffel@datasul.com.br> 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: avik@apache.org [mailto:avik@apache.org] 
> Enviada em: terça-feira, 24 de maio de 2005 11:23
> Para: jakarta-poi-cvs@apache.org
> 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:
> poi-dev-unsubscribe@jakarta.apache.org
> 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: poi-dev-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