Formulas in ASP.NET MVC Spreadsheet control

20 Jan 2022 / 7 minutes to read

Formulas are used for calculating the data in a worksheet. You can refer the cell reference from same sheet or from different sheets.

Usage

You can set formula for a cell in the following ways,

Using the formula property from cell, you can set the formula or expression to each cell at initial load.
Set the formula or expression through data binding.
You can set formula for a cell by editing.
Using the updateCell method, you can set or update the cell formula.

User Defined Functions

The list of formulas supported in the spreadsheet is sufficient for most of your calculations. If not, you can add your own custom function using the addCustomFunction method. Use computeExpression method, if you want to compute any formula or expression.

The following code example shows the calculation of data using supported and custom formulas in the spreadsheet.

razor

formulaController.cs

@Html.EJS().Spreadsheet("spreadsheet").ShowSheetTabs(false).Created("created").ShowRibbon(false).Sheets(sheet =>
{
    sheet.Ranges(ranges =>
    {
        ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A2").Add();
    }).Rows(row =>
    {
        row.Height(40).CustomHeight(true).Cells(cell =>
        {
            cell.ColSpan(5).Value("Monthly Expense").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, VerticalAlign = VerticalAlign.Middle, TextAlign=TextAlign.Center, FontSize="15pt", FontStyle= FontStyle.Italic }).Add();
        }).Add();
        row.Height(30).Add();
        row.Index(11).Cells(cell =>
        {
            cell.Value("Totals").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, FontStyle= FontStyle.Italic }).Add();
            cell.Formula("=SUM(B3:B11)").Add();
            cell.Formula("=SUM(C3:C11)").Add();
            cell.Formula("=SUM(D3:D11)").Add();
        }).Add();
         row.Cells(cell =>
        {
            cell.Index(1).Value("Number of Categories").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
            cell.Formula("=COUNTA(A3:A11)").Index(3).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(1).Value("Average Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
            cell.Formula("=AVERAGE(B3:B11)").Index(3).Format("$#,##0").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(1).Value("Min Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
            cell.Formula("=MIN(B3:B11)").Index(3).Format("$#,##0").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(1).Value("Max Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign= TextAlign.Right }).Add();
            cell.Formula("=MAX(B3:B11)").Index(3).Format("$#,##0").Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(150).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
    }).Add();
}).Render()

<script>

    function created() {
        this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
        this.numberFormat('$#,##0', 'B3:D12');
        this.numberFormat('0%', 'E3:E12');

        // Adding custom function for calculating the percentage between two cells.
        this.addCustomFunction(calculatePercentage, 'PERCENTAGE');
        // Calculate percentage using custom added formula in E12 cell.
        this.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
    }

    // Custom function to calculate percentage between two cell values.
    function calculatePercentage(firstCell,secondCell) {
       return (firstCell) / (secondCell);
    }
</script>

public IActionResult Index()
        {
           List<object> data = new List<object>()
            {
               new { Category= "Household Utilities",  MonthlySpend= "=C3/12",  AnnualSpend= "3000",  LastYearSpend= "3000",  PercentageChange= "=C3/D3"},
               new { Category= "Food",  MonthlySpend= "=C4/12",  AnnualSpend= "2500",  LastYearSpend= "2250",  PercentageChange= "=C4/D4"},
               new { Category= "Gasoline",  MonthlySpend= "=C5/12",  AnnualSpend= "1500",  LastYearSpend= "1200",  PercentageChange= "=C5/D5"},
               new { Category= "Clothes",  MonthlySpend= "=C6/12",  AnnualSpend= "1200",  LastYearSpend= "1000",  PercentageChange= "=C6/D6"},
               new { Category= "Insurance",  MonthlySpend= "=C7/12",  AnnualSpend= "1500",  LastYearSpend= "1500",  PercentageChange= "=C7/D7"},
               new { Category= "Taxes",  MonthlySpend= "=C8/12",  AnnualSpend= "3500",  LastYearSpend= "3500",  PercentageChange= "=C8/D8"},
               new { Category= "Entertainment",  MonthlySpend= "=C9/12",  AnnualSpend= "2000",  LastYearSpend= "2250",  PercentageChange= "=C9/D9"},
               new { Category= "Vacation",  MonthlySpend= "=C10/12",  AnnualSpend= "1500",  LastYearSpend= "2000",  PercentageChange= "=C10/D10"},
               new { Category= "Miscellaneous",  MonthlySpend= "=C11/12",  AnnualSpend= "1250",  LastYearSpend= "1558",  PercentageChange= "=C11/D11"},
            };
            ViewBag.DefaultData = data;
            return View();
        }

Formula bar

Formula bar is used to edit or enter cell data in much easier way. By default, the formula bar is enabled in the spreadsheet. Use the showFormulaBar property to enable or disable the formula bar.

Named Ranges

You can define a meaningful name for a cell range and use it in the formula for calculation. It makes your formula much easier to understand and maintain. You can add named ranges to the Spreadsheet in the following ways,

Using the definedNames collection, you can add multiple named ranges at initial load.
Use the addDefinedName method to add a named range dynamically.
You can remove an added named range dynamically using the removeDefinedName method.
Select the range of cells, and then enter the name for the selected range in the name box.

The following code example shows the usage of named ranges support.

razor

definedNameController.cs

@Html.EJS().Spreadsheet("spreadsheet").ShowSheetTabs(false).BeforeDataBound("beforeDataBound").Created("created").ShowRibbon(false).Sheets(sheet =>
{
    sheet.Name("Budget Details").Ranges(ranges =>
    {
        ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A2").Add();
    }).Rows(row =>
    {
        row.Height(40).CustomHeight(true).Cells(cell =>
        {
            cell.ColSpan(5).Value("Monthly Expense").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, VerticalAlign = VerticalAlign.Middle, TextAlign = TextAlign.Center, FontSize = "15pt", FontStyle = FontStyle.Italic }).Add();
        }).Add();
        row.Height(30).Add();
        row.Index(11).Cells(cell =>
        {
            cell.Value("Totals").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, FontStyle = FontStyle.Italic }).Add();
            cell.Formula("=SUM(MonthlySpendings)").Add();
            cell.Formula("=SUM(AnnualSpendings)").Add();
            cell.Formula("=SUM(LastYearSpendings)").Add();
            cell.Formula("=C12/D12").Add();
        }).Add();
         row.Cells(cell =>
        {
            cell.Index(1).Value("Number of Categories").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
            cell.Formula("=COUNTA(=C12/D12)").Index(3).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(1).Value("Average Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
            cell.Formula("=AVERAGE(MonthlySpendings)").Index(3).Format("$#,##0").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(1).Value("Min Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
            cell.Formula("=MIN(MonthlySpendings)").Index(3).Format("$#,##0").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(1).Value("Max Spend").ColSpan(2).Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
            cell.Formula("=MAX(MonthlySpendings)").Index(3).Format("$#,##0").Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(150).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
    }).Add();
}).DefinedNames(definedName => {
       definedName.Name("Categories").RefersTo("=Budget Details!A3:A11").Add();
       definedName.Name("MonthlySpendings").RefersTo("=Budget Details!B3:B11").Add();
     definedName.Name("AnnualSpendings").RefersTo("=Budget Details!C3:C11").Add();
   }).Render()

<script>

    function created() {
        // Removing the unwanted `PercentageChange` named range
        this.removeDefinedName('PercentageChange', '');

        this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
        this.numberFormat('$#,##0', 'B3:D12');
        this.numberFormat('0%', 'E3:E12');
    }

    function beforeDataBound() {
        // Adding name dynamically for `last year spending` and `percentage change` ranges.
        this.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
        this.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
    }
</script>

public IActionResult Index()
        {
           List<object> data = new List<object>()
            {
               new { Category= "Household Utilities",  MonthlySpend= "=C3/12",  AnnualSpend= "3000",  LastYearSpend= "3000",  PercentageChange= "=C3/D3"},
               new { Category= "Food",  MonthlySpend= "=C4/12",  AnnualSpend= "2500",  LastYearSpend= "2250",  PercentageChange= "=C4/D4"},
               new { Category= "Gasoline",  MonthlySpend= "=C5/12",  AnnualSpend= "1500",  LastYearSpend= "1200",  PercentageChange= "=C5/D5"},
               new { Category= "Clothes",  MonthlySpend= "=C6/12",  AnnualSpend= "1200",  LastYearSpend= "1000",  PercentageChange= "=C6/D6"},
               new { Category= "Insurance",  MonthlySpend= "=C7/12",  AnnualSpend= "1500",  LastYearSpend= "1500",  PercentageChange= "=C7/D7"},
               new { Category= "Taxes",  MonthlySpend= "=C8/12",  AnnualSpend= "3500",  LastYearSpend= "3500",  PercentageChange= "=C8/D8"},
               new { Category= "Entertainment",  MonthlySpend= "=C9/12",  AnnualSpend= "2000",  LastYearSpend= "2250",  PercentageChange= "=C9/D9"},
               new { Category= "Vacation",  MonthlySpend= "=C10/12",  AnnualSpend= "1500",  LastYearSpend= "2000",  PercentageChange= "=C10/D10"},
               new { Category= "Miscellaneous",  MonthlySpend= "=C11/12",  AnnualSpend= "1250",  LastYearSpend= "1558",  PercentageChange= "=C11/D11"},
            };
            ViewBag.DefaultData = data;
            return View();
        }

Supported Formulas

The following are the list of formulas supported in spreadsheet,

Formula	Description
ABS	Returns the value of a number without its sign.
ADDRESS	Returns a cell reference as text, given specified row and column numbers.
AND	Returns TRUE if all the arguments are TRUE, otherwise returns FALSE.
AVERAGE	Calculates average for the series of numbers and/or cells excluding text.
AVERAGEA	Calculates the average for the cells evaluating TRUE as 1, text and FALSE as 0.
AVERAGEIF	Clears content of the active cell and enables edit mode.
AVERAGEIFS	Calculates average for the cells based on specified conditions.
CEILING	Rounds a number up to the nearest multiple of a given factor.
CHOOSE	Returns a value from list of values, based on index number.
CHAR	Returns the character from the specified number.
CODE	Returns the numeric code for the first character in a given string.
CONCAT	Concatenates a list or a range of text strings.
CONCATENATE	Combines two or more strings together.
COUNT	Counts the cells that contain numeric values in a range.
COUNTA	Counts the cells that contains values in a range.
COUNTBLANK	Returns the number of empty cells in a specified range of cells.
COUNTIF	Counts the cells based on specified condition.
COUNTIFS	Counts the cells based on specified conditions.
DATE	Returns the date based on given year, month, and day.
DATEVALUE	Converts a date string into date value.
DAY	Returns the day from the given date.
DAYS	Returns the number of days between two dates.
DECIMAL	Converts a text representation of a number in a given base into a decimal number.
DEGREES	Converts radians to degrees.
DOLLAR	Converts the number to currency formatted text.
EDATE	Returns a date with given number of months before or after the specified date.
EVEN	Rounds a positive number up and negative number down to the nearest even integer.
EXACT	Checks whether a two text strings are exactly same and returns TRUE or FALSE.
EXP	Returns e raised to the power of the given number.
FACT	Returns the factorial of a number.
FIND	Returns the position of a string within another string, which is case sensitive.
FLOOR	Rounds a number down to the nearest multiple of a given factor.
HOUR	Returns the number of hours in a specified time string.
IF	Returns value based on the given expression.
IFERROR	Returns value if no error found else it will return specified value.
IFS	Returns value based on the given multiple expressions.
INDEX	Returns a value of the cell in a given range based on row and column number.
INT	Rounds a number down to the nearest integer.
INTERCEPT	Calculates the point of the Y-intercept line via linear regression.
ISNUMBER	Returns true when the value parses as a numeric value.
LARGE	Returns the k-th largest value in a given array.
LEN	Returns a number of characters in a given string.
LN	Returns the natural logarithm of a number.
LOG	Returns the logarithm of a number to the base that you specify.
MATCH	Returns the relative position of a specified value in given range.
MAX	Returns the largest number of the given arguments.
MEDIAN	Returns the median of the given set of numbers.
MINUTE	Returns the number of minutes in a specified time string.
MIN	Returns the smallest number of the given arguments.
MOD	Returns a remainder after a number is divided by divisor.
MONTH	Returns the number of months in a specified date string.
NOW	Returns the current date and time.
ODD	Rounds a positive number up and negative number down to the nearest odd integer.
OR	Returns TRUE if any of the arguments are TRUE, otherwise returns FALSE.
PI	Returns the value of pi.
POWER	Returns the result of a number raised to power.
PRODUCT	Multiplies a series of numbers and/or cells.
RADIANS	Converts degrees into radians.
RAND	Returns a random number between 0 and 1.
RANDBETWEEN	Returns a random integer based on specified values.
ROUND	Rounds a number to the specified number of digits.
ROUNDUP	Rounds a number up, away from zero.
SECOND	Returns the number of seconds in a specified time string.
SMALL	Returns the k-th smallest value in a given array.
SLOPE	Returns the slope of the line from linear regression of the data points.
SORT	Sorts the contents of a column, range, or array in ascending or descending order.
SUBTOTAL	Returns subtotal for a range using the given function number.
SUM	Adds a series of numbers and/or cells.
SUMIF	Adds the cells based on specified condition.
SUMIFS	Adds the cells based on specified conditions.
SUMPRODUCT	Returns the sum of the products of the corresponding array in given arrays.
T	Checks whether a value is text or not and returns the text.
TEXT	Converts the supplied value into text by using the user-specified format.
TIME	Converts hours, minutes, seconds to the time formatted text.
TODAY	Returns the current date.
TRUNC	Truncates a supplied number to a specified number of decimal places.
UNIQUE	Returns a unique values from a range or array.