Attachment #34246 for bug #60131

View | Details | Raw Unified | Return to bug 60131
Collapse All | Expand All





package org.apache.poi.ss.formula.functions;

import org.apache.poi.ss.formula.eval.AreaEval;
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.NotImplementedException;
import org.apache.poi.ss.formula.eval.NumericValueEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.StringValueEval;
import org.apache.poi.ss.formula.eval.ValueEval;

    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
            ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase) {
        // Input processing and error checks.
        if(!(database instanceof AreaEval) || !(conditionDatabase instanceof AreaEval)) {
            return ErrorEval.VALUE_INVALID;
        }
        AreaEval db = (AreaEval)database;
        AreaEval cdb = (AreaEval)conditionDatabase;
        
        try {
            filterColumn = OperandResolver.getSingleValue(filterColumn, srcRowIndex, srcColumnIndex);
        } catch (EvaluationException e) {
            return e.getErrorEval();
        }

        int fc;
        try {

            }
            // Filter each entry.
            if(matches) {
                ValueEval currentValueEval = resolveReference(db, row, fc);
                // Pass the match to the algorithm and conditionally abort the search.
                boolean shouldContinue = algorithm.processMatch(currentValueEval);
                if(! shouldContinue) {
                    break;
                        break;
                    }
                } catch (EvaluationException e) {
                    return e.getErrorEval();
                }
            }
        }

    }

    /**
     * 
     *
     * @param nameValueEval Must not be a RefEval or AreaEval. Thus make sure resolveReference() is called on the value first!
     * @return a ValueEval which is guaranteed not to be a RefEval
     * @throws EvaluationException If a multi-sheet reference was found along the way.
     */
    private static ValueEval solveReference(ValueEval field) throws EvaluationException {
        if (field instanceof RefEval) {
            RefEval refEval = (RefEval)field;
            if (refEval.getNumberOfSheets() > 1) {
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
            }
            return solveReference(refEval.getInnerValueEval(refEval.getFirstSheetIndex()));
        }
        else {
            return field;
        }
    }

    /**
     * Returns the first column index that matches the given name. The name can either be
     * a string or an integer, when it's an integer, then the respective column
     * (1 based index) is returned.
     * @param nameValueEval
     * @param db
     * @return
     * @throws EvaluationException
     */
    private static int getColumnForName(ValueEval nameValueEval, AreaEval db)
    private static int getColumnForTag(ValueEval nameValueEval, TwoDEval db)
            throws EvaluationException {
        int resultColumn = -1;

        // Numbers as column indicator are allowed, check that.
        if(nameValueEval instanceof NumericValueEval) {
            double doubleResultColumn = ((NumericValueEval)nameValueEval).getNumberValue();
            resultColumn = (int)doubleResultColumn;
            // Floating comparisions are usually not possible, but should work for 0.0.
            if(doubleResultColumn - resultColumn != 0.0)
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
            resultColumn -= 1; // Numbers are 1-based not 0-based.
        } else {
            resultColumn = getColumnForName(nameValueEval, db);
        }
        return resultColumn;
    }

    private static int getColumnForName(ValueEval nameValueEval, TwoDEval db)
            throws EvaluationException {
        String name = OperandResolver.coerceValueToString(nameValueEval);
        return getColumnForString(db, name);
    }


     * @return Corresponding column number.
     * @throws EvaluationException If it's not possible to turn all headings into strings.
     */
    private static int getColumnForString(AreaEval db,String name)
            throws EvaluationException {
        int resultColumn = -1;
        final int width = db.getWidth();
        for(int column = 0; column < width; ++column) {
            ValueEval columnNameValueEval = resolveReference(db, 0, column);
            if(columnNameValueEval instanceof BlankEval) {
                continue;
            }
            if(columnNameValueEval instanceof ErrorEval) {
                continue;
            }
            String columnName = OperandResolver.coerceValueToString(columnNameValueEval);
            if(name.equals(columnName)) {
                resultColumn = column;
                break;

     * @throws EvaluationException If references could not be resolved or comparison
     * operators and operands didn't match.
     */
    private static boolean fullfillsConditions(AreaEval db, int row, AreaEval cdb)
            throws EvaluationException {
        // Only one row must match to accept the input, so rows are ORed.
        // Each row is made up of cells where each cell is a condition,

                // special column that accepts formulas.
                boolean columnCondition = true;
                ValueEval condition = null;
                
                // The condition to apply.
                condition = resolveReference(cdb, conditionRow, column);
                
                    // It might be a special formula, then it is ok if it fails.
                    columnCondition = false;
                }
                // If the condition is empty it matches.
                if(condition instanceof BlankEval)
                    continue;
                // The column in the DB to apply the condition to.
                ValueEval targetHeader = resolveReference(cdb, 0, column);
                targetHeader = solveReference(targetHeader);


                if(!(targetHeader instanceof StringValueEval)) {
                    throw new EvaluationException(ErrorEval.VALUE_INVALID);


                if(columnCondition == true) { // normal column condition
                    // Should not throw, checked above.
                    ValueEval value = resolveReference(db, row, getColumnForName(targetHeader, db));
                            row, getColumnForName(targetHeader, db));
                    if(!testNormalCondition(value, condition)) {
                        matches = false;
                        break;
                    }
                } else { // It's a special formula condition.
                    // TODO: Check whether the condition cell contains a formula and return #VALUE! if it doesn't.
                    if(OperandResolver.coerceValueToString(condition).isEmpty()) {
                        throw new EvaluationException(ErrorEval.VALUE_INVALID);
                    }
                    throw new NotImplementedException(

                if(itsANumber) {
                    return testNumericCondition(value, operator.equal, stringOrNumber);
                } else { // It's a string.
                    String valueString = value instanceof BlankEval ? "" : OperandResolver.coerceValueToString(value);
                    return stringOrNumber.equals(valueString);
                }
            } else { // It's a text starts-with condition.

                    return value instanceof StringEval;
                }
                else {
                    String valueString = value instanceof BlankEval ? "" : OperandResolver.coerceValueToString(value);
                    return valueString.startsWith(conditionString);
                }
            }

            return null;
        }
    }
    
    /**
     * Resolve a ValueEval that's in an AreaEval.
     * It tries to resolve references if there are any.
     *
     * @param db AreaEval from which the cell to resolve is retrieved. 
     * @param dbRow Relative row in the AreaEval.
     * @param dbCol Relative column in the AreaEval.
     * @return A ValueEval that is a NumberEval, StringEval, BoolEval, BlankEval or ErrorEval.
     */
    private static ValueEval resolveReference(AreaEval db, int dbRow, int dbCol) {
        try {
            return OperandResolver.getSingleValue(db.getValue(dbRow, dbCol), db.getFirstRow()+dbRow, db.getFirstColumn()+dbCol);
        } catch (EvaluationException e) {
            return e.getErrorEval();
        }
    }
}

