poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Avik Sengupta" <avik.sengu...@itellix.com>
Subject Re: Re: Re: retrieving saved String value of formula
Date Sun, 26 Oct 2003 17:57:36 GMT
Actually, if you are using EventModel, its easier.  There is a StringRecord right after the
FormulaRecord. Thats the one that contains the formula result for string formula. The StringRecord
has a getString method, that should give you what you want. So basically, in your code, you
will have to listen for a StringRecord just AFTER the FormulaRecord. 

If u were using usermodel, you would call getStringValue in HSSFCell to get the string.  That
will do the right thing for formulas. The usermodel functionality however was added after
2.0p3 was released, so u'll have to get CVS. The eventmodel functionality however can be used
in older version. 

HTH
-
Avik


   -------Original Message-------
   &gt; From: Will Glass-Husain &lt;wglass@forio.com&gt;
   &gt; Subject: Re: Re: retrieving saved String value of formula
   &gt; Sent: 26 Oct 2003 08:16:44
   &gt;
   &gt;  Thanks Avik and Michael.  Appreciate the info that this may have been
   &gt;  recently added.  However, can you be a little more direct?  Which method do
   &gt;  you call to get the String that is the result of a formula in an Excel
   &gt;  created and saved document.  (No macros have been used, just human entry).
   &gt;  
   &gt;  Specifically, I've a cell B91which has the formula &quot;=G56&quot;.
 The result is a
   &gt;  String.  My debugging shows that a FormulaRecord is returned for this cell
   &gt;  (I'm using the EventModel, as recommended in the docs).  In  FormulaRecord
   &gt;  there's a getValue() method that returns a double (in this example, it
   &gt;  returns NaN).  The javadocs read: &quot;get the calculated value of the formula&quot;.
   &gt;  No sign of a parallel method that returns a SST index or StringRecord, even
   &gt;  in CVS.
   &gt;  
   &gt;  What am I missing?  Appreciate a pointer.
   &gt;  
   &gt;  Thanks, WILL
   &gt;  
   &gt;  ----- Original Message -----
   &gt;  From: &quot;Michael Zalewski&quot;
   &gt;  To: &quot;POI Users List&quot; ; &quot;Avik Sengupta&quot;
   &gt;  
   &gt;  Sent: Saturday, October 25, 2003 5:36 PM
   &gt;  Subject: RE: Re: retrieving saved String value of formula
   &gt;  
   &gt;  
   &gt;  &gt; If you load run the macro with the formula in Excel, then, yes, you
will
   &gt;  be
   &gt;  &gt; able to retrieve the String result of the formula using HSSF.
   &gt;  &gt;
   &gt;  &gt; If you load or change the data with HSSF, then you will also be able
to
   &gt;  put
   &gt;  &gt; the formula into the cell. But you will not be able to retrieve the
result
   &gt;  &gt; of that formula.
   &gt;  &gt;
   &gt;  &gt; However, when your user opens the spreadsheet that you created with
HSSF,
   &gt;  he
   &gt;  &gt; or she will see the formula result, because HSSF makes Excel recalculate
   &gt;  all
   &gt;  &gt; formulas the next time the worksheet is opened.
   &gt;  &gt;
   &gt;  &gt;
   &gt;  &gt; -----Original Message-----
   &gt;  &gt; From: Avik Sengupta [mailto:avik.sengupta@itellix.com]
   &gt;  &gt; Sent: Saturday, October 25, 2003 2:38 PM
   &gt;  &gt; To: POI Users List
   &gt;  &gt; Subject: Re: Re: retrieving saved String value of formula
   &gt;  &gt;
   &gt;  &gt; I think we recently added in the ability to retrieve string forumula
   &gt;  values.
   &gt;  &gt; I dont remember if it was before or after 2.0pre3 .. which version are
you
   &gt;  &gt; using.
   &gt;  &gt;
   &gt;  &gt;
   &gt;  &gt;
   &gt;  &gt;    -------Original Message-------
   &gt;  &gt;    &gt; From: Will Glass-Husain
   &gt;  &gt;    &gt; Subject: Re: retrieving saved String value of formula
   &gt;  &gt;    &gt; Sent: 25 Oct 2003 22:42:46
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;  Thanks.  Actually it's the reverse I want.  I have an Excel
   &gt;  &gt; spreadsheet that
   &gt;  &gt;    &gt;  contains a formula returning a String.  I've a tool written
in
   &gt;  Java
   &gt;  &gt; that
   &gt;  &gt;    &gt;  imports the data into a database.  My question is simple:
how
   &gt;  hard
   &gt;  &gt; is it to
   &gt;  &gt;    &gt;  implement the retrieval of the calculated String in Java.
 Is the
   &gt;  &gt; calculated
   &gt;  &gt;    &gt;  String stored (like the calculated double) when the Excel
