Although Spread has over 300 built-in Functions but there are times when we cannot achieve the results we want with a standard built-in function. In these cases Custom Functions are very handy. Custom Functions are user-defined functions which we write ourselves. If we have functions that we use on a regular basis that are not in the built-in functions list or if we wish to combine some of the built-in functions into a single function then we can do so by defining our own Custom Functions. In Spread, both Built-In Functions (e.g. SUM) and Custom Functions (e.g. CUBE) are defined using instances of the FunctionInfo class. The difference is in how formulas containing the functions are parsed and evaluated. For Built-In Functions, the spreadsheet uses early binding (i.e. function definition is determined at parse time). At parse time, the spreadsheet looks up the function definition (e.g. FunctionInfo.SumFunction) in the table of Built-In Functions and stores the function definition in the parsed expression (e.g. new FunctionExpression(FunctionInfo.SumFunction,..)). At the time of evaluation, the spreadsheet evaluates the function call using the function definition stored in the parsed expression. For Custom Functions, the spreadsheet uses late binding (i.e. function definition is determined at evaluation time). At parse time, the spreadsheet stores the function name (e.g. "Cube") in the parsed expression (e.g. new FunctionExpression("Cube",..)). At evaluation time, the spreadsheet looks up the function definition in the table of Custom Functions and evaluates the function call using the function definition stored in the table of Custom Functions. However in Spread, there is no built in method to determine whether or not a parsed expression contains a Custom Function. Hence, we'll have to create our own method to determine if the function in a cell is a Custom Function or not. This blog explains an approach to first determine if the function is a Custom Function and if it is then how to remove those Custom Functions while retaining the cell value. Method to check if the formula applied contains Custom Function.
bool ContainsCustomFunction(Expression expr)
{
if (expr is FunctionExpression)
{
FunctionExpression funcExpr = (FunctionExpression)expr;
if (funcExpr.Function == null)
{
return true;
}
for (int i = 0; i < funcExpr.ArgCount; i++)
{
if (ContainsCustomFunction(funcExpr.GetArg(i)))
{
return true;
}
}
}
else if (expr is BinaryOperatorExpression)
{
BinaryOperatorExpression operExpr = (BinaryOperatorExpression)expr;
if (ContainsCustomFunction(operExpr.Arg0))
{
return true;
}
if (ContainsCustomFunction(operExpr.Arg1))
{
return true;
}
}
else if (expr is UnaryOperatorExpression)
{
UnaryOperatorExpression operExpr = (UnaryOperatorExpression)expr;
if (ContainsCustomFunction(operExpr.Arg))
{
return true;
}
}
else if (expr is ParenthesesExpression)
{
ParenthesesExpression parenExpr = (ParenthesesExpression)expr;
if (ContainsCustomFunction(parenExpr.Arg))
{
return true;
}
}
return false;
}
We can then use this method (i.e. ContainsCustomFunction) to determine which formulas should be removed.
SheetView sheet = fpSpread1.ActiveSheet;
DefaultSheetDataModel.DefaultSheetDataModelEnumerator enumerator = ((DefaultSheetDataModel)sheet.Models.Data).EnumFormulas;
for (int r = enumerator.NextNonEmptyRow(-1); r != -1; r = enumerator.NextNonEmptyRow(r))
{
for (int c = enumerator.NextNonEmptyColumnInRow(r, -1); c != -1; c = enumerator.NextNonEmptyColumnInRow(r, c))
{
Expression expr = sheet.GetExpression(r, c);
if (ContainsCustomFunction(expr))
{
sheet.Cells[r, c].Formula = "";
}
}
}
With this, we come to the end of this blog. You may download the samples for detailed implementation of the same. DownloadSample_CS DownloadSample_VB
MESCIUS inc.