/// <summary>
/// Returns the sum of cells in a range that have the same color as a reference cell.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 2 parameters, first the reference cell and then
/// the range in which to sum. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 2, out Err))
{
return(Err);
}
//The first parameter should be a range
TXls3DRange SourceCell;
if (!TryGetCellRange(parameters[0], out SourceCell, out Err))
{
return(Err);
}
//The second parameter should be a range too.
TXls3DRange SumRange;
if (!TryGetCellRange(parameters[1], out SumRange, out Err))
{
return(Err);
}
#endregion
//Get the color in SourceCell. Note that if Source cell is a range with more than one cell,
//we will use the first cell in the range. Also, as different colors can have the same rgb value, we will compare the actual RGB values, not the ExcelColors
TFlxFormat fmt = arguments.Xls.GetCellVisibleFormatDef(SourceCell.Sheet1, SourceCell.Top, SourceCell.Left);
int SourceColor = fmt.FillPattern.FgColor.ToColor(arguments.Xls).ToArgb();
double Result = 0;
//Loop in the sum range and sum the corresponding values.
for (int s = SumRange.Sheet1; s <= SumRange.Sheet2; s++)
{
for (int r = SumRange.Top; r <= SumRange.Bottom; r++)
{
for (int c = SumRange.Left; c <= SumRange.Right; c++)
{
int XF = -1;
object val = arguments.Xls.GetCellValue(s, r, c, ref XF);
if (val is double) //we will only sum numeric values.
{
TFlxFormat sumfmt = arguments.Xls.GetCellVisibleFormatDef(s, r, c);
if (sumfmt.FillPattern.FgColor.ToColor(arguments.Xls).ToArgb() == SourceColor)
{
Result += (double)val;
}
}
}
}
}
return(Result);
}
/// <summary>
/// Chooses the lowest element in an array.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 1 parameter that should be an array. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 1, out Err))
{
return(Err);
}
//The first parameter should be an array.
object[,] SourceArray;
if (!TryGetArray(arguments.Xls, parameters[0], out SourceArray, out Err))
{
return(Err);
}
#endregion
double Result = 0;
bool First = true;
foreach (object o in SourceArray)
{
if (o is double)
{
if (First)
{
First = false;
Result = (double)o;
}
else
{
if ((double)o < Result)
{
Result = (double)o;
}
}
}
else
{
return(TFlxFormulaErrorValue.ErrValue);
}
}
return(Result);
}
/// <summary>
/// Chooses between 2 different strings.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 3 parameters: The first is a boolean, and the other 2 strings. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 3, out Err))
{
return(Err);
}
//The first parameter should be a boolean.
bool ChooseFirst;
if (!TryGetBoolean(arguments.Xls, parameters[0], out ChooseFirst, out Err))
{
return(Err);
}
//The second parameter should be a string.
string s1;
if (!TryGetString(arguments.Xls, parameters[1], out s1, out Err))
{
return(Err);
}
//The third parameter should be a string.
string s2;
if (!TryGetString(arguments.Xls, parameters[2], out s2, out Err))
{
return(Err);
}
#endregion
//Return s1 or s2 depending on ChooseFirst
if (ChooseFirst)
{
return(s1);
}
else
{
return(s2);
}
}
/// <summary>
/// Returns true if a number is prime.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 1 parameter with the number. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 1, out Err))
{
return(Err);
}
//The parameter should be a double or a range.
double Number;
if (!TryGetDouble(arguments.Xls, parameters[0], out Number, out Err))
{
return(Err);
}
#endregion
//Return true if the number is prime.
int n = Convert.ToInt32(Number);
if (n == 2)
{
return(true);
}
if (n < 2 || n % 2 == 0)
{
return(false);
}
for (int i = 3; i <= Math.Sqrt(n); i += 2)
{
if (n % i == 0)
{
return(false);
}
}
return(true);
}
