You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Jun Victorio <ju...@starcomsoft.com> on 2006/03/31 19:17:26 UTC

Problem With Cell Formula

Hi there,

 

I'm desperately need some help regarding  cell formula.  I'm using jakarta
poi 2.5 final and poi-scratchpad-3.0-alpha1-20050704.jar for the formula
evalution.

 

1.	First I need to get the cell formula in String.  So I base on the
user guide here is my code: 

HSSFCell cell = row.getCell(n);

If (cell.getCellType == HSSFCell.CELL_TYPE_FORMULA) {

            String formula = cell.getCellFormula();

}

What I've got is this value "NO IDEA SHARED FORMULA EXP PTG"

 

The formula is very simple: =A2*B2.

 

2.	Second, I need to get the cell formula value.  So from your
documentation I did the following 

HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);

HSSFRow row = sheet.getRow(n);

HSSFCell cell = row.getCell(n);

CellValue cellValue = evaluator.evaluate(cell);  

After this I get a NullPointerException on the last line.

 

So I change it to:

CellValue cellValue = evaluator.evaluateInCell(cell);  

Still get the NullPointerException

 

I've check thru the debugger the evaluator is not null, nor the cell.

 

I've even tried to use CellReference instead, but still the same problem.

 

I've attached the excel file and the sample source code.

 

I really appreciate in advance your help.

 

Thanks and God Bless

Jun Victorio

Re: Problem With Cell Formula

Posted by Daniel Noll <da...@nuix.com.au>.
Colin Zhao wrote:
> What I find is that poi sometime has difficulty parsing seemingly
> good formula texts.  When that happens, you will get what you got.
> Just retyping the formula over and saving the file will make the
> problem go away.  That's not a solution, just showing the problem.

Speaking more specifically, when you use Copy/Paste to duplicate a 
formula in Excel and then save the file, it stores one copy of the 
formula in a separate place in the spreadsheet file, and stores links to 
that "shared" formula in the actual cells.

POI can't currently handle this shared formula table, hence these will 
always break.

Retyping the formula over results in a completely new formula, which is 
why it will work after doing this.

The only solution would be adding shared formula support.  I'm not even 
sure where to start on that one...

Daniel

-- 
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia    Ph: +61 2 9280 0699
Web: http://www.nuix.com.au/                        Fax: +61 2 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.

---------------------------------------------------------------------
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: Problem With Cell Formula

Posted by Colin Zhao <co...@yahoo.com>.
What I find is that poi sometime has difficulty parsing seemingly good formula texts.  When that happens, you will get what you got.  Just retyping the formula over and saving the file will make the problem go away.  That's not a solution, just showing the problem.

Jun Victorio <ju...@starcomsoft.com> wrote:        st1\:*{behavior:url(#default#ieooui) }                Hi there,
   
  I’m desperately need some help regarding  cell formula.  I’m using jakarta poi 2.5 final and poi-scratchpad-3.0-alpha1-20050704.jar for the formula evalution.
   
    
   First I need to get the cell formula in String.  So I base on the user guide here is my code: 
  HSSFCell cell = row.getCell(n);
  If (cell.getCellType == HSSFCell.CELL_TYPE_FORMULA) {
              String formula = cell.getCellFormula();
  }
  What I’ve got is this value “NO IDEA SHARED FORMULA EXP PTG”
   
  The formula is very simple: =A2*B2.
   
    
   Second, I need to get the cell formula value.  So from your documentation I did the following 
  HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
  HSSFRow row = sheet.getRow(n);
  HSSFCell cell = row.getCell(n);
  CellValue cellValue = evaluator.evaluate(cell);  
  After this I get a NullPointerException on the last line.
   
  So I change it to:
  CellValue cellValue = evaluator.evaluateInCell(cell);  
  Still get the NullPointerException
   
  I’ve check thru the debugger the evaluator is not null, nor the cell.
   
  I’ve even tried to use CellReference instead, but still the same problem.
   
  I’ve attached the excel file and the sample source code.
   
  I really appreciate in advance your help.
   
  Thanks and God Bless
  Jun Victorio
   
   

---------------------------------------------------------------------
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/

		
---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.