Back To Razor Page Samples

Live Razor Page Samples

Custom Functions

This sample demonstrates creating your own custom functions in .NET and adding them to a workbook set. The worksheet formulas which utilize these custom functions are compatible with Microsoft Excel worksheet formulas which utilize VBA or XLL custom functions.
Start Sample
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.ComponentModel.DataAnnotations;

namespace Website.Pages.Support.Samples.RazorPages.Calculations
{
    public partial class CustomFunctionsModel : PageModel
    {
        [BindProperty]
        [StringLength(25)]
        [Display(Name = "Argument 1")]
        public string Arg1 { get; set; }

        [BindProperty]
        [StringLength(25)]
        [Display(Name = "Argument 2")]
        public string Arg2 { get; set; }

        public string Result { get; set; }

        public void OnGet()
        { }


        public IActionResult OnPost()
        {
            if (!ModelState.IsValid)
            {
                SetErrorMessage("Error occurred while running sample.  Please correct the errors shown below.");
                return Page();
            }

            // Create a new empty workbook. 
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

            // Add the custom function to the workbook set. 
            workbook.WorkbookSet.Add(MyAdd.MyAddSingleton);

            // Get the first sheet and its cells.
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
            SpreadsheetGear.IRange cells = worksheet.Cells;

            // Assign values and formulas to cells.
            cells["A1"].Value = Arg1;
            cells["B1"].Value = Arg2;
            cells["C1"].Formula = "=MYADD(A1,B1)";

            // Get automatically-calculated result from C1.
            Result = cells["C1"].Text;

            return Page();
        }
    }


    // A simple addition custom function. 
    public class MyAdd : SpreadsheetGear.CustomFunctions.Function
    {
        // Set to the one and only instance of MyAdd. 
        public static readonly MyAdd MyAddSingleton = new();

        // Add two numbers. 
        public override void Evaluate(SpreadsheetGear.CustomFunctions.IArguments arguments,
            SpreadsheetGear.CustomFunctions.IValue result)
        {
            // Verify that there are two arguments. 
            if (arguments.Count == 2)
                // Get the two arguments as numbers, and add them. 
                result.Number = arguments.GetNumber(0) + arguments.GetNumber(1);
            else
                // Return ValueError.Value. 
                result.Error = SpreadsheetGear.ValueError.Value;
        }

        // Singleton class - so make the constructor private. 
        private MyAdd()
            : base(
            // The name of the custom function. 
            "MYADD",
            // For a given set of inputs, this function always returns the same value. 
            SpreadsheetGear.CustomFunctions.Volatility.Invariant,
            // This function returns a number. 
            SpreadsheetGear.CustomFunctions.ValueType.Number)
        {
        }
    }
}

@page
@model CustomFunctionsModel
@{ Layout = "_SamplesLayout";}

<div class="row">
  <div class="col-md-12 col-lg-8 mb-3">
    <div class="card">
      <h2 class="card-header">Custom Function Calculator</h2>
      <div class="card-body">
        <form asp-page="CustomFunctions" method="post">
          <div class="row">
            <div class="col-auto"><label class="col-form-label">Enter Values for MYADD(...):</label></div>
            <div class="col"><input asp-for="Arg1" type="text" class="form-control" /></div>
            <div class="col"><input asp-for="Arg2" type="text" class="form-control" /></div>
            <div class="col"><button class="btn btn-primary"><i class="fa-fw fas fa-play-circle"></i> Calculate</button></div>
          </div>
          <div>
            <span asp-validation-for="Arg1" class="form-control-feedback d-block"></span>
            <span asp-validation-for="Arg2" class="form-control-feedback d-block"></span>
          </div>

          @if (Model.Result != null)
          {
            <div class="alert alert-info mt-3">
              Result calculated from MYADD(...) = <b>@Model.Result</b>
            </div>
          }
        </form>
      </div>
    </div>
  </div>

  <div class="col-md-12 col-lg-4">
    <div class="card">
      <div class="card-body">
        This sample demonstrates the use of a custom function named MYADD. This function was created by extending the
        SpreadsheetGear.CustomFunctions.Function class. This is a simple example to show the SpreadsheetGear custom function
        framework.
      </div>
    </div>
  </div>
</div>