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