poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Svede <jsv...@yahoo.com>
Subject Re: debugging evaluation failures when evaluating a cell in Excel
Date Thu, 16 Dec 2010 16:15:10 GMT
Yegor,

I'll need to create a more straightforward reproducer, but yes I can do that.

Jon




----- Original Message ----
From: Yegor Kozlov <yegor@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<jsvede@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


Mime
View raw message