/**
* Evaluate for NETWORKDAYS. Given two dates and a optional date or interval of holidays, determines how many working days are there
* between those dates.
*
* @return {@link ValueEval} for the number of days between two dates.
*/
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 2 || args.Length > 3)
{
return ErrorEval.VALUE_INVALID;
}
int srcCellRow = ec.RowIndex;
int srcCellCol = ec.ColumnIndex;
double start, end;
double[] holidays;
try
{
start = this.evaluator.EvaluateDateArg(args[0], srcCellRow, srcCellCol);
end = this.evaluator.EvaluateDateArg(args[1], srcCellRow, srcCellCol);
if (start > end)
{
return ErrorEval.NAME_INVALID;
}
ValueEval holidaysCell = args.Length == 3 ? args[2] : null;
holidays = this.evaluator.EvaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
return new NumberEval(WorkdayCalculator.instance.CalculateWorkdays(start, end, holidays));
}
catch (EvaluationException)
{
return ErrorEval.VALUE_INVALID;
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
double result;
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
try
{
double startDateAsNumber = GetValue(args[0]);
NumberEval offsetInYearsValue = (NumberEval)args[1];
int offsetInMonthAsNumber = (int)offsetInYearsValue.NumberValue;
// resolve the arguments
DateTime startDate = DateUtil.GetJavaDate(startDateAsNumber);
DateTime resultDate = startDate.AddMonths(offsetInMonthAsNumber);
result = DateUtil.GetExcelDate(resultDate);
NumericFunction.CheckValue(result);
return new NumberEval(result);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
/**
* Evaluate for WORKDAY. Given a date, a number of days and a optional date or interval of holidays, determines which date it is past
* number of parametrized workdays.
*
* @return {@link ValueEval} with date as its value.
*/
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 2 || args.Length > 3)
{
return ErrorEval.VALUE_INVALID;
}
int srcCellRow = ec.RowIndex;
int srcCellCol = ec.ColumnIndex;
double start;
int days;
double[] holidays;
try
{
start = this.evaluator.EvaluateDateArg(args[0], srcCellRow, srcCellCol);
days = (int)Math.Floor(this.evaluator.EvaluateNumberArg(args[1], srcCellRow, srcCellCol));
ValueEval holidaysCell = args.Length == 3 ? args[2] : null;
holidays = this.evaluator.EvaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
return new NumberEval(DateUtil.GetExcelDate(WorkdayCalculator.instance.CalculateWorkdays(start, days, holidays)));
}
catch (EvaluationException)
{
return ErrorEval.VALUE_INVALID;
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
DateTime date;
double numberOfMonths, result;
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
try
{
// resolve the arguments
date = DateUtil.GetJavaDate(OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex)));
numberOfMonths = OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex));
// calculate the result date (Excel rounds the second argument always to zero; but we have be careful about negative numbers)
DateTime resultDate = date.AddMonths((int)Math.Floor(Math.Abs(numberOfMonths)) * Math.Sign(numberOfMonths));
result = DateUtil.GetExcelDate(resultDate);
NumericFunction.CheckValue(result);
return new NumberEval(result);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 3 || args.Length % 2 == 0)
{
return ErrorEval.VALUE_INVALID;
}
try
{
AreaEval sumRange = ConvertRangeArg(args[0]);
// collect pairs of ranges and criteria
AreaEval[] ae = new AreaEval[(args.Length - 1) / 2];
I_MatchPredicate[] mp = new I_MatchPredicate[ae.Length];
for (int i = 1, k = 0; i < args.Length; i += 2, k++)
{
ae[k] = ConvertRangeArg(args[i]);
mp[k] = Countif.CreateCriteriaPredicate(args[i + 1], ec.RowIndex, ec.ColumnIndex);
}
ValidateCriteriaRanges(ae, sumRange);
double result = SumMatchingCells(ae, mp, sumRange);
return new NumberEval(result);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
return Evaluate(ec.RowIndex, ec.ColumnIndex, args[0], args[1]);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
ValueEval val;
try
{
val = EvaluateInternal(args[0], args[1], ec.RowIndex, ec.ColumnIndex);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
return val;
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
double number, multiple, result;
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
try
{
number = OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex));
multiple = OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex));
if (multiple == 0.0)
{
result = 0.0;
}
else
{
if (number * multiple < 0)
{
// Returns #NUM! because the number and the multiple have different signs
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
result = multiple * Math.Round(number / multiple, MidpointRounding.AwayFromZero);
}
NumericFunction.CheckValue(result);
return new NumberEval(result);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
/**
* Evaluate for RANDBETWEEN(). Must be given two arguments. Bottom must be greater than top.
* Bottom is rounded up and top value is rounded down. After rounding top has to be set greater
* than top.
*
* @see org.apache.poi.ss.formula.functions.FreeRefFunction#evaluate(org.apache.poi.ss.formula.eval.ValueEval[], org.apache.poi.ss.formula.OperationEvaluationContext)
*/
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
double bottom, top;
if (args.Length != 2)
{
return ErrorEval.VALUE_INVALID;
}
try
{
bottom = OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex));
top = OperandResolver.CoerceValueToDouble(OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex));
if (bottom > top)
{
return ErrorEval.NUM_ERROR;
}
}
catch (EvaluationException)
{
return ErrorEval.VALUE_INVALID;
}
bottom = Math.Ceiling(bottom);
top = Math.Floor(top);
if (bottom > top)
{
top = bottom;
}
Random rnd = new Random();
return new NumberEval((bottom + (int)(rnd.NextDouble() * ((top - bottom) + 1))));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length == 1)
{
return Evaluate(ec.RowIndex, ec.ColumnIndex, args[0]);
}
if (args.Length == 2)
{
return Evaluate(ec.RowIndex, ec.ColumnIndex, args[0], args[1]);
}
return Evaluate(ec.RowIndex, ec.ColumnIndex, args[0], args[1]);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
return(ErrorEval.NUM_ERROR);
}
private static ValueEval EvaluateFormula(Ptg[] ptgs)
{
OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null);
return(new WorkbookEvaluator(null, null, null).EvaluateFormula(ec, ptgs));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
throw new NotImplementedFunctionException(_functionName);
}
