Re: HSSFCell With Formula Errors.

[Skye Shaw]

> Hi,
>
> you should look at the HSSFFormulaEvaluator class.
>
> Pierre

Thanks Pierre. Unfortunately, the HSSFFormulaEvaluator causes problems
too:

public String getStringFromCell(HSSFCell cell)
{
  String val = cell.toString();
  if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
  //some stuff
  }
  else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
  val = getStringFromCell(fe.evaluateInCell(cell));
  }

  return val;
}

Exception in thread "main" java.lang.RuntimeException: Error Value can
only be 0
,7,15,23,29,36 or 42. It cannot be -60
at
org.apache.poi.hssf.record.BoolErrRecord.setValue(BoolErrRecord.java:124)
at
org.apache.poi.hssf.usermodel.HSSFCell.setCellErrorValue(HSSFCell.java:755)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setCellValue(HSSFFormulaEvaluator.java:267)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:239)

HSSFFormulaEvaluator doesn't like circular references; I don't blame it.

The alternate: HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell).
This is ugly, I think, as I have to (re)check the type of the CellValue
before I can get the value.

On a related note, when is a non-formula cell an error cell?

-Skye

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Pierre Lavignotte]

I don't think that :
HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell)
is ugly....

It's the right way to evaluate a cell formula.
From the CellValue, you can check the result type (which can be ERROR I
think), and read it accordingly.

I'm not used with the error cell so I can't tell you more on that.

Pierre

On Wed, Oct 8, 2008 at 1:14 AM, Skye Shaw <sshaw@...>wrote:

> Hi,
>>
>> you should look at the HSSFFormulaEvaluator class.
>>
>> Pierre
>>
>
> Thanks Pierre. Unfortunately, the HSSFFormulaEvaluator causes problems too:
>
> public String getStringFromCell(HSSFCell cell)
> {
>        String val = cell.toString();
>        if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
>                //some stuff
>        }
>        else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
>                val = getStringFromCell(fe.evaluateInCell(cell));
>        }
>
>        return val;
> }
>
> Exception in thread "main" java.lang.RuntimeException: Error Value can only
> be 0
> ,7,15,23,29,36 or 42. It cannot be -60
> at
> org.apache.poi.hssf.record.BoolErrRecord.setValue(BoolErrRecord.java:124)
> at
> org.apache.poi.hssf.usermodel.HSSFCell.setCellErrorValue(HSSFCell.java:755)
> at
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setCellValue(HSSFFormulaEvaluator.java:267)
> at
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:239)
>
> HSSFFormulaEvaluator doesn't like circular references; I don't blame it.
>
> The alternate: HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell).
> This is ugly, I think, as I have to (re)check the type of the CellValue
> before I can get the value.
>
> On a related note, when is a non-formula cell an error cell?
>
> -Skye
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>

--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com

[Skye Shaw]

On Wed, 8 Oct 2008, Pierre Lavignotte wrote:
> I don't think that :
> HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell)
> is ugly....
>
> It's the right way to evaluate a cell formula.


It's ugly because in order to get the cell's value I have to check
HSSFCell's type, then, if it's a formula, I have to check
HSSFFormulaEvaluator.CellValue's type against the same set of conditions
(almost) I've just applied to HSSFCell:


String getStringFromCell(HSSFCell cell)
{
String val = cell.toString();
if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
     short i = cell.getCellStyle().getDataFormat();
     String format = cellFormat.getFormat(i);
     if(HSSFDateUtil.isADateFormat((int)i,format)) {
  //date stuff
     }
}
else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
   HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell);
//Now recheck the type of HSSFFormulaEvaluator.CellValue
}
         return val;
  }

Well, I shouldn't say have -as there are alternatives, (not
evaluateInCell(HSSFCell cell) as that has problems; see earlier email)
though it seems to me that the public interface to HSSFCell is broken.

Maybe it's just my limited experience with POI, or maybe I'm just
confused, though it appears that HSSFCell already knows the result of its
formula upon construction, its already been evaluated. Yet I still have
to use the formula evaluator to re "evaluate"- to tell me the state of the
object, when the object (seems) to expose public methods for such
inquiries?

Namely getCachedFormulaResultType().

Unfortunately this method raises an exception when called on a numeric
cell:

Exception in thread "main" java.lang.IllegalStateException: Exception in
thread "main" java.lang.IllegalStateException: Cannot get a text value
from a numeric formula cell at
org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)

Get a text value? I just wanted the integer result type, why do I care
that about getting a text value from a numeric formula cell, I wanted an
integer representing the CachedFormulaResultType.


I could go on, but I might be on the wrong track.

Thanks,
Skye



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Anthony Andrews]

Just on a side issue, you would be wrong to rely on the value that getCachedFormulaTypeResult() may be able to supply to you.

You see, the problem is that the cached result of the formula MAY - and I do say may - be wrong as it relies upon when the user has chosen to perform the recalculation whilst they were interacting with the sheet using the Excel application. Think what may happen if they decided not to perform a recalculation of the sheet when they made a permanent change to a cell that was included in a formula and then saved that sheet away. Can you then assume that the cache holds the 'correct' value?

--- On Tue, 10/14/08, Skye Shaw <sshaw@...> wrote:
From: Skye Shaw <sshaw@...>
Subject: Re: HSSFCell With Formula Errors.
To: "POI Users List" <user@...>
Date: Tuesday, October 14, 2008, 6:49 PM

On Wed, 8 Oct 2008, Pierre Lavignotte wrote:
> I don't think that :
> HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell)
> is ugly....
>
> It's the right way to evaluate a cell formula.


It's ugly because in order to get the cell's value I have to check
HSSFCell's type, then, if it's a formula, I have to check
HSSFFormulaEvaluator.CellValue's type against the same set of conditions
(almost) I've just applied to HSSFCell:


String getStringFromCell(HSSFCell cell)
{
String val = cell.toString();
if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
     short i = cell.getCellStyle().getDataFormat();
     String format = cellFormat.getFormat(i);
     if(HSSFDateUtil.isADateFormat((int)i,format)) {
  //date stuff
     }
}
else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
   HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell);
//Now recheck the type of HSSFFormulaEvaluator.CellValue
}
         return val;
  }

Well, I shouldn't say have -as there are alternatives, (not
evaluateInCell(HSSFCell cell) as that has problems; see earlier email)
though it seems to me that the public interface to HSSFCell is broken.

Maybe it's just my limited experience with POI, or maybe I'm just
confused, though it appears that HSSFCell already knows the result of its
formula upon construction, its already been evaluated. Yet I still have
to use the formula evaluator to re "evaluate"- to tell me the state
of the
object, when the object (seems) to expose public methods for such
inquiries?

Namely getCachedFormulaResultType().

Unfortunately this method raises an exception when called on a numeric
cell:

Exception in thread "main" java.lang.IllegalStateException: Exception
in
thread "main" java.lang.IllegalStateException: Cannot get a text
value
from a numeric formula cell at
org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)

Get a text value? I just wanted the integer result type, why do I care
that about getting a text value from a numeric formula cell, I wanted an
integer representing the CachedFormulaResultType.


I could go on, but I might be on the wrong track.

Thanks,
Skye



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...




     

[Nick Burch]

> Maybe it's just my limited experience with POI, or maybe I'm just
> confused, though it appears that HSSFCell already knows the result of
> its formula upon construction, its already been evaluated.

The formula is stored in excel as:
* array of ptgs that make up the formaula contents (note - the text
   of the formula is not stored, only the parsed form)
* if the formula result is a number, the last evaluated answer for
   it is stored on the FormulaRecord
* if the formula result is a string, the last evaluated answer for
   it is stored in a following StringRecord

I think what you want is already available. If you know a cell is a
formula with a numeric response, just call getCellNumericValue() and
you'll get back the last (excel) evaluated value for it

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Skye Shaw]

On Wed, 15 Oct 2008, Anthony Andrews wrote:
> Just on a side issue, you would be wrong to rely on the value that
> getCachedFormulaTypeResult() may be able to supply to you.

Indeed.

> You see, the problem is that the cached result of the formula MAY - and
> I do say may - be wrong as it relies upon when the user has chosen to
> perform the recalculation whilst they were interacting with the sheet
> using the Excel application. Think what may happen if they decided not
> to perform a recalculation of the sheet when they made a permanent
> change to a cell that was included in a formula and then saved that
> sheet away. Can you then assume that the cache holds the 'correct'
> value?

