org.apache.poi.ss.formula.functions.Indirect Maven / Gradle / Ivy
Show all versions of poi Show documentation
/* ====================================================================
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.functions;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaParsingWorkbook;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.BlankEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.MissingArgEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.ptg.Area3DPxg;
import org.apache.poi.ss.usermodel.Table;
/**
* Implementation for Excel function INDIRECT
*
* INDIRECT() returns the cell or area reference denoted by the text argument.
*
* Syntax:
* INDIRECT(ref_text,isA1Style)
*
* ref_text a string representation of the desired reference as it would
* normally be written in a cell formula.
* isA1Style (default TRUE) specifies whether the ref_text should be
* interpreted as A1-style or R1C1-style.
*
* @author Josh Micich
*/
public final class Indirect implements FreeRefFunction {
public static final FreeRefFunction instance = new Indirect();
private Indirect() {
// enforce singleton
}
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
if (args.length < 1) {
return ErrorEval.VALUE_INVALID;
}
boolean isA1style;
String text;
try {
ValueEval ve = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec
.getColumnIndex());
text = OperandResolver.coerceValueToString(ve);
switch (args.length) {
case 1:
isA1style = true;
break;
case 2:
isA1style = evaluateBooleanArg(args[1], ec);
break;
default:
return ErrorEval.VALUE_INVALID;
}
} catch (EvaluationException e) {
return e.getErrorEval();
}
return evaluateIndirect(ec, text, isA1style);
}
private static boolean evaluateBooleanArg(ValueEval arg, OperationEvaluationContext ec)
throws EvaluationException {
ValueEval ve = OperandResolver.getSingleValue(arg, ec.getRowIndex(), ec.getColumnIndex());
if (ve == BlankEval.instance || ve == MissingArgEval.instance) {
return false;
}
// numeric quantities follow standard boolean conversion rules
// for strings, only "TRUE" and "FALSE" (case insensitive) are valid
return OperandResolver.coerceValueToBoolean(ve, false).booleanValue();
}
private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text,
boolean isA1style) {
// Search backwards for '!' because sheet names can contain '!'
int plingPos = text.lastIndexOf('!');
String workbookName;
String sheetName;
String refText; // whitespace around this gets trimmed OK
if (plingPos < 0) {
workbookName = null;
sheetName = null;
refText = text;
} else {
String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos));
if (parts == null) {
return ErrorEval.REF_INVALID;
}
workbookName = parts[0];
sheetName = parts[1];
refText = text.substring(plingPos + 1);
}
if (Table.isStructuredReference.matcher(refText).matches()) {
// The argument is structured reference
Area3DPxg areaPtg = null;
try {
areaPtg = FormulaParser.parseStructuredReference(refText, (FormulaParsingWorkbook) ec.getWorkbook(), ec.getRowIndex());
} catch (FormulaParseException e) {
return ErrorEval.REF_INVALID;
}
return ec.getArea3DEval(areaPtg);
} else {
// The argument is regular reference
String refStrPart1;
String refStrPart2;
int colonPos = refText.indexOf(':');
if (colonPos < 0) {
refStrPart1 = refText.trim();
refStrPart2 = null;
} else {
refStrPart1 = refText.substring(0, colonPos).trim();
refStrPart2 = refText.substring(colonPos + 1).trim();
}
return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style);
}
}
/**
* @return array of length 2: {workbookName, sheetName,}. Second element will always be
* present. First element may be null if sheetName is unqualified.
* Returns null if text cannot be parsed.
*/
private static String[] parseWorkbookAndSheetName(CharSequence text) {
int lastIx = text.length() - 1;
if (lastIx < 0) {
return null;
}
if (canTrim(text)) {
return null;
}
char firstChar = text.charAt(0);
if (Character.isWhitespace(firstChar)) {
return null;
}
if (firstChar == '\'') {
// workbookName or sheetName needs quoting
// quotes go around both
if (text.charAt(lastIx) != '\'') {
return null;
}
firstChar = text.charAt(1);
if (Character.isWhitespace(firstChar)) {
return null;
}
String wbName;
int sheetStartPos;
if (firstChar == '[') {
int rbPos = text.toString().lastIndexOf(']');
if (rbPos < 0) {
return null;
}
wbName = unescapeString(text.subSequence(2, rbPos));
if (wbName == null || canTrim(wbName)) {
return null;
}
sheetStartPos = rbPos + 1;
} else {
wbName = null;
sheetStartPos = 1;
}
// else - just sheet name
String sheetName = unescapeString(text.subSequence(sheetStartPos, lastIx));
if (sheetName == null) { // note - when quoted, sheetName can
// start/end with whitespace
return null;
}
return new String[] { wbName, sheetName, };
}
if (firstChar == '[') {
int rbPos = text.toString().lastIndexOf(']');
if (rbPos < 0) {
return null;
}
CharSequence wbName = text.subSequence(1, rbPos);
if (canTrim(wbName)) {
return null;
}
CharSequence sheetName = text.subSequence(rbPos + 1, text.length());
if (canTrim(sheetName)) {
return null;
}
return new String[] { wbName.toString(), sheetName.toString(), };
}
// else - just sheet name
return new String[] { null, text.toString(), };
}
/**
* @return null if there is a syntax error in any escape sequence
* (the typical syntax error is a single quote character not followed by another).
*/
private static String unescapeString(CharSequence text) {
int len = text.length();
StringBuilder sb = new StringBuilder(len);
int i = 0;
while (i < len) {
char ch = text.charAt(i);
if (ch == '\'') {
// every quote must be followed by another
i++;
if (i >= len) {
return null;
}
ch = text.charAt(i);
if (ch != '\'') {
return null;
}
}
sb.append(ch);
i++;
}
return sb.toString();
}
private static boolean canTrim(CharSequence text) {
int lastIx = text.length() - 1;
if (lastIx < 0) {
return false;
}
if (Character.isWhitespace(text.charAt(0))) {
return true;
}
if (Character.isWhitespace(text.charAt(lastIx))) {
return true;
}
return false;
}
}