/*
* 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.
*/
namespace HH.WMS.Utils.NPOI.SS.Formula.Functions
{
using System;
using HH.WMS.Utils.NPOI.SS.Formula.Eval;
using HH.WMS.Utils.NPOI.SS.Formula.Functions;
using HH.WMS.Utils.NPOI.SS.Formula;
public class SingleValueVector : ValueVector
{
private ValueEval _value;
public SingleValueVector(ValueEval value)
{
_value = value;
}
public ValueEval GetItem(int index)
{
if (index != 0)
{
throw new ArgumentException("Invalid index ("
+ index + ") only zero is allowed");
}
return _value;
}
public int Size
{
get
{
return 1;
}
}
}
/**
* Implementation for the MATCH() Excel function.
*
* Syntax:
* MATCH(lookup_value, lookup_array, match_type)
*
* Returns a 1-based index specifying at what position in the lookup_array the specified
* lookup_value Is found.
*
* Specific matching behaviour can be modified with the optional match_type parameter.
*
*
* Value | Matching Behaviour |
* 1 | (default) Find the largest value that Is less than or equal to lookup_value.
* The lookup_array must be in ascending order*. |
* 0 | Find the first value that Is exactly equal to lookup_value.
* The lookup_array can be in any order. |
* -1 | Find the smallest value that Is greater than or equal to lookup_value.
* The lookup_array must be in descending order*. |
*
*
* * Note regarding order - For the match_type cases that require the lookup_array to
* be ordered, MATCH() can produce incorrect results if this requirement Is not met. Observed
* behaviour in Excel Is to return the lowest index value for which every item after that index
* breaks the match rule.
* The (ascending) sort order expected by MATCH() Is:
* numbers (low to high), strings (A to Z), bool (FALSE to TRUE)
* MATCH() ignores all elements in the lookup_array with a different type to the lookup_value.
* Type conversion of the lookup_array elements Is never performed.
*
*
* @author Josh Micich
*/
public class Match : Function
{
public ValueEval Evaluate(ValueEval[] args, int srcCellRow, int srcCellCol)
{
double match_type = 1; // default
switch (args.Length)
{
case 3:
try
{
match_type = EvaluateMatchTypeArg(args[2], srcCellRow, srcCellCol);
}
catch (EvaluationException)
{
// 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!
return ErrorEval.REF_INVALID;
}
break;
case 2:
break;
default:
return ErrorEval.VALUE_INVALID;
}
bool matchExact = match_type == 0;
// Note - Excel does not strictly require -1 and +1
bool FindLargestLessThanOrEqual = match_type > 0;
try
{
ValueEval lookupValue = OperandResolver.GetSingleValue(args[0], srcCellRow, srcCellCol);
ValueVector 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 ValueVector EvaluateLookupRange(ValueEval eval)
{
if (eval is RefEval)
{
RefEval re = (RefEval)eval;
return new SingleValueVector(re.InnerValueEval);
}
if (eval is TwoDEval)
{
ValueVector result = LookupUtils.CreateVector((TwoDEval)eval);
if (result == null)
{
throw new EvaluationException(ErrorEval.NA);
}
return result;
}
// Error handling for lookup_range arg Is also Unusual
if (eval is NumericValueEval)
{
throw new EvaluationException(ErrorEval.NA);
}
if (eval is StringEval)
{
StringEval se = (StringEval)eval;
double d = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(d))
{
// plain string
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
// else looks like a number
throw new EvaluationException(ErrorEval.NA);
}
throw new Exception("Unexpected eval type (" + eval.GetType().Name + ")");
}
private static double EvaluateMatchTypeArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval match_type = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
if (match_type is ErrorEval)
{
throw new EvaluationException((ErrorEval)match_type);
}
if (match_type is NumericValueEval)
{
NumericValueEval ne = (NumericValueEval)match_type;
return ne.NumberValue;
}
if (match_type is StringEval)
{
StringEval se = (StringEval)match_type;
double d = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(d))
{
// plain string
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
// if the string Parses as a number, it Is OK
return d;
}
throw new Exception("Unexpected match_type type (" + match_type.GetType().Name + ")");
}
/**
* @return zero based index
*/
private static int FindIndexOfValue(ValueEval lookupValue, ValueVector lookupRange,
bool matchExact, bool FindLargestLessThanOrEqual)
{
LookupValueComparer lookupComparer = CreateLookupComparer(lookupValue, matchExact);
if (matchExact)
{
for (int i = 0; i < lookupRange.Size; i++)
{
if (lookupComparer.CompareTo(lookupRange.GetItem(i)).IsEqual)
{
return i;
}
}
throw new EvaluationException(ErrorEval.NA);
}
if (FindLargestLessThanOrEqual)
{
// Note - backward iteration
for (int i = lookupRange.Size - 1; i >= 0; i--)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(i));
if (cmp.IsTypeMismatch)
{
continue;
}
if (!cmp.IsLessThan)
{
return i;
}
}
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.Size; i++)
{
CompareResult cmp = lookupComparer.CompareTo(lookupRange.GetItem(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, bool matchExact)
{
if (matchExact && lookupValue is StringEval)
{
String stringValue = ((StringEval)lookupValue).StringValue;
if (IsLookupValueWild(stringValue))
{
throw new Exception("Wildcard lookup values '" + stringValue + "' not supported yet");
}
}
return LookupUtils.CreateLookupComparer(lookupValue);
}
private static bool IsLookupValueWild(String stringValue)
{
if (stringValue.IndexOf('?') >= 0 || stringValue.IndexOf('*') >= 0)
{
return true;
}
return false;
}
}
}