Josh,
Well I think that I have gotten to the bottom of this problem. It got
to be a lot bigger of a problem when I discovered that Excel 2007 will
let you create names with worksheet scope and/or global scope. So you
are able to create different ranges with the same name as long they have
worksheet scope and these identical names are defined on different
worksheets. This was not possible in Excel 2003. This caused me some
confusion with what I was seeing in the EXTERNNAME record. For example
with a workbook that has the same name defined, one of global scope and
two of worksheet scope may be shown by the BiffViewer as:
Offset=0x00002EB3(11955) recno=285 sid=0x0023 size=0x001E(30)
org.apache.poi.hssf.record.ExternalNameRecord [EXTERNALNAME Markup_Cost
ix=1]
Offset=0x00002ED5(11989) recno=286 sid=0x0023 size=0x001E(30)
org.apache.poi.hssf.record.ExternalNameRecord [EXTERNALNAME Markup_Cost
ix=3]
Offset=0x00002EF7(12023) recno=287 sid=0x0023 size=0x001E(30)
org.apache.poi.hssf.record.ExternalNameRecord [EXTERNALNAME Markup_Cost
ix=0]
Which I found odd because the Excel97-2007BinaryFileFormat Spec says the
ix field is reserved and must be zero. It appears that the value ix
value is equal to the sheetTabIx property of the NameRecord in the
referenced workbook. At least that is the way that I coded it to behave.
It would be nice to have some definitive answer on this assumption. It
did work with a few cases that I created.
The guts of what was done was in the OperationEvaluationContext class.
Here is where I ended up; this method being called from
WorkbookEvaluator.
public ValueEval getNameXEval(NameXPtg nameXPtg) {
ExternalSheet externSheet =
_workbook.getExternalSheet(nameXPtg.getSheetRefIndex());
if(externSheet == null)
return new NameXEval(nameXPtg);
String workbookName = externSheet.getWorkbookName();
ExternalName externName =
_workbook.getExternalName(nameXPtg);
try{
WorkbookEvaluator refWorkbookEvaluator =
_bookEvaluator.getOtherWorkbookEvaluator(workbookName);
EvaluationName evaluationName =
refWorkbookEvaluator.getName(externName.getName(),externName.getIx()-1);
if(evaluationName.hasFormula()){
if
(evaluationName.getNameDefinition().length > 1) {
throw new
RuntimeException("Complex name formulas not supported yet");
}
Ptg ptg =
evaluationName.getNameDefinition()[0];
if(ptg instanceof Ref3DPtg){
Ref3DPtg ref3D = (Ref3DPtg)ptg;
int sheetIndex =
refWorkbookEvaluator.getSheetIndexByExternIndex(ref3D.getExternSheetInde
x());
String sheetName =
refWorkbookEvaluator.getSheetName(sheetIndex);
SheetRefEvaluator sre =
createExternSheetRefEvaluator(workbookName, sheetName);
return new
LazyRefEval(ref3D.getRow(), ref3D.getColumn(), sre);
}else if(ptg instanceof Area3DPtg){
Area3DPtg area3D =
(Area3DPtg)ptg;
int sheetIndex =
refWorkbookEvaluator.getSheetIndexByExternIndex(area3D.getExternSheetInd
ex());
String sheetName =
refWorkbookEvaluator.getSheetName(sheetIndex);
SheetRefEvaluator sre =
createExternSheetRefEvaluator(workbookName, sheetName);
return new
LazyAreaEval(area3D.getFirstRow(), area3D.getFirstColumn(),
area3D.getLastRow(), area3D.getLastColumn(), sre);
}
}
return ErrorEval.REF_INVALID;
}catch(WorkbookNotFoundException wnfe){
return ErrorEval.REF_INVALID;
}
}
I can pack up all my changes and test case and submit it to bugzilla as
a [PATCH] if you think this is the way to go. If not let me know how to
handle this.
Thanks,
Steve
> Hello Steve,
>
> From what I can tell you are on the right track. I guess you are
> using NameXPtg._nameNumber to locate the correct item from
> LinkTable.ExternalBookBlock._externalNameRecords. These records not
> only have the name of the defined-name name but also the the formula
> tokens, however I think you are more correct in resolving the formula
> via the other workbook (as the code above seems to suggest).
>
> I am intrigued by the -2 sheet index values. I wonder if '-2' is a
> special distinguished value besides '-1', or whether the negated value
> (+2) has some relevance.
>
> This code might need changing:
> refWorkbookEvaluator.evaluateNameFormula(ptgs,this);
> There might be a problem with the scope of the
> OperationEvaluationContext (second param, 'this'). It is supposed to
> know the location of the cell under evaluation. Cell evaluation is
> recursive and every time a new cell is visited, a new OEC is created.
> I'm still not sure whether a separate context should be required when
> evaluating defined names. At the moment, there doesn't seem to be an
> issue because POI only supports evaluation of names that directly
> translate into cell references. If POI were to support more complex
> name evaluation this might change. In any case, it seems to be odd to
> be passing an OEC from a calling workbook into the other workbook
> evaluator. Maybe this is a sign of redundancy in the design. Perhaps
> we could experiment a little with formulas involving defined names to
> get a better feel of where the evaluation context boundaries seem to
> be.
>
> hope this helps,
> Josh
>
> ---------------------------------------------------------------------
> 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]
