/*
* 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 System.Text;
using System.Text.RegularExpressions;
using HH.WMS.Utils.NPOI.SS.Formula.Eval;
using HH.WMS.Utils.NPOI.SS.UserModel;
using System.Globalization;
/**
* Implementation for the function COUNTIF
*
* Syntax: COUNTIF ( range, criteria )
*
* range | is the range of cells to be Counted based on the criteria |
* criteria | is used to determine which cells to Count |
*
*
*
* @author Josh Micich
*/
public class Countif : Fixed2ArgFunction
{
private class CmpOp
{
public const int NONE = 0;
public const int EQ = 1;
public const int NE = 2;
public const int LE = 3;
public const int LT = 4;
public const int GT = 5;
public const int GE = 6;
public static CmpOp OP_NONE = op("", NONE);
public static CmpOp OP_EQ = op("=", EQ);
public static CmpOp OP_NE = op("<>", NE);
public static CmpOp OP_LE = op("<=", LE);
public static CmpOp OP_LT = op("<", LT);
public static CmpOp OP_GT = op(">", GT);
public static CmpOp OP_GE = op(">=", GE);
private String _representation;
private int _code;
private static CmpOp op(String rep, int code)
{
return new CmpOp(rep, code);
}
private CmpOp(String representation, int code)
{
_representation = representation;
_code = code;
}
/**
* @return number of characters used to represent this operator
*/
public int Length
{
get
{
return _representation.Length;
}
}
public int Code
{
get
{
return _code;
}
}
public static CmpOp GetOperator(String value)
{
int len = value.Length;
if (len < 1)
{
return OP_NONE;
}
char firstChar = value[0];
switch (firstChar)
{
case '=':
return OP_EQ;
case '>':
if (len > 1)
{
switch (value[1])
{
case '=':
return OP_GE;
}
}
return OP_GT;
case '<':
if (len > 1)
{
switch (value[1])
{
case '=':
return OP_LE;
case '>':
return OP_NE;
}
}
return OP_LT;
}
return OP_NONE;
}
public bool Evaluate(bool cmpResult)
{
switch (_code)
{
case NONE:
case EQ:
return cmpResult;
case NE:
return !cmpResult;
}
throw new Exception("Cannot call bool Evaluate on non-equality operator '"
+ _representation + "'");
}
public bool Evaluate(int cmpResult)
{
switch (_code)
{
case NONE:
case EQ:
return cmpResult == 0;
case NE: return cmpResult != 0;
case LT: return cmpResult < 0;
case LE: return cmpResult <= 0;
case GT: return cmpResult > 0;
case GE: return cmpResult <= 0;
}
throw new Exception("Cannot call bool Evaluate on non-equality operator '"
+ _representation + "'");
}
public override String ToString()
{
StringBuilder sb = new StringBuilder(64);
sb.Append(this.GetType().Name);
sb.Append(" [").Append(_representation).Append("]");
return sb.ToString();
}
public String Representation
{
get
{
return _representation;
}
}
}
private abstract class MatcherBase : I_MatchPredicate
{
private CmpOp _operator;
protected MatcherBase(CmpOp operator1)
{
_operator = operator1;
}
protected int Code
{
get
{
return _operator.Code;
}
}
protected bool Evaluate(int cmpResult)
{
return _operator.Evaluate(cmpResult);
}
protected bool Evaluate(bool cmpResult)
{
return _operator.Evaluate(cmpResult);
}
public override String ToString()
{
StringBuilder sb = new StringBuilder(64);
sb.Append(this.GetType().Name).Append(" [");
sb.Append(_operator.Representation);
sb.Append(ValueText);
sb.Append("]");
return sb.ToString();
}
protected abstract String ValueText { get; }
public abstract bool Matches(ValueEval x);
}
private class ErrorMatcher : MatcherBase
{
private int _value;
public ErrorMatcher(int errorCode, CmpOp operator1)
: base(operator1)
{
;
_value = errorCode;
}
protected override String ValueText
{
get
{
return ErrorConstants.GetText(_value);
}
}
public override bool Matches(ValueEval x)
{
if (x is ErrorEval)
{
int testValue = ((ErrorEval)x).ErrorCode;
return Evaluate(testValue - _value);
}
return false;
}
}
private class NumberMatcher : MatcherBase
{
private double _value;
public NumberMatcher(double value, CmpOp optr)
: base(optr)
{
_value = value;
}
public override bool Matches(ValueEval x)
{
double testValue;
if (x is StringEval)
{
// if the target(x) is a string, but parses as a number
// it may still count as a match, only for the equality operator
switch (Code)
{
case CmpOp.EQ:
case CmpOp.NONE:
break;
case CmpOp.NE:
// Always matches (inconsistent with above two cases).
// for example '<>123' matches '123', '4', 'abc', etc
return true;
default:
// never matches (also inconsistent with above three cases).
// for example '>5' does not match '6',
return false;
}
StringEval se = (StringEval)x;
Double val = OperandResolver.ParseDouble(se.StringValue);
if (double.IsNaN(val))
{
// x is text that is not a number
return false;
}
return _value == val;
}
else if ((x is NumberEval))
{
NumberEval ne = (NumberEval)x;
testValue = ne.NumberValue;
}
else
{
return false;
}
return Evaluate(testValue.CompareTo(_value));
}
protected override string ValueText
{
get { return _value.ToString(CultureInfo.InvariantCulture); }
}
}
private class BooleanMatcher : MatcherBase
{
private int _value;
public BooleanMatcher(bool value, CmpOp optr)
: base(optr)
{
_value = BoolToInt(value);
}
private static int BoolToInt(bool value)
{
return value == true ? 1 : 0;
}
public override bool Matches(ValueEval x)
{
int testValue;
if (x is StringEval)
{
#if !HIDE_UNREACHABLE_CODE
if (true)
{ // change to false to observe more intuitive behaviour
// Note - Unlike with numbers, it seems that COUNTIF never matches
// boolean values when the target(x) is a string
return false;
}
StringEval se = (StringEval)x;
Boolean? val = ParseBoolean(se.StringValue);
if (val == null)
{
// x is text that is not a boolean
return false;
}
testValue = BoolToInt(val.Value);
#else
return false;
#endif
}
else if ((x is BoolEval))
{
BoolEval be = (BoolEval)x;
testValue = BoolToInt(be.BooleanValue);
}
else
{
return false;
}
return Evaluate(testValue - _value);
}
protected override string ValueText
{
get { return _value == 1 ? "TRUE" : "FALSE"; }
}
}
private class StringMatcher : MatcherBase
{
private String _value;
private CmpOp _operator;
private Regex _pattern;
public StringMatcher(String value, CmpOp optr):base(optr)
{
_value = value;
_operator = optr;
switch (optr.Code)
{
case CmpOp.NONE:
case CmpOp.EQ:
case CmpOp.NE:
_pattern = GetWildCardPattern(value);
break;
default:
_pattern = null;
break;
}
}
public override bool Matches(ValueEval x)
{
if (x is BlankEval)
{
switch (_operator.Code)
{
case CmpOp.NONE:
case CmpOp.EQ:
return _value.Length == 0;
}
// no other criteria matches a blank cell
return false;
}
if (!(x is StringEval))
{
// must always be string
// even if match str is wild, but contains only digits
// e.g. '4*7', NumberEval(4567) does not match
return false;
}
String testedValue = ((StringEval)x).StringValue;
if ((testedValue.Length < 1 && _value.Length < 1))
{
// odd case: criteria '=' behaves differently to criteria ''
switch (_operator.Code)
{
case CmpOp.NONE: return true;
case CmpOp.EQ: return false;
case CmpOp.NE: return true;
}
return false;
}
if (_pattern != null)
{
return Evaluate(_pattern.IsMatch(testedValue));
}
//return Evaluate(testedValue.CompareTo(_value));
return Evaluate(string.Compare(testedValue, _value, StringComparison.CurrentCulture));
}
///
/// Translates Excel countif wildcard strings into .NET regex strings
///
/// Excel wildcard expression
/// return null if the specified value contains no special wildcard characters.
private static Regex GetWildCardPattern(String value)
{
int len = value.Length;
StringBuilder sb = new StringBuilder(len);
sb.Append("^");
bool hasWildCard = false;
for (int i = 0; i < len; i++)
{
char ch = value[i];
switch (ch)
{
case '?':
hasWildCard = true;
// match exactly one character
sb.Append('.');
continue;
case '*':
hasWildCard = true;
// match one or more occurrences of any character
sb.Append(".*");
continue;
case '~':
if (i + 1 < len)
{
ch = value[i + 1];
switch (ch)
{
case '?':
case '*':
hasWildCard = true;
//sb.Append("\\").Append(ch);
sb.Append('[').Append(ch).Append(']');
i++; // Note - incrementing loop variable here
continue;
}
}
// else not '~?' or '~*'
sb.Append('~'); // just plain '~'
continue;
case '.':
case '$':
case '^':
case '[':
case ']':
case '(':
case ')':
// escape literal characters that would have special meaning in regex
sb.Append("\\").Append(ch);
continue;
}
sb.Append(ch);
}
sb.Append("$");
if (hasWildCard)
{
return new Regex(sb.ToString());
}
return null;
}
protected override string ValueText
{
get
{
if (_pattern == null)
{
return _value;
}
return _pattern.ToString();
}
}
}
/**
* @return the number of evaluated cells in the range that match the specified criteria
*/
private double CountMatchingCellsInArea(ValueEval rangeArg, I_MatchPredicate criteriaPredicate)
{
if (rangeArg is RefEval)
{
return CountUtils.CountMatchingCell((RefEval)rangeArg, criteriaPredicate);
}
else if (rangeArg is TwoDEval)
{
return CountUtils.CountMatchingCellsInArea((TwoDEval)rangeArg, criteriaPredicate);
}
else
{
throw new ArgumentException("Bad range arg type (" + rangeArg.GetType().Name + ")");
}
}
/**
*
* @return the de-referenced criteria arg (possibly {@link ErrorEval})
*/
private static ValueEval EvaluateCriteriaArg(ValueEval arg, int srcRowIndex, int srcColumnIndex)
{
try
{
return OperandResolver.GetSingleValue(arg, srcRowIndex, (short)srcColumnIndex);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
/**
* When the second argument is a string, many things are possible
*/
private static I_MatchPredicate CreateGeneralMatchPredicate(StringEval stringEval)
{
String value = stringEval.StringValue;
CmpOp operator1 = CmpOp.GetOperator(value);
value = value.Substring(operator1.Length);
bool? booleanVal = ParseBoolean(value);
if (booleanVal != null)
{
return new BooleanMatcher(booleanVal.Value, operator1);
}
Double doubleVal = OperandResolver.ParseDouble(value);
if (!double.IsNaN(doubleVal))
{
return new NumberMatcher(doubleVal, operator1);
}
ErrorEval ee = ParseError(value);
if (ee != null)
{
return new ErrorMatcher(ee.ErrorCode, operator1);
}
//else - just a plain string with no interpretation.
return new StringMatcher(value, operator1);
}
/**
* Creates a criteria predicate object for the supplied criteria arg
* @return null
if the arg evaluates to blank.
*/
public static I_MatchPredicate CreateCriteriaPredicate(ValueEval arg, int srcRowIndex, int srcColumnIndex)
{
ValueEval evaluatedCriteriaArg = EvaluateCriteriaArg(arg, srcRowIndex, srcColumnIndex);
if (evaluatedCriteriaArg is NumberEval)
{
return new NumberMatcher(((NumberEval)evaluatedCriteriaArg).NumberValue, CmpOp.OP_NONE);
}
if (evaluatedCriteriaArg is BoolEval)
{
return new BooleanMatcher(((BoolEval)evaluatedCriteriaArg).BooleanValue, CmpOp.OP_NONE);
}
if (evaluatedCriteriaArg is StringEval)
{
return CreateGeneralMatchPredicate((StringEval)evaluatedCriteriaArg);
}
if (evaluatedCriteriaArg is ErrorEval)
{
return new ErrorMatcher(((ErrorEval)evaluatedCriteriaArg).ErrorCode, CmpOp.OP_NONE);
}
if (evaluatedCriteriaArg == BlankEval.instance)
{
return null;
}
throw new Exception("Unexpected type for criteria ("
+ evaluatedCriteriaArg.GetType().Name + ")");
}
private static ErrorEval ParseError(String value)
{
if (value.Length < 4 || value[0] != '#')
{
return null;
}
if (value.Equals("#NULL!")) return ErrorEval.NULL_INTERSECTION;
if (value.Equals("#DIV/0!")) return ErrorEval.DIV_ZERO;
if (value.Equals("#VALUE!")) return ErrorEval.VALUE_INVALID;
if (value.Equals("#REF!")) return ErrorEval.REF_INVALID;
if (value.Equals("#NAME?")) return ErrorEval.NAME_INVALID;
if (value.Equals("#NUM!")) return ErrorEval.NUM_ERROR;
if (value.Equals("#N/A")) return ErrorEval.NA;
return null;
}
/**
* bool literals ('TRUE', 'FALSE') treated similarly but NOT same as numbers.
*/
/* package */
public static bool? ParseBoolean(String strRep)
{
if (strRep.Length < 1)
{
return null;
}
switch (strRep[0])
{
case 't':
case 'T':
if ("TRUE".Equals(strRep, StringComparison.OrdinalIgnoreCase))
{
return true;
}
break;
case 'f':
case 'F':
if ("FALSE".Equals(strRep, StringComparison.OrdinalIgnoreCase))
{
return false;
}
break;
}
return null;
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
I_MatchPredicate mp = CreateCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
if (mp == null)
{
// If the criteria arg is a reference to a blank cell, countif always returns zero.
return NumberEval.ZERO;
}
double result = CountMatchingCellsInArea(arg0, mp);
return new NumberEval(result);
}
}
}