Attachment #21556 for bug #44450

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

Lines 21-26 Link Here

(-)src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java (-1 / +7 lines)
21		21
22	import org.apache.poi.hssf.record.*;	22	import org.apache.poi.hssf.record.*;
23		23
		24	import java.io.EOFException;
24	import java.util.Iterator;	25	import java.util.Iterator;
25	import java.util.List;	26	import java.util.List;
26		27
Lines 34-40 Link Here
34	* @author Jason Height (jheight at chariot dot net dot au)	35	* @author Jason Height (jheight at chariot dot net dot au)
35	*/	36	*/
36		37
37	public class ValueRecordsAggregate	38	public final class ValueRecordsAggregate
38	extends Record	39	extends Record
39	{	40	{
40	public final static short sid = -1000;	41	public final static short sid = -1000;
Lines 135-140 Link Here
135	if (rec instanceof SharedFormulaRecord) {	136	if (rec instanceof SharedFormulaRecord) {
136	sharedFormulas.add(rec);	137	sharedFormulas.add(rec);
137	}	138	}
		139	// jm hack. better version of this patch in 44449
		140	if (rec instanceof EOFRecord) {
		141	break;
		142	}
		143	// end hack
138	}	144	}
139		145
140	// Now do the main processing sweep	146	// Now do the main processing sweep

Lines 28-34 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java (-6 / +10 lines)
28	/**	28	/**
29	* Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)	29	* Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)
30	*/	30	*/
31	private static final class ErrorCode {	31	public static final class ErrorCode {
32	/** <b>#NULL!</b> - Intersection of two cell ranges is empty */	32	/** <b>#NULL!</b> - Intersection of two cell ranges is empty */
33	public static final int NULL = 0x00;	33	public static final int NULL = 0x00;
34	/** <b>#DIV/0!</b> - Division by zero */	34	/** <b>#DIV/0!</b> - Division by zero */
Lines 44-49 Link Here
44	/** <b>#N/A</b> - Argument or function not available */	44	/** <b>#N/A</b> - Argument or function not available */
45	public static final int N_A = 0x2A;	45	public static final int N_A = 0x2A;
46		46
		47	/** <b>~CYCLIC~REFERENCE~</b> - Cyclic reference detected (not a real Excel error code) */
		48	public static final int CYCLIC_REF = 0xFFFFFFC4;
		49
47	public static final String getText(int errorCode) {	50	public static final String getText(int errorCode) {
48	switch(errorCode) {	51	switch(errorCode) {
49	case NULL: return "#NULL!";	52	case NULL: return "#NULL!";
Lines 53-60 Link Here
53	case NAME: return "#NAME?";	56	case NAME: return "#NAME?";
54	case NUM: return "#NUM!";	57	case NUM: return "#NUM!";
55	case N_A: return "#N/A";	58	case N_A: return "#N/A";
		59
		60	case CYCLIC_REF: return "~CYCLIC~REFERENCE~";
56	}	61	}
57	return "???";	62	return "err(" + errorCode + ")???" ;
58	}	63	}
59	}	64	}
60		65
Lines 83-99 Link Here
83	case ErrorCode.NULL: return NULL_INTERSECTION;	88	case ErrorCode.NULL: return NULL_INTERSECTION;
84	case ErrorCode.DIV_0: return DIV_ZERO;	89	case ErrorCode.DIV_0: return DIV_ZERO;
85	case ErrorCode.VALUE: return VALUE_INVALID;	90	case ErrorCode.VALUE: return VALUE_INVALID;
86	// case ErrorCode.REF: return REF_INVALID;	91	case ErrorCode.REF: return REF_INVALID;
87	case ErrorCode.REF: return UNKNOWN_ERROR;
88	case ErrorCode.NAME: return NAME_INVALID;	92	case ErrorCode.NAME: return NAME_INVALID;
89	case ErrorCode.NUM: return NUM_ERROR;	93	case ErrorCode.NUM: return NUM_ERROR;
90	case ErrorCode.N_A: return NA;	94	case ErrorCode.N_A: return NA;
		95	case ErrorCode.CYCLIC_REF: return CIRCULAR_REF_ERROR;
