﻿ Custom Functions | Documents for Excel .NET Edition | GCDocuments
Documents for Excel .NET Edition | GCDocuments
Features / Custom Functions
In This Topic
Custom Functions
In This Topic

GcExcel provides support for adding custom functions, thus enabling users to implement custom arithmetic logic to spreadsheets. These functions run extremely fast, can make web service calls, look similar to the native Excel functions, and can be used across all Excel platforms including major operating systems (Windows, Mac, Mobile OS and Office: both online and offline).

For instance, you can use company’s proprietary functions, apply a nested formula with custom functions, or use a combination of standard built-in functions in order to handle complex spreadsheet calculations.

To implement custom functions in GcExcel .NET, you need to create a derived class from the CustomFunction class and declare the custom function in the new class along with the function name, return type, and parameters.

You can also use custom objects in custom functions as demonstrated by the last example in this topic. If one parameter of overloaded Parameter method is set to FunctionValueType.Object and acceptCustomObjects is set toTrue, custom objects can be used. Similarly, if the return type is FunctionValueType.Object, the formula can return custom objects.

### Using Code

• Step 1: Define a custom function
• Step 2: Register the custom function in your worksheet using the AddCustomFunction() method
• Step 3: Implement the custom function

Shared below are some examples of custom functions that can be created and used to perform complex calculation tasks:

### Example 1: Conditional Sum Function

Refer to the following example code to create and use custom conditional sum function in your spreadsheet. This function can sum cell values based on specific display format or style (like cells with interior color as red).

C#
Copy Code
```// Step 1- Defining custom function: MyConditionalSum
// Creating a new class MyConditionalSumFunctionX by inheriting the CustomFunction class
public class MyConditionalSumFunctionX : CustomFunction
{
public MyConditionalSumFunctionX() : base("MyConditionalSum", FunctionValueType.Number, CreateParameters())
{
}
private static Parameter[] CreateParameters()
{
Parameter[] parameters = new Parameter[254];
for (int i = 0; i < 254; i++)
{
parameters[i] = new Parameter(FunctionValueType.Object, true);
}
return parameters;
}
public override object Evaluate(object[] arguments, ICalcContext context)
{
double sum = 0d;
foreach (var argument in arguments)
{
foreach (var item in Enumerate(argument))
{
if (item is CalcError)
{
return item;
}
if (item is double)
{
sum += (double)item;
}
}
}
return sum;
}
private static IEnumerable<object> Enumerate(object obj)
{
if (obj is IEnumerable<object>)
{
foreach (var item in obj as IEnumerable<object>)
{
foreach (var item2 in Enumerate(item))
{
yield return item2;
}
}
}
else if (obj is object[,])
{
var array = obj as object[,];
int rowCount = array.GetLength(0);
int colCount = array.GetLength(1);
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
yield return array[i, j];
}
}
}
else if (obj is CalcReference)
{
foreach (var item in Enumerate(obj as CalcReference))
{
yield return item;
}
}
yield return obj;
}
private static IEnumerable<object> Enumerate(CalcReference reference)
{
foreach (var range in reference.GetRanges())
{
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
if (range.Cells[i, j].DisplayFormat.Interior.Color == System.Drawing.Color.Red)
{
yield return range.Cells[i, j].Value;
}
}
}
}
}
}```
C#
Copy Code
```// Step2: Register the custom function using AddCustomFunction() method
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

// Step3- Implement the Custom Function
worksheet.Range["A1:A10"].Value = new object[,] { { 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7 }, { 8 }, { 9 }, { 10 } };
IFormatCondition cellValueRule = worksheet.Range["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5) as IFormatCondition;
cellValueRule.Interior.Color = System.Drawing.Color.Red;
// Sum cells value which display format interior color are red.
worksheet.Range["C1"].Formula = "=MyConditionalSum(A1:A10)";
// Range["C1"]'s value is 40.
var result = worksheet.Range["C1"].Value;
// Display result in cell E2
worksheet.Range["E2"].Value = result;```

### Example 2: Custom Concatenation Function

Refer to the following example code to create and use custom concatenation function in your spreadsheet.

