You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Jon Svede <js...@yahoo.com> on 2010/12/14 02:02:05 UTC
debugging evaluation failures when evaluating a cell in Excel
I have some problems evaluating certain cells for a variety of reasons. While I
can step through and evaluation of a cell in the Eclipse debugger, this can be
very time consuming, especially if the problem exists near the end of the
stack. Is there some other way I can find the failure? In my cases, I usually
have cells that reference cells, that reference cells, so on and so forth, that
cross between worksheets.
When I noticed the issue with the NPV function I had actually modified the
source to have a listener which would notify me of each call to the
WorkbookEvaluator.evaluateAny() method. It was crude, but it helped me track
down the problem. However in that method I now notice that there is there is
already an IEvaluationListener within POI already. Is it possible to use this
listener to get the full list of cells/tokens/Ptgs that make up the evaluation
of a cell?
The specific issues that I am having relate to either being unable to evaluate
specific named variables and failures related to "Complex name formulas not
supported yet". I haven't spent much time on the named variables other than to
verify that under simple conditions POI can handle them (and it can). The
second issue makes no sense given the formulas of the cell it is complaining
about, so it has to be something deeper in the "stack" of formulas based on the
cells.
Any suggestions?
Thanks,
Jon
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: debugging evaluation failures when evaluating a cell in Excel
Posted by Jon Svede <js...@yahoo.com>.
Yegor,
I'll need to create a more straightforward reproducer, but yes I can do that.
Jon
----- Original Message ----
From: Yegor Kozlov <ye...@dinom.ru>
To: user@poi.apache.org
Sent: Thu, December 16, 2010 6:20:10 AM
Subject: Re: debugging evaluation failures when evaluating a cell in Excel
It might be a bug or an unsupported feature. In any case I would like to
debug the problem in my IDE.
Can you create a bug in Bugzilla and attach the problematic file and a
sample code that shows the issue. Ideally it would a junit test case.
Yegor
On 12/15/2010 7:23 PM, Jon Svede wrote:
> I was able to track my error back to a two cells that are blank in the
> spreadsheet. Excel doesn't seem to mind this, but when POI went to evaluate a
> complicate IF statement, it was failing. Here is the IF statement:
>
>=IF(C85="","",IF(C85<0,IF(AND(C85=1,start_time<1),-Input_Sheet_Template!H107*E85*percnt_var*start_time+Input_Sheet_Template!H107*E85*(1-start_time)),IF(C85<=start_time,-Input_Sheet_Template!H107*E85*percnt_var,-Input_Sheet_Template!H107*E85)))
>)
>
>
> In my case, H107 was blank.
>
> The error wasn't explicit, it did not throw any exception (which I think is
the
> appropriate and expected behavior now that I understand the issue). Instead
> when I evaluated the cell I was interested in, I got a non-zero in the
> ValueCell.getErrorCode(). However, there is actually an 'error' in this case,
> it is the ErrorConstants.ERROR_REF. It would be helpful if the return value
of
> the ValueCell.getErrorCode() returned an object rather than an int.
> Additionally, it would be great if there were text that could be retrieved (in
> this case, in the ErrorEval object the comments for each error code are really
> helpful).
>
> I've modified the ErrorEval class to have a private member variable to contain
> the String and added a constructor with (int, String) to allow instances to be
> constructed with the nice error message. I also added a public static method
> to retrieve the message based on the int. I need to do a little clean up and
> write a test for this, but after that I'd be happy to contribute these if they
> are helpful. I can also work on updating the ErrorEval class to return these
> items, again, if it is helpful.
>
> On the issue of the error code/exception, I assume that the reason for the
>error
> code rather than an exception is that this mimics Excel's behavior - it fails
> quietly. Would it be possible to have POI at least print a stacktrace or
> optionally throw an error? In my case, I am not an expert in Excel, nor even
> remotely familiar with the spreadsheets I am working with. It would help me
to
> have POI work in some 'strict' mode where it throws explicit exceptions with
> detailed info about the underlying error so I can get the right person to
> address it. Is this possible?
>
> Thanks,
>
> Jon
>
>
>
>
> ----- Original Message ----
> From: Jon Svede<js...@yahoo.com>
> To: user@poi.apache.org
> Sent: Mon, December 13, 2010 6:02:05 PM
> Subject: debugging evaluation failures when evaluating a cell in Excel
>
> I have some problems evaluating certain cells for a variety of reasons. While
>I
>
> can step through and evaluation of a cell in the Eclipse debugger, this can be
> very time consuming, especially if the problem exists near the end of the
> stack. Is there some other way I can find the failure? In my cases, I
usually
> have cells that reference cells, that reference cells, so on and so forth,
that
> cross between worksheets.
>
> When I noticed the issue with the NPV function I had actually modified the
> source to have a listener which would notify me of each call to the
> WorkbookEvaluator.evaluateAny() method. It was crude, but it helped me track
> down the problem. However in that method I now notice that there is there is
> already an IEvaluationListener within POI already. Is it possible to use this
> listener to get the full list of cells/tokens/Ptgs that make up the evaluation
> of a cell?
>
> The specific issues that I am having relate to either being unable to evaluate
> specific named variables and failures related to "Complex name formulas not
> supported yet". I haven't spent much time on the named variables other than
to
> verify that under simple conditions POI can handle them (and it can). The
> second issue makes no sense given the formulas of the cell it is complaining
> about, so it has to be something deeper in the "stack" of formulas based on
the
> cells.
>
> Any suggestions?
>
> Thanks,
>
> 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
>
>
---------------------------------------------------------------------
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: debugging evaluation failures when evaluating a cell in Excel
Posted by Yegor Kozlov <ye...@dinom.ru>.
It might be a bug or an unsupported feature. In any case I would like to
debug the problem in my IDE.
Can you create a bug in Bugzilla and attach the problematic file and a
sample code that shows the issue. Ideally it would a junit test case.
Yegor
On 12/15/2010 7:23 PM, Jon Svede wrote:
> I was able to track my error back to a two cells that are blank in the
> spreadsheet. Excel doesn't seem to mind this, but when POI went to evaluate a
> complicate IF statement, it was failing. Here is the IF statement:
>
> =IF(C85="","",IF(C85<0,IF(AND(C85=1,start_time<1),-Input_Sheet_Template!H107*E85*percnt_var*start_time+Input_Sheet_Template!H107*E85*(1-start_time)),IF(C85<=start_time,-Input_Sheet_Template!H107*E85*percnt_var,-Input_Sheet_Template!H107*E85)))
>
>
> In my case, H107 was blank.
>
> The error wasn't explicit, it did not throw any exception (which I think is the
> appropriate and expected behavior now that I understand the issue). Instead
> when I evaluated the cell I was interested in, I got a non-zero in the
> ValueCell.getErrorCode(). However, there is actually an 'error' in this case,
> it is the ErrorConstants.ERROR_REF. It would be helpful if the return value of
> the ValueCell.getErrorCode() returned an object rather than an int.
> Additionally, it would be great if there were text that could be retrieved (in
> this case, in the ErrorEval object the comments for each error code are really
> helpful).
>
> I've modified the ErrorEval class to have a private member variable to contain
> the String and added a constructor with (int, String) to allow instances to be
> constructed with the nice error message. I also added a public static method
> to retrieve the message based on the int. I need to do a little clean up and
> write a test for this, but after that I'd be happy to contribute these if they
> are helpful. I can also work on updating the ErrorEval class to return these
> items, again, if it is helpful.
>
> On the issue of the error code/exception, I assume that the reason for the error
> code rather than an exception is that this mimics Excel's behavior - it fails
> quietly. Would it be possible to have POI at least print a stacktrace or
> optionally throw an error? In my case, I am not an expert in Excel, nor even
> remotely familiar with the spreadsheets I am working with. It would help me to
> have POI work in some 'strict' mode where it throws explicit exceptions with
> detailed info about the underlying error so I can get the right person to
> address it. Is this possible?
>
> Thanks,
>
> Jon
>
>
>
>
> ----- Original Message ----
> From: Jon Svede<js...@yahoo.com>
> To: user@poi.apache.org
> Sent: Mon, December 13, 2010 6:02:05 PM
> Subject: debugging evaluation failures when evaluating a cell in Excel
>
> I have some problems evaluating certain cells for a variety of reasons. While I
>
> can step through and evaluation of a cell in the Eclipse debugger, this can be
> very time consuming, especially if the problem exists near the end of the
> stack. Is there some other way I can find the failure? In my cases, I usually
> have cells that reference cells, that reference cells, so on and so forth, that
> cross between worksheets.
>
> When I noticed the issue with the NPV function I had actually modified the
> source to have a listener which would notify me of each call to the
> WorkbookEvaluator.evaluateAny() method. It was crude, but it helped me track
> down the problem. However in that method I now notice that there is there is
> already an IEvaluationListener within POI already. Is it possible to use this
> listener to get the full list of cells/tokens/Ptgs that make up the evaluation
> of a cell?
>
> The specific issues that I am having relate to either being unable to evaluate
> specific named variables and failures related to "Complex name formulas not
> supported yet". I haven't spent much time on the named variables other than to
> verify that under simple conditions POI can handle them (and it can). The
> second issue makes no sense given the formulas of the cell it is complaining
> about, so it has to be something deeper in the "stack" of formulas based on the
> cells.
>
> Any suggestions?
>
> Thanks,
>
> 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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: debugging evaluation failures when evaluating a cell in Excel
Posted by Jon Svede <js...@yahoo.com>.
I was able to track my error back to a two cells that are blank in the
spreadsheet. Excel doesn't seem to mind this, but when POI went to evaluate a
complicate IF statement, it was failing. Here is the IF statement:
=IF(C85="","",IF(C85<0,IF(AND(C85=1,start_time<1),-Input_Sheet_Template!H107*E85*percnt_var*start_time+Input_Sheet_Template!H107*E85*(1-start_time)),IF(C85<=start_time,-Input_Sheet_Template!H107*E85*percnt_var,-Input_Sheet_Template!H107*E85)))
In my case, H107 was blank.
The error wasn't explicit, it did not throw any exception (which I think is the
appropriate and expected behavior now that I understand the issue). Instead
when I evaluated the cell I was interested in, I got a non-zero in the
ValueCell.getErrorCode(). However, there is actually an 'error' in this case,
it is the ErrorConstants.ERROR_REF. It would be helpful if the return value of
the ValueCell.getErrorCode() returned an object rather than an int.
Additionally, it would be great if there were text that could be retrieved (in
this case, in the ErrorEval object the comments for each error code are really
helpful).
I've modified the ErrorEval class to have a private member variable to contain
the String and added a constructor with (int, String) to allow instances to be
constructed with the nice error message. I also added a public static method
to retrieve the message based on the int. I need to do a little clean up and
write a test for this, but after that I'd be happy to contribute these if they
are helpful. I can also work on updating the ErrorEval class to return these
items, again, if it is helpful.
On the issue of the error code/exception, I assume that the reason for the error
code rather than an exception is that this mimics Excel's behavior - it fails
quietly. Would it be possible to have POI at least print a stacktrace or
optionally throw an error? In my case, I am not an expert in Excel, nor even
remotely familiar with the spreadsheets I am working with. It would help me to
have POI work in some 'strict' mode where it throws explicit exceptions with
detailed info about the underlying error so I can get the right person to
address it. Is this possible?
Thanks,
Jon
----- Original Message ----
From: Jon Svede <js...@yahoo.com>
To: user@poi.apache.org
Sent: Mon, December 13, 2010 6:02:05 PM
Subject: debugging evaluation failures when evaluating a cell in Excel
I have some problems evaluating certain cells for a variety of reasons. While I
can step through and evaluation of a cell in the Eclipse debugger, this can be
very time consuming, especially if the problem exists near the end of the
stack. Is there some other way I can find the failure? In my cases, I usually
have cells that reference cells, that reference cells, so on and so forth, that
cross between worksheets.
When I noticed the issue with the NPV function I had actually modified the
source to have a listener which would notify me of each call to the
WorkbookEvaluator.evaluateAny() method. It was crude, but it helped me track
down the problem. However in that method I now notice that there is there is
already an IEvaluationListener within POI already. Is it possible to use this
listener to get the full list of cells/tokens/Ptgs that make up the evaluation
of a cell?
The specific issues that I am having relate to either being unable to evaluate
specific named variables and failures related to "Complex name formulas not
supported yet". I haven't spent much time on the named variables other than to
verify that under simple conditions POI can handle them (and it can). The
second issue makes no sense given the formulas of the cell it is complaining
about, so it has to be something deeper in the "stack" of formulas based on the
cells.
Any suggestions?
Thanks,
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