91		96
92	// these cases probably shouldn't be coming through here	97	// these cases probably shouldn't be coming through here
93	// but (as of Jan-2008) a lot of code depends on it.	98	// but (as of Jan-2008) a lot of code depends on it.
94	// case -20: return UNKNOWN_ERROR;	99	// case -20: return UNKNOWN_ERROR;
95	// case -30: return FUNCTION_NOT_IMPLEMENTED;	100	// case -30: return FUNCTION_NOT_IMPLEMENTED;
96	// case -60: return CIRCULAR_REF_ERROR;
97	}	101	}
98	throw new RuntimeException("Unexpected error code (" + errorCode + ")");	102	throw new RuntimeException("Unexpected error code (" + errorCode + ")");
99	}	103	}
Lines 102-108 Link Here
102	public static final ErrorEval UNKNOWN_ERROR = new ErrorEval(-20);	106	public static final ErrorEval UNKNOWN_ERROR = new ErrorEval(-20);
103	public static final ErrorEval FUNCTION_NOT_IMPLEMENTED = new ErrorEval(-30);	107	public static final ErrorEval FUNCTION_NOT_IMPLEMENTED = new ErrorEval(-30);
104	// Note - Excel does not seem to represent this condition with an error code	108	// Note - Excel does not seem to represent this condition with an error code
105	public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(-60);	109	public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(ErrorCode.CYCLIC_REF);
106		110
107		111
108	private int errorCode;	112	private int errorCode;




* See the License for the specific language governing permissions and
* limitations under the License.
*/

 * Created on May 8, 2005
 *
 */
package org.apache.poi.hssf.record.formula.eval;

/**

 */
public interface StringValueEval extends ValueEval {

	/**
	 * @return never <code>null</code>, possibly empty string.
	 */
    String getStringValue();
}




* See the License for the specific language governing permissions and
* limitations under the License.
*/

 * Created on May 8, 2005
 *
 */
package org.apache.poi.hssf.record.formula.eval;

import org.apache.poi.hssf.record.formula.Ptg;

 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
 *  
 */
public final class StringEval implements StringValueEval {

    public static final StringEval EMPTY_INSTANCE = new StringEval("");
    
    private final String value;

    public StringEval(Ptg ptg) {
        this(((StringPtg) ptg).getValue());
    }

    public StringEval(String value) {
    	if(value == null) {
    		throw new IllegalArgumentException("value must not be null");
    	}
        this.value = value;
    }

    public String getStringValue() {
        return value;
    }
    public String toString() {
        StringBuffer sb = new StringBuffer(64);
        sb.append(getClass().getName()).append(" [");
        sb.append(value);
        sb.append("]");
        return sb.toString();
    }
}




/* ====================================================================
   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.
==================================================================== */

 * Created on May 15, 2005
 *
 */
package org.apache.poi.hssf.record.formula.functions;

import org.apache.poi.hssf.record.formula.eval.AreaEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
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.LookupUtils.ValueVector;

/**
 * Implementation of Excel function LOOKUP.<p/>
 * 
 * LOOKUP finds an index  row in a lookup table by the first column value and returns the value from another column.
 * 
 * <b>Syntax</b>:<br/>
 * <b>VLOOKUP</b>(<b>lookup_value</b>, <b>lookup_vector</b>, result_vector)<p/>
 * 
 * <b>lookup_value</b>  The value to be found in the lookup vector.<br/>
 * <b>lookup_vector</> An area reference for the lookup data. <br/>
 * <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/>
 * 
 * @author Josh Micich
 */
public final class Lookup implements Function {
	private static final class SimpleValueVector implements ValueVector {
		private final ValueEval[] _values;

