lookuputils.java

来自「EXCEL read and write」· Java 代码 · 共 617 行 · 第 1/2 页

/* ====================================================================
   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.hssf.record.formula.functions;

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.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;

/**
 * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH
 *
 * @author Josh Micich
 */
final class LookupUtils {

	/**
	 * Represents a single row or column within an <tt>AreaEval</tt>.
	 */
	public interface ValueVector {
		ValueEval getItem(int index);
		int getSize();
	}


	private static final class RowVector implements ValueVector {

		private final AreaEval _tableArray;
		private final int _size;
		private final int _rowIndex;

		public RowVector(AreaEval tableArray, int rowIndex) {
			_rowIndex = rowIndex;
			int _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.getWidth();
		}

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

	private static final class ColumnVector implements ValueVector {

		private final AreaEval _tableArray;
		private final int _size;
		private final int _columnIndex;

		public ColumnVector(AreaEval tableArray, int columnIndex) {
			_columnIndex = columnIndex;
			int _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.getHeight();
		}

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

	public static ValueVector createRowVector(AreaEval tableArray, int relativeRowIndex) {
		return new RowVector(tableArray, relativeRowIndex);
	}
	public static ValueVector createColumnVector(AreaEval tableArray, int relativeColumnIndex) {
		return new ColumnVector(tableArray, relativeColumnIndex);
	}
	/**
	 * @return <code>null</code> if the supplied area is neither a single row nor a single colum
	 */
	public static ValueVector createVector(AreaEval ae) {
		if (ae.isColumn()) {
			return createColumnVector(ae, 0);
		}
		if (ae.isRow()) {
			return createRowVector(ae, 0);
		}
		return null;
	}

	/**
	 * Enumeration to support <b>4</b> valued comparison results.<p/>
	 * Excel lookup functions have complex behaviour in the case where the lookup array has mixed
	 * types, and/or is unordered.  Contrary to suggestions in some Excel documentation, there
	 * does not appear to be a universal ordering across types.  The binary search algorithm used
	 * changes behaviour when the evaluated 'mid' value has a different type to the lookup value.<p/>
	 *
	 * A simple int might have done the same job, but there is risk in confusion with the well
	 * known <tt>Comparable.compareTo()</tt> and <tt>Comparator.compare()</tt> which both use
	 * a ubiquitous 3 value result encoding.
	 */
	public static final class CompareResult {
		private final boolean _isTypeMismatch;
		private final boolean _isLessThan;
		private final boolean _isEqual;
		private final boolean _isGreaterThan;

		private CompareResult(boolean isTypeMismatch, int simpleCompareResult) {
			if(isTypeMismatch) {
				_isTypeMismatch = true;
				_isLessThan = false;
				_isEqual = false;
				_isGreaterThan = false;
			} else {
				_isTypeMismatch = false;
				_isLessThan = simpleCompareResult < 0;
				_isEqual = simpleCompareResult == 0;
				_isGreaterThan = simpleCompareResult > 0;
			}
		}
		public static final CompareResult TYPE_MISMATCH = new CompareResult(true, 0);
		public static final CompareResult LESS_THAN = new CompareResult(false, -1);
		public static final CompareResult EQUAL = new CompareResult(false, 0);
		public static final CompareResult GREATER_THAN = new CompareResult(false, +1);

		public static final CompareResult valueOf(int simpleCompareResult) {
			if(simpleCompareResult < 0) {
				return LESS_THAN;
			}
			if(simpleCompareResult > 0) {
				return GREATER_THAN;
			}
			return EQUAL;
		}

		public boolean isTypeMismatch() {
			return _isTypeMismatch;
		}
		public boolean isLessThan() {
			return _isLessThan;
		}
		public boolean isEqual() {
			return _isEqual;
		}
		public boolean isGreaterThan() {
			return _isGreaterThan;
		}
		public String toString() {
			StringBuffer sb = new StringBuffer(64);
			sb.append(getClass().getName()).append(" [");
			sb.append(formatAsString());
			sb.append("]");
			return sb.toString();
		}

		private String formatAsString() {
			if(_isTypeMismatch) {
				return "TYPE_MISMATCH";
			}
			if(_isLessThan) {
				return "LESS_THAN";
			}
			if(_isEqual) {
				return "EQUAL";
			}
			if(_isGreaterThan) {
				return "GREATER_THAN";
			}
			// toString must be reliable
			return "??error??";
		}
	}

	public interface LookupValueComparer {
		/**
		 * @return one of 4 instances or <tt>CompareResult</tt>: <tt>LESS_THAN</tt>, <tt>EQUAL</tt>,
		 * <tt>GREATER_THAN</tt> or <tt>TYPE_MISMATCH</tt>
		 */
		CompareResult compareTo(ValueEval other);
	}

	private static abstract class LookupValueComparerBase implements LookupValueComparer {

		private final Class _targetClass;
		protected LookupValueComparerBase(ValueEval targetValue) {
			if(targetValue == null) {
				throw new RuntimeException("targetValue cannot be null");
			}
			_targetClass = targetValue.getClass();
		}
		public final CompareResult compareTo(ValueEval other) {
			if (other == null) {
				throw new RuntimeException("compare to value cannot be null");
			}
			if (_targetClass != other.getClass()) {
				return CompareResult.TYPE_MISMATCH;
			}
			if (_targetClass == StringEval.class) {

			}
			return compareSameType(other);
		}
		public String toString() {
			StringBuffer sb = new StringBuffer(64);
			sb.append(getClass().getName()).append(" [");
			sb.append(getValueAsString());
			sb.append("]");
			return sb.toString();
		}
		protected abstract CompareResult compareSameType(ValueEval other);
		/** used only for debug purposes */
		protected abstract String getValueAsString();
	}

	private static final class StringLookupComparer extends LookupValueComparerBase {
		private String _value;

		protected StringLookupComparer(StringEval se) {
			super(se);
			_value = se.getStringValue();
		}
		protected CompareResult compareSameType(ValueEval other) {
			StringEval se = (StringEval) other;
			return CompareResult.valueOf(_value.compareToIgnoreCase(se.getStringValue()));
		}
		protected String getValueAsString() {
			return _value;
		}
	}
	private static final class NumberLookupComparer extends LookupValueComparerBase {
		private double _value;

		protected NumberLookupComparer(NumberEval ne) {
			super(ne);
			_value = ne.getNumberValue();
		}
		protected CompareResult compareSameType(ValueEval other) {
			NumberEval ne = (NumberEval) other;
			return CompareResult.valueOf(Double.compare(_value, ne.getNumberValue()));
		}
		protected String getValueAsString() {
			return String.valueOf(_value);
		}
	}
	private static final class BooleanLookupComparer extends LookupValueComparerBase {
		private boolean _value;

		protected BooleanLookupComparer(BoolEval be) {
			super(be);
			_value = be.getBooleanValue();
		}
		protected CompareResult compareSameType(ValueEval other) {
			BoolEval be = (BoolEval) other;
			boolean otherVal = be.getBooleanValue();
			if(_value == otherVal) {
				return CompareResult.EQUAL;
			}
			// TRUE > FALSE
			if(_value) {
				return CompareResult.GREATER_THAN;
			}
			return CompareResult.LESS_THAN;
		}
		protected String getValueAsString() {
			return String.valueOf(_value);
		}
	}

	/**
	 * Processes the third argument to VLOOKUP, or HLOOKUP (<b>col_index_num</b>
	 * or <b>row_index_num</b> respectively).<br>
	 * Sample behaviour: