Using Custom Functions with an Excel API in .NET Applications

January 25 2022
0 Comments

Using custom functions to perform Excel calculations offers a greater extensibility within your spreadsheet. For example, you may need to perform calculations using a deeply nested formula, company proprietary formulas from your finance team, or a combination of standard functions.

Yes, you can add two cells using a built-in function; however, suppose you want to concatenate string values of two cells. Additionally, you may need to calculate the sum of those cells in a range. In such situations, calculations cannot be handled using a standard built-in function. You’ll need to write a custom function or a user-defined function.

Advantages of custom functions

They run across all Excel platforms (Win, Mac, Mobile, Office-online)
They run fast
The look and feel are like native Excel functions (e.g., formula tips)
They can make web service calls
They can run offline if they don’t depend on the web
They can run even when the workbook is unattended

While built-in functions may be faster and better in memory usage, custom functions can help you add more extensibility to your Excel sheet's data calculations.

Custom functions in GrapeCity Documents for Excel

GrapeCity Documents for Excel (GcExcel) supports custom functions for spreadsheets in .NET 6 targeted applications.

Get started with custom functions:

Custom Functions Help .NET | Custom Functions Help Java

This tutorial solves a use case where using a custom function would be beneficial. This article will guide you through the steps of solving the problem in a .NET Core application.

Use cases for custom functions

In this example, we'll compare a family’s monthly income vs. monthly expenses. The spreadsheet calculates percentage of income that is spent on household expenses. These calculations are repeated every month with data being replaced with the next month's information.

It can be a challenge to analyze data when the list of family expenses is large, or if the data spans across multiple sheets. It can become difficult to scan through all rows to find out the highest expenses.

Suppose a person wants to analyze monthly expenses to know the highest expenditure. This can be easy to solve using a standard function. We can use =MAX(B11:B23) function, which will give the highest expense for a month.

Drilling into the data, say a person wants to explore how they can reduce current expenses. The highest two expenses that month will need to be understood. You may be able to calculate this using some combinations of standard in-built functions. However, to calculate the second highest value, there may be some coding involved. In this case, it's easier to calculate using a custom function.

How to add a custom function to the spreadsheet to calculate the highest two expenses in a month in C# .NET and Java applications

Step 1:

In order to add the data for monthly income and monthly expenses, follow the getting started in .NET and Java steps to create a basic spreadsheet with GcExcel. At the end of the blog, your spreadsheet will look like this: income

Simple Budget

Step 2:

Create a class that derives from CustomFunction class.

public class HighestValues : CustomFunction  
{  
}

Java

public class HighestValues extends CustomFunction {  
}


Step 3:

Within the class, initialize an instance of the custom function, with the name of the function, the return type, and parameters for the custom function.

public HighestValues(): base("HighestValues", FunctionValueType.Text, new Parameter[] { new Parameter(FunctionValueType.Object), new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) })  
{

}

Java

public HighestValues() {  
       super("HighestValues", FunctionValueType.Text, new Parameter[]{new Parameter(FunctionValueType.Object), new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number)});  
   }

Here, the name of the custom function is Highest Values. The return type for the function would be the list of item names for the expenses, so the return type would be Text value. This function will receive the parameter values for calculating the highest two values.

Step 4:

We’ll define the Evaluate function to find two highest values. This function performs some validations, such as the length of array received and the row, col values in it. Then, it adds the given array to a list of Temp objects (a class that holds the text and number values of range of cells). This list sorts the array and returns the highest two numbers.

public override object Evaluate(object[] arguments, ICalcContext context)  
   {  
       if (arguments.Length < 3)  
       {  
           return CalcError.Value;  
       }

       var values = arguments[0];  
       if (values is IEnumerable<object>)  
       {  
           values = (values as IEnumerable<object>).FirstOrDefault();  
       }  
       object[,] array = values as object[,];  
       if (array == null)  
       {  
           return CalcError.Value;  
       }  
       int rowCount = array.GetLength(0);  
       int colCount = array.GetLength(1);  
       if (rowCount <= 0 || colCount <= 0)  
       {  
           return CalcError.Value;  
       }  
       int resultCol = (int)(double)arguments[1] - 1;  
       if (resultCol < 0 || resultCol >= colCount)  
       {  
           return CalcError.Num;  
       }

       int numberCol = (int)(double)arguments[2] - 1;  
       if (numberCol < 0 || resultCol >= colCount)  
       {  
           return CalcError.Num;  
       }

       List<temp> list = new List<temp>();  
       for (int i = 0; i < rowCount; i++)  
       {  
           string text = array[i, resultCol]?.ToString();  
           double number = array[i, numberCol] is double ? (double)array[i, numberCol] : 0;  
           list.Add(new temp(text, number));  
       }  
       list.Sort((x, y) =>  
       {  
           if (x.Number > y.Number)  
           {  
               return -1;  
           }  
           else if (x.Number == y.Number)  
           {  
               return 0;  
           }  
           else  
           {  
               return 1;  
           }  
       });  
       string result = null;  
       int count = Math.Min(list.Count, 2);  
       for (int i = 0; i < count; i++)  
       {  
           if (result != null)  
           {  
               result += ",";  
           }  
           result += list[i].Text;  
       }

       return result;  
   }  