		public SimpleValueVector(ValueEval[] values) {
			_values = values;
		}
		public ValueEval getItem(int index) {
			return _values[index];
		}
		public int getSize() {
			return _values.length;
		}
	}

	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
		switch(args.length) {
			case 3:
				break;
			case 2:
				// complex rules to choose lookupVector and resultVector from the single area ref
				throw new RuntimeException("Two arg version of LOOKUP not supported yet");
			default:
				return ErrorEval.VALUE_INVALID;
		}
		
		
		try {
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
			AreaEval aeLookupVector = LookupUtils.resolveTableArrayArg(args[1]);
			AreaEval aeResultVector = LookupUtils.resolveTableArrayArg(args[2]);
			
			ValueVector lookupVector = createVector(aeLookupVector);
			ValueVector resultVector = createVector(aeResultVector);
			if(lookupVector.getSize() > resultVector.getSize()) {
				// Excel seems to handle this by accessing past the end of the result vector.
				throw new RuntimeException("Lookup vector and result vector of differing sizes not supported yet");
			}
			int index = LookupUtils.lookupIndexOfValue(lookupValue, lookupVector, true);
			
			return resultVector.getItem(index);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}

	private static ValueVector createVector(AreaEval ae) {
		
		if(!ae.isRow() && !ae.isColumn()) {
			// extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
			throw new RuntimeException("non-vector lookup or result areas not supported yet");
		}
		return new SimpleValueVector(ae.getValues());
	}
}




/* ====================================================================
   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.
==================================================================== */

 * Created on May 15, 2005
 *
 */
package org.apache.poi.hssf.record.formula.functions;

import org.apache.poi.hssf.record.formula.eval.AreaEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
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.LookupUtils.ValueVector;
/**
 * Implementation of the VLOOKUP() function.<p/>
 * 
 * VLOOKUP finds a row in a lookup table by the first column value and returns the value from another column.
 * 
 * <b>Syntax</b>:<br/>
 * <b>VLOOKUP</b>(<b>lookup_value</b>, <b>table_array</b>, <b>col_index_num</b>, range_lookup)<p/>
 * 
 * <b>lookup_value</b>  The value to be found in the first column of the table array.<br/>
 * <b>table_array</> An area reference for the lookup data. <br/>
 * <b>col_index_num</b> a 1 based index specifying which column value of the lookup data will be returned.<br/>
 * <b>range_lookup</b> If TRUE (default), VLOOKUP finds the largest value less than or equal to 
 * the lookup_value.  If FALSE, only exact matches will be considered<br/>   
 * 
 * @author Josh Micich
 */
public final class Vlookup implements Function {
	
	private static final class ColumnVector implements ValueVector {

		private final AreaEval _tableArray;
		private final int _size;
		private final int _columnAbsoluteIndex;
		private final int _firstRowAbsoluteIndex;

		public ColumnVector(AreaEval tableArray, int columnIndex) {
			_columnAbsoluteIndex = tableArray.getFirstColumn() + columnIndex;
			if(!tableArray.containsColumn((short)_columnAbsoluteIndex)) {
				int lastColIx =  tableArray.getLastColumn() -  tableArray.getFirstColumn();
				throw new IllegalArgumentException("Specified column index (" + columnIndex 
						+ ") is outside the allowed range (0.." + lastColIx + ")");
			}
			_tableArray = tableArray;
			_size = tableArray.getLastRow() - tableArray.getFirstRow() + 1;
			if(_size < 1) {
				throw new RuntimeException("bad table array size zero");
			}
			_firstRowAbsoluteIndex = tableArray.getFirstRow();
		}