No. I agree. But lets say that stale results are not a concern.
getCachedFormulaResultType() still does not act as expected. As pointed
out in my prior emails, it does not give you a cached result, or any
result, when the result is numeric.

This is something for Bugzilla.

I appreciate your insight, thanks.

-Skye




Sure, agreeded. Lets say that working with a stale value in not a cencern

> --- On Tue, 10/14/08, Skye Shaw <sshaw@...> wrote:
> From: Skye Shaw <sshaw@...>
> Subject: Re: HSSFCell With Formula Errors.
> To: "POI Users List" <user@...>
> Date: Tuesday, October 14, 2008, 6:49 PM
>
> On Wed, 8 Oct 2008, Pierre Lavignotte wrote:
>> I don't think that :
>> HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell)
>> is ugly....
>>
>> It's the right way to evaluate a cell formula.
>
>
> It's ugly because in order to get the cell's value I have to check
> HSSFCell's type, then, if it's a formula, I have to check
> HSSFFormulaEvaluator.CellValue's type against the same set of conditions
> (almost) I've just applied to HSSFCell:
>
>
> String getStringFromCell(HSSFCell cell)
> {
> String val = cell.toString();
> if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
>    short i = cell.getCellStyle().getDataFormat();
>    String format = cellFormat.getFormat(i);
>    if(HSSFDateUtil.isADateFormat((int)i,format)) {
> //date stuff
>    }
> }
> else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA) {
>   HSSFFormulaEvaluator.CellValue v = fe.evaluate(cell);
> //Now recheck the type of HSSFFormulaEvaluator.CellValue
> }
>         return val;
>  }
>
> Well, I shouldn't say have -as there are alternatives, (not
> evaluateInCell(HSSFCell cell) as that has problems; see earlier email)
> though it seems to me that the public interface to HSSFCell is broken.
>
> Maybe it's just my limited experience with POI, or maybe I'm just
> confused, though it appears that HSSFCell already knows the result of its
> formula upon construction, its already been evaluated. Yet I still have
> to use the formula evaluator to re "evaluate"- to tell me the state
> of the
> object, when the object (seems) to expose public methods for such
> inquiries?
>
> Namely getCachedFormulaResultType().
>
> Unfortunately this method raises an exception when called on a numeric
> cell:
>
> Exception in thread "main" java.lang.IllegalStateException: Exception
> in
> thread "main" java.lang.IllegalStateException: Cannot get a text
> value
> from a numeric formula cell at
> org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:625)
>
> Get a text value? I just wanted the integer result type, why do I care
> that about getting a text value from a numeric formula cell, I wanted an
> integer representing the CachedFormulaResultType.
>
>
> I could go on, but I might be on the wrong track.
>
> Thanks,
> Skye
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>
>
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Skye Shaw]

On Wed, 15 Oct 2008, Nick Burch wrote:
> I think what you want is already available. If you know a cell is a formula
> with a numeric response, just call getCellNumericValue() and you'll get back
> the last (excel) evaluated value for it

Hi Nick,

  Thanks for the info. Yes the cell's value is available via
fe.evaluate(cell).getCellType(), but this, IMHO, as I said earlier, is
ugly, the cell already knows the formula's state (cached state at least),
so why does another class and another type have to be used?

The cell's type must then be rechecked on this different
class against the same set of conditions that have just been applied the
cell.


To me, it's just a hack to get around the problems/exceptions/bugs/whatever
with HSSFCell.getCachedResultType() and HSSFFormulaEvaluator.evaluateInCell().

I'm repeating my self but my point was that using the formula evaluator
to determine the cells type -as read from the spreadsheet,
should be unnecessary. getCachedFormulaResultType() should suffice.

Thanks,
Skye

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Nick Burch]

On Wed, 15 Oct 2008, Skye Shaw wrote:
> To me, it's just a hack to get around the problems/exceptions/bugs/whatever
> with HSSFCell.getCachedResultType() and
> HSSFFormulaEvaluator.evaluateInCell().

If you think there's a bug in getCachedResultType(), please create a
simple unit test that shows it up, and open a new bug on bugzilla with it.
Someone can then take a look

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...