Custom Formula in Windows Forms Spreadsheet

23 Sep 2020 / 1 minute to read

Spreadsheet allows you to add custom formulas into its function library. You can add the custom formula into the Spreadsheet by using the AddFunction method of FormulaEngine,

spreadsheet.WorkbookLoaded += spreadsheet_WorkbookLoaded;

void spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{

  foreach (var grid in args.GridCollection)
    AddCustomFormula(grid); 
  
  //Computing the formula at runtime
   var range = spreadsheet.ActiveSheet.Range["B2"];
   spreadsheet.ActiveGrid.SetCellValue(range,"=FindLength(Sample)");
         
}  

private void AddCustomFormula(SpreadsheetGrid grid)
{

  // Add a formula named FindLength to the Library.
   grid.FormulaEngine.AddFunction("FindLength", new FormulaEngine.LibraryFunction(ComputeLength));      
}    

//Implementation of formula
    
public string ComputeLength(string range)
{

  //Used to calculate the length of the string
    return range.Length.ToString();
}