		public ValueEval getItem(int index) {
			if(index>_size) {
				throw new ArrayIndexOutOfBoundsException("Specified index (" + index 
						+ ") is outside the allowed range (0.." + (_size-1) + ")");
			}
			return _tableArray.getValueAt(_firstRowAbsoluteIndex + index, (short)_columnAbsoluteIndex);
		}
		public int getSize() {
			return _size;
		}
	}

	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
		Eval arg3 = null;
		switch(args.length) {
			case 4:
				arg3 = args[3]; // important: assumed array element is never null
			case 3:
				break;
			default:
				// wrong number of arguments
				return ErrorEval.VALUE_INVALID;
		}
		try {
			// Evaluation order:
			// arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 col_index, fetch result
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
			AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
			boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
			int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, new ColumnVector(tableArray, 0), isRangeLookup);
			ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
			int colIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
			ValueVector resultCol = createResultColumnVector(tableArray, colIndex);
			return resultCol.getItem(rowIndex);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}


	/**
	 * Returns one column from an <tt>AreaEval</tt>
	 * 
	 * @throws EvaluationException (#VALUE!) if colIndex is negative, (#REF!) if colIndex is too high
	 */
	private ValueVector createResultColumnVector(AreaEval tableArray, int colIndex) throws EvaluationException {
		if(colIndex < 0) {
			throw EvaluationException.invalidValue();
		}
		int nCols = tableArray.getLastColumn() - tableArray.getFirstColumn() + 1;
		
		if(colIndex >= nCols) {
			throw EvaluationException.invalidRef();
		}
		return new ColumnVector(tableArray, colIndex);
	}
}




/* ====================================================================
   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.
==================================================================== */

 * Created on May 15, 2005
 *
 */
package org.apache.poi.hssf.record.formula.functions;

import org.apache.poi.hssf.record.formula.eval.AreaEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
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.LookupUtils.ValueVector;
/**
 * Implementation of the VLOOKUP() function.<p/>
 * 
 * HLOOKUP finds a column in a lookup table by the first row value and returns the value from another row.
 * 
 * <b>Syntax</b>:<br/>
 * <b>HLOOKUP</b>(<b>lookup_value</b>, <b>table_array</b>, <b>row_index_num</b>, range_lookup)<p/>
 * 
 * <b>lookup_value</b>  The value to be found in the first column of the table array.<br/>
 * <b>table_array</> An area reference for the lookup data. <br/>
 * <b>row_index_num</b> a 1 based index specifying which row value of the lookup data will be returned.<br/>
 * <b>range_lookup</b> If TRUE (default), HLOOKUP finds the largest value less than or equal to 
 * the lookup_value.  If FALSE, only exact matches will be considered<br/>   
 * 
 * @author Josh Micich
 */
public final class Hlookup implements Function {
	
	private static final class RowVector implements ValueVector {

		private final AreaEval _tableArray;
		private final int _size;
		private final int _rowAbsoluteIndex;
		private final int _firstColumnAbsoluteIndex;

		public RowVector(AreaEval tableArray, int rowIndex) {
			_rowAbsoluteIndex = tableArray.getFirstRow() + rowIndex;
			if(!tableArray.containsRow(_rowAbsoluteIndex)) {
				int lastRowIx =  tableArray.getLastRow() -  tableArray.getFirstRow();
				throw new IllegalArgumentException("Specified row index (" + rowIndex 
						+ ") is outside the allowed range (0.." + lastRowIx + ")");
			}
			_tableArray = tableArray;
			_size = tableArray.getLastColumn() - tableArray.getFirstColumn() + 1;
			if(_size < 1) {
				throw new RuntimeException("bad table array size zero");
			}
			_firstColumnAbsoluteIndex = tableArray.getFirstColumn();
		}

