org.apache.poi.ss.formula.eval.OperandResolver Maven / Gradle / Ivy
Show all versions of apache-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.eval;
import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import java.time.DateTimeException;
import java.util.regex.Pattern;
/**
* Provides functionality for evaluating arguments to functions and operators.
*/
public final class OperandResolver {
// Based on regular expression defined in JavaDoc at {@link java.lang.Double#valueOf}
// modified to remove support for NaN, Infinity, Hexadecimal support and floating type suffixes
private static final String Digits = "(\\p{Digit}+)";
private static final String Exp = "[eE][+-]?"+Digits;
private static final String fpRegex =
("[\\x00-\\x20]*" +
"[+-]?(" +
"("+Digits+"(\\.)?("+Digits+"?)("+Exp+")?)|"+
"(\\."+Digits+"("+Exp+")?))"+
"[\\x00-\\x20]*");
private static final Pattern fpPattern = Pattern.compile(fpRegex);
private OperandResolver() {
// no instances of this class
}
/**
* Retrieves a single value from a variety of different argument types according to standard
* Excel rules. Does not perform any type conversion.
* @param arg the evaluated argument as passed to the function or operator.
* @param srcCellRow used when arg is a single column AreaRef
* @param srcCellCol used when arg is a single row AreaRef
* @return a {@code NumberEval}, {@code StringEval}, {@code BoolEval} or {@code BlankEval}.
* Never {@code null} or {@code ErrorEval}.
* @throws EvaluationException if srcCellRow or srcCellCol do not properly index into
* an AreaEval. If the actual value retrieved is an ErrorEval, a corresponding
* EvaluationException is thrown.
*/
public static ValueEval getSingleValue(ValueEval arg, int srcCellRow, int srcCellCol)
throws EvaluationException {
final ValueEval result;
if (arg instanceof RefEval) {
result = chooseSingleElementFromRef((RefEval) arg);
} else if (arg instanceof AreaEval) {
result = chooseSingleElementFromArea((AreaEval) arg, srcCellRow, srcCellCol);
} else {
result = arg;
}
if (result instanceof ErrorEval) {
throw new EvaluationException((ErrorEval) result);
}
return result;
}
/**
* Retrieves a single value from an area evaluation utilizing the 2D indices of the cell
* within its own area reference to index the value in the area evaluation.
*
* @param ae area reference after evaluation
* @param cell the source cell of the formula that contains its 2D indices
* @return a {@code NumberEval}, {@code StringEval}, {@code BoolEval} or {@code BlankEval}. or {@code ErrorEval}
* Never {@code null}.
*/
public static ValueEval getElementFromArray(AreaEval ae, EvaluationCell cell) {
CellRangeAddress range = cell.getArrayFormulaRange();
int relativeRowIndex = cell.getRowIndex() - range.getFirstRow();
int relativeColIndex = cell.getColumnIndex() - range.getFirstColumn();
if (ae.isColumn()) {
if (ae.isRow()) {
return ae.getRelativeValue(0, 0);
}
else if(relativeRowIndex < ae.getHeight()) {
return ae.getRelativeValue(relativeRowIndex, 0);
}
}
else if (!ae.isRow() && relativeRowIndex < ae.getHeight() && relativeColIndex < ae.getWidth()) {
return ae.getRelativeValue(relativeRowIndex, relativeColIndex);
}
else if (ae.isRow() && relativeColIndex < ae.getWidth()) {
return ae.getRelativeValue(0, relativeColIndex);
}
return ErrorEval.NA;
}
/**
* Implements (some perhaps not well known) Excel functionality to select a single cell from an
* area depending on the coordinates of the calling cell. Here is an example demonstrating
* both selection from a single row area and a single column area in the same formula.
*
*
* sample spreadsheet
* A B C D 1 15 20 25 2 200 3 300 3 400
*
* If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spreadsheet
* will look like this:
*
*
* sample spreadsheet
* A B C D 1 15 20 25 2 1215 1220 #VALUE! 200 3 1315 1320 #VALUE! 300 4 #VALUE! #VALUE! #VALUE! 400
*
* Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does
* not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula
* as written, but in the 4 cells A2:B3, the row and column selection works ok.
*
* The same concept is extended to references across sheets, such that even multi-row,
* multi-column areas can be useful.
*
* Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and
* hence this method can throw a 'circular reference' EvaluationException. Note that
* this method does not attempt to detect cycles. Every cell in the specified Area {@code ae}
* has already been evaluated prior to this method call. Any cell (or cells) part of
* {@code ae} that would incur a cyclic reference error if selected by this method, will
* already have the value {@code ErrorEval.CIRCULAR_REF_ERROR} upon entry to this method. It
* is assumed logic exists elsewhere to produce this behaviour.
*
* @return whatever the selected cell's evaluated value is. Never {@code null}. Never
* {@code ErrorEval}.
* @throws EvaluationException if there is a problem with indexing into the area, or if the
* evaluated cell has an error.
*/
public static ValueEval chooseSingleElementFromArea(AreaEval ae,
int srcCellRow, int srcCellCol) throws EvaluationException {
ValueEval result = chooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol);
if (result instanceof ErrorEval) {
throw new EvaluationException((ErrorEval) result);
}
return result;
}
/**
* @return possibly {@code ErrorEval}, and {@code null}
*/
private static ValueEval chooseSingleElementFromAreaInternal(AreaEval ae,
int srcCellRow, int srcCellCol) throws EvaluationException {
// if(false) {
// // this is too simplistic
// if(ae.containsRow(srcCellRow) && ae.containsColumn(srcCellCol)) {
// throw new EvaluationException(ErrorEval.CIRCULAR_REF_ERROR);
// }
// /*
// Circular references are not dealt with directly here, but it is worth noting some issues.
//
// ANY one of the return statements in this method could return a cell that is identical
// to the one immediately being evaluated. The evaluating cell is identified by srcCellRow,
// srcCellRow AND sheet. The sheet is not available in any nearby calling method, so that's
// one reason why circular references are not easy to detect here. (The sheet of the returned
// cell can be obtained from ae if it is an Area3DEval.)
//
// Another reason there's little value in attempting to detect circular references here is
// that only direct circular references could be detected. If the cycle involved two or more
// cells this method could not detect it.
//
// Logic to detect evaluation cycles of all kinds has been coded in EvaluationCycleDetector
// (and FormulaEvaluator).
// */
// }
if (ae.isColumn()) {
if(ae.isRow()) {
return ae.getRelativeValue(0, 0);
}
if(!ae.containsRow(srcCellRow)) {
throw EvaluationException.invalidValue();
}
return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
}
if(!ae.isRow()) {
// multi-column, multi-row area
if(ae.containsRow(srcCellRow) && ae.containsColumn(srcCellCol)) {
return ae.getAbsoluteValue(srcCellRow, srcCellCol);
}
throw EvaluationException.invalidValue();
}
if(!ae.containsColumn(srcCellCol)) {
throw EvaluationException.invalidValue();
}
return ae.getAbsoluteValue(ae.getFirstRow(), srcCellCol);
}
private static ValueEval chooseSingleElementFromRef(RefEval ref) {
return ref.getInnerValueEval( ref.getFirstSheetIndex() );
}
/**
* Applies some conversion rules if the supplied value is not already an integer.
* Value is first coerced to a {@code double} ( See {@code coerceValueToDouble()} ).
* Note - {@code BlankEval} is converted to {@code 0}.
*
* Excel typically converts doubles to integers by truncating toward negative infinity.
* The equivalent java code is:
* {@code return (int)Math.floor(d);}
* not:
* {@code return (int)d; // wrong - rounds toward zero}
*
*/
public static int coerceValueToInt(ValueEval ev) throws EvaluationException {
if (ev == BlankEval.instance) {
return 0;
}
double d = coerceValueToDouble(ev);
// Note - the standard java type conversion from double to int truncates toward zero.
// but Math.floor() truncates toward negative infinity
return (int)Math.floor(d);
}
/**
* Applies some conversion rules if the supplied value is not already a number.
* Note - {@code BlankEval} is converted to {@link NumberEval#ZERO}.
* @param ev must be a {@link NumberEval}, {@link StringEval}, {@link BoolEval} or
* {@link BlankEval}
* @return actual, parsed or interpreted double value (respectively).
* @throws EvaluationException if a StringEval is supplied and cannot be parsed
* as a double (See {@code parseDouble()} for allowable formats).
* @throws RuntimeException if the supplied parameter is not {@link NumberEval},
* {@link StringEval}, {@link BoolEval} or {@link BlankEval}
*/
public static double coerceValueToDouble(ValueEval ev) throws EvaluationException {
if (ev == BlankEval.instance) {
return 0.0;
}
if (ev instanceof NumericValueEval) {
// this also handles booleans
return ((NumericValueEval)ev).getNumberValue();
}
if (ev instanceof StringEval) {
String sval = ((StringEval) ev).getStringValue();
Double dd = parseDouble(sval);
if(dd == null) dd = parseDateTime(sval);
if (dd == null) {
throw EvaluationException.invalidValue();
}
return dd;
}
throw new RuntimeException("Unexpected arg eval type (" + ev.getClass().getName() + ")");
}
/**
* Converts a string to a double using standard rules that Excel would use.
* Tolerates leading and trailing spaces,
*
* Doesn't support currency prefixes, commas, percentage signs or arithmetic operations strings.
*
* Some examples:
* " 123 " -> 123.0
* ".123" -> 0.123
* "1E4" -> 1000
* "-123" -> -123.0
* These not supported yet:
* " $ 1,000.00 " -> 1000.0
* "$1.25E4" -> 12500.0
* "5**2" -> 500
* "250%" -> 2.5
*
* @return {@code null} if the specified text cannot be parsed as a number
*/
public static Double parseDouble(String pText) {
if (fpPattern.matcher(pText).matches())
try {
return Double.parseDouble(pText);
} catch (NumberFormatException e) {
return null;
}
else {
return null;
}
}
public static Double parseDateTime(String pText) {
try {
return DateUtil.parseDateTime(pText);
} catch (DateTimeException e) {
return null;
}
}
/**
* @param ve must be a {@code NumberEval}, {@code StringEval}, {@code BoolEval}, or {@code BlankEval}
* @return the converted string value. never {@code null}
*/
public static String coerceValueToString(ValueEval ve) {
if (ve instanceof StringValueEval) {
StringValueEval sve = (StringValueEval) ve;
return sve.getStringValue();
}
if (ve == BlankEval.instance) {
return "";
}
throw new IllegalArgumentException("Unexpected eval class (" + ve.getClass().getName() + ")");
}
/**
* @return {@code null} to represent blank values
* @throws EvaluationException if ve is an ErrorEval, or if a string value cannot be converted
*/
public static Boolean coerceValueToBoolean(ValueEval ve, boolean stringsAreBlanks) throws EvaluationException {
if (ve == null || ve == BlankEval.instance) {
// TODO - remove 've == null' condition once AreaEval is fixed
return null;
}
if (ve instanceof BoolEval) {
return ((BoolEval) ve).getBooleanValue();
}
if (ve instanceof StringEval) {
if (stringsAreBlanks) {
return null;
}
String str = ((StringEval) ve).getStringValue();
if (str.equalsIgnoreCase("true")) {
return Boolean.TRUE;
}
if (str.equalsIgnoreCase("false")) {
return Boolean.FALSE;
}
// else - string cannot be converted to boolean
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
if (ve instanceof NumericValueEval) {
NumericValueEval ne = (NumericValueEval) ve;
double d = ne.getNumberValue();
if (Double.isNaN(d)) {
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return d != 0;
}
if (ve instanceof ErrorEval) {
throw new EvaluationException((ErrorEval) ve);
}
throw new RuntimeException("Unexpected eval (" + ve.getClass().getName() + ")");
}
}