From 13628c0b8c4f6c360d2b2a492f307a45144b978a Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sat, 19 Jul 2014 20:30:31 +0000 Subject: [PATCH] Start to add XSSF specific handling for NameX (named ranges or functions from another file) #56737 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1611958 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/FormulaParser.java | 2 +- .../ss/formula/FormulaParsingWorkbook.java | 3 +- .../formula/OperationEvaluationContext.java | 88 ++++++++++------- .../poi/ss/formula/WorkbookEvaluator.java | 2 +- .../apache/poi/ss/formula/ptg/Area3DPxg.java | 99 +++++++++++++++++++ .../apache/poi/ss/formula/ptg/NameXPtg.java | 8 +- .../apache/poi/ss/formula/ptg/NameXPxg.java | 93 +++++++++++++++++ .../usermodel/XSSFEvaluationWorkbook.java | 19 ++-- 8 files changed, 263 insertions(+), 51 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java create mode 100644 src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java index 570dd5bcaa..51f1a50b1d 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParser.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java @@ -399,7 +399,7 @@ public final class FormulaParser { throw new FormulaParseException("Cell reference or Named Range " + "expected after sheet name at index " + _pointer + "."); } - NameXPtg nameXPtg = _book.getNameXPtg(name, sheetIden); + Ptg nameXPtg = _book.getNameXPtg(name, sheetIden); if (nameXPtg == null) { throw new FormulaParseException("Specified name '" + name + "' for sheet " + sheetIden.asFormulaString() + " not found"); diff --git a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java index ef2256025e..a2be6f8f2e 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java @@ -18,7 +18,6 @@ package org.apache.poi.ss.formula; import org.apache.poi.ss.SpreadsheetVersion; -import org.apache.poi.ss.formula.ptg.NameXPtg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellReference; @@ -36,7 +35,7 @@ public interface FormulaParsingWorkbook { */ EvaluationName getName(String name, int sheetIndex); - NameXPtg getNameXPtg(String name, SheetIdentifier sheet); + Ptg getNameXPtg(String name, SheetIdentifier sheet); /** * Produce the appropriate Ptg for a 3d cell reference diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index 3b25240006..4a7ac7a8ef 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -30,6 +30,7 @@ import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.ptg.Area3DPtg; import org.apache.poi.ss.formula.ptg.Area3DPxg; import org.apache.poi.ss.formula.ptg.NameXPtg; +import org.apache.poi.ss.formula.ptg.NameXPxg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.Ref3DPtg; import org.apache.poi.ss.formula.ptg.Ref3DPxg; @@ -297,41 +298,58 @@ public final class OperationEvaluationContext { aptg.getLastRow(), aptg.getLastColumn(), sre); } - public ValueEval getNameXEval(NameXPtg nameXPtg) { - // TODO Need HSSF and XSSF versions of these - ExternalSheet externSheet = _workbook.getExternalSheet(nameXPtg.getSheetRefIndex()); - if(externSheet == null) - return new NameXEval(nameXPtg); - String workbookName = externSheet.getWorkbookName(); - ExternalName externName = _workbook.getExternalName( - nameXPtg.getSheetRefIndex(), - nameXPtg.getNameIndex() - ); - try{ - WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); - EvaluationName evaluationName = refWorkbookEvaluator.getName(externName.getName(),externName.getIx()-1); - if(evaluationName != null && 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.getExternSheetIndex()); - 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.getExternSheetIndex()); - String sheetName = refWorkbookEvaluator.getSheetName(sheetIndex); - SheetRefEvaluator sre = createExternSheetRefEvaluator(workbookName, sheetName); - return new LazyAreaEval(area3D.getFirstRow(), area3D.getFirstColumn(), area3D.getLastRow(), area3D.getLastColumn(), sre); + public ValueEval getNameXEval(NameXPtg nameXPtg) { + ExternalSheet externSheet = _workbook.getExternalSheet(nameXPtg.getSheetRefIndex()); + if(externSheet == null || externSheet.getWorkbookName() == null) { + // External reference to our own workbook's name + return new NameXEval(nameXPtg); + } + + String workbookName = externSheet.getWorkbookName(); + ExternalName externName = _workbook.getExternalName( + nameXPtg.getSheetRefIndex(), + nameXPtg.getNameIndex() + ); + return getNameXEval(externName, workbookName); + } + public ValueEval getNameXEval(NameXPxg nameXPxg) { + ExternalSheet externSheet = _workbook.getExternalSheet(nameXPxg.getSheetName(), nameXPxg.getExternalWorkbookNumber()); + if(externSheet == null || externSheet.getWorkbookName() == null) { + // External reference to our own workbook's name + // TODO How to do this? + return new NameXEval(null); + } + + // TODO + return null; +// return getNameXEval(nameXPxg.getNameName(), externSheet.getWorkbookName()); + } + private ValueEval getNameXEval(ExternalName externName, String workbookName) { + try { + WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); + EvaluationName evaluationName = refWorkbookEvaluator.getName(externName.getName(),externName.getIx()-1); + if (evaluationName != null && 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.getExternSheetIndex()); + 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.getExternSheetIndex()); + 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; - } + return ErrorEval.REF_INVALID; + } catch(WorkbookNotFoundException wnfe){ + return ErrorEval.REF_INVALID; + } } } diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 41a11c95d8..ec3f984315 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -637,7 +637,7 @@ public final class WorkbookEvaluator { EvaluationName nameRecord = _workbook.getName(namePtg); return getEvalForNameRecord(nameRecord, ec); } - if (ptg instanceof NameXPtg) { + if (ptg instanceof NameXPtg) { // TODO Generalise for NameXPxg // Externally defined named ranges or macro functions NameXPtg nameXPtg = (NameXPtg)ptg; ValueEval eval = ec.getNameXEval(nameXPtg); diff --git a/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java b/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java new file mode 100644 index 0000000000..84872f57e8 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java @@ -0,0 +1,99 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.formula.ptg; + +import org.apache.poi.ss.util.AreaReference; +import org.apache.poi.util.LittleEndianOutput; + +/** + *

