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 <[EMAIL PROTECTED]> wrote:
From: Skye Shaw <[EMAIL PROTECTED]>
Subject: Re: HSSFCell With Formula Errors.
To: "POI Users List" <[email protected]>
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
