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 Fri, 19 Mar 2010 21:15:47 GMT

Sorry,  accidentally sent previous message with hot keys.


> Josh,
> 
> Thanks again for the direction.  Today I looked into the direct
> evaluation using the path that you suggested.  First off I noticed the
> BiffViewer shows the ExternalSheetRecord to have a XTI record to the
> reference name from the formula: NameXPtg:[sheetRefIndex:1 ,
> nameNumber:1]
> 
> [EXTERNSHEET]
>    numOfRefs     = 2
> refrec         #0: extBook=0 firstSheet=0 lastSheet=0
> refrec         #1: extBook=1 firstSheet=-2 lastSheet=-2
> [/EXTERNSHEET]
> 
> This causes an exception with the call to get the workbook name:
> 
> 
>
_workbook.getExternalSheet(nameXPtg.getSheetRefIndex()).getWorkbookName
> (
> );
> 

PartII

The problem comes from the LinkTable class and the method public
String[] getExternalBookAndSheetName(int extRefIndex) not able to handle
the negative sheet index.

I changed LinkTable to return null if the sheetindex is negative and
then am trying to add a method to the OperationEvaluationContext class
like the one below.


        public ValueEval getNameXEval(NameXPtg nameXPtg) {
                String workbookName =
_workbook.getExternalSheet(nameXPtg.getSheetRefIndex()).getWorkbookName(
);
                String nameX = _workbook.resolveNameXText(nameXPtg);
                try{
                    WorkbookEvaluator refWorkbookEvaluator =
_bookEvaluator.getOtherWorkbookEvaluator(workbookName);
                    Ptg[] ptgs =
{refWorkbookEvaluator.getNamePtg(nameX,nameXPtg.getSheetRefIndex())};
                    return
refWorkbookEvaluator.evaluateNameFormula(ptgs,this);
                }catch(WorkbookNotFoundException wnfe){
                    return ErrorEval.REF_INVALID;
                }

	}

Still receiving an error but will get to it later.  Just wondering if I
am on the right track?  Any assistance is appreciated.

Thanks,

Steve

> 
> 
> 
> > -----Original Message-----
> > Hello Steve,
> >
> > Thanks for your investigation with multiple workbook evaluation.  I
> > didn't realise that your defined-name was in the *other* workbook.
> > POI currently supports evaluation cross-workbook references
involving
> > defined-names but only if they are defined in the workbook
containing
> > the cell under evaluation.  Your first example shows the unsupported
> > case.  This functionality needs to be fixed (it shouldn't make any
> > difference which book has the defined name).  The solution probably
> > involves the class WorkbookEvaluator, calling methods
> > getOtherWorkbookEvaluator(), and some variation of
> > evaluateNameFormula().
> >
> > Assuming that the other workbook is available, direct evaluation
> would
> > be preferred over reading cached values (from CRNRecords or
> wherever).
> >  This is because (for the most part) the POI evaluator supports
> > evaluating cells after input values have been modified. It would be
> > inconsistent to handle external names differently.
> >
> > If you want to pursue the CRNRecord route, we might be able to make
> an
> > API for accessing those values (similar to reading/setting cached
> cell
> > formula results).  The formula evaluator could optionally use the
> > cached values when the external workbook is unavailable.
> >
> > regards,
> > 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


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


Mime
View raw message