support basic R1C1 refs in INDIRECT function

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897777 13f79535-47bb-0310-9956-ffa450edef68

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
- *
+ * <p>
  * 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<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 OperationEvaluationContext {
     }
 
 
-
     /**
      * 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 OperationEvaluationContext {
 
         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 OperationEvaluationContext {
                 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 OperationEvaluationContext {
         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 OperationEvaluationContext {
             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 OperationEvaluationContext {
                 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 OperationEvaluationContext {
         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 OperationEvaluationContext {
     }
 
     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 OperationEvaluationContext {
     }
 
     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 OperationEvaluationContext {
         }
 
         return new CacheAreaEval(firstRowIndex, firstColumnIndex, lastRowIndex,
-                                 lastColumnIndex, values);
+                lastColumnIndex, values);
     }
 
     private ValueEval convertObjectEval(Object token) {
@@ -369,7 +389,7 @@ public final class OperationEvaluationContext {
             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 OperationEvaluationContext {
             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 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) {
+        if (externSheet == null || externSheet.getWorkbookName() == null) {
             // External reference to our own workbook's name
             return getLocalNameXEval(nameXPtg);
         }
@@ -395,14 +415,15 @@ public final class OperationEvaluationContext {
         // 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 OperationEvaluationContext {
         // 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 OperationEvaluationContext {
             return new FunctionNameEval(name);
         }
     }
+
     private ValueEval getLocalNameXEval(NameXPtg nameXPtg) {
         String name = _workbook.resolveNameXText(nameXPtg);
 
@@ -444,7 +466,7 @@ public final class OperationEvaluationContext {
         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 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;
 
 /**
@@ -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));
-    }
 }