		public ValueEval getItem(int index) {
			if(index>_size) {
				throw new ArrayIndexOutOfBoundsException("Specified index (" + index 
						+ ") is outside the allowed range (0.." + (_size-1) + ")");
			}
			return _tableArray.getValueAt(_rowAbsoluteIndex, (short) (_firstColumnAbsoluteIndex + index));
		}
		public int getSize() {
			return _size;
		}
	}

	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
		Eval arg3 = null;
		switch(args.length) {
			case 4:
				arg3 = args[3]; // important: assumed array element is never null
			case 3:
				break;
			default:
				// wrong number of arguments
				return ErrorEval.VALUE_INVALID;
		}
		try {
			// Evaluation order:
			// arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 row_index, fetch result
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
			AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
			boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
			int colIndex = LookupUtils.lookupIndexOfValue(lookupValue, new RowVector(tableArray, 0), isRangeLookup);
			ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
			int rowIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
			ValueVector resultCol = createResultColumnVector(tableArray, rowIndex);
			return resultCol.getItem(colIndex);
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}


	/**
	 * Returns one column from an <tt>AreaEval</tt>
	 * 
	 * @throws EvaluationException (#VALUE!) if colIndex is negative, (#REF!) if colIndex is too high
	 */
	private ValueVector createResultColumnVector(AreaEval tableArray, int colIndex) throws EvaluationException {
		if(colIndex < 0) {
			throw EvaluationException.invalidValue();
		}
		int nCols = tableArray.getLastColumn() - tableArray.getFirstRow() + 1;
		
		if(colIndex >= nCols) {
			throw EvaluationException.invalidRef();
		}
		return new RowVector(tableArray, colIndex);
	}
}




package org.apache.poi.hssf.record.formula.functions;

import org.apache.poi.hssf.record.formula.eval.AreaEval;
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.Eval;
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
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.record.formula.functions.LookupUtils.CompareResult;
import org.apache.poi.hssf.record.formula.functions.LookupUtils.LookupValueComparer;

/**
 * Implementation for the MATCH() Excel function.<p/>

 */
public final class Match implements Function {
	
	private static final class EvalEx extends Exception {
		private final ErrorEval _error;

		public EvalEx(ErrorEval error) {
			_error = error;
		}
		public ErrorEval getError() {
			return _error;
		}
	}
	

	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
		
		double match_type = 1; // default

			case 3:
				try {
					match_type = evaluateMatchTypeArg(args[2], srcCellRow, srcCellCol);
				} catch (EvaluationException e) {
					// Excel/MATCH() seems to have slightly abnormal handling of errors with
					// the last parameter.  Errors do not propagate up.  Every error gets
					// translated into #REF!

		
		
		try {
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
			ValueEval[] lookupRange = evaluateLookupRange(args[1]);
			int index = findIndexOfValue(lookupValue, lookupRange, matchExact, findLargestLessThanOrEqual);
			return new NumberEval(index + 1); // +1 to convert to 1-based
		} catch (EvaluationException e) {
			return e.getErrorEval();
		}
	}

	private static ValueEval[] evaluateLookupRange(Eval eval) throws EvaluationException {
			int srcCellRow, short srcCellCol) throws EvalEx {
		if (ae.isColumn()) {
			if(ae.isRow()) {
				return ae.getValues()[0];
			}
			if(!ae.containsRow(srcCellRow)) {
				throw new EvalEx(ErrorEval.VALUE_INVALID);
			}
			return ae.getValueAt(srcCellRow, ae.getFirstColumn());
		}
		if(!ae.isRow()) {
			throw new EvalEx(ErrorEval.VALUE_INVALID);
		}
		if(!ae.containsColumn(srcCellCol)) {
			throw new EvalEx(ErrorEval.VALUE_INVALID);
		}
		return ae.getValueAt(ae.getFirstRow(), srcCellCol);
		
	}

	private static ValueEval evaluateLookupValue(Eval eval, int srcCellRow, short srcCellCol)
			throws EvalEx {
		if (eval instanceof RefEval) {
			RefEval re = (RefEval) eval;
			return re.getInnerValueEval();
		}
		if (eval instanceof AreaEval) {
			return chooseSingleElementFromArea((AreaEval) eval, srcCellRow, srcCellCol);
		}
		if (eval instanceof ValueEval) {
			return (ValueEval) eval;
		}
		throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
	}


