It is a red herring, as I suspected. I was calling toFormulaString() on the ptg and for several different Ptg subclasses this method throws that runtime exception. Sorry about that!
Jon ----- Original Message ---- From: Jon Svede <[email protected]> To: POI Developers List <[email protected]> Sent: Fri, December 3, 2010 4:22:11 PM Subject: Re: Error Values 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 manner. 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, Jon ----- Original Message ---- From: Jon Svede <[email protected]> To: POI Developers List <[email protected]> Sent: Thu, December 2, 2010 10:24:46 PM Subject: Re: Error Values Dave, 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. Thanks, Jon ----- Original Message ---- From: David Fisher <[email protected]> To: POI Users List <[email protected]> Cc: [email protected] 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 >in > > 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 >want > > to figure where the problem actually is so I can address it. Is there a way >for > > POI to give me the list dependencies for a given cell? > > Does anyone have any pointers for debugging this or teasing out more >information > > regarding the error? I would try 3.7-FINAL first. Regards, Dave > > Thanks in advance, > > Jon > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
