Add customize formula functions
To add customize function, use the FormulaExtension class. The following example that adds a function to make given string uppercase.
(Note: FormulaExtension is new interface available from version 0.8.8.2, it replaces the old customize function interface that was implemented via ReoScript integration)
unvell.ReoGrid.Formula.FormulaExtension.CustomFunctions["myUpper"] =
(cell, args) =>
{
if (args.Length == 0)
{
// this function needs at least one argument
return null;
}
return Convert.ToString(args[0]).ToUpper();
};
Test this function, input the formula =myUpper("hello"):
Press enter, the cell value will be changed to uppercase.
About FormulaExtension
The FormulaExtension class is a new class added from 0.8.8.2, it be designed to work with the core formula parser, and make customize function easier, even not using ReoScript integration.
ReoScript integration provides the powerful script language execution feature, but it is only available in Full functionality release package, which contains additional two libraries. By using FormulaExtension interface, even in the Standard functionality set (single DLL edition) can also have the ability to use customize functions.
The CustomFunctions property is defined as:
Dictionary<string, Func<ReoGridCell, object[], object>> CustomFunctions
Simplest function example
C#:
FormulaExtension.CustomFunctions["myFunc"] = (cell, args) => {
// function body goes here
return null;
};
VB.NET:
FormulaExtension.CustomFunctions("myFunc") = Function(cell, args)
' function body goes here
Return Nothing
End Function
Using delegate function
It’s also possible to use normal delegate function rather than lambda expression. To define a function:
C#:
static object MyFunc(ReoGridCell cell, params object[] arguments)
{
return null;
}
To make this static method available in formula as customize function:
FormulaExtension.CustomFunctions["myFunc"] = MyFunc;
VB.NET:
Shared Function MyFunc(cell As ReoGridCell, ParamArray args As Object()) As Object Return Nothing End Function
To make this shared method available in formula as customize function:
FormulaExtension.CustomFunctions("myFunc") = AddressOf MyFunc
Cell instance
The first argument is the cell instance that is the owner of the formula, this function now is called from the formula, for example:
worksheet["A1"] = "=myFunc()";
The A1 cell has a formula that calls the customize function “myFunc”, the function body in .NET code defined above will be invoked, the cell argument is the “A1” cell.
static object MyFunc(ReoGridCell cell, params object[] arguments)
{
MessageBox.Show(cell.Position.ToAddress()); // output: A1
return null;
}
Get worksheet instance from function
By getting the property Worksheet of cell instance to get worksheet instance, as well as the workbook instance from Workbook property of worksheet instance.
FormulaExtension.CustomFunctions["myFunc"] = (cell, args) =>
{
var worksheet = cell.Worksheet;
var workbook = worksheet.Workbook;
...
return null;
};
Limit the function scope only available in specified workbook or worksheet:
{
var worksheet = cell.Worksheet;
var workbook = worksheet.Workbook;
if (workbook != myWorkbook)
{
// not in valid workbook scope
return null;
}
}
Get function arguments
The second argument is the arguments passed from caller in formula.
FormulaExtension.CustomFunctions["myFunc"] = (cell, args) =>
{
if (args.Length < 2)
{
// this function requires at least two arguments
return null;
}
return null;
};
Data type in formula
There are 5 types of data used in formula calculation:
- Number (int, long, short, float, double)
- String (string and StringBuffer)
- Boolean
- DateTime
- Object
ReoGrid always use double as the number type in formula calculation. ReoGrid performs the following conversions before value passed into functions:
- int, long, short, float will be converted into double
- string and StringBuffer object will be converted into string
Numeric constants
Numeric constants used in formula will be initialized as double type, for example:
worksheet["A1"] = "=myFunc(10)";
The number 10 will be parsed and stored in memory as double type. The first argument in argument array will be double type.
FormulaExtension.CustomFunctions["myFunc"] = (cell, args) =>
{
if (args.Length < 1 || !(args[0] is double))
{
// this function requires a double value
return null;
}
...
};
Numeric variables
When the numeric value was given from a cell, it will be converted into double type. Create the following function:
FormulaExtension.CustomFunctions["myFunc"] = (cell, args) =>
{
double value = (double)args[0];
return value * 2;
};
Then use it in formula:
sheet["A1"] = (int)10; sheet["B1"] = "=myFunc(A1)";
There will no data type conversion error happen. (But be careful the argument length)
References in function
Cell reference
ReoGrid will get the value from referenced cell that is used in formula automatically, like the example of ‘Numeric variables’.
Range reference
If a range literal used in formula, ReoGrid will pass RangePosition struct into function, for example:
worksheet["G2:K3"] = new object[] { 1, 2, 5, 7, 8, 10, 12, 15, 16, 19};
worksheet["L2"] = "=CountEvenNumber(G2:K3)";
There is a range reference (G2:K3) that was used in the formula, it’s necessary to handle it manually inside customize function body. Following example function that counts the even numbers from given range:
FormulaExtension.CustomFunctions["CountEvenNumber"] = (cell, args) =>
{
if (args.Length < 1 || !(args[0] is RangePosition))
{
return null; // we need a range reference
}
RangePosition range = (RangePosition)args[0];
int count = 0;
// iterate over cells inside a range
cell.Worksheet.IterateCells(range, (r, c, inCell) => {
double value;
// try get a number from cell data
if (ReoGridCellUtility.TryGetNumberData(inCell.Data, out value) {
if ((value % 2) == 0) {
count++;
}
}
// continue iterate
return true;
});
return count;
};
Name reference
Name to cell
If a name reference to a cell, the cell’s value will be get and passed into the function automatically.
worksheet.DefineNamedRange("r1", "A1");
sheet["r1"] = (int)20;
sheet["B1"] = "=myFunc(r1)";
Name to range
If a name reference to a range used in formula, ReoGrid will find the range by the name firstly, then pass the RangePosition struct object, it could be handled like range reference.
worksheet.DefineNamedRange("evenRange", "G2:K3");
worksheet["evenRange"] = new object[] { 1, 2, 4, 6, 9, 11, 13, 14, 15, 17 };
worksheet["I2"] = "=CountEvenNumber(evenRange)";
AssertSame(worksheet["I2"], 4);
Customize name reference provider
It’s possible to add constant value that is used in formula but provided by .NET program. The following example adds two names that return different two constant values:
unvell.ReoGrid.Formula.FormulaExtension.NameReferenceProvider =
(cell, name) =>
{
if (name == "myName1")
return 10;
else if (name == "myName2")
return 20;
else
return null;
};
The NameReferenceProvider is defined as:
Func<ReoGridCell, string, object> NameReferenceProvider { get; set; }
Test the name 1, input =myName1 in cell:
The name returns value 10:
Input =myName2 in cell:
The name returns value 20:
Add mathematical constants
By using the customize name reference provider, it’s also possible to add some constants such as mathematical numbers, the following code defines a constant provider:
VB.NET:
Shared Function MathConstantProvider(cell As ReoGridCell, name As String) As Object
Select Case (name)
Case "PI"
Return Math.PI
Case "E"
Return Math.E
Case "MyConst"
Return 1.23456
End Select
Return Nothing
End Function
Apply this name reference provider:
FormulaExtension.NameReferenceProvider = AddressOf MathConstantProvider
And use the constant in formula:
worksheet("A1") = "=MyConst"
Result:
Combine customize function and customize name provider
Using customize name provider in a customize function is also supported. The name will be converted into value and passed into functions.
FormulaExtension.NameReferenceProvider = (cell, name) =>
{
switch (name)
{
case "Pi": return Math.PI;
case "E": return Math.E;
default: return null;
}
};
Use the name Pi in customize function:
worksheet["D3"] = "=Div100(Pi)"; AssertSame(worksheet["D3"], Math.PI / 100);
Add ReoScript language customize function
When the ReoScript module extension is enable, ReoScript script language could be used to handle the cell’s data, style, border, data formats and etc, it’s similar to the VBA in Excel. ReoScript supports that make .NET extension for script language, including call .NET functions in script.
ReoGrid supports that invoke ReoScript customize extension functions, before 0.8.8 version, this is the way to add customize functions.
Note: ReoScript language extension is only available in Full release package.
Add ReoScript function in C#
Add customize function to extend control:
workbook.Srm["sqrt"] = new NativeFunctionObject("sqrt", (ctx, owner, args) =>
{
if (args.Length < 1)
return NaNValue.Value;
else
return Math.Sqrt(ScriptRunningMachine.GetDoubleValue(args[0], 0));
});
Test:
var worksheet = workbook.CurrentWorksheet; worksheet[2, 1] = "=sqrt(400)";
Double click on cell to view its formula:
Customize function can also be used in script.
Customize functions in ReoScript context
Object ‘workbook’ is the global object which is available in both formula and script. (same as ‘window’ in JavaScript)
Run the code above once and use it in formula.
Press ‘enter’:
Run Script to add functions in C#
It is also possible to run the script to create ReoScript functions in C#, below is the ReoScript lambda expression used to create new function.
grid.RunScript("script.myfunc = data => '[' + data + ']';");