	private static ValueEval[] evaluateLookupRange(Eval eval) throws EvalEx {
		if (eval instanceof RefEval) {
			RefEval re = (RefEval) eval;
			return new ValueEval[] { re.getInnerValueEval(), };
		}
		if (eval instanceof AreaEval) {
			AreaEval ae = (AreaEval) eval;
			if(!ae.isColumn() && !ae.isRow()) {
				throw new EvaluationException(ErrorEval.NA);
			}
			return ae.getValues();
		}
		
		// Error handling for lookup_range arg is also unusual
		if(eval instanceof NumericValueEval) {
			throw new EvaluationException(ErrorEval.NA);
		}
		if (eval instanceof StringEval) {
			StringEval se = (StringEval) eval;
			Double d = OperandResolver.parseDouble(se.getStringValue());
			if(d == null) {
				// plain string
				throw new EvaluationException(ErrorEval.VALUE_INVALID);
			}
			// else looks like a number
			throw new EvaluationException(ErrorEval.NA);
		}
		throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
	}


	private static Double parseDouble(String stringValue) {
		// TODO find better home for parseDouble
		return Countif.parseDouble(stringValue);
	}



	private static double evaluateMatchTypeArg(Eval arg, int srcCellRow, short srcCellCol) 
			throws EvaluationException {
		Eval match_type = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);