C#
Copy Code
```// Step 1- Defining custom function: MyConcatenate
// Creating a new class MyConcatenateFunctionX by inheriting the CustomFunction class
public class MyConcatenateFunctionX : CustomFunction
{
public MyConcatenateFunctionX() : base("MyConcatenate", FunctionValueType.Text, CreateParameters())
{
}
private static Parameter[] CreateParameters()
{
Parameter[] parameters = new Parameter[254];
for (int i = 0; i < 254; i++)
{
parameters[i] = new Parameter(FunctionValueType.Variant);
}
return parameters;
}
public override object Evaluate(object[] arguments, ICalcContext context)
{
StringBuilder sb = new StringBuilder();
string result = string.Empty;
foreach (var argument in arguments)
{
if (argument is CalcError)
{
return argument;
}
if (argument is string || argument is double)
{
sb.Append(argument);
}
}
return sb.ToString();
}
}```
C#
Copy Code
```// Step2: Register the custom function using AddCustomFunction() method
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

// Step3- Implement the Custom Function
worksheet.Range["A1"].Formula = "=MyConcatenate(\"I\", \" \", \"work\", \" \", \"with\", \" \", \"GcExcel\", \".\")";
worksheet.Range["A2"].Formula = "=MyConcatenate(A1, \"Documents.\")";
// Value of cell A1 is "I work with GcExcel."
var resultA1 = worksheet.Range["A1"].Value;
// Display result in cell C1
worksheet.Range["C1"].Value = resultA1;
// Value of cell A2 is "I work with GcExcel Documents."
var resultA2 = worksheet.Range["A2"].Value;
// Display result in cell C2
worksheet.Range["C2"].Value = resultA2;```

### Example 3: Merged Range Function

Refer to the following example code to create and use custom merged range function in your spreadsheet.

C#
Copy Code
```// Step 1- Defining custom function: MyIsMergedRange
// Creating a new class MyIsMergedRangeFunctionX by inheriting the CustomFunction class
public class MyIsMergedRangeFunctionX : CustomFunction
{
public MyIsMergedRangeFunctionX()
: base("MyIsMergedRange", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Object, true) })
{
}
public override object Evaluate(object[] arguments, ICalcContext context)
{
if (arguments[0] is CalcReference)
{
IEnumerable<IRange> ranges = (arguments[0] as CalcReference).GetRanges();

foreach (var range in ranges)
{
return range.MergeCells;
}
}
return false;
}
}```
C#
Copy Code
```// Step2: Register the custom function using AddCustomFunction() method
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

// Step3- Implement the Custom Function
worksheet.Range["A1:B2"].Merge();
worksheet.Range["C1"].Formula = "=MyIsMergedRange(A1)";
worksheet.Range["C2"].Formula = "=MyIsMergedRange(H2)";
//A1 is a merged cell, Range["C1"]'s value is true.
var resultC1 = worksheet.Range["C1"].Value;
// Display result in cell D1
worksheet.Range["D1"].Value = resultC1;
//H2 is not a merged cell, Range["C2"]'s value is false.
var resultC2 = worksheet.Range["C2"].Value;
// Display result in cell D2
worksheet.Range["D2"].Value = resultC2;```

### Example 4: Error Detection Function

Refer to the following example code to create and use custom error detection function in your spreadsheet.

C#
Copy Code
```// Step 1- Defining custom function: MyIsError
// Creating a new class MyIsErrorFunctionX by inheriting the CustomFunction class
public class MyIsErrorFunctionX : CustomFunction
{
public MyIsErrorFunctionX()
: base("MyIsError", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Variant) })
{
}
public override object Evaluate(object[] arguments, ICalcContext context)
{
if (arguments[0] is CalcError)
{
if ((CalcError)arguments[0] != CalcError.None && (CalcError)arguments[0] != CalcError.GettingData)
{
return true;
}
else
{
return false;
}
}
return false;
}
}```
C#
Copy Code
```// Step2: Register the custom function using AddCustomFunction() method
var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

// Step3: Implement the custom function
worksheet.Range["A1"].Value = CalcError.Num;
worksheet.Range["A2"].Value = 100;
worksheet.Range["B1"].Formula = "=MyIsError(A1)";
worksheet.Range["B2"].Formula = "=MyIsError(A2)";
// Range["B1"]'s value is true.
var resultB1 = worksheet.Range["B1"].Value;
// Display Result in cell C1
worksheet.Range["C1"].Value = resultB1;
// Range["B2"]'s value is false.
var resultB2 = worksheet.Range["B2"].Value;
// Display Result in cell C2
worksheet.Range["C2"].Value = resultB2;```

