From b5d5077681c3bb9ded6610945ddead20b99e6ade Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Fri, 4 Feb 2022 18:46:25 +0000 Subject: [PATCH] support basic R1C1 refs in INDIRECT function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897777 13f79535-47bb-0310-9956-ffa450edef68 --- .../formula/OperationEvaluationContext.java | 200 ++++++++++++------ .../poi/ss/formula/functions/Indirect.java | 10 +- .../ss/formula/functions/TestIndirect.java | 47 +++- 3 files changed, 178 insertions(+), 79 deletions(-) diff --git a/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java index 60fc0145e7..4050d7b11f 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java @@ -33,14 +33,20 @@ import org.apache.poi.ss.formula.eval.RefEval; import org.apache.poi.ss.formula.eval.StringEval; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.functions.FreeRefFunction; -import org.apache.poi.ss.formula.ptg.*; +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; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.CellReference.NameType; /** * Contains all the contextual information required to evaluate an operation * within a formula - * + *

* For POI internal use only */ public final class OperationEvaluationContext { @@ -55,12 +61,12 @@ public final class OperationEvaluationContext { private boolean _isInArrayContext; public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, - int srcColNum, EvaluationTracker tracker) { + int srcColNum, EvaluationTracker tracker) { this(bookEvaluator, workbook, sheetIndex, srcRowNum, srcColNum, tracker, true); } public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, - int srcColNum, EvaluationTracker tracker, boolean isSingleValue) { + int srcColNum, EvaluationTracker tracker, boolean isSingleValue) { _bookEvaluator = bookEvaluator; _workbook = workbook; _sheetIndex = sheetIndex; @@ -70,10 +76,11 @@ public final class OperationEvaluationContext { _isSingleValue = isSingleValue; } - public boolean isArraymode(){ + public boolean isArraymode() { return _isInArrayContext; } - public void setArrayMode(boolean value){ + + public void setArrayMode(boolean value) { _isInArrayContext = value; } @@ -92,14 +99,17 @@ public final class OperationEvaluationContext { SheetRangeEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) { return createExternSheetRefEvaluator(ptg.getExternSheetIndex()); } + SheetRangeEvaluator createExternSheetRefEvaluator(String firstSheetName, String lastSheetName, int externalWorkbookNumber) { ExternalSheet externalSheet = _workbook.getExternalSheet(firstSheetName, lastSheetName, externalWorkbookNumber); return createExternSheetRefEvaluator(externalSheet); } + SheetRangeEvaluator createExternSheetRefEvaluator(int externSheetIndex) { ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex); return createExternSheetRefEvaluator(externalSheet); } + SheetRangeEvaluator createExternSheetRefEvaluator(ExternalSheet externalSheet) { WorkbookEvaluator targetEvaluator; int otherFirstSheetIndex; @@ -107,14 +117,14 @@ public final class OperationEvaluationContext { if (externalSheet == null || externalSheet.getWorkbookName() == null) { // sheet is in same workbook targetEvaluator = _bookEvaluator; - if(externalSheet == null) { + if (externalSheet == null) { otherFirstSheetIndex = 0; } else { otherFirstSheetIndex = _workbook.getSheetIndex(externalSheet.getSheetName()); } if (externalSheet instanceof ExternalSheetRange) { - String lastSheetName = ((ExternalSheetRange)externalSheet).getLastSheetName(); + String lastSheetName = ((ExternalSheetRange) externalSheet).getLastSheetName(); otherLastSheetIndex = _workbook.getSheetIndex(lastSheetName); } } else { @@ -128,7 +138,7 @@ public final class OperationEvaluationContext { otherFirstSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName()); if (externalSheet instanceof ExternalSheetRange) { - String lastSheetName = ((ExternalSheetRange)externalSheet).getLastSheetName(); + String lastSheetName = ((ExternalSheetRange) externalSheet).getLastSheetName(); otherLastSheetIndex = targetEvaluator.getSheetIndex(lastSheetName); } @@ -143,9 +153,9 @@ public final class OperationEvaluationContext { otherLastSheetIndex = otherFirstSheetIndex; } - SheetRefEvaluator[] evals = new SheetRefEvaluator[otherLastSheetIndex-otherFirstSheetIndex+1]; - for (int i=0; i -1) { // Sheet based name String sheetName = name.substring(0, sheetNameAt); - String nameName = name.substring(sheetNameAt+1); + String nameName = name.substring(sheetNameAt + 1); evalName = _workbook.getName(nameName, _workbook.getSheetIndex(sheetName)); } else { // Workbook based name @@ -459,12 +481,14 @@ public final class OperationEvaluationContext { return new FunctionNameEval(name); } } + public int getSheetIndex() { return _sheetIndex; } /** * default true + * * @return flag indicating whether evaluation should "unwrap" the result to a single value based on the context row/column */ public boolean isSingleValue() { @@ -475,8 +499,8 @@ public final class OperationEvaluationContext { try { // Fetch the workbook this refers to, and the name as defined with that WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); - EvaluationName evaluationName = refWorkbookEvaluator.getName(externName.getName(),externName.getIx()-1); - if (evaluationName != null && evaluationName.hasFormula()){ + 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"); } @@ -486,23 +510,61 @@ public final class OperationEvaluationContext { refWorkbookEvaluator, refWorkbookEvaluator.getWorkbook(), -1, -1, -1, _tracker); Ptg ptg = evaluationName.getNameDefinition()[0]; - if (ptg instanceof Ref3DPtg){ - Ref3DPtg ref3D = (Ref3DPtg)ptg; + if (ptg instanceof Ref3DPtg) { + Ref3DPtg ref3D = (Ref3DPtg) ptg; return refWorkbookContext.getRef3DEval(ref3D); - } else if (ptg instanceof Ref3DPxg){ - Ref3DPxg ref3D = (Ref3DPxg)ptg; + } else if (ptg instanceof Ref3DPxg) { + Ref3DPxg ref3D = (Ref3DPxg) ptg; return refWorkbookContext.getRef3DEval(ref3D); - } else if(ptg instanceof Area3DPtg){ - Area3DPtg area3D = (Area3DPtg)ptg; + } else if (ptg instanceof Area3DPtg) { + Area3DPtg area3D = (Area3DPtg) ptg; return refWorkbookContext.getArea3DEval(area3D); - } else if(ptg instanceof Area3DPxg){ - Area3DPxg area3D = (Area3DPxg)ptg; + } else if (ptg instanceof Area3DPxg) { + Area3DPxg area3D = (Area3DPxg) ptg; return refWorkbookContext.getArea3DEval(area3D); } } return ErrorEval.REF_INVALID; - } catch(WorkbookNotFoundException wnfe){ + } catch (WorkbookNotFoundException wnfe) { return ErrorEval.REF_INVALID; } - } + } + + public static CellReference applyR1C1Reference(CellReference anchorReference, String relativeReference) { + int rpos = relativeReference.indexOf('R'); + int cpos = relativeReference.indexOf('C'); + if (rpos >= 0 && cpos > rpos) { + String rval = relativeReference.substring(rpos + 1, cpos).trim(); + String cval = relativeReference.substring(cpos + 1).trim(); + int absoluteR = -1; + int relativeR = 0; + if (rval.startsWith("[") && rval.endsWith("]")) { + relativeR = Integer.parseInt(rval.substring(1, rval.length() - 1).trim()); + } else if (!rval.isEmpty()) { + absoluteR = Integer.parseInt(rval); + } + int absoluteC = -1; + int relativeC = 0; + if (cval.startsWith("[") && cval.endsWith("]")) { + relativeC = Integer.parseInt(cval.substring(1, cval.length() - 1).trim()); + } else if (!cval.isEmpty()) { + absoluteC = Integer.parseInt(cval); + } + int newR; + if (absoluteR >= 0) { + newR = absoluteR - 1; + } else { + newR = anchorReference.getRow() + relativeR; + } + int newC; + if (absoluteC >= 0) { + newC = absoluteC - 1; + } else { + newC = anchorReference.getCol() + relativeC; + } + return new CellReference(newR, newC); + } else { + throw new IllegalArgumentException(relativeReference + " is not a valid R1C1 reference"); + } + } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java index 2728e0ea25..90b581d765 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java @@ -93,7 +93,7 @@ public final class Indirect implements FreeRefFunction { } private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text, - boolean isA1style) { + boolean isA1style) { // Search backwards for '!' because sheet names can contain '!' int plingPos = text.lastIndexOf('!'); @@ -115,7 +115,7 @@ public final class Indirect implements FreeRefFunction { refText = text.substring(plingPos + 1); } - if (Table.isStructuredReference.matcher(refText).matches()) { + if (isA1style && Table.isStructuredReference.matcher(refText).matches()) { // The argument is structured reference Area3DPxg areaPtg; try { @@ -130,8 +130,8 @@ public final class Indirect implements FreeRefFunction { String refStrPart2; int colonPos = refText.indexOf(':'); if (colonPos < 0) { - refStrPart1 = refText.trim(); - refStrPart2 = null; + refStrPart1 = refText.trim(); + refStrPart2 = null; } else { refStrPart1 = refText.substring(0, colonPos).trim(); refStrPart2 = refText.substring(colonPos + 1).trim(); @@ -187,7 +187,7 @@ public final class Indirect implements FreeRefFunction { // else - just sheet name String sheetName = unescapeString(text.subSequence(sheetStartPos, lastIx)); if (sheetName == null) { // note - when quoted, sheetName can - // start/end with whitespace + // start/end with whitespace return null; } return new String[] { wbName, sheetName, }; diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java index 45422e19e7..7a4ec7f270 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java @@ -25,12 +25,14 @@ import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.util.CellReference; import org.junit.jupiter.api.Test; /** @@ -154,6 +156,30 @@ final class TestIndirect { wbA.close(); } + @Test + void testBasicR1C1() throws Exception { + try (HSSFWorkbook wbA = createWBA()) { + HSSFCell c = wbA.getSheetAt(0).createRow(5).createCell(2); + HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA); + + // non-error cases + confirm(feA, c, "INDIRECT(\"R2C3\", FALSE)", 23); + confirm(feA, c, "INDIRECT(\"R[-4]C[0]\", FALSE)", 23); + confirm(feA, c, "INDIRECT(\"R[-4]C\", FALSE)", 23); + confirm(feA, c, "INDIRECT(\"R1C1:R1C7\", FALSE)", 13); // de-reference area ref (note formula is in C4) + confirm(feA, c, "SUM(INDIRECT(\"Sheet2!R1C2:R3C3\", FALSE))", 351); // area ref + confirm(feA, c, "SUM(INDIRECT(\"Sheet2! R1C2 : R3C3 \", FALSE))", 351); // spaces in area ref + + //scenarios yet to support + //R[-4] -- supports getting full row + //C[-4] -- supports getting full column + + // simple error propagation: + + confirm(feA, c, "INDIRECT(\"'Sheet1 '!R3C4\", FALSE)", ErrorEval.REF_INVALID); + } + } + @Test void testMultipleWorkbooks() throws Exception { HSSFWorkbook wbA = createWBA(); @@ -179,6 +205,22 @@ final class TestIndirect { wbA.close(); } + @Test + void testInvalidInput() { + assertEquals(ErrorEval.VALUE_INVALID, Indirect.instance.evaluate(new ValueEval[] {}, null)); + } + + @Test + void testRelativeR1C1() { + CellReference cr = new CellReference("C3"); + assertEquals(new CellReference("A3"), OperationEvaluationContext.applyR1C1Reference(cr, "RC[-2]")); + assertEquals(new CellReference("E3"), OperationEvaluationContext.applyR1C1Reference(cr, "RC[2]")); + assertEquals(new CellReference("C2"), OperationEvaluationContext.applyR1C1Reference(cr, "R[-1]C")); + assertEquals(new CellReference("C4"), OperationEvaluationContext.applyR1C1Reference(cr, "R[1]C")); + assertEquals(new CellReference("D4"), OperationEvaluationContext.applyR1C1Reference(cr, "R[1]C[1]")); + assertEquals(new CellReference("A1"), OperationEvaluationContext.applyR1C1Reference(cr, "R1C1")); + } + private static void confirm(FormulaEvaluator fe, Cell cell, String formula, double expectedResult) { fe.clearAllCachedResultValues(); cell.setCellFormula(formula); @@ -195,9 +237,4 @@ final class TestIndirect { int expCode = expectedResult.getErrorCode(); assertEquals(expCode, cv.getErrorValue(), "Expected error '" + ErrorEval.getText(expCode) + "' but got '" + cv.formatAsString() + "'."); } - - @Test - void testInvalidInput() { - assertEquals(ErrorEval.VALUE_INVALID, Indirect.instance.evaluate(new ValueEval[] {}, null)); - } } -- 2.39.5