			AreaEval ae = (AreaEval) arg;
			// an area ref can work as a scalar value if it is 1x1
			if(ae.isColumn() &&  ae.isRow()) {
				match_type = ae.getValues()[0];
			} else {
				match_type = chooseSingleElementFromArea(ae, srcCellRow, srcCellCol);
			}
		}
		
		if(match_type instanceof RefEval) {
			RefEval re = (RefEval) match_type;
			match_type = re.getInnerValueEval();
		}
		if(match_type instanceof ErrorEval) {
			throw new EvaluationException((ErrorEval)match_type);
		}
		if(match_type instanceof NumericValueEval) {
			NumericValueEval ne = (NumericValueEval) match_type;

		}
		if (match_type instanceof StringEval) {
			StringEval se = (StringEval) match_type;
			Double d = OperandResolver.parseDouble(se.getStringValue());
			if(d == null) {
				// plain string
				throw new EvaluationException(ErrorEval.VALUE_INVALID);
			}
			// if the string parses as a number, it is OK
			return d.doubleValue();
		}
		throw new RuntimeException("Unexpected match_type type (" + match_type.getClass().getName() + ")");

	 * @return zero based index
	 */
	private static int findIndexOfValue(ValueEval lookupValue, ValueEval[] lookupRange,
			boolean matchExact, boolean findLargestLessThanOrEqual) throws EvaluationException {

		LookupValueComparer lookupComparer = createLookupComparer(lookupValue, matchExact);
		
		if(matchExact) {
			for (int i = 0; i < lookupRange.length; i++) {
				if(lookupComparer.compareTo(lookupRange[i]).isEqual()) {
				if(lri.getClass() != lookupValue.getClass()) {
					continue;
				}
				if(compareValues(lookupValue, lri) == 0) {
					return i;
				}
			}
			throw new EvaluationException(ErrorEval.NA);
		}
		
		if(findLargestLessThanOrEqual) {
			// Note - backward iteration
			for (int i = lookupRange.length - 1; i>=0;  i--) {
				CompareResult cmp = lookupComparer.compareTo(lookupRange[i]);
				if(cmp.isTypeMismatch()) {
					continue;
						continue;
					}
					int cmp = compareValues(lookupValue, lri);
					if(cmp == 0) {
						return i;
					}
					if(cmp > 0) {
						return i;
					}
				}
				if(!cmp.isLessThan()) {
					return i;
				for (int i = 0; i<lookupRange.length; i++) {
					ValueEval lri = lookupRange[i];
					if(lri.getClass() != lookupValue.getClass()) {
						continue;
					}
					int cmp = compareValues(lookupValue, lri);
					if(cmp == 0) {
						return i;
					}
					if(cmp > 0) {
						if(i<1) {
							throw new EvalEx(ErrorEval.NA);
						}
						return i-1;
					}
				}
				
			}
			throw new EvaluationException(ErrorEval.NA);
		}
		
		// else - find smallest greater than or equal to
		// TODO - is binary search used for (match_type==+1) ?
		for (int i = 0; i<lookupRange.length; i++) {
			CompareResult cmp = lookupComparer.compareTo(lookupRange[i]);
			if(cmp.isEqual()) {
				return i;
			}
			if(cmp.isGreaterThan()) {
				if(i<1) {
					throw new EvaluationException(ErrorEval.NA);
				}
				return i-1;
			}
		}

		throw new EvaluationException(ErrorEval.NA);
	}

	private static LookupValueComparer createLookupComparer(ValueEval lookupValue, boolean matchExact) throws EvaluationException {
		if (matchExact && lookupValue instanceof StringEval) {
			String stringValue = ((StringEval) lookupValue).getStringValue();
			if(isLookupValueWild(stringValue)) {
				throw new RuntimeException("Wildcard lookup values '" + stringValue + "' not supported yet");
	 */
	private static int compareValues(ValueEval a, ValueEval b) {
		if (a instanceof StringEval) {
			StringEval sa = (StringEval) a;
			StringEval sb = (StringEval) b;
			return sa.getStringValue().compareToIgnoreCase(sb.getStringValue());
		}
		if (a instanceof NumericValueEval) {
			NumericValueEval na = (NumericValueEval) a;
			NumericValueEval nb = (NumericValueEval) b;
			return Double.compare(na.getNumberValue(), nb.getNumberValue());
		}
		if (a instanceof BoolEval) {
			boolean ba = ((BoolEval) a).getBooleanValue();
			boolean bb = ((BoolEval) b).getBooleanValue();
			if(ba == bb) {
				return 0;
			}
			
			if(ba) {
				return +1;
			}
			return -1;
		}
		return LookupUtils.createLookupComparer(lookupValue);
	}

	private static boolean isLookupValueWild(String stringValue) {
		if(stringValue.indexOf('?') >=0 || stringValue.indexOf('*') >=0) {
			return true;
		}
		return false;
	}
}

Return to bug 44450

Lines 14-23 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/StringValueEval.java (-5 / +5 lines)
14	* See the License for the specific language governing permissions and	14	* See the License for the specific language governing permissions and
15	* limitations under the License.	15	* limitations under the License.
16	*/	16	*/
17	/*	17
18	* Created on May 8, 2005
19	*
20	*/
21	package org.apache.poi.hssf.record.formula.eval;	18	package org.apache.poi.hssf.record.formula.eval;
22		19
23	/**	20	/**
Lines 26-30 Link Here
26	*/	23	*/
27	public interface StringValueEval extends ValueEval {	24	public interface StringValueEval extends ValueEval {
28		25
29	public String getStringValue();	26	/**
		27	* @return never <code>null</code>, possibly empty string.
		28	*/
		29	String getStringValue();
30	}	30	}

Lines 1-25 Link Here

(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Lookup.java (-21 / +92 lines)
1	/*	1	/* ====================================================================
2	* Licensed to the Apache Software Foundation (ASF) under one or more	2	Licensed to the Apache Software Foundation (ASF) under one or more
3	* contributor license agreements. See the NOTICE file distributed with	3	contributor license agreements. See the NOTICE file distributed with
4	* this work for additional information regarding copyright ownership.	4	this work for additional information regarding copyright ownership.
5	* The ASF licenses this file to You under the Apache License, Version 2.0	5	The ASF licenses this file to You under the Apache License, Version 2.0
6	* (the "License"); you may not use this file except in compliance with	6	(the "License"); you may not use this file except in compliance with
7	* the License. You may obtain a copy of the License at	7	the License. You may obtain a copy of the License at
8	*	8
9	* http://www.apache.org/licenses/LICENSE-2.0	9	http://www.apache.org/licenses/LICENSE-2.0
10	*	10
11	* Unless required by applicable law or agreed to in writing, software	11	Unless required by applicable law or agreed to in writing, software
12	* distributed under the License is distributed on an "AS IS" BASIS,	12	distributed under the License is distributed on an "AS IS" BASIS,
13	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.	13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14	* See the License for the specific language governing permissions and	14	See the License for the specific language governing permissions and
15	* limitations under the License.	15	limitations under the License.
16	*/	16	==================================================================== */
17	/*	17
18	* Created on May 15, 2005
19	*
20	*/
21	package org.apache.poi.hssf.record.formula.functions;	18	package org.apache.poi.hssf.record.formula.functions;
22		19
23	public class Lookup extends NotImplementedFunction {	20	import org.apache.poi.hssf.record.formula.eval.AreaEval;
		21	import org.apache.poi.hssf.record.formula.eval.ErrorEval;
		22	import org.apache.poi.hssf.record.formula.eval.Eval;
		23	import org.apache.poi.hssf.record.formula.eval.EvaluationException;
		24	import org.apache.poi.hssf.record.formula.eval.OperandResolver;
		25	import org.apache.poi.hssf.record.formula.eval.ValueEval;
		26	import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
24		27
		28	/**
		29	* Implementation of Excel function LOOKUP.<p/>
		30	*
		31	* LOOKUP finds an index row in a lookup table by the first column value and returns the value from another column.
		32	*
		33	* <b>Syntax</b>:<br/>
		34	* <b>VLOOKUP</b>(<b>lookup_value</b>, <b>lookup_vector</b>, result_vector)<p/>
		35	*
		36	* <b>lookup_value</b> The value to be found in the lookup vector.<br/>
		37	* <b>lookup_vector</> An area reference for the lookup data. <br/>
		38	* <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/>
		39	*
		40	* @author Josh Micich
		41	*/
		42	public final class Lookup implements Function {
		43	private static final class SimpleValueVector implements ValueVector {
		44	private final ValueEval[] _values;
		45
		46	public SimpleValueVector(ValueEval[] values) {
		47	_values = values;
		48	}
		49	public ValueEval getItem(int index) {
		50	return _values[index];
		51	}
		52	public int getSize() {
		53	return _values.length;
		54	}
		55	}
		56
		57	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
		58	switch(args.length) {
		59	case 3:
		60	break;
		61	case 2:
		62	// complex rules to choose lookupVector and resultVector from the single area ref
		63	throw new RuntimeException("Two arg version of LOOKUP not supported yet");
		64	default:
		65	return ErrorEval.VALUE_INVALID;
		66	}
		67
		68
		69	try {
		70	ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
		71	AreaEval aeLookupVector = LookupUtils.resolveTableArrayArg(args[1]);
		72	AreaEval aeResultVector = LookupUtils.resolveTableArrayArg(args[2]);
		73
		74	ValueVector lookupVector = createVector(aeLookupVector);
		75	ValueVector resultVector = createVector(aeResultVector);
		76	if(lookupVector.getSize() > resultVector.getSize()) {
		77	// Excel seems to handle this by accessing past the end of the result vector.
		78	throw new RuntimeException("Lookup vector and result vector of differing sizes not supported yet");
		79	}
		80	int index = LookupUtils.lookupIndexOfValue(lookupValue, lookupVector, true);
		81
		82	return resultVector.getItem(index);
		83	} catch (EvaluationException e) {
		84	return e.getErrorEval();
		85	}
		86	}
		87
		88	private static ValueVector createVector(AreaEval ae) {
		89
		90	if(!ae.isRow() && !ae.isColumn()) {
		91	// extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
92	throw new RuntimeException("non-vector lookup or result areas not supported yet");
93	}
94	return new SimpleValueVector(ae.getValues());
95	}
25	}	96	}