Title: XSSF Area 3D Reference (Sheet + Area)

+ *

Description: Defined an area in an external or different sheet.

+ *

REFERENCE:

+ * + *

This is XSSF only, as it stores the sheet / book references + * in String form. The HSSF equivalent using indexes is {@link Area3DPtg}

+ */ +public final class Area3DPxg extends AreaPtgBase { + private int externalWorkbookNumber = -1; + private String sheetName; + + public Area3DPxg(int externalWorkbookNumber, String sheetName, String arearef) { + this(externalWorkbookNumber, sheetName, new AreaReference(arearef)); + } + public Area3DPxg(int externalWorkbookNumber, String sheetName, AreaReference arearef) { + super(arearef); + this.externalWorkbookNumber = externalWorkbookNumber; + this.sheetName = sheetName; + } + + public Area3DPxg(String sheetName, String arearef) { + this(sheetName, new AreaReference(arearef)); + } + public Area3DPxg(String sheetName, AreaReference arearef) { + this(-1, sheetName, arearef); + } + + @Override + public String toString() { + StringBuffer sb = new StringBuffer(); + sb.append(getClass().getName()); + sb.append(" ["); + if (externalWorkbookNumber >= 0) { + sb.append(" ["); + sb.append("workbook=").append(getExternalWorkbookNumber()); + sb.append("] "); + } + sb.append("sheet=").append(getSheetName()); + sb.append(" ! "); + sb.append(formatReferenceAsString()); + sb.append("]"); + return sb.toString(); + } + + public int getExternalWorkbookNumber() { + return externalWorkbookNumber; + } + public String getSheetName() { + return sheetName; + } + + public String format2DRefAsString() { + return formatReferenceAsString(); + } + + public String toFormulaString() { + StringBuffer sb = new StringBuffer(); + if (externalWorkbookNumber >= 0) { + sb.append('['); + sb.append(externalWorkbookNumber); + sb.append(']'); + } + sb.append(sheetName); + sb.append('!'); + sb.append(formatReferenceAsString()); + return sb.toString(); + } + + public int getSize() { + return 1; + } + public void write(LittleEndianOutput out) { + throw new IllegalStateException("XSSF-only Ptg, should not be serialised"); + } + +} diff --git a/src/java/org/apache/poi/ss/formula/ptg/NameXPtg.java b/src/java/org/apache/poi/ss/formula/ptg/NameXPtg.java index e604fbe102..de88d1b8c2 100644 --- a/src/java/org/apache/poi/ss/formula/ptg/NameXPtg.java +++ b/src/java/org/apache/poi/ss/formula/ptg/NameXPtg.java @@ -23,8 +23,12 @@ import org.apache.poi.util.LittleEndianInput; import org.apache.poi.util.LittleEndianOutput; /** - * - * @author aviks + * A Name, be that a Named Range or a Function / User Defined + * Function, addressed in the HSSF External Sheet style. + * + *

This is HSSF only, as it matches the HSSF file format way of + * referring to the sheet by an extern index. The XSSF equivalent + * is {@link NameXPxg} */ public final class NameXPtg extends OperandPtg implements WorkbookDependentFormula { public final static short sid = 0x39; diff --git a/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java b/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java new file mode 100644 index 0000000000..62e56c28f7 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java @@ -0,0 +1,93 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.formula.ptg; + +import org.apache.poi.util.LittleEndianOutput; + +/** + * A Name, be that a Named Range or a Function / User Defined + * Function, addressed in the HSSF External Sheet style. + * + *

This is XSSF only, as it stores the sheet / book references + * in String form. The HSSF equivalent using indexes is {@link NameXPtg}

+ */ +public final class NameXPxg extends OperandPtg { + private int externalWorkbookNumber = -1; + private String sheetName; + private String nameName; + + public NameXPxg(int externalWorkbookNumber, String sheetName, String nameName) { + this.externalWorkbookNumber = externalWorkbookNumber; + this.sheetName = sheetName; + this.nameName = nameName; + } + public NameXPxg(String sheetName, String nameName) { + this(-1, sheetName, nameName); + } + + public String toString(){ + StringBuffer sb = new StringBuffer(); + sb.append(getClass().getName()); + sb.append(" ["); + if (externalWorkbookNumber >= 0) { + sb.append(" ["); + sb.append("workbook=").append(getExternalWorkbookNumber()); + sb.append("] "); + } + sb.append("sheet=").append(getSheetName()); + sb.append(" ! "); + sb.append("name="); + sb.append(nameName); + sb.append("]"); + return sb.toString(); + } + + public int getExternalWorkbookNumber() { + return externalWorkbookNumber; + } + public String getSheetName() { + return sheetName; + } + public String getNameName() { + return nameName; + } + + public String toFormulaString() { + StringBuffer sb = new StringBuffer(); + if (externalWorkbookNumber >= 0) { + sb.append('['); + sb.append(externalWorkbookNumber); + sb.append(']'); + } + sb.append(sheetName); + sb.append('!'); + sb.append(nameName); + return sb.toString(); + } + + public byte getDefaultOperandClass() { + return Ptg.CLASS_VALUE; + } + + public int getSize() { + return 1; + } + public void write(LittleEndianOutput out) { + throw new IllegalStateException("XSSF-only Ptg, should not be serialised"); + } +} diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java index 9dd475b047..0014693f27 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -31,6 +31,7 @@ import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.ptg.Area3DPxg; import org.apache.poi.ss.formula.ptg.NamePtg; import org.apache.poi.ss.formula.ptg.NameXPtg; +import org.apache.poi.ss.formula.ptg.NameXPxg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.Ref3DPxg; import org.apache.poi.ss.formula.udf.IndexedUDFFinder; @@ -42,11 +43,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; /** * Internal POI use only - * - * @author Josh Micich */ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, EvaluationWorkbook, FormulaParsingWorkbook { - private final XSSFWorkbook _uBook; public static XSSFEvaluationWorkbook create(XSSFWorkbook book) { @@ -122,21 +120,22 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E throw new RuntimeException("Not implemented yet"); } - public NameXPtg getNameXPtg(String name, SheetIdentifier sheet) { + public NameXPxg getNameXPtg(String name, SheetIdentifier sheet) { // First, try to find it as a User Defined Function IndexedUDFFinder udfFinder = (IndexedUDFFinder)getUDFFinder(); FreeRefFunction func = udfFinder.findFunction(name); if (func != null) { - return new NameXPtg(0, udfFinder.getFunctionIndex(name)); + return new NameXPxg(null, name); } // Otherwise, try it as a named range - XSSFName xname = _uBook.getName(name); - if (xname != null) { - int nameAt = _uBook.getNameIndex(name); - return new NameXPtg(xname.getSheetIndex(), nameAt); + String sheetName = sheet._sheetIdentifier.getName(); + + if (sheet._bookName != null) { + int bookIndex = resolveBookIndex(sheet._bookName); + return new NameXPxg(bookIndex, sheetName, name); } else { - return null; + return new NameXPxg(sheetName, name); } } public Ptg get3DReferencePtg(CellReference cell, SheetIdentifier sheet) { -- 2.39.5