poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Steve Wolke" <Swo...@roughbros.com>
Subject RE: External Data References
Date Thu, 18 Mar 2010 18:57:53 GMT
Josh,

Thanks for the direction.

I looked into the HSSFFormulaEvaluator.setupEnvironment(String[],> HSSFFormulaEvaluator[])
method and I could get it to work if I replaced the external referenced name with the external
cell area.  For example:

=VLOOKUP(PART,Costs.xls!COSTS,3,FALSE)*Quant

will give an error because the formula evaluator builds a NameXPtg object for the Costs.xls!COSTS
parameter.

But the formula below will work:

=VLOOKUP(PART,[Costs.xls]Sheet1!$A$2:$C$3,3,FALSE)*Quant

Off to find out more about the NameXPtg and CRNRecord relationships.

Thanks again,

Steve



> 
> Hello Steve,
> 
> Contributions are always welcome.  Check out this page for more
> details on how to submit patches:
> http://poi.apache.org/getinvolved/index.html
> 
> Don't forget unit tests for any functionality you are adding/changing.
> 
> Regarding cached values of external cell references, I believe they
> are stored in CRNRecords.  These records are managed in the LinkTable
> and POI currently only supports serialization/deserialization.
> Presumably there is a way to navigate from the NameXPtg to the
> corresponding CRNRecord but it wasn't immediately obvious to me.  I'm
> not sure if there is any good documentation on how this should work.
> You might have to experiment with BiffViewer on sample files to find
> out.
> 
> By the way, if all you are concerned about is evaluating
> multi-workbook formulas you can do it with the help of this method:
>     HSSFFormulaEvaluator.setupEnvironment(String[],
> HSSFFormulaEvaluator[])
> There is a test case which serves as an example:
>     TestWorkbookEvaluator.testEvaluateMultipleWorkbooks()
> Of course, multi-workbook evaluation requires all referenced workbooks
> to be present.  If not, you're stuck with digging the cached results
> out of the CRNRecords.
> 
> Hope this helps,
> Josh
> 
> ---------------------------------------------------------------------
> 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