Return to bug 60131

Lines 17-29 Link Here

(-)a/src/java/org/apache/poi/ss/formula/functions/DStarRunner.java (-91 / +51 lines)
17		17
18	package org.apache.poi.ss.formula.functions;	18	package org.apache.poi.ss.formula.functions;
19		19
20	import org.apache.poi.ss.formula.TwoDEval;	20	import org.apache.poi.ss.formula.eval.AreaEval;
21	import org.apache.poi.ss.formula.eval.BlankEval;	21	import org.apache.poi.ss.formula.eval.BlankEval;
22	import org.apache.poi.ss.formula.eval.ErrorEval;	22	import org.apache.poi.ss.formula.eval.ErrorEval;
23	import org.apache.poi.ss.formula.eval.EvaluationException;	23	import org.apache.poi.ss.formula.eval.EvaluationException;
24	import org.apache.poi.ss.formula.eval.NotImplementedException;	24	import org.apache.poi.ss.formula.eval.NotImplementedException;
25	import org.apache.poi.ss.formula.eval.NumericValueEval;	25	import org.apache.poi.ss.formula.eval.NumericValueEval;
26	import org.apache.poi.ss.formula.eval.RefEval;	26	import org.apache.poi.ss.formula.eval.OperandResolver;
27	import org.apache.poi.ss.formula.eval.StringEval;	27	import org.apache.poi.ss.formula.eval.StringEval;
28	import org.apache.poi.ss.formula.eval.StringValueEval;	28	import org.apache.poi.ss.formula.eval.StringValueEval;
29	import org.apache.poi.ss.formula.eval.ValueEval;	29	import org.apache.poi.ss.formula.eval.ValueEval;
Lines 62-72 public final class DStarRunner implements Function3Arg { Link Here
62	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,	62	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
63	ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase) {	63	ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase) {
64	// Input processing and error checks.	64	// Input processing and error checks.
65	if(!(database instanceof TwoDEval) \|\| !(conditionDatabase instanceof TwoDEval)) {	65	if(!(database instanceof AreaEval) \|\| !(conditionDatabase instanceof AreaEval)) {
66	return ErrorEval.VALUE_INVALID;	66	return ErrorEval.VALUE_INVALID;
67	}	67	}
68	TwoDEval db = (TwoDEval)database;	68	AreaEval db = (AreaEval)database;
69	TwoDEval cdb = (TwoDEval)conditionDatabase;	69	AreaEval cdb = (AreaEval)conditionDatabase;
		70
		71	try {
		72	filterColumn = OperandResolver.getSingleValue(filterColumn, srcRowIndex, srcColumnIndex);
		73	} catch (EvaluationException e) {
		74	return e.getErrorEval();
		75	}
70		76
71	int fc;	77	int fc;
72	try {	78	try {
Lines 100-114 public final class DStarRunner implements Function3Arg { Link Here
100	}	106	}
101	// Filter each entry.	107	// Filter each entry.
102	if(matches) {	108	if(matches) {
103	try {	109	ValueEval currentValueEval = resolveReference(db, row, fc);
104	ValueEval currentValueEval = solveReference(db.getValue(row, fc));	110	// Pass the match to the algorithm and conditionally abort the search.
105	// Pass the match to the algorithm and conditionally abort the search.	111	boolean shouldContinue = algorithm.processMatch(currentValueEval);
106	boolean shouldContinue = algorithm.processMatch(currentValueEval);	112	if(! shouldContinue) {
107	if(! shouldContinue) {	113	break;
108	break;
109	}
110	} catch (EvaluationException e) {
111	return e.getErrorEval();
112	}	114	}
113	}	115	}
114	}	116	}
Lines 126-181 public final class DStarRunner implements Function3Arg { Link Here
126	}	128	}
127		129
128	/**	130	/**
129	* Resolve reference(-chains) until we have a normal value.	131	*
130	*	132	*
131	* @param field a ValueEval which can be a RefEval.	133	* @param nameValueEval Must not be a RefEval or AreaEval. Thus make sure resolveReference() is called on the value first!
132	* @return a ValueEval which is guaranteed not to be a RefEval
133	* @throws EvaluationException If a multi-sheet reference was found along the way.
134	*/
135	private static ValueEval solveReference(ValueEval field) throws EvaluationException {
136	if (field instanceof RefEval) {
137	RefEval refEval = (RefEval)field;
138	if (refEval.getNumberOfSheets() > 1) {
139	throw new EvaluationException(ErrorEval.VALUE_INVALID);
140	}
141	return solveReference(refEval.getInnerValueEval(refEval.getFirstSheetIndex()));
142	}
143	else {
144	return field;
145	}
146	}
147
148	/**
149	* Returns the first column index that matches the given name. The name can either be
150	* a string or an integer, when it's an integer, then the respective column
151	* (1 based index) is returned.
152	* @param nameValueEval
153	* @param db	134	* @param db
154	* @return the first column index that matches the given name (or int)	135	* @return
155	* @throws EvaluationException	136	* @throws EvaluationException
156	*/	137	*/
157	@SuppressWarnings("unused")	138	private static int getColumnForName(ValueEval nameValueEval, AreaEval db)
158	private static int getColumnForTag(ValueEval nameValueEval, TwoDEval db)
159	throws EvaluationException {
160	int resultColumn = -1;
161
162	// Numbers as column indicator are allowed, check that.
163	if(nameValueEval instanceof NumericValueEval) {
164	double doubleResultColumn = ((NumericValueEval)nameValueEval).getNumberValue();
165	resultColumn = (int)doubleResultColumn;
166	// Floating comparisions are usually not possible, but should work for 0.0.
167	if(doubleResultColumn - resultColumn != 0.0)
168	throw new EvaluationException(ErrorEval.VALUE_INVALID);
169	resultColumn -= 1; // Numbers are 1-based not 0-based.
170	} else {
171	resultColumn = getColumnForName(nameValueEval, db);
172	}
173	return resultColumn;
174	}
175
176	private static int getColumnForName(ValueEval nameValueEval, TwoDEval db)
177	throws EvaluationException {	139	throws EvaluationException {
178	String name = getStringFromValueEval(nameValueEval);	140	String name = OperandResolver.coerceValueToString(nameValueEval);
179	return getColumnForString(db, name);	141	return getColumnForString(db, name);
180	}	142	}
181		143
Lines 187-202 public final class DStarRunner implements Function3Arg { Link Here
187	* @return Corresponding column number.	149	* @return Corresponding column number.
188	* @throws EvaluationException If it's not possible to turn all headings into strings.	150	* @throws EvaluationException If it's not possible to turn all headings into strings.
189	*/	151	*/
190	private static int getColumnForString(TwoDEval db,String name)	152	private static int getColumnForString(AreaEval db,String name)
191	throws EvaluationException {	153	throws EvaluationException {
192	int resultColumn = -1;	154	int resultColumn = -1;
193	final int width = db.getWidth();	155	final int width = db.getWidth();
194	for(int column = 0; column < width; ++column) {	156	for(int column = 0; column < width; ++column) {
195	ValueEval columnNameValueEval = db.getValue(0, column);	157	ValueEval columnNameValueEval = resolveReference(db, 0, column);
196	if(solveReference(columnNameValueEval) instanceof BlankEval) {	158	if(columnNameValueEval instanceof BlankEval) {
197	continue;	159	continue;
198	}	160	}
199	String columnName = getStringFromValueEval(columnNameValueEval);	161	if(columnNameValueEval instanceof ErrorEval) {
		162	continue;
		163	}
		164	String columnName = OperandResolver.coerceValueToString(columnNameValueEval);
200	if(name.equals(columnName)) {	165	if(name.equals(columnName)) {
201	resultColumn = column;	166	resultColumn = column;
202	break;	167	break;
Lines 215-221 public final class DStarRunner implements Function3Arg { Link Here
215	* @throws EvaluationException If references could not be resolved or comparison	180	* @throws EvaluationException If references could not be resolved or comparison
216	* operators and operands didn't match.	181	* operators and operands didn't match.
217	*/	182	*/
218	private static boolean fullfillsConditions(TwoDEval db, int row, TwoDEval cdb)	183	private static boolean fullfillsConditions(AreaEval db, int row, AreaEval cdb)
219	throws EvaluationException {	184	throws EvaluationException {
220	// Only one row must match to accept the input, so rows are ORed.	185	// Only one row must match to accept the input, so rows are ORed.
221	// Each row is made up of cells where each cell is a condition,	186	// Each row is made up of cells where each cell is a condition,
Lines 229-248 public final class DStarRunner implements Function3Arg { Link Here
229	// special column that accepts formulas.	194	// special column that accepts formulas.
230	boolean columnCondition = true;	195	boolean columnCondition = true;
231	ValueEval condition = null;	196	ValueEval condition = null;
232	try {	197
233	// The condition to apply.	198	// The condition to apply.
234	condition = solveReference(cdb.getValue(conditionRow, column));	199	condition = resolveReference(cdb, conditionRow, column);
235	} catch (java.lang.RuntimeException e) {	200
236	// It might be a special formula, then it is ok if it fails.
237	columnCondition = false;
238	}
239	// If the condition is empty it matches.	201	// If the condition is empty it matches.
240	if(condition instanceof BlankEval)	202	if(condition instanceof BlankEval)
241	continue;	203	continue;
242	// The column in the DB to apply the condition to.	204	// The column in the DB to apply the condition to.
243	ValueEval targetHeader = solveReference(cdb.getValue(0, column));	205	ValueEval targetHeader = resolveReference(cdb, 0, column);
244	targetHeader = solveReference(targetHeader);
245
246		206
247	if(!(targetHeader instanceof StringValueEval)) {	207	if(!(targetHeader instanceof StringValueEval)) {
248	throw new EvaluationException(ErrorEval.VALUE_INVALID);	208	throw new EvaluationException(ErrorEval.VALUE_INVALID);
Lines 254-267 public final class DStarRunner implements Function3Arg { Link Here
254		214
255	if(columnCondition == true) { // normal column condition	215	if(columnCondition == true) { // normal column condition
256	// Should not throw, checked above.	216	// Should not throw, checked above.
257	ValueEval value = db.getValue(	217	ValueEval value = resolveReference(db, row, getColumnForName(targetHeader, db));
258	row, getColumnForName(targetHeader, db));
259	if(!testNormalCondition(value, condition)) {	218	if(!testNormalCondition(value, condition)) {
260	matches = false;	219	matches = false;
261	break;	220	break;
262	}	221	}
263	} else { // It's a special formula condition.	222	} else { // It's a special formula condition.
264	if(getStringFromValueEval(condition).isEmpty()) {	223	// TODO: Check whether the condition cell contains a formula and return #VALUE! if it doesn't.
		224	if(OperandResolver.coerceValueToString(condition).isEmpty()) {
265	throw new EvaluationException(ErrorEval.VALUE_INVALID);	225	throw new EvaluationException(ErrorEval.VALUE_INVALID);
266	}	226	}
267	throw new NotImplementedException(	227	throw new NotImplementedException(
Lines 328-334 public final class DStarRunner implements Function3Arg { Link Here
328	if(itsANumber) {	288	if(itsANumber) {
329	return testNumericCondition(value, operator.equal, stringOrNumber);	289	return testNumericCondition(value, operator.equal, stringOrNumber);
330	} else { // It's a string.	290	} else { // It's a string.
331	String valueString = value instanceof BlankEval ? "" : getStringFromValueEval(value);	291	String valueString = value instanceof BlankEval ? "" : OperandResolver.coerceValueToString(value);
332	return stringOrNumber.equals(valueString);	292	return stringOrNumber.equals(valueString);
333	}	293	}
334	} else { // It's a text starts-with condition.	294	} else { // It's a text starts-with condition.
Lines 336-342 public final class DStarRunner implements Function3Arg { Link Here
336	return value instanceof StringEval;	296	return value instanceof StringEval;
337	}	297	}
338	else {	298	else {
339	String valueString = value instanceof BlankEval ? "" : getStringFromValueEval(value);	299	String valueString = value instanceof BlankEval ? "" : OperandResolver.coerceValueToString(value);
340	return valueString.startsWith(conditionString);	300	return valueString.startsWith(conditionString);
341	}	301	}
342	}	302	}
Lines 424-443 public final class DStarRunner implements Function3Arg { Link Here
424	return null;	384	return null;
425	}	385	}
426	}	386	}
427		387
428	/**	388	/**
429	* Takes a ValueEval and tries to retrieve a String value from it.	389	* Resolve a ValueEval that's in an AreaEval.
430	* It tries to resolve references if there are any.
431	*	390	*
432	* @param value ValueEval to retrieve the string from.	391	* @param db AreaEval from which the cell to resolve is retrieved.
433	* @return String corresponding to the given ValueEval.	392	* @param dbRow Relative row in the AreaEval.
434	* @throws EvaluationException If it's not possible to retrieve a String value.	393	* @param dbCol Relative column in the AreaEval.
		394	* @return A ValueEval that is a NumberEval, StringEval, BoolEval, BlankEval or ErrorEval.
435	*/	395	*/
436	private static String getStringFromValueEval(ValueEval value)	396	private static ValueEval resolveReference(AreaEval db, int dbRow, int dbCol) {
437	throws EvaluationException {	397	try {
438	value = solveReference(value);	398	return OperandResolver.getSingleValue(db.getValue(dbRow, dbCol), db.getFirstRow()+dbRow, db.getFirstColumn()+dbCol);
439	if(!(value instanceof StringValueEval))	399	} catch (EvaluationException e) {
440	throw new EvaluationException(ErrorEval.VALUE_INVALID);	400	return e.getErrorEval();
441	return ((StringValueEval)value).getStringValue();	401	}
442	}	402	}
443	}	403	}