file is
   &gt;  &gt; saved?
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;  Thanks, WILL
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;  ----- Original Message -----
   &gt;  &gt;    &gt;  From: &quot;Michael Zalewski&quot;
   &gt;  &gt;    &gt;  To: &quot;POI Users List&quot;
   &gt;  &gt;    &gt;  Sent: Friday, October 24, 2003 9:53 PM
   &gt;  &gt;    &gt;  Subject: RE: retrieving saved String value of formula
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;  &gt; POI HSSF does not interpret your formula. There
is no
   &gt;  &gt; 'mini-interpreter'.
   &gt;  &gt;    &gt;  If
   &gt;  &gt;    &gt;  &gt; you see a value returned from .getValue(), it
is because
   &gt;  Excel
   &gt;  &gt; computed it
   &gt;  &gt;    &gt;  &gt; before the template was saved.
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; However, when you set a formula in a cell, your
client
   &gt;  should
   &gt;  &gt; recomputed
   &gt;  &gt;    &gt;  the
   &gt;  &gt;    &gt;  &gt; formula (after it receives the xls generated by
POI). This
   &gt;  is
   &gt;  &gt; because HSSF
   &gt;  &gt;    &gt;  &gt; sets an internal flag on each worksheet that tells
Excel to
   &gt;  &gt; recomputed all
   &gt;  &gt;    &gt;  &gt; formulas when the spreadsheet is opened.
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; So it shouldn't be hard to implement the feature
you need.
   &gt;  &gt; Just put the
   &gt;  &gt;    &gt;  &gt; formula in, and forget about the actual cell value.
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; Michael Zalewski
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; -----Original Message-----
   &gt;  &gt;    &gt;  &gt; From: Will Glass-Husain [mailto:wglass@forio.com]
   &gt;  &gt;    &gt;  &gt; Sent: Friday, October 24, 2003 8:52 PM
   &gt;  &gt;    &gt;  &gt; To: POI Users List
   &gt;  &gt;    &gt;  &gt; Subject: retrieving saved String value of formula
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; Hi,
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; I've been playing with the Formula support in
the latest
   &gt;  POI.
   &gt;  &gt; I can't
   &gt;  &gt;    &gt;  quite
   &gt;  &gt;    &gt;  &gt; tell from the &quot;Formula Support&quot;
docs.  When I
   &gt;  &gt; retrieve a value with
   &gt;  &gt;    &gt;  &gt; FormulaRecord.getValue(), is this calculated by
POI with a
   &gt;  &gt;    &gt;  mini-interpreter,
   &gt;  &gt;    &gt;  &gt; or was the saved value part of the XLS file.
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; Part of the reason that I ask is that my import
tool needs
   &gt;  to
   &gt;  &gt; import some
   &gt;  &gt;    &gt;  &gt; calculated cells that return Strings. (I'm already
   &gt;  &gt; successfully doing this
   &gt;  &gt;    &gt;  &gt; with calculated doubles).  I'm wondering if the
saved String
   &gt;  &gt; values are
   &gt;  &gt;    &gt;  &gt; actually in the XLS file and how hard it would
be to add
   &gt;  &gt; support for this.
   &gt;  &gt;    &gt;  &gt; Any pointers?
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; Thanks,
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; WILL
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt; _______________________________________
   &gt;  &gt;    &gt;  &gt; Forio Business Simulations
   &gt;  &gt;    &gt;  &gt; Will Glass-Husain
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;
   &gt;  &gt;
   &gt;  &gt; ---------------------------------------------------------------------
   &gt;  &gt;    &gt;  &gt; To unsubscribe, e-mail:
   &gt;  &gt; poi-user-unsubscribe@jakarta.apache.org
   &gt;  &gt;    &gt;  &gt; For additional commands, e-mail:
   &gt;  &gt; poi-user-help@jakarta.apache.org
   &gt;  &gt;    &gt;  &gt;
   &gt;  &gt;    &gt;
   &gt;  &gt;    &gt;
   &gt;  &gt;
   &gt;  &gt;
   &gt;  &gt;  ---------------------------------------------------------------------
   &gt;  &gt;    &gt;  To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
   &gt;  &gt;    &gt;  For additional commands, e-mail: poi-user-help@jakarta.apache.org
   &gt;  &gt;    -------Original Message-------
   &gt;  &gt;
   &gt;  &gt;
   &gt;  &gt;
   &gt;  &gt; ---------------------------------------------------------------------
   &gt;  &gt; To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
   &gt;  &gt; For additional commands, e-mail: poi-user-help@jakarta.apache.org
   &gt;  &gt;
   &gt;  
   &gt;  
   &gt;  ---------------------------------------------------------------------
   &gt;  To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
   &gt;  For additional commands, e-mail: poi-user-help@jakarta.apache.org
   -------Original Message-------


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message