From 42cd819573d152420058e009c2ff2c7f9ac2cfc9 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sun, 20 Jul 2014 17:51:51 +0000 Subject: [PATCH] Update the formula evaluator to support XSSF style external named ranges too git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1612133 13f79535-47bb-0310-9956-ffa450edef68 --- .../usermodel/HSSFEvaluationWorkbook.java | 4 + .../apache/poi/hssf/usermodel/HSSFSheet.java | 4 +- .../poi/ss/formula/EvaluationWorkbook.java | 8 ++ .../apache/poi/ss/formula/FormulaShifter.java | 60 +++++++++++-- .../formula/OperationEvaluationContext.java | 69 +++++++++++++-- .../poi/ss/formula/UserDefinedFunction.java | 6 +- .../poi/ss/formula/WorkbookEvaluator.java | 70 ++++----------- .../eval/forked/ForkedEvaluationWorkbook.java | 5 +- .../poi/ss/formula/ptg/Deleted3DPxg.java | 87 +++++++++++++++++++ .../usermodel/XSSFEvaluationWorkbook.java | 26 +++++- .../apache/poi/xssf/usermodel/XSSFSheet.java | 58 ++++++++++++- .../usermodel/helpers/XSSFRowShifter.java | 32 ++++--- .../org/apache/poi/hssf/model/TestSheet.java | 37 ++++++-- .../poi/ss/formula/TestFormulaShifter.java | 2 +- 14 files changed, 367 insertions(+), 101 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/ptg/Deleted3DPxg.java diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java index 199f7ca3c3..748b42a43d 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java @@ -146,6 +146,10 @@ public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E return _iBook.getExternalName(externSheetIndex, externNameIndex); } + public ExternalName getExternalName(String nameName, String sheetName, int externalWorkbookNumber) { + throw new IllegalStateException("XSSF-style external names are not supported for HSSF"); + } + public String resolveNameXText(NameXPtg n) { return _iBook.resolveNameXText(n.getSheetRefIndex(), n.getNameIndex()); } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 170c015ac5..ff33868c57 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -1504,8 +1504,10 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { // Update any formulas on this sheet that point to // rows which have been moved int sheetIndex = _workbook.getSheetIndex(this); + String sheetName = _workbook.getSheetName(sheetIndex); short externSheetIndex = _book.checkExternSheet(sheetIndex); - FormulaShifter shifter = FormulaShifter.createForRowShift(externSheetIndex, startRow, endRow, n); + FormulaShifter shifter = FormulaShifter.createForRowShift( + externSheetIndex, sheetName, startRow, endRow, n); _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex); int nSheets = _workbook.getNumberOfSheets(); diff --git a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java index f91dacf0de..855ae5929c 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java @@ -59,7 +59,15 @@ public interface EvaluationWorkbook { */ int convertFromExternSheetIndex(int externSheetIndex); + /** + * HSSF Only - fetch the external-style name details + */ ExternalName getExternalName(int externSheetIndex, int externNameIndex); + /** + * XSSF Only - fetch the external-style name details + */ + ExternalName getExternalName(String nameName, String sheetName, int externalWorkbookNumber); + EvaluationName getName(NamePtg namePtg); EvaluationName getName(String name, int sheetIndex); String resolveNameXText(NameXPtg ptg); diff --git a/src/java/org/apache/poi/ss/formula/FormulaShifter.java b/src/java/org/apache/poi/ss/formula/FormulaShifter.java index 74804a8845..9439000d23 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaShifter.java +++ b/src/java/org/apache/poi/ss/formula/FormulaShifter.java @@ -17,11 +17,25 @@ package org.apache.poi.ss.formula; -import org.apache.poi.ss.formula.ptg.*; +import org.apache.poi.ss.formula.ptg.Area2DPtgBase; +import org.apache.poi.ss.formula.ptg.Area3DPtg; +import org.apache.poi.ss.formula.ptg.Area3DPxg; +import org.apache.poi.ss.formula.ptg.AreaErrPtg; +import org.apache.poi.ss.formula.ptg.AreaPtg; +import org.apache.poi.ss.formula.ptg.AreaPtgBase; +import org.apache.poi.ss.formula.ptg.Deleted3DPxg; +import org.apache.poi.ss.formula.ptg.DeletedArea3DPtg; +import org.apache.poi.ss.formula.ptg.DeletedRef3DPtg; +import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.formula.ptg.Ref3DPtg; +import org.apache.poi.ss.formula.ptg.Ref3DPxg; +import org.apache.poi.ss.formula.ptg.RefErrorPtg; +import org.apache.poi.ss.formula.ptg.RefPtg; +import org.apache.poi.ss.formula.ptg.RefPtgBase; /** - * @author Josh Micich + * Updates Formulas as rows or sheets are shifted */ public final class FormulaShifter { @@ -31,9 +45,16 @@ public final class FormulaShifter { } /** - * Extern sheet index of sheet where moving is occurring + * Extern sheet index of sheet where moving is occurring, + * used for updating HSSF style 3D references */ private final int _externSheetIndex; + /** + * Sheet name of the sheet where moving is occurring, + * used for updating XSSF style 3D references on row shifts. + */ + private final String _sheetName; + private final int _firstMovedIndex; private final int _lastMovedIndex; private final int _amountToMove; @@ -48,7 +69,7 @@ public final class FormulaShifter { * * For example, this will be called on {@link org.apache.poi.hssf.usermodel.HSSFSheet#shiftRows(int, int, int)} } */ - private FormulaShifter(int externSheetIndex, int firstMovedIndex, int lastMovedIndex, int amountToMove) { + private FormulaShifter(int externSheetIndex, String sheetName, int firstMovedIndex, int lastMovedIndex, int amountToMove) { if (amountToMove == 0) { throw new IllegalArgumentException("amountToMove must not be zero"); } @@ -56,6 +77,7 @@ public final class FormulaShifter { throw new IllegalArgumentException("firstMovedIndex, lastMovedIndex out of order"); } _externSheetIndex = externSheetIndex; + _sheetName = sheetName; _firstMovedIndex = firstMovedIndex; _lastMovedIndex = lastMovedIndex; _amountToMove = amountToMove; @@ -71,14 +93,15 @@ public final class FormulaShifter { */ private FormulaShifter(int srcSheetIndex, int dstSheetIndex) { _externSheetIndex = _firstMovedIndex = _lastMovedIndex = _amountToMove = -1; + _sheetName = null; _srcSheetIndex = srcSheetIndex; _dstSheetIndex = dstSheetIndex; _mode = ShiftMode.Sheet; } - public static FormulaShifter createForRowShift(int externSheetIndex, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) { - return new FormulaShifter(externSheetIndex, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove); + public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove) { + return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove); } public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) { @@ -145,6 +168,14 @@ public final class FormulaShifter { } return rowMoveRefPtg(rptg); } + if(ptg instanceof Ref3DPxg) { + Ref3DPxg rpxg = (Ref3DPxg)ptg; + if (rpxg.getExternalWorkbookNumber() > 0 || + ! _sheetName.equals(rpxg.getSheetName())) { + // only move 3D refs that refer to the sheet with cells being moved + } + return rowMoveRefPtg(rpxg); + } if(ptg instanceof Area2DPtgBase) { if (currentExternSheetIx != _externSheetIndex) { // local refs on other sheets are unaffected @@ -161,6 +192,15 @@ public final class FormulaShifter { } return rowMoveAreaPtg(aptg); } + if(ptg instanceof Area3DPxg) { + Area3DPxg apxg = (Area3DPxg)ptg; + if (apxg.getExternalWorkbookNumber() > 0 || + ! _sheetName.equals(apxg.getSheetName())) { + // only move 3D refs that refer to the sheet with cells being moved + return null; + } + return rowMoveAreaPtg(apxg); + } return null; } @@ -348,6 +388,14 @@ public final class FormulaShifter { Area3DPtg area3DPtg = (Area3DPtg) ptg; return new DeletedArea3DPtg(area3DPtg.getExternSheetIndex()); } + if (ptg instanceof Ref3DPxg) { + Ref3DPxg pxg = (Ref3DPxg)ptg; + return new Deleted3DPxg(pxg.getExternalWorkbookNumber(), pxg.getSheetName()); + } + if (ptg instanceof Area3DPxg) { + Area3DPxg pxg = (Area3DPxg)ptg; + return new Deleted3DPxg(pxg.getExternalWorkbookNumber(), pxg.getSheetName()); + } throw new IllegalArgumentException("Unexpected ref ptg class (" + ptg.getClass().getName() + ")"); } diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index 4a7ac7a8ef..ab87208227 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -23,7 +23,8 @@ import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalName; import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet; import org.apache.poi.ss.formula.eval.AreaEval; import org.apache.poi.ss.formula.eval.ErrorEval; -import org.apache.poi.ss.formula.eval.NameXEval; +import org.apache.poi.ss.formula.eval.ExternalNameEval; +import org.apache.poi.ss.formula.eval.FunctionNameEval; import org.apache.poi.ss.formula.eval.RefEval; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.functions.FreeRefFunction; @@ -299,32 +300,82 @@ public final class OperationEvaluationContext { } public ValueEval getNameXEval(NameXPtg nameXPtg) { + // Is the name actually on our workbook? ExternalSheet externSheet = _workbook.getExternalSheet(nameXPtg.getSheetRefIndex()); if(externSheet == null || externSheet.getWorkbookName() == null) { // External reference to our own workbook's name - return new NameXEval(nameXPtg); + return getLocalNameXEval(nameXPtg); } + // Look it up for the external workbook String workbookName = externSheet.getWorkbookName(); ExternalName externName = _workbook.getExternalName( nameXPtg.getSheetRefIndex(), nameXPtg.getNameIndex() ); - return getNameXEval(externName, workbookName); + return getExternalNameXEval(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); + return getLocalNameXEval(nameXPxg); } - // TODO - return null; -// return getNameXEval(nameXPxg.getNameName(), externSheet.getWorkbookName()); + // Look it up for the external workbook + String workbookName = externSheet.getWorkbookName(); + ExternalName externName = _workbook.getExternalName( + nameXPxg.getNameName(), + nameXPxg.getSheetName(), + nameXPxg.getExternalWorkbookNumber() + ); + return getExternalNameXEval(externName, workbookName); + } + + private ValueEval getLocalNameXEval(NameXPxg nameXPxg) { + // Look up the sheet, if present + int sIdx = -1; + if (nameXPxg.getSheetName() != null) { + sIdx = _workbook.getSheetIndex(nameXPxg.getSheetName()); + } + + // Is it a name or a function? + String name = nameXPxg.getNameName(); + EvaluationName evalName = _workbook.getName(name, sIdx); + if (evalName != null) { + // Process it as a name + return new ExternalNameEval(evalName); + } else { + // Must be an external function + return new FunctionNameEval(name); + } + } + private ValueEval getLocalNameXEval(NameXPtg nameXPtg) { + String name = _workbook.resolveNameXText(nameXPtg); + + // Try to parse it as a name + int sheetNameAt = name.indexOf('!'); + EvaluationName evalName = null; + if (sheetNameAt > -1) { + // Sheet based name + String sheetName = name.substring(0, sheetNameAt); + String nameName = name.substring(sheetNameAt+1); + evalName = _workbook.getName(nameName, _workbook.getSheetIndex(sheetName)); + } else { + // Workbook based name + evalName = _workbook.getName(name, -1); + } + + if (evalName != null) { + // Process it as a name + return new ExternalNameEval(evalName); + } else { + // Must be an external function + return new FunctionNameEval(name); + } } - private ValueEval getNameXEval(ExternalName externName, String workbookName) { + + private ValueEval getExternalNameXEval(ExternalName externName, String workbookName) { try { WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); EvaluationName evaluationName = refWorkbookEvaluator.getName(externName.getName(),externName.getIx()-1); diff --git a/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java b/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java index 47df90a00d..c526137554 100644 --- a/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java +++ b/src/java/org/apache/poi/ss/formula/UserDefinedFunction.java @@ -17,7 +17,7 @@ package org.apache.poi.ss.formula; -import org.apache.poi.ss.formula.eval.NameEval; +import org.apache.poi.ss.formula.eval.FunctionNameEval; import org.apache.poi.ss.formula.eval.NotImplementedFunctionException; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.functions.FreeRefFunction; @@ -45,8 +45,8 @@ final class UserDefinedFunction implements FreeRefFunction { ValueEval nameArg = args[0]; String functionName; - if (nameArg instanceof NameEval) { - functionName = ((NameEval) nameArg).getFunctionName(); + if (nameArg instanceof FunctionNameEval) { + functionName = ((FunctionNameEval) nameArg).getFunctionName(); } else { throw new RuntimeException("First argument should be a NameEval, but got (" + nameArg.getClass().getName() + ")"); diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 6dd5dd2068..4cbcee40cf 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -30,10 +30,10 @@ import org.apache.poi.ss.formula.eval.BlankEval; import org.apache.poi.ss.formula.eval.BoolEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.ExternalNameEval; import org.apache.poi.ss.formula.eval.FunctionEval; +import org.apache.poi.ss.formula.eval.FunctionNameEval; import org.apache.poi.ss.formula.eval.MissingArgEval; -import org.apache.poi.ss.formula.eval.NameEval; -import org.apache.poi.ss.formula.eval.NameXEval; import org.apache.poi.ss.formula.eval.NotImplementedException; import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.OperandResolver; @@ -638,37 +638,13 @@ public final class WorkbookEvaluator { EvaluationName nameRecord = _workbook.getName(namePtg); return getEvalForNameRecord(nameRecord, ec); } - if (ptg instanceof NameXPtg) { // TODO Generalise for NameXPxg + if (ptg instanceof NameXPtg) { // Externally defined named ranges or macro functions - NameXPtg nameXPtg = (NameXPtg)ptg; - ValueEval eval = ec.getNameXEval(nameXPtg); - - if (eval instanceof NameXEval) { - // Could not be directly evaluated, so process as a name - return getEvalForNameX(nameXPtg, ec); - } else { - // Use the evaluated version - return eval; - } + return processNameEval(ec.getNameXEval((NameXPtg)ptg), ec); } if (ptg instanceof NameXPxg) { - // TODO This is a temporary hack.... - NameXPxg pxg = (NameXPxg)ptg; - int sIdx = -1; - if (pxg.getSheetName() != null) { - sIdx = _workbook.getSheetIndex(pxg.getSheetName()); - } - EvaluationName evalName = _workbook.getName(pxg.getNameName(), sIdx); - if (evalName == null) { - // We don't know about that name, sorry - // TODO What about UDFs? - logInfo("Unknown Name referenced: " + pxg.getNameName()); - return ErrorEval.NAME_INVALID; - } - - int nIdx = evalName.createPtg().getIndex(); - NameXPtg nptg = new NameXPtg(sIdx, nIdx); - return getEvalForPtg(nptg, ec); + // Externally defined named ranges or macro functions + return processNameEval(ec.getNameXEval((NameXPxg)ptg), ec); } if (ptg instanceof IntPtg) { @@ -728,10 +704,18 @@ public final class WorkbookEvaluator { throw new RuntimeException("Unexpected ptg class (" + ptg.getClass().getName() + ")"); } + + private ValueEval processNameEval(ValueEval eval, OperationEvaluationContext ec) { + if (eval instanceof ExternalNameEval) { + EvaluationName name = ((ExternalNameEval)eval).getName(); + return getEvalForNameRecord(name, ec); + } + return eval; + } private ValueEval getEvalForNameRecord(EvaluationName nameRecord, OperationEvaluationContext ec) { if (nameRecord.isFunctionName()) { - return new NameEval(nameRecord.getNameText()); + return new FunctionNameEval(nameRecord.getNameText()); } if (nameRecord.hasFormula()) { return evaluateNameFormula(nameRecord.getNameDefinition(), ec); @@ -739,30 +723,6 @@ public final class WorkbookEvaluator { throw new RuntimeException("Don't now how to evalate name '" + nameRecord.getNameText() + "'"); } - private ValueEval getEvalForNameX(NameXPtg nameXPtg, OperationEvaluationContext ec) { - String name = _workbook.resolveNameXText(nameXPtg); - - // Try to parse it as a name - int sheetNameAt = name.indexOf('!'); - EvaluationName nameRecord = null; - if (sheetNameAt > -1) { - // Sheet based name - String sheetName = name.substring(0, sheetNameAt); - String nameName = name.substring(sheetNameAt+1); - nameRecord = _workbook.getName(nameName, _workbook.getSheetIndex(sheetName)); - } else { - // Workbook based name - nameRecord = _workbook.getName(name, -1); - } - - if (nameRecord != null) { - // Process it as a name - return getEvalForNameRecord(nameRecord, ec); - } else { - // Must be an external function - return new NameEval(name); - } - } /** * YK: Used by OperationEvaluationContext to resolve indirect names. diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java index 442b748593..93d3ec5565 100644 --- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java @@ -117,8 +117,11 @@ final class ForkedEvaluationWorkbook implements EvaluationWorkbook { public ExternalName getExternalName(int externSheetIndex, int externNameIndex) { return _masterBook.getExternalName(externSheetIndex, externNameIndex); } + public ExternalName getExternalName(String nameName, String sheetName, int externalWorkbookNumber) { + return _masterBook.getExternalName(nameName, sheetName, externalWorkbookNumber); + } - public int getSheetIndex(EvaluationSheet sheet) { + public int getSheetIndex(EvaluationSheet sheet) { if (sheet instanceof ForkedEvaluationSheet) { ForkedEvaluationSheet mes = (ForkedEvaluationSheet) sheet; return mes.getSheetIndex(_masterBook); diff --git a/src/java/org/apache/poi/ss/formula/ptg/Deleted3DPxg.java b/src/java/org/apache/poi/ss/formula/ptg/Deleted3DPxg.java new file mode 100644 index 0000000000..772e10b2f1 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/ptg/Deleted3DPxg.java @@ -0,0 +1,87 @@ +/* ==================================================================== + 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.usermodel.ErrorConstants; +import org.apache.poi.util.LittleEndianOutput; + + +/** + * An XSSF only representation of a reference to a deleted area + */ +public final class Deleted3DPxg extends OperandPtg { + private int externalWorkbookNumber = -1; + private String sheetName; + + public Deleted3DPxg(int externalWorkbookNumber, String sheetName) { + this.externalWorkbookNumber = externalWorkbookNumber; + this.sheetName = sheetName; + } + public Deleted3DPxg(String sheetName) { + this(-1, sheetName); + } + + 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(ErrorConstants.getText(ErrorConstants.ERROR_REF)); + sb.append("]"); + return sb.toString(); + } + + public int getExternalWorkbookNumber() { + return externalWorkbookNumber; + } + public String getSheetName() { + return sheetName; + } + + public String toFormulaString() { + StringBuffer sb = new StringBuffer(); + if (externalWorkbookNumber >= 0) { + sb.append('['); + sb.append(externalWorkbookNumber); + sb.append(']'); + } + if (sheetName != null) { + sb.append(sheetName); + } + sb.append('!'); + sb.append(ErrorConstants.getText(ErrorConstants.ERROR_REF)); + 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 17d92f902c..9728e73ab4 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -119,10 +119,24 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E } public ExternalName getExternalName(int externSheetIndex, int externNameIndex) { - throw new RuntimeException("Not implemented yet"); + throw new IllegalStateException("HSSF-style external references are not supported for XSSF"); } - public NameXPxg getNameXPtg(String name, SheetIdentifier sheet) { + public ExternalName getExternalName(String nameName, String sheetName, int externalWorkbookNumber) { + if (externalWorkbookNumber > 0) { + // External reference - reference is 1 based, link table is 0 based + int linkNumber = externalWorkbookNumber - 1; + ExternalLinksTable linkTable = _uBook.getExternalLinksTable().get(linkNumber); + return new ExternalName(nameName, -1, -1); // TODO Finish this + } else { + // Internal reference + int nameIdx = _uBook.getNameIndex(nameName); + return new ExternalName(nameName, nameIdx, -1); // TODO Is this right? + } + + } + + 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); @@ -131,6 +145,12 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E } // Otherwise, try it as a named range + if (sheet == null) { + if (_uBook.getNameIndex(name) > -1) { + return new NameXPxg(null, name); + } + return null; + } if (sheet._sheetIdentifier == null) { // Workbook + Named Range only int bookIndex = resolveBookIndex(sheet._bookName); @@ -229,7 +249,7 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.create(_uBook); return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); } - + public UDFFinder getUDFFinder(){ return _uBook.getUDFFinder(); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index a7d4b5dded..b6b9ee6f2f 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -42,7 +42,16 @@ import org.apache.poi.openxml4j.opc.PackageRelationshipCollection; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.formula.SheetNameFormatter; -import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellRange; +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.DataValidation; +import org.apache.poi.ss.usermodel.DataValidationHelper; +import org.apache.poi.ss.usermodel.Footer; +import org.apache.poi.ss.usermodel.Header; +import org.apache.poi.ss.usermodel.IndexedColors; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.ss.util.CellReference; @@ -58,7 +67,48 @@ import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlOptions; import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBreak; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidations; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDrawing; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHeaderFooter; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHyperlink; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLegacyDrawing; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOutlinePr; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageBreak; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageMargins; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetUpPr; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPane; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPrintOptions; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSelection; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetCalcPr; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetPr; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTablePart; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableParts; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCalcMode; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPaneState; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STUnsignedShortHex; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument; /** * High level representation of a SpreadsheetML worksheet. @@ -2498,7 +2548,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { XSSFRowShifter rowShifter = new XSSFRowShifter(this); int sheetIndex = getWorkbook().getSheetIndex(this); - FormulaShifter shifter = FormulaShifter.createForRowShift(sheetIndex, startRow, endRow, n); + String sheetName = getWorkbook().getSheetName(sheetIndex); + FormulaShifter shifter = FormulaShifter.createForRowShift( + sheetIndex, sheetName, startRow, endRow, n); rowShifter.updateNamedRanges(shifter); rowShifter.updateFormulas(shifter); diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java index 245d7c1c19..da65972edb 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java @@ -17,21 +17,30 @@ package org.apache.poi.xssf.usermodel.helpers; -import org.apache.poi.xssf.usermodel.*; -import org.apache.poi.ss.util.CellRangeAddress; +import java.util.ArrayList; +import java.util.List; + import org.apache.poi.ss.formula.FormulaParser; -import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.FormulaRenderer; -import org.apache.poi.ss.usermodel.Row; -import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.formula.FormulaShifter; -import org.apache.poi.ss.formula.ptg.Ptg; -import org.apache.poi.ss.formula.ptg.AreaPtg; +import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.ptg.AreaErrPtg; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; - -import java.util.List; -import java.util.ArrayList; +import org.apache.poi.ss.formula.ptg.AreaPtg; +import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.xssf.usermodel.XSSFCell; +import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook; +import org.apache.poi.xssf.usermodel.XSSFName; +import org.apache.poi.xssf.usermodel.XSSFRow; +import org.apache.poi.xssf.usermodel.XSSFSheet; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; /** * @author Yegor Kozlov @@ -115,7 +124,6 @@ public final class XSSFRowShifter { String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); name.setRefersToFormula(shiftedFmla); } - } } diff --git a/src/testcases/org/apache/poi/hssf/model/TestSheet.java b/src/testcases/org/apache/poi/hssf/model/TestSheet.java index 57ccf1f6ba..176bb04464 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestSheet.java +++ b/src/testcases/org/apache/poi/hssf/model/TestSheet.java @@ -17,11 +17,39 @@ package org.apache.poi.hssf.model; +import java.io.IOException; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + import junit.framework.AssertionFailedError; import junit.framework.TestCase; + import org.apache.poi.ddf.EscherDggRecord; import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.hssf.record.*; +import org.apache.poi.hssf.record.BOFRecord; +import org.apache.poi.hssf.record.BlankRecord; +import org.apache.poi.hssf.record.CellValueRecordInterface; +import org.apache.poi.hssf.record.ColumnInfoRecord; +import org.apache.poi.hssf.record.DimensionsRecord; +import org.apache.poi.hssf.record.DrawingRecord; +import org.apache.poi.hssf.record.EOFRecord; +import org.apache.poi.hssf.record.EscherAggregate; +import org.apache.poi.hssf.record.FormulaRecord; +import org.apache.poi.hssf.record.GutsRecord; +import org.apache.poi.hssf.record.IndexRecord; +import org.apache.poi.hssf.record.MergeCellsRecord; +import org.apache.poi.hssf.record.MulBlankRecord; +import org.apache.poi.hssf.record.NoteRecord; +import org.apache.poi.hssf.record.NumberRecord; +import org.apache.poi.hssf.record.ObjRecord; +import org.apache.poi.hssf.record.Record; +import org.apache.poi.hssf.record.RecordBase; +import org.apache.poi.hssf.record.RowRecord; +import org.apache.poi.hssf.record.StringRecord; +import org.apache.poi.hssf.record.TextObjectRecord; +import org.apache.poi.hssf.record.UncalcedRecord; +import org.apache.poi.hssf.record.WindowTwoRecord; import org.apache.poi.hssf.record.aggregates.ConditionalFormattingTable; import org.apache.poi.hssf.record.aggregates.PageSettingsBlock; import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor; @@ -35,11 +63,6 @@ import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.HexRead; -import java.io.IOException; -import java.util.ArrayList; -import java.util.Arrays; -import java.util.List; - /** * Unit test for the {@link InternalSheet} class. * @@ -632,7 +655,7 @@ public final class TestSheet extends TestCase { List sheetRecs = sheet.getRecords(); assertEquals(23, sheetRecs.size()); - FormulaShifter shifter = FormulaShifter.createForRowShift(0, 0, 0, 1); + FormulaShifter shifter = FormulaShifter.createForRowShift(0, "", 0, 0, 1); sheet.updateFormulasAfterCellShift(shifter, 0); if (sheetRecs.size() == 24 && sheetRecs.get(22) instanceof ConditionalFormattingTable) { throw new AssertionFailedError("Identified bug 46547a"); diff --git a/src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java b/src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java index e3e955850a..d1848a1009 100644 --- a/src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java +++ b/src/testcases/org/apache/poi/ss/formula/TestFormulaShifter.java @@ -97,7 +97,7 @@ public final class TestFormulaShifter extends TestCase { int firstRowMoved, int lastRowMoved, int numberRowsMoved, int expectedAreaFirstRow, int expectedAreaLastRow) { - FormulaShifter fs = FormulaShifter.createForRowShift(0, firstRowMoved, lastRowMoved, numberRowsMoved); + FormulaShifter fs = FormulaShifter.createForRowShift(0, "", firstRowMoved, lastRowMoved, numberRowsMoved); boolean expectedChanged = aptg.getFirstRow() != expectedAreaFirstRow || aptg.getLastRow() != expectedAreaLastRow; AreaPtg copyPtg = (AreaPtg) aptg.copy(); // clone so we can re-use aptg in calling method -- 2.39.5