=> More power to the user!! :)
This will break existing code. Very Bad Thing! Also, with my Alan
Cooper fanboy
hat on, I think recommending to /prefer/ calling setCellType() is in itself a
Bad Thing.
I would say, add a method called setCellFormulaResult() overloaded for
string/num etc. Remember, in many situations, people just depend on excel to
recalc formula's, and this will enable existing code to work as-is for them.
For users for whom this does not work, we provide additional powers.
I am sure we can fix the class cast exception on calling setCellFormula
followed
setCellValue independently.
Thoughts??
Regards
-
Avik
Quoting Amol Deshmukh <[EMAIL PROTECTED]>:
** WARNING: LONG MAIL **
Now the only thing that keeps me from updating the
patch as i discussed earlier is this:
Normally, users do not need to use HSSFCell.setCellType
since setvalue takes care of that... unless the original
record is/was a FormulaAggregateRecord. In which
case my patch will simply set the value as result of
formula, but if the user meant to actually overwrite
the cell contents (which was a formula string) with
the new value in the setCellValue (s)he will have to
explicitly call HSSFCell.setCellType to force the
underlying record to change from FormulaRecordAggregate
to LabelSSTRecord/NumerRecord/BoolErrRecord etc.
Code to illustrate how things will function after
the patch:
1 HSSFCell cell = row.getCell(idx); //get existing cell
2
3 cell.setCellValue("test"); // set a string value
If cell contained a formula, then after line 3,
the formula's result value will be set to the string,
the cell contents will STILL BE A FORMULA.
To force the contents to change from formula to string:
1 HSSFCell cell = row.getCell(idx); // get existing cell
2 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // IMP!!
3 cell.setCellValue("test"); // set a string value
Line 2 is now required to force the underlying record
to change from FormulaRecordAggregate to LabelSSTRecord.
This code will result in the cell containing String,
not formula.
This changes the semantics of the API slightly, since
now it may be recommended to /prefer/ calling
setCellType(..) explicitly in the user code. Changing
the semantics is a BadThing, but the advantage is that:
a. setCellFormula(..) followed by setCellValue(..)
will NOT throw ClassCastException :)
b. setCellFormula(..) followed by setCellValue(..)
will allow user to set the default value for the
formula evaluation. Whereas, setCellType(..)
followed by setCellValue(..) will overwrite the
formula in the cell with the string/number/bool
value specified => More power to the user!! :)
Regards,
~ amol
-----Original Message-----
From: Avik Sengupta [mailto:[EMAIL PROTECTED]
Sent: Friday, June 10, 2005 10:32 AM
To: poi-dev@jakarta.apache.org
Subject: RE: FormulaRecord getValue &setVal ue enhancement
Sounds good!
AFAIK, getValue for numbers and strings should already work
in HSSFCell,
no? there is, of course, no implementation for setting.
getValueType -- is there enuf logic to know this for sure?
Also, if you are doing this, you may as well update the HSSFCell
methods ... pretty please! :)
On Thu, 2005-06-09 at 17:17 -0400, Amol Deshmukh wrote:
> Ok.. thanks for the info! I didnt realise that
> FormulaRecordAggregate stored the string value
> of the formula evaluation.
>
> I dont think the PATCH for 35290 will have any
> adverse effects on the StringRecord. But looks
> like there needs to be a setValue(..) in the
> FormulaRecordAggregate that will set the correct
> value in a StringRecord if the calculated value
> is String.
>
> So here's what I've done locally in addition to the
> changes in current PATCH:
>
> In FormulaRecordAggregate.java
> 1. add setValue(..) overloaded for double, String
> , errorCode, blank value types
> 2. add getNumberValue(), getBooleanValue()
> , getErrorCodeValue()
> 3. add getValueType()
>
> I plan on doing testing with actual excel files
> as well to ensure deserializing and serializing
> both work fine after the change.
>
> If that seems fine to you, I will update the
> PATCH file for 35290.
>
> Regards,
> ~ amol
>
>
>
>
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, June 09, 2005 2:19 PM
> > To: Amol Deshmukh
> > Subject: Re: FormulaRecord getValue &setVal ue enhancement
> >
> >
> > String formula results are stored as a String record that
> > follows a formula
> > record. POI handles them together as FormulaRecordAggregate.
> > Can you ensure
> > your patch does not mess this up? Thanks.
> >
> > Quoting Amol Deshmukh <[EMAIL PROTECTED]>:
> >
> > >
> > > I started of trying to implement fix for 35288 but=20
> > > realised that FormulaRecord assumes and stores only
> > > double value as the result of the formula.
> > >
> > > I checked OO documentation=20
> > > (pg 163 of http://sc.openoffice.org/excelfileformat.pdf)=20
> > > and it has description of how excel stores different
> > > value types for a formula result.
> > >
> > > So the patch attempts to do just that...
> > >
> > >
> > > ... but I assumed that for string formulas, the
> > > value is not stored since the value bytes are
> > > fixed length (8 bytes) so cannot hold a String
> > > of arbitrary length.
> > >
> > > Now, FormulaRecord can accept and serialize=20
> > > values for formula of types other than double,
> > > this will enable a fix for 35288 (since instead
> > > of casting to specific Record, we hace option=20
> > > of casting to FormulaRecord too since now it
> > > can accept different data types in the setvalue)
> > >
> > > (hope I made some sense :)
> > >
> > > ~ amol
> > >
> > >
> > >
> > >
> > >> -----Original Message-----
> > >> From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
> > >> Sent: Thursday, June 09, 2005 1:39 PM
> > >> To: poi-dev@jakarta.apache.org
> > >> Subject: Re: DO NOT REPLY [Bug 35290] - FormulaRecord=20
> > >> getValue &setValue
> > >> enhancement
> > >> =20
> > >> =20
> > >> Amol,
> > >> =20
> > >> Can you please talk us thru this patch.. I am not clear what=20
> > >> it does. In
> > >> particular, I am not sure how this interacts with=20
> > >> StringRecord. Also does this
> > >> help with your other bug (35288)
> > >> =20
> > >> Thanks for the patch.
> > >> =20
> > >> Regards
> > >> -
> > >> Avik
> > >> =20
> > >> =20
> > >> Quoting [EMAIL PROTECTED]:
> > >> =20
> > >> > DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG=B7
> > >> > RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
> > >> > <http://issues.apache.org/bugzilla/show_bug.cgi?id=3D35290>.
> > >> > ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND=B7
> > >> > INSERTED IN THE BUG DATABASE.
> > >> >
> > >> > http://issues.apache.org/bugzilla/show_bug.cgi?id=3D35290
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > ------- Additional Comments From [EMAIL PROTECTED]
> > >> 2005-06-09 18:37 -------
> > >> > Created an attachment (id=3D15355)
> > >> > -->=20
> > >>
(http://issues.apache.org/bugzilla/attachment.cgi?id=3D15355&act
> >> ion=3Dview)
> >> > [PATCH] Enhancements to FormulaRecord for reading=20
> >> calculated value of formula
> >> >
> >> > Prior to this Patch, FormulaRecord ignored non numeric values=20
> >> > (treated them as
> >> > UNKNOWN). This patch modifies FormulaRecord so that it
> correctly=20
> >> > identifies the
> >> > type of the value and stores it. Testcases have been=20
> >> included in patch.
> >> >
> >> > Results were verified by running BiffView (since=20
> >> FormulaRecord.toString() has
> >> > also been modified accordingly).
> >> >
> >> > --
> >> > Configure bugmail:=20
> > http://issues.apache.org/bugzilla/userprefs.cgi?tab=3Demail
> >> ------- You are receiving this mail because: -------
> >> You are the assignee for the bug, or are watching the assignee.
> >>
> >>
> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: [EMAIL PROTECTED]
> >> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> >> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> >>
> >>
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> >
> >
>
>
--
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
