Author: fanningpj
Date: Fri Feb 4 18:46:25 2022
New Revision: 1897777
URL: http://svn.apache.org/viewvc?rev=1897777&view=rev
Log:
support basic R1C1 refs in INDIRECT function
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java?rev=1897777&r1=1897776&r2=1897777&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
Fri Feb 4 18:46:25 2022
@@ -33,14 +33,20 @@ import org.apache.poi.ss.formula.eval.Re
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
- *
+ * <p>
* For POI internal use only
*/
public final class OperationEvaluationContext {
@@ -55,12 +61,12 @@ public final class OperationEvaluationCo
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 OperationEvaluationCo
_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 OperationEvaluationCo
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 OperationEvaluationCo
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 OperationEvaluationCo
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 OperationEvaluationCo
otherLastSheetIndex = otherFirstSheetIndex;
}
- SheetRefEvaluator[] evals = new
SheetRefEvaluator[otherLastSheetIndex-otherFirstSheetIndex+1];
- for (int i=0; i<evals.length; i++) {
- int otherSheetIndex = i+otherFirstSheetIndex;
+ SheetRefEvaluator[] evals = new SheetRefEvaluator[otherLastSheetIndex
- otherFirstSheetIndex + 1];
+ for (int i = 0; i < evals.length; i++) {
+ int otherSheetIndex = i + otherFirstSheetIndex;
evals[i] = new SheetRefEvaluator(targetEvaluator, _tracker,
otherSheetIndex);
}
return new SheetRangeEvaluator(otherFirstSheetIndex,
otherLastSheetIndex, evals);
@@ -181,29 +191,25 @@ public final class OperationEvaluationCo
}
-
/**
* Resolves a cell or area reference dynamically.
+ *
* @param workbookName the name of the workbook containing the reference.
If {@code null}
- * the current workbook is assumed. Note - to evaluate formulas which use
multiple workbooks,
- * a {@link CollaboratingWorkbooksEnvironment} must be set up.
- * @param sheetName the name of the sheet containing the reference. May
be {@code null}
- * (when {@code workbookName} is also null) in which case the current
workbook and sheet is
- * assumed.
- * @param refStrPart1 the single cell reference or first part of the area
reference. Must not
- * be {@code null}.
- * @param refStrPart2 the second part of the area reference. For single
cell references this
- * parameter must be {@code null}
- * @param isA1Style specifies the format for {@code refStrPart1} and
{@code refStrPart2}.
- * Pass {@code true} for 'A1' style and {@code false} for 'R1C1' style.
- * TODO - currently POI only supports 'A1' reference style
+ * the current workbook is assumed. Note - to
evaluate formulas which use multiple workbooks,
+ * a {@link CollaboratingWorkbooksEnvironment} must be
set up.
+ * @param sheetName the name of the sheet containing the reference.
May be {@code null}
+ * (when {@code workbookName} is also null) in which
case the current workbook and sheet is
+ * assumed.
+ * @param refStrPart1 the single cell reference or first part of the area
reference. Must not
+ * be {@code null}.
+ * @param refStrPart2 the second part of the area reference. For single
cell references this
+ * parameter must be {@code null}
+ * @param isA1Style specifies the format for {@code refStrPart1} and
{@code refStrPart2}.
+ * Pass {@code true} for 'A1' style and {@code false}
for 'R1C1' style.
* @return a {@link RefEval} or {@link AreaEval}
*/
public ValueEval getDynamicReference(String workbookName, String
sheetName, String refStrPart1,
- String refStrPart2, boolean isA1Style) {
- if (!isA1Style) {
- throw new RuntimeException("R1C1 style not supported yet");
- }
+ String refStrPart2, boolean
isA1Style) {
SheetRefEvaluator se = createExternSheetRefEvaluator(workbookName,
sheetName);
if (se == null) {
return ErrorEval.REF_INVALID;
@@ -212,17 +218,17 @@ public final class OperationEvaluationCo
SpreadsheetVersion ssVersion = _workbook.getSpreadsheetVersion();
- NameType part1refType = classifyCellReference(refStrPart1, ssVersion);
+ NameType part1refType = isA1Style ? classifyCellReference(refStrPart1,
ssVersion) : NameType.CELL;
switch (part1refType) {
case BAD_CELL_OR_NAMED_RANGE:
return ErrorEval.REF_INVALID;
case NAMED_RANGE:
EvaluationName nm = _workbook.getName(refStrPart1,
_sheetIndex);
- if(nm == null) {
+ if (nm == null) {
throw new RuntimeException("Specified name '" +
refStrPart1 +
- "' is not found in the workbook (sheetIndex=" +
_sheetIndex +").");
+ "' is not found in the workbook (sheetIndex=" +
_sheetIndex + ").");
}
- if(!nm.isRange()) {
+ if (!nm.isRange()) {
throw new RuntimeException("Specified name '" +
refStrPart1 + "' is not a range as expected.");
}
return
_bookEvaluator.evaluateNameFormula(nm.getNameDefinition(), this);
@@ -234,12 +240,17 @@ public final class OperationEvaluationCo
case ROW:
return ErrorEval.REF_INVALID;
case CELL:
- CellReference cr = new CellReference(refStrPart1);
+ CellReference cr;
+ if (isA1Style) {
+ cr = new CellReference(refStrPart1);
+ } else {
+ cr = applyR1C1Reference(new
CellReference(getRowIndex(), getColumnIndex()), refStrPart1);
+ }
return new LazyRefEval(cr.getRow(), cr.getCol(), sre);
}
throw new IllegalStateException("Unexpected reference
classification of '" + refStrPart1 + "'.");
}
- NameType part2refType = classifyCellReference(refStrPart1, ssVersion);
+ NameType part2refType = isA1Style ? classifyCellReference(refStrPart1,
ssVersion) : NameType.CELL;
switch (part2refType) {
case BAD_CELL_OR_NAMED_RANGE:
return ErrorEval.REF_INVALID;
@@ -255,14 +266,12 @@ public final class OperationEvaluationCo
int firstRow, firstCol, lastRow, lastCol;
switch (part1refType) {
case COLUMN:
- firstRow =0;
- if (part2refType.equals(NameType.COLUMN))
- {
+ firstRow = 0;
+ if (part2refType.equals(NameType.COLUMN)) {
lastRow = ssVersion.getLastRowIndex();
firstCol = parseRowRef(refStrPart1);
lastCol = parseRowRef(refStrPart2);
- }
- else {
+ } else {
lastRow = ssVersion.getLastRowIndex();
firstCol = parseColRef(refStrPart1);
lastCol = parseColRef(refStrPart2);
@@ -271,8 +280,7 @@ public final class OperationEvaluationCo
case ROW:
// support of cell range in the form of integer:integer
firstCol = 0;
- if (part2refType.equals(NameType.ROW))
- {
+ if (part2refType.equals(NameType.ROW)) {
firstRow = parseColRef(refStrPart1);
lastRow = parseColRef(refStrPart2);
lastCol = ssVersion.getLastColumnIndex();
@@ -284,10 +292,18 @@ public final class OperationEvaluationCo
break;
case CELL:
CellReference cr;
- cr = new CellReference(refStrPart1);
+ if (isA1Style) {
+ cr = new CellReference(refStrPart1);
+ } else {
+ cr = applyR1C1Reference(new CellReference(getRowIndex(),
getColumnIndex()), refStrPart1);
+ }
firstRow = cr.getRow();
firstCol = cr.getCol();
- cr = new CellReference(refStrPart2);
+ if (isA1Style) {
+ cr = new CellReference(refStrPart2);
+ } else {
+ cr = applyR1C1Reference(new CellReference(getRowIndex(),
getColumnIndex()), refStrPart2);
+ }
lastRow = cr.getRow();
lastCol = cr.getCol();
break;
@@ -321,10 +337,12 @@ public final class OperationEvaluationCo
SheetRangeEvaluator sre = getRefEvaluatorForCurrentSheet();
return new LazyRefEval(rowIndex, columnIndex, sre);
}
+
public ValueEval getRef3DEval(Ref3DPtg rptg) {
SheetRangeEvaluator sre =
createExternSheetRefEvaluator(rptg.getExternSheetIndex());
return new LazyRefEval(rptg.getRow(), rptg.getColumn(), sre);
}
+
public ValueEval getRef3DEval(Ref3DPxg rptg) {
SheetRangeEvaluator sre = createExternSheetRefEvaluator(
rptg.getSheetName(), rptg.getLastSheetName(),
rptg.getExternalWorkbookNumber());
@@ -332,15 +350,17 @@ public final class OperationEvaluationCo
}
public ValueEval getAreaEval(int firstRowIndex, int firstColumnIndex,
- int lastRowIndex, int lastColumnIndex) {
+ int lastRowIndex, int lastColumnIndex) {
SheetRangeEvaluator sre = getRefEvaluatorForCurrentSheet();
return new LazyAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex,
lastColumnIndex, sre);
}
+
public ValueEval getArea3DEval(Area3DPtg aptg) {
SheetRangeEvaluator sre =
createExternSheetRefEvaluator(aptg.getExternSheetIndex());
return new LazyAreaEval(aptg.getFirstRow(), aptg.getFirstColumn(),
aptg.getLastRow(), aptg.getLastColumn(), sre);
}
+
public ValueEval getArea3DEval(Area3DPxg aptg) {
SheetRangeEvaluator sre = createExternSheetRefEvaluator(
aptg.getSheetName(), aptg.getLastSheetName(),
aptg.getExternalWorkbookNumber());
@@ -349,7 +369,7 @@ public final class OperationEvaluationCo
}
public ValueEval getAreaValueEval(int firstRowIndex, int firstColumnIndex,
- int lastRowIndex, int lastColumnIndex, Object[][] tokens) {
+ int lastRowIndex, int lastColumnIndex,
Object[][] tokens) {
ValueEval[] values = new ValueEval[tokens.length * tokens[0].length];
@@ -361,7 +381,7 @@ public final class OperationEvaluationCo
}
return new CacheAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex,
- lastColumnIndex, values);
+ lastColumnIndex, values);
}
private ValueEval convertObjectEval(Object token) {
@@ -369,7 +389,7 @@ public final class OperationEvaluationCo
throw new RuntimeException("Array item cannot be null");
}
if (token instanceof String) {
- return new StringEval((String)token);
+ return new StringEval((String) token);
}
if (token instanceof Double) {
return new NumberEval((Double) token);
@@ -378,7 +398,7 @@ public final class OperationEvaluationCo
return BoolEval.valueOf((Boolean) token);
}
if (token instanceof ErrorConstant) {
- return ErrorEval.valueOf(((ErrorConstant)token).getErrorCode());
+ return ErrorEval.valueOf(((ErrorConstant) token).getErrorCode());
}
throw new IllegalArgumentException("Unexpected constant class (" +
token.getClass().getName() + ")");
}
@@ -387,7 +407,7 @@ public final class OperationEvaluationCo
public ValueEval getNameXEval(NameXPtg nameXPtg) {
// Is the name actually on our workbook?
ExternalSheet externSheet =
_workbook.getExternalSheet(nameXPtg.getSheetRefIndex());
- if(externSheet == null || externSheet.getWorkbookName() == null) {
+ if (externSheet == null || externSheet.getWorkbookName() == null) {
// External reference to our own workbook's name
return getLocalNameXEval(nameXPtg);
}
@@ -395,14 +415,15 @@ public final class OperationEvaluationCo
// Look it up for the external workbook
String workbookName = externSheet.getWorkbookName();
ExternalName externName = _workbook.getExternalName(
- nameXPtg.getSheetRefIndex(),
- nameXPtg.getNameIndex()
+ nameXPtg.getSheetRefIndex(),
+ nameXPtg.getNameIndex()
);
return getExternalNameXEval(externName, workbookName);
}
+
public ValueEval getNameXEval(NameXPxg nameXPxg) {
ExternalSheet externSheet =
_workbook.getExternalSheet(nameXPxg.getSheetName(), null,
nameXPxg.getExternalWorkbookNumber());
- if(externSheet == null || externSheet.getWorkbookName() == null) {
+ if (externSheet == null || externSheet.getWorkbookName() == null) {
// External reference to our own workbook's name
return getLocalNameXEval(nameXPxg);
}
@@ -410,9 +431,9 @@ public final class OperationEvaluationCo
// Look it up for the external workbook
String workbookName = externSheet.getWorkbookName();
ExternalName externName = _workbook.getExternalName(
- nameXPxg.getNameName(),
- nameXPxg.getSheetName(),
- nameXPxg.getExternalWorkbookNumber()
+ nameXPxg.getNameName(),
+ nameXPxg.getSheetName(),
+ nameXPxg.getExternalWorkbookNumber()
);
return getExternalNameXEval(externName, workbookName);
}
@@ -435,6 +456,7 @@ public final class OperationEvaluationCo
return new FunctionNameEval(name);
}
}
+
private ValueEval getLocalNameXEval(NameXPtg nameXPtg) {
String name = _workbook.resolveNameXText(nameXPtg);
@@ -444,7 +466,7 @@ public final class OperationEvaluationCo
if (sheetNameAt > -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 OperationEvaluationCo
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 OperationEvaluationCo
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 OperationEvaluationCo
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");
+ }
+ }
}
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java?rev=1897777&r1=1897776&r2=1897777&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Indirect.java
Fri Feb 4 18:46:25 2022
@@ -93,7 +93,7 @@ public final class Indirect implements F
}
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 F
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 F
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 F
// 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, };
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java?rev=1897777&r1=1897776&r2=1897777&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndirect.java
Fri Feb 4 18:46:25 2022
@@ -25,12 +25,14 @@ import org.apache.poi.hssf.usermodel.HSS
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;
/**
@@ -155,6 +157,30 @@ final class TestIndirect {
}
@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();
HSSFCell cellA = wbA.getSheetAt(0).createRow(10).createCell(0);
@@ -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));
- }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