### Example 5: Greatest Common Division Function using Custom Objects

Refer to the following example code to create and use BigInteger function to calculate greatest common division.

C#
Copy Code
```// Step 1.1- Defining custom function: BigIntegerMultiplyFunction
internal class BigIntegerMultiplyFunction : CustomFunction
{
public BigIntegerMultiplyFunction() : base("BIG.INTEGER.MULT", FunctionValueType.Object, new[]
{
new Parameter(FunctionValueType.Text),
new Parameter(FunctionValueType.Text)
})
{
}

public override object Evaluate(object[] arguments, ICalcContext context)
{
if (!(arguments[0] is string) || !(arguments[1] is string))
{
return CalcError.Value;
}
var leftNumber = (string)arguments[0];
var rightNumber = (string)arguments[1];
try
{
return BigInteger.Parse(leftNumber) * BigInteger.Parse(rightNumber);
}
catch (FormatException)
{
return CalcError.Value;
}
catch (ArgumentException)
{
return CalcError.Value;
}
}

}
// Step 1.2- Defining custom function: BigIntegerPowFunction
internal class BigIntegerPowFunction : CustomFunction
{
public BigIntegerPowFunction() : base("BIG.INTEGER.POW", FunctionValueType.Object, new[]
{
new Parameter(FunctionValueType.Text),
new Parameter(FunctionValueType.Number)
})
{
}

public override object Evaluate(object[] arguments, ICalcContext context)
{
if (!(arguments[0] is string) || !(arguments[1] is double))
{
return CalcError.Value;
}
var number = (string)arguments[0];
var exp = (double)arguments[1];
if (exp > int.MaxValue || exp < int.MinValue)
{
return CalcError.Value;
}
var iExp = Convert.ToInt32(exp);
try
{
return BigInteger.Pow(BigInteger.Parse(number), iExp);
}
catch (FormatException)
{
return CalcError.Value;
}
catch (ArgumentException)
{
return CalcError.Value;
}
}
}
// Step 1.3- Defining custom function: GreatestCommonDivisionFunction
internal class GreatestCommonDivisionFunction : CustomFunction
{
public GreatestCommonDivisionFunction() : base("BIG.INTEGER.GCD", FunctionValueType.Object, new[] {
new Parameter(FunctionValueType.Object, false, true),
new Parameter(FunctionValueType.Object, false, true)
})
{
}

public override object Evaluate(object[] arguments, ICalcContext context)
{
if (!(arguments[0] is BigInteger) || !(arguments[1] is BigInteger))
{
return CalcError.Value;
}
var leftNumber = (BigInteger)arguments[0];
var rightNumber = (BigInteger)arguments[1];
try
{
return BigInteger.GreatestCommonDivisor(leftNumber, rightNumber);
}
catch (ArgumentException)
{
return CalcError.Value;
}

}
}```
C#
Copy Code
```//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

try
{
// Step2.1: Register the custom function using AddCustomFunction() method
}
catch (Exception)
{
} // End Try
try
{
// Step2.2: Register the custom function using AddCustomFunction() method
}
catch (Exception)
{
} // End Try
try
{
// Step2.3: Register the custom function using AddCustomFunction() method
}
catch (Exception)
{
} // End Try

// Use BigInteger to calculate results
IWorksheet worksheet = workbook.ActiveSheet;
// Step3- Implement the Custom Function
worksheet.Range["A1"].Value = "154382190 ^ 3 = ";
worksheet.Range["A2"].Value = "1643590 * 166935 = ";
worksheet.Range["A3"].Value = "Greatest common division = ";
worksheet.Range["B1"].Formula = "=BIG.INTEGER.POW(\"154382190\", 3)";
worksheet.Range["B2"].Formula = "=BIG.INTEGER.MULT(\"1643590\", \"166935\")";
worksheet.Range["B3"].Formula = "=BIG.INTEGER.GCD(B1,B2)";

// Arrange
worksheet.Columns[0].AutoFit();
worksheet.Columns[1].ColumnWidth = worksheet.Range["B1"].Text.Length + 1;

//save to a pdf file
workbook.Save("customobjectincustomfunction.pdf");```