From 8b72143476cef702f74e2c81c1ad1a3cc316e163 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Fri, 21 Aug 2009 23:37:17 +0000 Subject: Bugzilla 47721 - Added implementation for INDIRECT() git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@806759 13f79535-47bb-0310-9956-ffa450edef68 --- .../hssf/record/formula/atp/AnalysisToolPak.java | 5 +- .../hssf/record/formula/atp/ParityFunction.java | 7 +- .../poi/hssf/record/formula/atp/YearFrac.java | 8 +- .../poi/hssf/record/formula/eval/FunctionEval.java | 102 ++------ .../hssf/record/formula/eval/OperationEval.java | 9 +- .../record/formula/eval/UserDefinedFunction.java | 8 +- .../record/formula/functions/FreeRefFunction.java | 26 +-- .../hssf/record/formula/functions/Indirect.java | 207 +++++++++++++++- .../formula/CollaboratingWorkbooksEnvironment.java | 28 ++- .../apache/poi/ss/formula/EvaluationWorkbook.java | 4 + .../org/apache/poi/ss/formula/LazyRefEval.java | 13 +- .../poi/ss/formula/OperationEvaluationContext.java | 259 +++++++++++++++++++++ .../poi/ss/formula/OperationEvaluatorFactory.java | 4 +- .../apache/poi/ss/formula/SheetRefEvaluator.java | 18 +- .../apache/poi/ss/formula/WorkbookEvaluator.java | 90 +++---- 15 files changed, 601 insertions(+), 187 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (limited to 'src/java/org') diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java index af56041521..24423d04a0 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java @@ -22,7 +22,7 @@ import java.util.Map; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.formula.EvaluationWorkbook; +import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.NotImplementedException; public final class AnalysisToolPak { @@ -34,8 +34,7 @@ public final class AnalysisToolPak { _functionName = functionName; } - public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, - int srcCellRow, int srcCellCol) { + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { throw new NotImplementedException(_functionName); } }; diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java index 9235d15b1c..5ab9a70b7b 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java @@ -23,7 +23,7 @@ import org.apache.poi.hssf.record.formula.eval.EvaluationException; import org.apache.poi.hssf.record.formula.eval.OperandResolver; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.formula.EvaluationWorkbook; +import org.apache.poi.ss.formula.OperationEvaluationContext; /** * Implementation of Excel 'Analysis ToolPak' function ISEVEN() ISODD()
* @@ -39,15 +39,14 @@ final class ParityFunction implements FreeRefFunction { _desiredParity = desiredParity; } - public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, - int srcCellCol) { + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { if (args.length != 1) { return ErrorEval.VALUE_INVALID; } int val; try { - val = evaluateArgParity(args[0], srcCellRow, srcCellCol); + val = evaluateArgParity(args[0], ec.getRowIndex(), ec.getColumnIndex()); } catch (EvaluationException e) { return e.getErrorEval(); } diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java index 4b2ba89071..d36b05a36b 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java @@ -28,7 +28,7 @@ import org.apache.poi.hssf.record.formula.eval.OperandResolver; import org.apache.poi.hssf.record.formula.eval.StringEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.formula.EvaluationWorkbook; +import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.usermodel.DateUtil; /** * Implementation of Excel 'Analysis ToolPak' function YEARFRAC()
@@ -58,9 +58,9 @@ final class YearFrac implements FreeRefFunction { // enforce singleton } - public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, - int srcCellCol) { - + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + int srcCellRow = ec.getRowIndex(); + int srcCellCol = ec.getColumnIndex(); double result; try { int basis = 0; // default diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java index 5d04de0fc3..19476c9f13 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java @@ -20,63 +20,8 @@ package org.apache.poi.hssf.record.formula.eval; import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; import org.apache.poi.hssf.record.formula.function.FunctionMetadata; import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; -import org.apache.poi.hssf.record.formula.functions.AggregateFunction; -import org.apache.poi.hssf.record.formula.functions.And; -import org.apache.poi.hssf.record.formula.functions.CalendarFieldFunction; -import org.apache.poi.hssf.record.formula.functions.Choose; -import org.apache.poi.hssf.record.formula.functions.Column; -import org.apache.poi.hssf.record.formula.functions.Columns; -import org.apache.poi.hssf.record.formula.functions.Count; -import org.apache.poi.hssf.record.formula.functions.Counta; -import org.apache.poi.hssf.record.formula.functions.Countif; -import org.apache.poi.hssf.record.formula.functions.DateFunc; -import org.apache.poi.hssf.record.formula.functions.Errortype; -import org.apache.poi.hssf.record.formula.functions.Even; -import org.apache.poi.hssf.record.formula.functions.False; -import org.apache.poi.hssf.record.formula.functions.FinanceFunction; -import org.apache.poi.hssf.record.formula.functions.Find; -import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.hssf.record.formula.functions.Function; -import org.apache.poi.hssf.record.formula.functions.Hlookup; -import org.apache.poi.hssf.record.formula.functions.Hyperlink; -import org.apache.poi.hssf.record.formula.functions.If; -import org.apache.poi.hssf.record.formula.functions.Index; -import org.apache.poi.hssf.record.formula.functions.Indirect; -import org.apache.poi.hssf.record.formula.functions.IsError; -import org.apache.poi.hssf.record.formula.functions.IsNa; -import org.apache.poi.hssf.record.formula.functions.Isblank; -import org.apache.poi.hssf.record.formula.functions.Isref; -import org.apache.poi.hssf.record.formula.functions.LogicalFunction; -import org.apache.poi.hssf.record.formula.functions.Lookup; -import org.apache.poi.hssf.record.formula.functions.Match; -import org.apache.poi.hssf.record.formula.functions.MinaMaxa; -import org.apache.poi.hssf.record.formula.functions.Mode; -import org.apache.poi.hssf.record.formula.functions.Na; -import org.apache.poi.hssf.record.formula.functions.Not; -import org.apache.poi.hssf.record.formula.functions.NotImplementedFunction; -import org.apache.poi.hssf.record.formula.functions.Now; -import org.apache.poi.hssf.record.formula.functions.NumericFunction; -import org.apache.poi.hssf.record.formula.functions.Odd; -import org.apache.poi.hssf.record.formula.functions.Offset; -import org.apache.poi.hssf.record.formula.functions.Or; -import org.apache.poi.hssf.record.formula.functions.Pi; -import org.apache.poi.hssf.record.formula.functions.Rand; -import org.apache.poi.hssf.record.formula.functions.Replace; -import org.apache.poi.hssf.record.formula.functions.Row; -import org.apache.poi.hssf.record.formula.functions.Rows; -import org.apache.poi.hssf.record.formula.functions.Substitute; -import org.apache.poi.hssf.record.formula.functions.Sumif; -import org.apache.poi.hssf.record.formula.functions.Sumproduct; -import org.apache.poi.hssf.record.formula.functions.Sumx2my2; -import org.apache.poi.hssf.record.formula.functions.Sumx2py2; -import org.apache.poi.hssf.record.formula.functions.Sumxmy2; -import org.apache.poi.hssf.record.formula.functions.T; -import org.apache.poi.hssf.record.formula.functions.TextFunction; -import org.apache.poi.hssf.record.formula.functions.Time; -import org.apache.poi.hssf.record.formula.functions.Today; -import org.apache.poi.hssf.record.formula.functions.True; -import org.apache.poi.hssf.record.formula.functions.Value; -import org.apache.poi.hssf.record.formula.functions.Vlookup; +import org.apache.poi.hssf.record.formula.functions.*; +import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.NotImplementedException; /** @@ -99,26 +44,10 @@ public final class FunctionEval implements OperationEval { // convenient access to namespace private static final FunctionID ID = null; - protected static final Function[] functions = produceFunctions(); - - /** - * @return null if specified function + * Array elements corresponding to unimplemented functions are null */ - private Function getFunction() { - short fidx = getFunctionIndex(); - return functions[fidx]; - } - public boolean isFreeRefFunction() { - return getFreeRefFunction() != null; - } - public FreeRefFunction getFreeRefFunction() { - switch (getFunctionIndex()) { - case FunctionID.INDIRECT: return Indirect.instance; - case FunctionID.EXTERNAL_FUNC: return UserDefinedFunction.instance; - } - return null; - } + protected static final Function[] functions = produceFunctions(); private static Function[] produceFunctions() { Function[] retval = new Function[368]; @@ -299,19 +228,26 @@ public final class FunctionEval implements OperationEval { _delegate = funcPtg; } - public ValueEval evaluate(ValueEval[] operands, int srcRow, short srcCol) { - Function f = getFunction(); + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + int fidx = _delegate.getFunctionIndex(); + // check for 'free ref' functions first + switch (fidx) { + case FunctionID.INDIRECT: + return Indirect.instance.evaluate(args, ec); + case FunctionID.EXTERNAL_FUNC: + return UserDefinedFunction.instance.evaluate(args, ec); + } + // else - must be plain function + Function f = functions[fidx]; if (f == null) { - throw new NotImplementedException("FuncIx=" + getFunctionIndex()); + throw new NotImplementedException("FuncIx=" + fidx); } - return f.evaluate(operands, srcRow, srcCol); + int srcCellRow = ec.getRowIndex(); + int srcCellCol = ec.getColumnIndex(); + return f.evaluate(args, srcCellRow, (short) srcCellCol); } public int getNumberOfOperands() { return _delegate.getNumberOfOperands(); } - - private short getFunctionIndex() { - return _delegate.getFunctionIndex(); - } } diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java index d66c314d7d..481caa0b6b 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java @@ -17,6 +17,8 @@ package org.apache.poi.hssf.record.formula.eval; +import org.apache.poi.ss.formula.OperationEvaluationContext; + /** * Common interface for implementations of Excel formula operations. * @@ -29,11 +31,10 @@ public interface OperationEval { * @param args the evaluated operation arguments. Elements of this array typically implement * {@link ValueEval}. Empty values are represented with {@link BlankEval} or {@link * MissingArgEval}, never null. - * @param srcRowIndex row index of the cell containing the formula under evaluation - * @param srcColumnIndex column index of the cell containing the formula under evaluation + * @param ec used to identify the current cell under evaluation, and potentially to + * dynamically create references * @return The evaluated result, possibly an {@link ErrorEval}, never null. */ - ValueEval evaluate(ValueEval[] args, int srcRowIndex, short srcColumnIndex); - + ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec); int getNumberOfOperands(); } diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java b/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java index 384b576eec..f12c96e59f 100755 --- a/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java @@ -20,6 +20,7 @@ package org.apache.poi.hssf.record.formula.eval; import org.apache.poi.hssf.record.formula.atp.AnalysisToolPak; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.EvaluationWorkbook; +import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.NotImplementedException; /** * @@ -36,8 +37,7 @@ final class UserDefinedFunction implements FreeRefFunction { // enforce singleton } - public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, - int srcCellSheet, int srcCellRow,int srcCellCol) { + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { int nIncomingArgs = args.length; if(nIncomingArgs < 1) { @@ -49,7 +49,7 @@ final class UserDefinedFunction implements FreeRefFunction { if (nameArg instanceof NameEval) { targetFunc = findInternalUserDefinedFunction((NameEval) nameArg); } else if (nameArg instanceof NameXEval) { - targetFunc = findExternalUserDefinedFunction(workbook, (NameXEval) nameArg); + targetFunc = findExternalUserDefinedFunction(ec.getWorkbook(), (NameXEval) nameArg); } else { throw new RuntimeException("First argument should be a NameEval, but got (" + nameArg.getClass().getName() + ")"); @@ -57,7 +57,7 @@ final class UserDefinedFunction implements FreeRefFunction { int nOutGoingArgs = nIncomingArgs -1; ValueEval[] outGoingArgs = new ValueEval[nOutGoingArgs]; System.arraycopy(args, 1, outGoingArgs, 0, nOutGoingArgs); - return targetFunc.evaluate(outGoingArgs, workbook, srcCellSheet, srcCellRow, srcCellCol); + return targetFunc.evaluate(outGoingArgs, ec); } private static FreeRefFunction findExternalUserDefinedFunction(EvaluationWorkbook workbook, diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java index c806472068..119592101d 100755 --- a/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java @@ -18,7 +18,7 @@ package org.apache.poi.hssf.record.formula.functions; import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.ss.formula.EvaluationWorkbook; +import org.apache.poi.ss.formula.OperationEvaluationContext; /** @@ -28,28 +28,24 @@ import org.apache.poi.ss.formula.EvaluationWorkbook; * argument.
* Two important functions with this feature are INDIRECT and OFFSET

* - * In POI, the HSSFFormulaEvaluator evaluates every cell in each reference argument before - * calling the function. This means that functions using fixed references do not need access to - * the rest of the workbook to execute. Hence the evaluate() method on the common - * interface Function does not take a workbook parameter.

+ * When POI evaluates formulas, each reference argument is capable of evaluating any cell inside + * its range. Actually, even cells outside the reference range but on the same sheet can be + * evaluated. This allows OFFSET to be implemented like most other functions - taking only + * the arguments, and source cell coordinates. * - * This interface recognises the requirement of some functions to freely create and evaluate - * references beyond those passed in as arguments. + * For the moment this interface only exists to serve the INDIRECT which can decode + * arbitrary text into cell references, and evaluate them.. * * @author Josh Micich */ public interface FreeRefFunction { /** - * * @param args the pre-evaluated arguments for this function. args is never null, - * nor are any of its elements. - * @param srcCellSheet zero based sheet index of the cell containing the currently evaluating formula - * @param srcCellRow zero based row index of the cell containing the currently evaluating formula - * @param srcCellCol zero based column index of the cell containing the currently evaluating formula - * @param workbook is the workbook containing the formula/cell being evaluated + * nor are any of its elements. + * @param ec primarily used to identify the source cell containing the formula being evaluated. + * may also be used to dynamically create reference evals. * @return never null. Possibly an instance of ErrorEval in the case of * a specified Excel error (Exceptions are never thrown to represent Excel errors). - * */ - ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol); + ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec); } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java b/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java index d7b031d565..80d515ac14 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java @@ -17,9 +17,13 @@ package org.apache.poi.hssf.record.formula.functions; +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.EvaluationException; +import org.apache.poi.hssf.record.formula.eval.MissingArgEval; +import org.apache.poi.hssf.record.formula.eval.OperandResolver; import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.ss.formula.EvaluationWorkbook; -import org.apache.poi.ss.formula.eval.NotImplementedException; +import org.apache.poi.ss.formula.OperationEvaluationContext; /** * Implementation for Excel function INDIRECT

@@ -29,11 +33,10 @@ import org.apache.poi.ss.formula.eval.NotImplementedException; * 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. - * + * 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 */ @@ -45,8 +48,192 @@ public final class Indirect implements FreeRefFunction { // enforce singleton } - public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) { - // TODO - implement INDIRECT() - throw new NotImplementedException("INDIRECT"); + 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(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); + } + + 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; } } diff --git a/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java b/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java index 9a0cd8511f..21d3fd0c6d 100644 --- a/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java +++ b/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java @@ -34,9 +34,15 @@ import java.util.Set; * @author Josh Micich */ public final class CollaboratingWorkbooksEnvironment { - + + public static final class WorkbookNotFoundException extends Exception { + WorkbookNotFoundException(String msg) { + super(msg); + } + } + public static final CollaboratingWorkbooksEnvironment EMPTY = new CollaboratingWorkbooksEnvironment(); - + private final Map _evaluatorsByName; private final WorkbookEvaluator[] _evaluators; @@ -48,7 +54,7 @@ public final class CollaboratingWorkbooksEnvironment { public static void setup(String[] workbookNames, WorkbookEvaluator[] evaluators) { int nItems = workbookNames.length; if (evaluators.length != nItems) { - throw new IllegalArgumentException("Number of workbook names is " + nItems + throw new IllegalArgumentException("Number of workbook names is " + nItems + " but number of evaluators is " + evaluators.length); } if (nItems < 1) { @@ -82,7 +88,7 @@ public final class CollaboratingWorkbooksEnvironment { } private static void hookNewEnvironment(WorkbookEvaluator[] evaluators, CollaboratingWorkbooksEnvironment env) { - + // All evaluators will need to share the same cache. // but the cache takes an optional evaluation listener. int nItems = evaluators.length; @@ -95,12 +101,15 @@ public final class CollaboratingWorkbooksEnvironment { } } EvaluationCache cache = new EvaluationCache(evalListener); - + for(int i=0; i oldEnvs = new HashSet(); for(int i=0; inull if either workbook or sheet is not found + */ + private SheetRefEvaluator createExternSheetRefEvaluator(String workbookName, String sheetName) { + WorkbookEvaluator targetEvaluator; + if (workbookName == null) { + targetEvaluator = _bookEvaluator; + } else { + if (sheetName == null) { + throw new IllegalArgumentException("sheetName must not be null if workbookName is provided"); + } + try { + targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName); + } catch (WorkbookNotFoundException e) { + return null; + } + } + int otherSheetIndex = sheetName == null ? _sheetIndex : targetEvaluator.getSheetIndex(sheetName); + if (otherSheetIndex < 0) { + return null; + } + return new SheetRefEvaluator(targetEvaluator, _tracker, otherSheetIndex); + } + + public SheetRefEvaluator getRefEvaluatorForCurrentSheet() { + return new SheetRefEvaluator(_bookEvaluator, _tracker, _sheetIndex); + } + + + + /** + * Resolves a cell or area reference dynamically. + * @param workbookName the name of the workbook containing the reference. If 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 null + * (when 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 null. + * @param refStrPart2 the second part of the area reference. For single cell references this + * parameter must be null + * @param isA1Style specifies the format for refStrPart1 and refStrPart2. + * Pass true for 'A1' style and false for 'R1C1' style. + * TODO - currently POI only supports 'A1' reference 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"); + } + SheetRefEvaluator sre = createExternSheetRefEvaluator(workbookName, sheetName); + if (sre == null) { + return ErrorEval.REF_INVALID; + } + // ugly typecast - TODO - make spreadsheet version more easily accessible + SpreadsheetVersion ssVersion = ((FormulaParsingWorkbook)_workbook).getSpreadsheetVersion(); + + NameType part1refType = classifyCellReference(refStrPart1, ssVersion); + switch (part1refType) { + case BAD_CELL_OR_NAMED_RANGE: + return ErrorEval.REF_INVALID; + case NAMED_RANGE: + throw new RuntimeException("Cannot evaluate '" + refStrPart1 + + "'. Indirect evaluation of defined names not supported yet"); + } + if (refStrPart2 == null) { + // no ':' + switch (part1refType) { + case COLUMN: + case ROW: + return ErrorEval.REF_INVALID; + case CELL: + CellReference cr = new CellReference(refStrPart1); + return new LazyRefEval(cr.getRow(), cr.getCol(), sre); + } + throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'."); + } + NameType part2refType = classifyCellReference(refStrPart1, ssVersion); + switch (part2refType) { + case BAD_CELL_OR_NAMED_RANGE: + return ErrorEval.REF_INVALID; + case NAMED_RANGE: + throw new RuntimeException("Cannot evaluate '" + refStrPart1 + + "'. Indirect evaluation of defined names not supported yet"); + } + + if (part2refType != part1refType) { + // LHS and RHS of ':' must be compatible + return ErrorEval.REF_INVALID; + } + int firstRow, firstCol, lastRow, lastCol; + switch (part1refType) { + case COLUMN: + firstRow =0; + lastRow = ssVersion.getLastRowIndex(); + firstCol = parseColRef(refStrPart1); + lastCol = parseColRef(refStrPart2); + break; + case ROW: + firstCol = 0; + lastCol = ssVersion.getLastColumnIndex(); + firstRow = parseRowRef(refStrPart1); + lastRow = parseRowRef(refStrPart2); + break; + case CELL: + CellReference cr; + cr = new CellReference(refStrPart1); + firstRow = cr.getRow(); + firstCol = cr.getCol(); + cr = new CellReference(refStrPart2); + lastRow = cr.getRow(); + lastCol = cr.getCol(); + break; + default: + throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'."); + } + return new LazyAreaEval(new AI(firstRow, firstCol, lastRow, lastCol), sre); + } + + private static int parseRowRef(String refStrPart) { + return CellReference.convertColStringToIndex(refStrPart); + } + + private static int parseColRef(String refStrPart) { + return Integer.parseInt(refStrPart) - 1; + } + + private static final class AI implements AreaI { + + private final int _fr; + private final int _lr; + private final int _fc; + private final int _lc; + + public AI(int fr, int fc, int lr, int lc) { + _fr = Math.min(fr, lr); + _lr = Math.max(fr, lr); + _fc = Math.min(fc, lc); + _lc = Math.max(fc, lc); + } + public int getFirstColumn() { + return _fc; + } + public int getFirstRow() { + return _fr; + } + public int getLastColumn() { + return _lc; + } + public int getLastRow() { + return _lr; + } + } + + private static NameType classifyCellReference(String str, SpreadsheetVersion ssVersion) { + int len = str.length(); + if (len < 1) { + return CellReference.NameType.BAD_CELL_OR_NAMED_RANGE; + } + return CellReference.classifyCellReference(str, ssVersion); + } +} diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java index b459b4c99c..449c0b18c6 100755 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java @@ -106,8 +106,8 @@ final class OperationEvaluatorFactory { _numberOfOperands = argCount; } - public ValueEval evaluate(ValueEval[] args, int rowIndex, short columnIndex) { - return _function.evaluate(args, rowIndex, columnIndex); + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + return _function.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex()); } public int getNumberOfOperands() { diff --git a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java index b2a6ed123d..97568cfec4 100644 --- a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java @@ -27,14 +27,15 @@ final class SheetRefEvaluator { private final WorkbookEvaluator _bookEvaluator; private final EvaluationTracker _tracker; - private final EvaluationSheet _sheet; private final int _sheetIndex; + private EvaluationSheet _sheet; - public SheetRefEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker, - EvaluationWorkbook _workbook, int sheetIndex) { + public SheetRefEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker, int sheetIndex) { + if (sheetIndex < 0) { + throw new IllegalArgumentException("Invalid sheetIndex: " + sheetIndex + "."); + } _bookEvaluator = bookEvaluator; _tracker = tracker; - _sheet = _workbook.getSheet(sheetIndex); _sheetIndex = sheetIndex; } @@ -43,6 +44,13 @@ final class SheetRefEvaluator { } public ValueEval getEvalForCell(int rowIndex, int columnIndex) { - return _bookEvaluator.evaluateReference(_sheet, _sheetIndex, rowIndex, columnIndex, _tracker); + return _bookEvaluator.evaluateReference(getSheet(), _sheetIndex, rowIndex, columnIndex, _tracker); + } + + private EvaluationSheet getSheet() { + if (_sheet == null) { + _sheet = _bookEvaluator.getSheet(_sheetIndex); + } + return _sheet; } } diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index abb925713e..806c002bb1 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -51,7 +51,6 @@ import org.apache.poi.hssf.record.formula.eval.AreaEval; import org.apache.poi.hssf.record.formula.eval.BlankEval; import org.apache.poi.hssf.record.formula.eval.BoolEval; import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.hssf.record.formula.eval.FunctionEval; import org.apache.poi.hssf.record.formula.eval.MissingArgEval; import org.apache.poi.hssf.record.formula.eval.NameEval; import org.apache.poi.hssf.record.formula.eval.NameXEval; @@ -61,7 +60,7 @@ import org.apache.poi.hssf.record.formula.eval.RefEval; import org.apache.poi.hssf.record.formula.eval.StringEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.util.CellReference; -import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet; +import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException; import org.apache.poi.ss.formula.eval.NotImplementedException; import org.apache.poi.ss.usermodel.Cell; @@ -80,10 +79,12 @@ public final class WorkbookEvaluator { private final EvaluationWorkbook _workbook; private EvaluationCache _cache; + /** part of cache entry key (useful when evaluating multiple workbooks) */ private int _workbookIx; private final IEvaluationListener _evaluationListener; private final Map _sheetIndexesBySheet; + private final Map _sheetIndexesByName; private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment; private final IStabilityClassifier _stabilityClassifier; @@ -96,6 +97,7 @@ public final class WorkbookEvaluator { _evaluationListener = evaluationListener; _cache = new EvaluationCache(evaluationListener); _sheetIndexesBySheet = new IdentityHashMap(); + _sheetIndexesByName = new IdentityHashMap(); _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; _workbookIx = 0; _stabilityClassifier = stabilityClassifier; @@ -108,6 +110,10 @@ public final class WorkbookEvaluator { return _workbook.getSheetName(sheetIndex); } + /* package */ EvaluationSheet getSheet(int sheetIndex) { + return _workbook.getSheet(sheetIndex); + } + private static boolean isDebugLogEnabled() { return false; } @@ -125,11 +131,22 @@ public final class WorkbookEvaluator { return _collaboratingWorkbookEnvironment; } + /** + * Discards the current workbook environment and attaches to the default 'empty' environment. + * Also resets evaluation cache. + */ /* package */ void detachFromEnvironment() { _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; _cache = new EvaluationCache(_evaluationListener); _workbookIx = 0; } + /** + * @return the evaluator for another workbook which is part of the same {@link CollaboratingWorkbooksEnvironment} + */ + /* package */ WorkbookEvaluator getOtherWorkbookEvaluator(String workbookName) throws WorkbookNotFoundException { + return _collaboratingWorkbookEnvironment.getWorkbookEvaluator(workbookName); + } + /* package */ IEvaluationListener getEvaluationListener() { return _evaluationListener; } @@ -179,6 +196,23 @@ public final class WorkbookEvaluator { return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache)); } + /** + * Case-insensitive. + * @return -1 if sheet with specified name does not exist + */ + /* package */ int getSheetIndex(String sheetName) { + Integer result = _sheetIndexesByName.get(sheetName); + if (result == null) { + int sheetIndex = _workbook.getSheetIndex(sheetName); + if (sheetIndex < 0) { + return -1; + } + result = new Integer(sheetIndex); + _sheetIndexesByName.put(sheetName, result); + } + return result.intValue(); + } + /** * @return never null, never {@link BlankEval} @@ -207,15 +241,16 @@ public final class WorkbookEvaluator { if (!tracker.startEvaluate(cce)) { return ErrorEval.CIRCULAR_REF_ERROR; } + OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker); try { Ptg[] ptgs = _workbook.getFormulaTokens(srcCell); if (evalListener == null) { - result = evaluateFormula(sheetIndex, rowIndex, columnIndex, ptgs, tracker); + result = evaluateFormula(ec, ptgs); } else { evalListener.onStartEvaluate(srcCell, cce, ptgs); - result = evaluateFormula(sheetIndex, rowIndex, columnIndex, ptgs, tracker); + result = evaluateFormula(ec, ptgs); evalListener.onEndEvaluate(cce, result); } @@ -286,7 +321,7 @@ public final class WorkbookEvaluator { throw new RuntimeException("Unexpected cell type (" + cellType + ")"); } // visibility raised for testing - /* package */ ValueEval evaluateFormula(int sheetIndex, int srcRowNum, int srcColNum, Ptg[] ptgs, EvaluationTracker tracker) { + /* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) { Stack stack = new Stack(); for (int i = 0, iSize = ptgs.length; i < iSize; i++) { @@ -329,12 +364,12 @@ public final class WorkbookEvaluator { ops[j] = p; } // logDebug("invoke " + operation + " (nAgs=" + numops + ")"); - opResult = invokeOperation(operation, ops, _workbook, sheetIndex, srcRowNum, srcColNum); + opResult = operation.evaluate(ops, ec); if (opResult == MissingArgEval.instance) { opResult = BlankEval.INSTANCE; } } else { - opResult = getEvalForPtg(ptg, sheetIndex, tracker); + opResult = getEvalForPtg(ptg, ec); } if (opResult == null) { throw new RuntimeException("Evaluation result must not be null"); @@ -347,7 +382,7 @@ public final class WorkbookEvaluator { if (!stack.isEmpty()) { throw new IllegalStateException("evaluation stack not empty"); } - value = dereferenceValue(value, srcRowNum, srcColNum); + value = dereferenceValue(value, ec.getRowIndex(), ec.getColumnIndex()); if (value == BlankEval.INSTANCE) { // Note Excel behaviour here. A blank final final value is converted to zero. return NumberEval.ZERO; @@ -384,31 +419,6 @@ public final class WorkbookEvaluator { return evaluationResult; } - private static ValueEval invokeOperation(OperationEval operation, ValueEval[] ops, - EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, int srcColNum) { - - if(operation instanceof FunctionEval) { - FunctionEval fe = (FunctionEval) operation; - if(fe.isFreeRefFunction()) { - return fe.getFreeRefFunction().evaluate(ops, workbook, sheetIndex, srcRowNum, srcColNum); - } - } - return operation.evaluate(ops, srcRowNum, (short)srcColNum); - } - private SheetRefEvaluator createExternSheetRefEvaluator(EvaluationTracker tracker, - ExternSheetReferenceToken ptg) { - int externSheetIndex = ptg.getExternSheetIndex(); - ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex); - if (externalSheet != null) { - WorkbookEvaluator otherEvaluator = _collaboratingWorkbookEnvironment.getWorkbookEvaluator(externalSheet.getWorkbookName()); - EvaluationWorkbook otherBook = otherEvaluator._workbook; - int otherSheetIndex = otherBook.getSheetIndex(externalSheet.getSheetName()); - return new SheetRefEvaluator(otherEvaluator, tracker, otherBook, otherSheetIndex); - } - int otherSheetIndex = _workbook.convertFromExternSheetIndex(externSheetIndex); - return new SheetRefEvaluator(this, tracker, _workbook, otherSheetIndex); - - } /** * returns an appropriate Eval impl instance for the Ptg. The Ptg must be @@ -416,7 +426,7 @@ public final class WorkbookEvaluator { * StringPtg, BoolPtg
special Note: OperationPtg subtypes cannot be * passed here! */ - private ValueEval getEvalForPtg(Ptg ptg, int sheetIndex, EvaluationTracker tracker) { + private ValueEval getEvalForPtg(Ptg ptg, OperationEvaluationContext ec) { // consider converting all these (ptg instanceof XxxPtg) expressions to (ptg.getClass() == XxxPtg.class) if (ptg instanceof NamePtg) { @@ -427,7 +437,7 @@ public final class WorkbookEvaluator { return new NameEval(nameRecord.getNameText()); } if (nameRecord.hasFormula()) { - return evaluateNameFormula(nameRecord.getNameDefinition(), sheetIndex, tracker); + return evaluateNameFormula(nameRecord.getNameDefinition(), ec); } throw new RuntimeException("Don't now how to evalate name '" + nameRecord.getNameText() + "'"); @@ -460,15 +470,15 @@ public final class WorkbookEvaluator { } if (ptg instanceof Ref3DPtg) { Ref3DPtg refPtg = (Ref3DPtg) ptg; - SheetRefEvaluator sre = createExternSheetRefEvaluator(tracker, refPtg); + SheetRefEvaluator sre = ec.createExternSheetRefEvaluator(refPtg); return new LazyRefEval(refPtg, sre); } if (ptg instanceof Area3DPtg) { Area3DPtg aptg = (Area3DPtg) ptg; - SheetRefEvaluator sre = createExternSheetRefEvaluator(tracker, aptg); + SheetRefEvaluator sre = ec.createExternSheetRefEvaluator(aptg); return new LazyAreaEval(aptg, sre); } - SheetRefEvaluator sre = new SheetRefEvaluator(this, tracker, _workbook, sheetIndex); + SheetRefEvaluator sre = ec.getRefEvaluatorForCurrentSheet(); if (ptg instanceof RefPtg) { return new LazyRefEval(((RefPtg) ptg), sre); } @@ -490,11 +500,11 @@ public final class WorkbookEvaluator { throw new RuntimeException("Unexpected ptg class (" + ptg.getClass().getName() + ")"); } - private ValueEval evaluateNameFormula(Ptg[] ptgs, int sheetIndex, EvaluationTracker tracker) { + private ValueEval evaluateNameFormula(Ptg[] ptgs, OperationEvaluationContext ec) { if (ptgs.length > 1) { throw new RuntimeException("Complex name formulas not supported yet"); } - return getEvalForPtg(ptgs[0], sheetIndex, tracker); + return getEvalForPtg(ptgs[0], ec); } /** -- cgit v1.2.3