poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Svede <jsv...@yahoo.com>
Subject Re: Error Values
Date Fri, 03 Dec 2010 23:22:11 GMT
I spent some time trying to track down the root cause of the error.  In the 
following scenario, I am seeing this error message:

"3D references need a workbook to determine formula text"

Now, this error doesn't show up when I run my spreadsheet with POI 3.7 
unmodified.   I was messing around with the source to try to see if I could 
capture the parse tree, so I modified the WorkbookEvaluator class to accept a 
listener.  In the WorkbookEvaluator.evaluateFormula( OperationEvaluationContext 
ec, Ptg[] ptgs) method, I notify the listener(s) of the args that are passed in. 
In my listener I simply enumerate the Ptg[] array and the 
OperationEvaluationContext.  I am only evaluating 1 cell per execution because 
of the verbosity this creates. For reasons I can't explain, only when I 
enumerate the Ptgs does that message get written out.  The cell contents that 
cause this, at least one example of it, is as follows:

=INDEX('MACRS_Depr. Table'!C92:I115, MATCH("NPV (Real)",'MACRS_Depr. 
Table'!C92:C115,0),MATCH($B$84,'MACRS_Depr. Table'!C92:I92,0))*(1+B158)

I am seeing this error in any place (so far) use the INDEX function in a similar 

Does this shed anymore light on the nature of these errors?  I don't really know 
Excel well enough to understand what that error message is trying to tell me.

This may be a red herring, as I have modified the source, but again, my issue is 
related to the complexity of the parse tree that is created when evaluating a 
cell that has cell references which have references....etc.  If there were 
another way to capture the full stack of the cells,  I would use that instead. 

Thanks in advance,


----- Original Message ----
From: Jon Svede <jsvede@yahoo.com>
To: POI Developers List <dev@poi.apache.org>
Sent: Thu, December 2, 2010 10:24:46 PM
Subject: Re: Error Values


I updated my env to use 3.7 and while the number of instances of this issue has 
decreased (I think) the issue is still present.  It affects the main cells that 
I need to be evaluate, unfortunately.   The main error values I am seeing are 15 

and -60.

Any other ideas how I can track this down?

What would be very helpful (and probably not easy to get) is the fully resolved 
(or as close as one can get) tree of cell dependencies.  I'd imagine though that 

this isn't something that is built until a cell is evaluated.  If I had that I 
could probably figure out where the issue is.



----- Original Message ----
From: David Fisher <dfisher@jmlafferty.com>
To: POI Users List <user@poi.apache.org>
Cc: dev@poi.apache.org
Sent: Thu, December 2, 2010 4:23:25 PM
Subject: Re: Error Values

Hi Jon -

> I am using POI 3.6.

A lot of bug fixes and improvements with formula evaluation between 3.6 and 3.7.

> I have a .xls file in which I am evaluating some cells that have formulas and 
> macros in them.  When I get to evaluate a cell with formulas in it, instead of 

> getting a number I am getting #VALUE! as the return (although not always, in 
> most cases the cells evaluate normally).  After a little digging I noticed that 
> when I look at the CellValue.getErrorValue() in this case it is always > 0, 
> usually 15 or 60.  I tried looking in the source of the CellValue class to see 

> what these values mean, but I didn't find anything.  Here is my code:
>        CellReference cellReference = new CellReference( row.getRowNum(), 
> cell.getColumnIndex() ) ;
>        CellValue cellValue = evaluator.evaluate( cell ) ;
>        byte err = cellValue.getErrorValue() ;
> It doesn't fail outright, like it would when you encounter a function written 
> VB or something, so it's not as fatal as that.
> It would appear that it is a reference issue, assuming that the value "#VALUE!" 
> has the same meaning in POI as it does when using Excel.  What I don't get is 
> that this spreadsheet works fine in Excel (not surprising, I know).  I just 
> to figure where the problem actually is so I can address it.  Is there a way 
> POI to give me the list dependencies for a given cell?
> Does anyone have any pointers for debugging this or teasing out more 
> regarding the error?

I would try 3.7-FINAL first.


> Thanks in advance,
> Jon
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org

To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

View raw message