public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
AreaEval aeRange;
double result;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
result = OperandResolver.CoerceValueToDouble(ve);
if (Double.IsNaN(result) || Double.IsInfinity(result))
{
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
if (arg1 is RefListEval)
{
return(eval(result, ((RefListEval)arg1), true));
}
aeRange = ConvertRangeArg(arg1);
return(eval(result, aeRange, true));
}
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)))));
}
/**
* Evaluate a generic {@link ValueEval} argument to an array of double values that represents dates in POI.
*
* @param arg {@link ValueEval} an argument.
* @param srcCellRow number cell row.
* @param srcCellCol number cell column.
* @return an array of doubles representing dates in POI.
* @throws EvaluationException exception upon argument evaluation.
*/
public double[] EvaluateDatesArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
if (arg == null)
{
return(new double[0]);
}
if (arg is StringEval)
{
return(new double[] { EvaluateDateArg(arg, srcCellRow, srcCellCol) });
}
else if (arg is AreaEvalBase)
{
List <Double> valuesList = new List <Double>();
AreaEvalBase area = (AreaEvalBase)arg;
for (int i = area.FirstRow; i <= area.LastRow; i++)
{
for (int j = area.FirstColumn; j <= area.LastColumn; j++)
{
valuesList.Add(EvaluateDateArg(area.GetValue(i, j), i, j));
}
}
double[] values = new double[valuesList.Count];
for (int i = 0; i < valuesList.Count; i++)
{
values[i] = valuesList[(i)];
}
return(values);
}
return(new double[] { OperandResolver.CoerceValueToDouble(arg) });
}
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());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval venumerator, ValueEval vedenominator)
{
double enumerator = 0;
try
{
ValueEval ve = OperandResolver.GetSingleValue(venumerator, srcRowIndex, srcColumnIndex);
enumerator = OperandResolver.CoerceValueToDouble(ve);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
double denominator = 0;
try
{
ValueEval ve = OperandResolver.GetSingleValue(vedenominator, srcRowIndex, srcColumnIndex);
denominator = OperandResolver.CoerceValueToDouble(ve);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
if (denominator == 0)
{
return(ErrorEval.DIV_ZERO);
}
return(new NumberEval((int)(enumerator / denominator)));
}
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());
}
}
/**
* Evaluate a generic {@link ValueEval} argument to an array of double values that represents dates in POI.
*
* @param arg {@link ValueEval} an argument.
* @param srcCellRow number cell row.
* @param srcCellCol number cell column.
* @return an array of doubles representing dates in POI.
* @throws EvaluationException exception upon argument evaluation.
*/
public double[] EvaluateDatesArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
if (arg == null)
{
return(new double[0]);
}
if (arg is StringEval)
{
return(new double[] { EvaluateDateArg(arg, srcCellRow, srcCellCol) });
}
else if (arg is AreaEvalBase)
{
List <Double> valuesList = new List <Double>();
AreaEvalBase area = (AreaEvalBase)arg;
for (int i = area.FirstRow; i <= area.LastRow; i++)
{
for (int j = area.FirstColumn; j <= area.LastColumn; j++)
{
// getValue() is replaced with getAbsoluteValue() because loop variables i, j are
// absolute indexes values, but getValue() works with relative indexes values
valuesList.Add(EvaluateDateArg(area.GetAbsoluteValue(i, j), i, j));
}
}
double[] values = new double[valuesList.Count];
for (int i = 0; i < valuesList.Count; i++)
{
values[i] = valuesList[(i)];
}
return(values);
}
return(new double[] { OperandResolver.CoerceValueToDouble(arg) });
}
protected override double Eval(ValueEval[] args, int srcCellRow, int srcCellCol)
{
if (args.Length != 4)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
double result;
ValueEval v1 = OperandResolver.GetSingleValue(args[0], srcCellRow, srcCellCol);
ValueEval v2 = OperandResolver.GetSingleValue(args[1], srcCellRow, srcCellCol);
ValueEval v3 = OperandResolver.GetSingleValue(args[2], srcCellRow, srcCellCol);
ValueEval v4 = OperandResolver.GetSingleValue(args[3], srcCellRow, srcCellCol);
double interestRate = OperandResolver.CoerceValueToDouble(v1);
int period = OperandResolver.CoerceValueToInt(v2);
int numberPayments = OperandResolver.CoerceValueToInt(v3);
double PV = OperandResolver.CoerceValueToDouble(v4);
result = Finance.IPMT(interestRate, period, numberPayments, PV);
CheckValue(result);
return(result);
}
public static double SingleOperandEvaluate(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
double result = OperandResolver.CoerceValueToDouble(ve);
CheckValue(result);
return(result);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
ValueEval arg1)
{
double dn;
try
{
ValueEval ve1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
dn = OperandResolver.CoerceValueToDouble(ve1);
}
catch (EvaluationException e1)
{
// all errors in the second arg translate to #VALUE!
return(ErrorEval.VALUE_INVALID);
}
if (dn < 0 || dn > 1)
{ // has to be percentage
return(ErrorEval.NUM_ERROR);
}
double result;
try
{
double[] ds = NPOI.SS.Formula.Functions.AggregateFunction.ValueCollector.CollectValues(arg0);
int N = ds.Length;
if (N == 0 || N > 8191)
{
return(ErrorEval.NUM_ERROR);
}
double n = (N - 1) * dn + 1;
if (n == 1d)
{
result = StatsLib.kthSmallest(ds, 1);
}
else if (n == N)
{
result = StatsLib.kthLargest(ds, 1);
}
else
{
int k = (int)n;
double d = n - k;
result = StatsLib.kthSmallest(ds, k) + d
* (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k));
}
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
if (args.Length < 3)
{ //First 3 parameters are mandatory
return(ErrorEval.VALUE_INVALID);
}
double periods, payment, present_val, future_val = 0, type = 0, estimate = 0.1, rate;
try
{
ValueEval v1 = OperandResolver.GetSingleValue(args[0], srcRowIndex, srcColumnIndex);
ValueEval v2 = OperandResolver.GetSingleValue(args[1], srcRowIndex, srcColumnIndex);
ValueEval v3 = OperandResolver.GetSingleValue(args[2], srcRowIndex, srcColumnIndex);
ValueEval v4 = null;
if (args.Length >= 4)
{
v4 = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
}
ValueEval v5 = null;
if (args.Length >= 5)
{
v5 = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
}
ValueEval v6 = null;
if (args.Length >= 6)
{
v6 = OperandResolver.GetSingleValue(args[5], srcRowIndex, srcColumnIndex);
}
periods = OperandResolver.CoerceValueToDouble(v1);
payment = OperandResolver.CoerceValueToDouble(v2);
present_val = OperandResolver.CoerceValueToDouble(v3);
if (args.Length >= 4)
{
future_val = OperandResolver.CoerceValueToDouble(v4);
}
if (args.Length >= 5)
{
type = OperandResolver.CoerceValueToDouble(v5);
}
if (args.Length >= 6)
{
estimate = OperandResolver.CoerceValueToDouble(v6);
}
rate = CalculateRate(periods, payment, present_val, future_val, type, estimate);
CheckValue(rate);
}
catch (EvaluationException e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.StackTrace);
return(e.GetErrorEval());
}
return(new NumberEval(rate));
}
/**
* Evaluate a generic {@link ValueEval} argument to a double value.
*
* @param arg {@link ValueEval} an argument.
* @param srcCellRow number cell row.
* @param srcCellCol number cell column.
* @return a double value.
* @throws EvaluationException exception upon argument evaluation.
*/
public double EvaluateNumberArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
if (arg == null)
{
return(0f);
}
return(OperandResolver.CoerceValueToDouble(arg));
}
private ValueEval doFixed(
ValueEval numberParam, ValueEval placesParam,
ValueEval skipThousandsSeparatorParam,
int srcRowIndex, int srcColumnIndex)
{
try
{
ValueEval numberValueEval =
OperandResolver.GetSingleValue(
numberParam, srcRowIndex, srcColumnIndex);
decimal number = (decimal)OperandResolver.CoerceValueToDouble(numberValueEval);
ValueEval placesValueEval =
OperandResolver.GetSingleValue(
placesParam, srcRowIndex, srcColumnIndex);
int places = OperandResolver.CoerceValueToInt(placesValueEval);
ValueEval skipThousandsSeparatorValueEval =
OperandResolver.GetSingleValue(
skipThousandsSeparatorParam, srcRowIndex, srcColumnIndex);
bool?skipThousandsSeparator =
OperandResolver.CoerceValueToBoolean(
skipThousandsSeparatorValueEval, false);
// Round number to respective places.
//number = number.SetScale(places, RoundingMode.HALF_UP);
if (places < 0)
{
number = number / (decimal)Math.Pow(10, -places);
number = Math.Round(number, 0);
number = number * (decimal)Math.Pow(10, -places);
}
else
{
number = Math.Round(number, places);
}
// Format number conditionally using a thousands separator.
/*NumberFormat nf = NumberFormat.GetNumberInstance(Locale.US);
* DecimalFormat formatter = (DecimalFormat)nf;
* formatter.setGroupingUsed(!skipThousandsSeparator);
* formatter.setMinimumFractionDigits(places >= 0 ? places : 0);
* formatter.setMaximumFractionDigits(places >= 0 ? places : 0);
* String numberString = formatter.Format(number);*/
//System.Globalization.CultureInfo culture = System.Globalization.CultureInfo.CurrentCulture;
string numberString = skipThousandsSeparator != null && skipThousandsSeparator.Value ?
number.ToString(places > 0 ? "F" + places : "F0")
: number.ToString(places > 0 ? "N" + places : "N0", System.Globalization.CultureInfo.InvariantCulture);
// Return the result as a StringEval.
return(new StringEval(numberString));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
protected static double singleOperandEvaluate(ValueEval arg, int srcRowIndex, int srcColumnIndex)
{
if (arg == null)
{
throw new ArgumentException("arg must not be null");
}
ValueEval ve = OperandResolver.GetSingleValue(arg, srcRowIndex, srcColumnIndex);
double result = OperandResolver.CoerceValueToDouble(ve);
checkValue(result);
return(result);
}
private static int EvaluateArgParity(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
double d = OperandResolver.CoerceValueToDouble(ve);
if (d < 0)
{
d = -d;
}
long v = (long)Math.Floor(d);
return((int)(v & 0x0001));
}
private static double EvaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, (short)srcCellCol);
if (ve is StringEval)
{
String strVal = ((StringEval)ve).StringValue;
Double dVal = OperandResolver.ParseDouble(strVal);
if (!double.IsNaN(dVal))
{
return(dVal);
}
DateTime date = ParseDate(strVal);
return(NPOI.SS.UserModel.DateUtil.GetExcelDate(date, false));
}
return(OperandResolver.CoerceValueToDouble(ve));
}
/**
* Evaluate a generic {@link ValueEval} argument to a double value that represents a date in POI.
*
* @param arg {@link ValueEval} an argument.
* @param srcCellRow number cell row.
* @param srcCellCol number cell column.
* @return a double representing a date in POI.
* @throws EvaluationException exception upon argument evaluation.
*/
public double EvaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, (short)srcCellCol);
if (ve is StringEval)
{
String strVal = ((StringEval)ve).StringValue;
Double dVal = OperandResolver.ParseDouble(strVal);
if (!Double.IsNaN(dVal))
{
return(dVal);
}
DateTime dt = DateParser.ParseDate(strVal);
return(DateUtil.GetExcelDate(dt, false));
}
return(OperandResolver.CoerceValueToDouble(ve));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
ValueEval arg1)
{
double dn;
try
{
ValueEval ve1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
dn = OperandResolver.CoerceValueToDouble(ve1);
}
catch (EvaluationException e1)
{
// all errors in the second arg translate to #VALUE!
return(ErrorEval.VALUE_INVALID);
}
// weird Excel behaviour on second arg
if (dn < 1.0)
{
// values between 0.0 and 1.0 result in #NUM!
return(ErrorEval.NUM_ERROR);
}
// all other values are rounded up to the next integer
int k = (int)Math.Ceiling(dn);
double result;
try
{
double[] ds = NPOI.SS.Formula.Functions.AggregateFunction.ValueCollector.CollectValues(arg0);
if (k > ds.Length)
{
return(ErrorEval.NUM_ERROR);
}
result = _isLarge ? StatsLib.kthLargest(ds, k) : StatsLib.kthSmallest(ds, k);
NumericFunction.CheckValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
double val;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
val = OperandResolver.CoerceValueToDouble(ve);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (val < 0)
{
return(ErrorEval.NUM_ERROR);
}
return(new NumberEval(GetCalField(val)));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
double result;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
result = OperandResolver.CoerceValueToDouble(ve);
if (Double.IsNaN(result) || Double.IsInfinity(result))
{
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
bool order = false;
ve = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
int order_value = OperandResolver.CoerceValueToInt(ve);
if (order_value == 0)
{
order = true;
}
else if (order_value == 1)
{
order = false;
}
else
{
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
if (arg1 is RefListEval)
{
return(eval(result, ((RefListEval)arg1), order));
}
AreaEval aeRange = ConvertRangeArg(arg1);
return(eval(result, aeRange, order));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval text, ValueEval number_times)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(text, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
double numberOfTime = 0;
try
{
numberOfTime = OperandResolver.CoerceValueToDouble(number_times);
}
catch (EvaluationException e)
{
return(ErrorEval.VALUE_INVALID);
}
int numberOfTimeInt = (int)numberOfTime;
StringBuilder strb = new StringBuilder(strText1.Length * numberOfTimeInt);
for (int i = 0; i < numberOfTimeInt; i++)
{
strb.Append(strText1);
}
if (strb.ToString().Length > 32767)
{
return(ErrorEval.VALUE_INVALID);
}
return(new StringEval(strb.ToString()));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval real_num, ValueEval i_num, ValueEval suffix)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(real_num, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
double realNum = 0;
try
{
realNum = OperandResolver.CoerceValueToDouble(veText1);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
ValueEval veINum;
try
{
veINum = OperandResolver.GetSingleValue(i_num, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
double realINum = 0;
try
{
realINum = OperandResolver.CoerceValueToDouble(veINum);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
string suffixValue = OperandResolver.CoerceValueToString(suffix);
if (suffixValue.Length == 0)
{
suffixValue = DEFAULT_SUFFIX;
}
if (suffixValue.Equals(DEFAULT_SUFFIX.ToUpper()) || suffixValue.Equals(SUPPORTED_SUFFIX.ToUpper()))
{
return(ErrorEval.VALUE_INVALID);
}
if (!(suffixValue.Equals(DEFAULT_SUFFIX) || suffixValue.Equals(SUPPORTED_SUFFIX)))
{
return(ErrorEval.VALUE_INVALID);
}
StringBuilder strb = new StringBuilder("");
if (realNum != 0)
{
if (isDoubleAnInt(realNum))
{
strb.Append((int)realNum);
}
else
{
strb.Append(realNum);
}
}
if (realINum != 0)
{
if (strb.Length != 0)
{
if (realINum > 0)
{
strb.Append("+");
}
}
if (realINum != 1 && realINum != -1)
{
if (isDoubleAnInt(realINum))
{
strb.Append((int)realINum);
}
else
{
strb.Append(realINum);
}
}
strb.Append(suffixValue);
}
return(new StringEval(strb.ToString()));
}
public static double EvaluateDoubleArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
return(OperandResolver.CoerceValueToDouble(ve));
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
if (args.Length < 2)
{
return(ErrorEval.VALUE_INVALID);
}
double x;
try
{
ValueEval ev = OperandResolver.GetSingleValue(args[1], srcRowIndex, srcColumnIndex);
x = OperandResolver.CoerceValueToDouble(ev);
}
catch (EvaluationException e)
{
ValueEval error = e.GetErrorEval();
if (error == ErrorEval.NUM_ERROR)
{
return(error);
}
return(ErrorEval.NUM_ERROR);
}
List <Double> numbers = new List <double>();
try
{
List <ValueEval> values = getValues(args[0], srcRowIndex, srcColumnIndex);
foreach (ValueEval ev in values)
{
if (ev is BlankEval || ev is MissingArgEval)
{
//skip
}
else
{
numbers.Add(OperandResolver.CoerceValueToDouble(ev));
}
}
}
catch (EvaluationException e)
{
ValueEval error = e.GetErrorEval();
if (error != ErrorEval.NA)
{
return(error);
}
return(ErrorEval.NUM_ERROR);
}
if (numbers.Count == 0)
{
return(ErrorEval.NUM_ERROR);
}
int significance = 3;
if (args.Length > 2)
{
try
{
ValueEval ev = OperandResolver.GetSingleValue(args[2], srcRowIndex, srcColumnIndex);
significance = OperandResolver.CoerceValueToInt(ev);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
return(calculateRank(numbers, x, significance, true));
}
/* for Var1or2ArgFunction:
* @Override
* public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
* }
*
* @Override
* public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
* }
*/
/**
* Perform WEEKDAY(date, returnOption) function.
* Note: Parameter texts are from German EXCEL-2010 help.
* Parameters in args[]:
* args[0] serialDate
* EXCEL-date value
* Standardmaessig ist der 1. Januar 1900 die fortlaufende Zahl 1 und
* der 1. Januar 2008 die fortlaufende Zahl 39.448, da dieser Tag nach 39.448 Tagen
* auf den 01.01.1900 folgt.
* @return Option (optional)
* Bestimmt den Rueckgabewert:
* 1 oder nicht angegeben Zahl 1 (Sonntag) bis 7 (Samstag). Verhaelt sich wie fruehere Microsoft Excel-Versionen.
* 2 Zahl 1 (Montag) bis 7 (Sonntag).
* 3 Zahl 0 (Montag) bis 6 (Sonntag).
* 11 Die Zahlen 1 (Montag) bis 7 (Sonntag)
* 12 Die Zahlen 1 (Dienstag) bis 7 (Montag)
* 13 Die Zahlen 1 (Mittwoch) bis 7 (Dienstag)
* 14 Die Zahlen 1 (Donnerstag) bis 7 (Mittwoch)
* 15 Die Zahlen 1 (Freitag) bis 7 (Donnerstag)
* 16 Die Zahlen 1 (Samstag) bis 7 (Freitag)
* 17 Die Zahlen 1 (Sonntag) bis 7 (Samstag)
*/
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex)
{
try
{
if (args.Length < 1 || args.Length > 2)
{
return(ErrorEval.VALUE_INVALID);
}
// extract first parameter
ValueEval serialDateVE = OperandResolver.GetSingleValue(args[0], srcRowIndex, srcColumnIndex);
double serialDate = OperandResolver.CoerceValueToDouble(serialDateVE);
if (!DateUtil.IsValidExcelDate(serialDate))
{
return(ErrorEval.NUM_ERROR); // EXCEL uses this and no VALUE_ERROR
}
DateTime date = DateUtil.GetJavaCalendar(serialDate, false); // (XXX 1904-windowing not respected)
int weekday = (int)date.DayOfWeek; // => sunday = 1, monday = 2, ..., saturday = 7
// extract second parameter
int returnOption = 1; // default value
if (args.Length == 2)
{
ValueEval ve = OperandResolver.GetSingleValue(args[1], srcRowIndex, srcColumnIndex);
if (ve == MissingArgEval.instance || ve == BlankEval.instance)
{
return(ErrorEval.NUM_ERROR); // EXCEL uses this and no VALUE_ERROR
}
returnOption = OperandResolver.CoerceValueToInt(ve);
if (returnOption == 2)
{
returnOption = 11; // both mean the same
}
} // if
// perform calculation
double result;
if (returnOption == 1)
{
result = weekday;
// value 2 is handled above (as value 11)
}
else if (returnOption == 3)
{
result = (weekday + 6 - 1) % 7;
}
else if (returnOption >= 11 && returnOption <= 17)
{
result = (weekday + 6 - (returnOption - 10)) % 7 + 1; // rotate in the value range 1 to 7
}
else
{
return(ErrorEval.NUM_ERROR); // EXCEL uses this and no VALUE_ERROR
}
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
} // evaluate()