You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Jon Svede <js...@yahoo.com> on 2010/12/02 23:50:22 UTC
Error Values
Hi,
I am using POI 3.6.
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?
Thanks in advance,
Jon
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Error Values
Posted by Jon Svede <js...@yahoo.com>.
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 <js...@yahoo.com>
To: POI Developers List <de...@poi.apache.org>
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 <df...@jmlafferty.com>
To: POI Users List <us...@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
>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: 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
Re: Error Values
Posted by Jon Svede <js...@yahoo.com>.
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 <df...@jmlafferty.com>
To: POI Users List <us...@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
>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: 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
Re: Error Values
Posted by David Fisher <df...@jmlafferty.com>.
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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Error Values
Posted by David Fisher <df...@jmlafferty.com>.
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: 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