cvs commit: jakarta-poi/src/documentation/content/xdocs/hssf eval.xml

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

cvs commit: jakarta-poi/src/documentation/content/xdocs/hssf eval.xml

AviK-5
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: [hidden email]
Mailing List:    http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/

Reply | Threaded
Open this post in threaded view
|

RES: cvs commit: jakarta-poi/src/documentation/content/xdocs/hssf eval.xml

Glauco Vinicius Scheffel
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: [hidden email] [mailto:[hidden email]]
Enviada em: terça-feira, 24 de maio de 2005 11:23
Para: [hidden email]
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: [hidden email]
Mailing List:    http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List:    http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/

Reply | Threaded
Open this post in threaded view
|

Re: RES: cvs commit: jakarta-poi/src/documentation/content/xdocs/hssf eval.xml

Amol Deshmukh-2
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
<[hidden email]> 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: [hidden email] [mailto:[hidden email]]
> Enviada em: ter?a-feira, 24 de maio de 2005 11:23
> Para: [hidden email]
> 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:
> [hidden email]
> 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: [hidden email]
Mailing List:    http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/