[]
GcExcel 支持添加自定义函数，允许用户在电子表格中实现专属计算逻辑。自定义函数性能高效，可调用 Web 服务，使用方式与原生 Excel 函数一致。支持所有主流平台，包括 Windows、Mac、移动端及 Office 在线/离线版本。
您可以用自定义函数实现公司专有流程、嵌套组合公式，或与内置函数混用以解决复杂计算问题。
在 GcExcel 中实现自定义函数，需要继承 CustomFunction 类，并在新类中定义函数的名称、返回类型及参数。
自定义函数可支持自定义对象。如示例5所示，当重载的 Parameter 方法的参数类型为 FunctionValueType.Object，并且 acceptCustomObjects 设置为 true 时，函数可接收自定义对象。同样，若返回类型为 FunctionValueType.Object，函数也可返回自定义对象。
创建自定义函数，步骤如下：
定义自定义函数：创建一个派生自 CustomFunction 的新类，并实现所需的逻辑。
注册自定义函数：通过 AddCustomFunction() 方法将自定义函数注册到工作表。
实现函数功能：编写自定义函数的具体实现逻辑。
在将自定义函数导出为 .xlsx 文件时，由于 Excel 无法识别这些函数，包含自定义函数的单元格会显示 #NAME 错误。
Custom functions in the same column store resultant value as cache. Hence, when a custom function in a column is called subsequently with previous parameter, custom function uses the cached value instead of calculating it again. This feature helps in optimizing performance especially in case of repetitive use of the custom function in a single column.
However, to control this caching behavior of custom functions, GcExcel.Net provides IsVolatile property in the class inherited from CustomFunction class. The property lets you choose whether to recalculate a custom function for a column having same parameters every time or use the cached result. The default value of this property is false, which means custom function applied on a single column maintains its own cache and reuses it on a repeated call.
Following example demonstrates how to create a custom function for generating GUID. To generate a unique GUID every time, custom function should not be using cache. Hence, example code sets the IsVolatile property to true, so that a new GUID is generated on every call.
// Creating a new class GeneralID by inheriting the CustomFunction class
internal class GeneralID : CustomFunction
{
public GeneralID() : base("GeneralID", FunctionValueType.Object)
{
// set IsVolatile to true to avoid using cached value
this.IsVolatile = true;
}
public override object Evaluate(object[] arguments, ICalcContext context)
{
return Guid.NewGuid().ToString("N");
}
}// Step2: Register the custom function using AddCustomFunction() method
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GeneralID());
IWorksheet worksheet = workbook.Worksheets[0];
// Step3- Implement the Custom Function
worksheet.Range["A1"].Formula = "GeneralID()";
var valueA1Before = worksheet.Range["A1"].Value;
Console.WriteLine(valueA1Before);
worksheet.Range["A2"].Formula = "GeneralID()";
// Observe A1's value has changed because it's not using cached value.
var valueA1After = worksheet.Range["A1"].Value;
Console.WriteLine(valueA1After);在使用自定义函数时，传递给函数的输入参数有可能包含错误值，例如 #DIV/0!、#N/A 、#VALUE!等。与MS Excel类似，默认情况下如果参数包含错误，函数不会被调用，错误会直接作为公式单元格的结果返回。
为了便于开发者灵活处理函数参数中的错误值，GcExcel在 CustomFunction 类中提供了 AcceptErrors 属性。将该属性设置为 true 时，即使输入参数存在错误，自定义函数依然会被调用。开发者可以在函数逻辑中接收、检测并根据需要处理错误输入。该属性的默认值为 false，即只要输入参数包含错误，自定义函数便不会被调用，而是直接返回该错误。
具体实现方式，请参见以下示例代码。
public class MyFunctionWithAcceptErrors : CustomFunction
{
public MyFunctionWithAcceptErrors(string funcName, bool acceptError)
: base(funcName, FunctionValueType.Text)
{
this._acceptErrors = acceptError;
}
private bool _acceptErrors = false;
override public bool AcceptErrors
{
get => _acceptErrors;
set => _acceptErrors = value;
}
public override object Evaluate(object[] arguments, ICalcContext context)
{
if (arguments == null || arguments.Length == 0)
{
return null;
}
StringBuilder result = new StringBuilder();
for (int i = 0; i < arguments.Length; i++)
{
if (arguments[i] is CalcError calcError)
{
result.Append(result.Length > 0 ? $", #{calcError}" : $"Exist errors: #{calcError}");
}
}
return result.Length > 0 ? result.ToString() : "No error";
}
}// 初始化工作薄。
var workbook = new GrapeCity.Documents.Excel.Workbook();
Excel.Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncCanAcceptErrors", true));
Excel.Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncNotAcceptErrors", false));
IWorksheet worksheet = workbook.ActiveSheet;
worksheet.Range["A1:B1"].Merge();
worksheet.Range["A7:B7"].Merge();
worksheet.Range["A1:B1"].Value = "Custom function that accepts errors";
worksheet.Range["A7:B7"].Value = "Custom function that does not accept errors";
worksheet.Range["A2, A8"].Value = "Formula";
worksheet.Range["B2, B8"].Value = "Result";
worksheet.Range["A1:B1, A7:B7, A2, A8, B2, B8"].Font.Bold = true;
worksheet.Range["A2, A8, B2, B8"].HorizontalAlignment = HorizontalAlignment.Center;
// B3的值为 “Exist errors: #Div0”。
worksheet.Range["A3"].Value = "=MyFuncCanAcceptErrors(1, 2, 1/0)";
worksheet.Range["B3"].Formula = "=MyFuncCanAcceptErrors(1, 2, 1/0)";
// B4的值为"Exist errors: #Value"。
worksheet.Range["A4"].Value = "=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)";
worksheet.Range["B4"].Formula = "=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)";
// B5的值为"Exist errors: #Name, #Num"。
worksheet.Range["A5"].Value = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)";
worksheet.Range["B5"].Formula = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)";
// B9的值是#DIV/0!错误。
worksheet.Range["A9"].Value = "=MyFuncNotAcceptErrors(1, 2, 1/0)";
worksheet.Range["B9"].Formula = "=MyFuncNotAcceptErrors(1, 2, 1/0)";
// B10的值是#VALUE!错误。
worksheet.Range["A10"].Value = "=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)";
worksheet.Range["B10"].Formula = "=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)";
// B11的值是#NAME?错误。
worksheet.Range["A11"].Value = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)";
worksheet.Range["B11"].Formula = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)";
worksheet.Range["A:B"].AutoFit();
worksheet.PageSetup.PrintHeadings = true;
worksheet.PageSetup.PrintGridlines = true;
worksheet.PageSetup.Orientation = PageOrientation.Landscape;
worksheet.PageSetup.PrintArea = "$A$1:$G$20";
// 保存为PDF文件。
workbook.Save("AcceptErrorsInCustomFunction.pdf");结果如下图所示：
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).
// 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;
}
}
}
}
}
}// Step2: Register the custom function using AddCustomFunction() method
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
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;Refer to the following example code to create and use custom concatenation function in your spreadsheet.
// 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();
}
}// Step2: Register the custom function using AddCustomFunction() method
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConcatenateFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
// Step3- Implement the Custom Function
worksheet.Range["A1"].Formula = "=MyConcatenate(\"I\", \" \", \"work\", \" \", \"with\", \" \", \"Excel\", \".\")";
worksheet.Range["A2"].Formula = "=MyConcatenate(A1, \"Documents.\")";
// Value of cell A1 is "I work with Excel."
var resultA1 = worksheet.Range["A1"].Value;
// Display result in cell C1
worksheet.Range["C1"].Value = resultA1;
// Value of cell A2 is "I work with Excel Documents."
var resultA2 = worksheet.Range["A2"].Value;
// Display result in cell C2
worksheet.Range["C2"].Value = resultA2;Refer to the following example code to create and use custom merged range function in your spreadsheet.
// 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;
}
}// Step2: Register the custom function using AddCustomFunction() method
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyIsMergedRangeFunctionX());
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;Refer to the following example code to create and use custom error detection function in your spreadsheet.
// 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;
}
}// Step2: Register the custom function using AddCustomFunction() method
var workbook = new Workbook();
Workbook.AddCustomFunction(new MyIsErrorFunctionX());
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;Refer to the following example code to create and use BigInteger function to calculate greatest common division.
// 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;
}
}
}//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
try
{
// Step2.1: Register the custom function using AddCustomFunction() method
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new BigIntegerPowFunction());
}
catch (Exception)
{
// Function was added
} // End Try
try
{
// Step2.2: Register the custom function using AddCustomFunction() method
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new BigIntegerMultiplyFunction());
}
catch (Exception)
{
// Function was added
} // End Try
try
{
// Step2.3: Register the custom function using AddCustomFunction() method
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GreatestCommonDivisionFunction());
}
catch (Exception)
{
// Function was added
} // 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");