private class temp  
   {  
       public string Text;  
       public double Number;

       public temp(string text, double number)  
       {  
           this.Text = text;  
           this.Number = number;  
       }  
   }

Java

public Object evaluate(Object[] arguments, ICalcContext context) {  
       if (arguments.length < 3) {  
           return CalcError.Value;  
       } else {  
           Object values = arguments[0];  
           if (values instanceof Iterable) {  
               Iterator var4 = ((Iterable)((Iterable)values)).iterator();  
               if (var4.hasNext()) {  
                   Object x = var4.next();  
                   values = x;  
               }  
           }

           Object[][] array = values instanceof Object[][] ? (Object[][])((Object[][])values) : (Object[][])null;  
           if (array == null) {  
               return CalcError.Value;  
           } else {  
               int rowCount = array.length;  
               int colCount = array.length == 0 ? 0 : array[0].length;  
               if (rowCount > 0 && colCount > 0) {  
                   int resultCol = ((Double)((Double)arguments[1])).intValue() - 1;  
                   if (resultCol >= 0 && resultCol < colCount) {  
                       int numberCol = ((Double)((Double)arguments[2])).intValue() - 1;  
                       if (numberCol >= 0 && resultCol < colCount) {  
                           ArrayList<HighestValues.temp> list = **new** ArrayList();

                           for(int i = 0; i < rowCount; ++i) {  
                               String text = array[i][resultCol] == null ? null : array[i][resultCol].toString();  
                               double number = array[i][numberCol] instanceof Double ? (Double)array[i][numberCol] : 0.0D;  
                               list.add(new HighestValues.temp(text, number));  
                           }

                           Collections.sort(list, (xx, y) -> {  
                               if (xx.Number > y.Number) {  
                                   return -1;  
                               } else {  
                                   return xx.Number == y.Number ? 0 : 1;  
                               }  
                           });  
                           String result = null;  
                           int count = Math.min(list.size(), 2);

                           for(int i = 0; i < count; ++i) {  
                               if (result != null) {  
                                   result = result + ",";  
                               } else {  
                                   result = "";  
                               }

                               result = result + ((HighestValues.temp)list.get(i)).Text;  
                           }

                           return result;  
                       } else {  
                           return CalcError.Num;  
                       }  
                   } else {  
                       return CalcError.Num;  
                   }  
               } else {  
                   return CalcError.Value;  
               }  
           }  
       }

Step 5:

In static void Main[] function, call the AddCustomFunction() method. Create new GcExcel workbook and open the Excel spreadsheet in the workbook.

Workbook.AddCustomFunction(new HighestValues());  
var workbook = new Workbook();  
workbook.Open("SimpleBudget.xlsx");

Java

Workbook.AddCustomFunction(new HighestValues());  
Workbook workbook = new Workbook();  
workbook.open("SimpleBudget.xlsx");

Step 6:

Call the HighestValues custom function and pass the range of cells from which highest values are needed. Then collect the result in a variable and set the result in a cell.

workbook.Worksheets[0].Range["B25"].Formula = "HighestValues(B11:C23, 1, 2)";   
var result = workbook.Worksheets[0].Range["B25"].Value; int rowIndex, columnIndex;   
GrapeCity.Documents.Excel.CellInfo.CellNameToIndex("C25", out rowIndex, out columnIndex);   
workbook.Worksheets[0].Range[rowIndex, columnIndex].Value = result;

Java

Object result = workbook.getWorksheets().get(0).getRange("B25").getValue();  
int rowIndex, columnIndex;  
int[] index = com.grapecity.documents.excel.CellInfo.CellNameToIndex("C25");  
columnIndex = index[1];  
rowIndex = index[0];  
workbook.getWorksheets().get(0).getRange(rowIndex, columnIndex).setValue(result);


Step 7:

Save your workbook.

workbook.Save("SimpleBudget.xlsx");

Java

workbook.save("SimpleBudget.xlsx");

Run the application and you will see the highest expenses collected by the custom function in cell C25.

budget

Note: MS Excel does not know any of our custom functions, so after saving to Excel, a #NAME error will be shown in the cell that contains the formula (B25).

Download the complete sample .NET

Download the complete sample Java

How will you use this feature? Leave your comment below!