/// <summary>
/// Returns the average of its arguments
/// </summary>
/// <param name="ctrl">instance of ReoGrid control</param>
/// <param name="range">range to count</param>
/// <returns></returns>
public static double AVERAGE(Worksheet ctrl, ReoGridRange range)
{
double val = 0;
int count = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
var data = cell.InnerData;
if (data != null)
{
double cellValue;
if (!RGUtility.ConvertCellData <double>(data, out cellValue))
{
throw new FormulaEvalutionException("Input value is not in number: " + cell.InternalPos.ToAddress());
}
count++;
}
return(true);
});
return(val / count);
}
/// <summary>
/// Rounds a number to the nearest integer or to the nearest multiple of significance
/// </summary>
/// <param name="ctrl">instance of ReoGrid control</param>
/// <param name="args">function arguments</param>
/// <returns>nearest integer or to the nearest multiple of significance</returns>
public static double CEILING(Worksheet ctrl, object[] args)
{
if (args.Length < 1)
{
return(double.NaN);
}
double input = RGUtility.ConvertCellData <double>(args[0]);
if (args.Length < 2)
{
return(Math.Ceiling(input));
}
double significance = RGUtility.ConvertCellData <double>(args[1]);
if ((input % significance) != 0)
{
return(((int)(input / significance) * significance) + significance);
}
else
{
return(Math.Round(input));
}
}
/// <summary>
/// Adds its arguments
/// </summary>
/// <param name="ctrl"></param>
/// <param name="range"></param>
/// <returns></returns>
public static double SUM(Worksheet ctrl, ReoGridRange range)
{
double val = 0;
ctrl.IterateCells(range, (row, col, cell) =>
{
var data = cell.InnerData;
if (data != null)
{
double cellValue;
if (RGUtility.ConvertCellData <double>(data, out cellValue))
{
val += cellValue;
}
}
return(true);
});
return(val);
}
internal static void SetupBuiltinFunctions(Worksheet grid)
{
#if EX_SCRIPT
var srm = grid.Srm;
srm["SUM"] = new NativeFunctionObject("SUM", (ctx, owner, args) => SUM(grid, RSUtility.GetRangeFromArgs(grid, args)));
srm["AVERAGE"] = new NativeFunctionObject("AVERAGE", (ctx, owner, args) => AVERAGE(grid, RSUtility.GetRangeFromArgs(grid, args)));
srm["COUNT"] = new NativeFunctionObject("COUNT", (ctx, owner, args) => COUNT(grid, RSUtility.GetRangeFromArgs(grid, args)));
srm["CEILING"] = new NativeFunctionObject("CEILING", (ctx, owner, args) => CEILING(grid, args));
#region ROW & COLUMN
srm["ROW"] = new NativeFunctionObject("ROW", (ctx, owner, args) =>
{
var cell = ctx["__cell__"] as ReoGridCell;
if (cell == null)
{
throw new FormulaEvalutionException("ROW function must be used in an evaluation of formula belonging to a cell.");
}
return(cell.InternalRow);
});
srm["COLUMN"] = new NativeFunctionObject("COLUMN", (ctx, owner, args) =>
{
var cell = ctx["__cell__"] as ReoGridCell;
if (cell == null)
{
throw new FormulaEvalutionException("COLUMN function must be used in an evaluation of formula belonging to a cell.");
}
return(cell.InternalCol);
});
#endregion // ROW & COLUMN
#region ADDRESS
srm["ADDRESS"] = new NativeFunctionObject("ADDRESS", (ctx, owner, args) =>
{
if (args.Length < 2)
{
throw new FormulaEvalutionException("ADDRESS function needs at least 2 arguments.");
}
int row = RGUtility.ConvertCellData <int>(args[0]);
int col = RGUtility.ConvertCellData <int>(args[1]);
int absNum = 1;
if (args.Length > 2)
{
absNum = RGUtility.ConvertCellData <int>(args[2]);
}
// Excel standard: false
bool a1style = true;
if (args.Length > 3)
{
a1style = RGUtility.ConvertCellData <bool>(args[3]);
}
return(RGUtility.ToAddress(row, col, absNum, a1style));
});
#endregion // ADDRESS
srm["ROUND"] = new NativeFunctionObject("ROUND", (ctx, owner, args) =>
{
if (args.Length < 1)
{
throw new FormulaEvalutionException("ROUND function needs at least 2 arguments.");
}
int digit = 0;
if (args.Length > 1)
{
digit = RGUtility.ConvertCellData <int>(args[1]);
}
double value = RGUtility.ConvertCellData <double>(args[0]);
return(Math.Round(value, digit));
});
srm["RANGE"] = new NativeFunctionObject("RANGE", (ctx, owner, args) =>
{
if (args.Length < 2)
{
throw new FormulaEvalutionException("RANGE function needs at least 2 arguments.");
}
int row = RGUtility.ConvertCellData <int>(args[0]);
int col = RGUtility.ConvertCellData <int>(args[1]);
int rows = 1, cols = 1;
if (args.Length > 2)
{
rows = RGUtility.ConvertCellData <int>(args[2]);
}
if (args.Length > 3)
{
cols = RGUtility.ConvertCellData <int>(args[3]);
}
return(grid.FixRange(new ReoGridRange(row, col, rows, cols)));
});
srm["INDIRECT"] = new NativeFunctionObject("INDIRECT", (ctx, owner, args) =>
{
if (args.Length == 0)
{
throw new FormulaEvalutionException("INDIRECT function needs at least 1 arguments.");
}
var arg1 = args[0];
if (arg1 is string || arg1 is StringBuilder)
{
return(grid.GetCellData(Convert.ToString(arg1)));
}
else if (arg1 is ReoGridPos || arg1 is RSCellObject || arg1 is ReoGridCell)
{
return(grid.GetCellData(RSUtility.GetPosFromValue(grid, arg1)));
}
else
{
return(0);
}
});
#endif // EX_SCRIPT
}