The ComponentOne Blazor Edition offers a set of high-performance controls, including grid, charts, input, and other controls. FlexGrid has always been a popular grid control, and it keeps its praise even when incorporated as a control in ComponentOne Blazor Edition. The basic features such as tabular data editing, sorting, filtering, and grouping FlexGrid even provides other advanced features including on-demand loading, custom cells, built-in editor controls, full-text search & filter, responsive column layout, selection, and export. Such a feature-rich control, when combined with Blazor, lets you create next-level web applications. Refer to demos and documentation for more information on Blazor Edition.

This blog will use the feature-rich FlexGrid control to implement an interesting use case scenario i.e. an Investment Calculator. An investment calculator helps you calculate the future returns on your present investments and are being commonly used these days. There are many templates for an Investment Calculator calculating different aspects of return on investment. This blog picks up one such Investment Calculator template and helps you understand how to implement this template using Blazor and ComponentOne Blazor FlexGrid control.

Try ComponentOne Studio

Download the latest version of ComponentOne Studio Enterprise

Download Now!

Use Case

The Investment Calculator created in this blog calculates the return specific to each period of investment based on the deposited amount and rate of return. As a cumulative measure, it calculates the total interest earned over the specified time duration along with the future value of the present investment. Here is a quick view of the calculator that we would be learning to design in the sections ahead:

Image1

The steps below will help further demonstrate how we can create this Investment Calculator using Blazor.

Setup a Blazor Application using FlexGrid

We begin by creating a Blazor application using the Blazor Server App template:

Image2

After the application has been created, we need to install C1.Blazor.FlexGrid package using Nuget Package Manager and add the required client-side references to start working with the FlexGrid control. The FlexGrid quickstart can provide you detailed steps on how to add the FlexGrid control to the Blazor application.

FlexGrid works well in both bound and unbound mode. For this application, we will be working with the unbound mode of FlexGrid, as we need to enter few values based on which calculations will be performed to populate the other cells in FlexGrid. Refer to demo and documentation describing the unbound mode of FlexGrid.

The code below assumes that the project has been configured as per the FlexGrid quickstart and a Razor Component has already been added to the project. Now, add the following code to the razor page to add and configure the FlexGrid control for unbound mode by explicitly adding the required number of rows and columns:

@page "/"
@using C1.Blazor.Core
@using C1.Blazor.Grid

<FlexGrid @ref="grid"
          CellFactory="@cellFactory"
          MergeManager="@custommergemanager"
          HeadersVisibility="GridHeadersVisibility.None"
          SelectionMode="GridSelectionMode.Cell"
          GridLinesVisibility="GridLinesVisibility.None"
          CellEditEnded="OnCellEditEnded" BeginningEdit="OnBeginningEdit" SelectionChanging="OnSelectionChanging"
          Style="@("max-height:100vh; max-width:97vh;position: absolute; top: 10%; left: 25%; ")">
    <FlexGridColumns>
        <GridColumn Width="14" IsReadOnly="true" />
        <GridColumn Width="67" IsReadOnly="true" />
        <GridColumn Width="147" Format="c2" IsReadOnly="true" />
        <GridColumn Width="147" Format="c2" IsReadOnly="true" />
        <GridColumn Width="147" Format="c2" IsReadOnly="true" />
        <GridColumn Width="164" Format="c2" />
        <GridColumn Width="14" IsReadOnly="true" />
    </FlexGridColumns>
    <FlexGridRows>
        @for (int i = 0; i < 378; i++)
        {
            if (i == 0)
            {
                <GridRow Height="50" />
            }
            else
            {
                <GridRow Height="25" />
            }
        }
    </FlexGridRows>
</FlexGrid>

@code
{
    FlexGrid grid;
    GridCellFactory cellFactory = new CustomCellFactory();
    CustomMergeManager custommergemanager = new CustomMergeManager();
}

Note: You will get a few errors, but they will be resolved as we continue with the remaining steps and add the appropriate code.

Design Investment Calculator Layout

Now, let's start to customize the FlexGrid appearance to resemble an investment calculator. We would accomplish the same by adjusting the column width, row height, merging cells, formatting cells, and populate the calculator field labels to appropriate cells in FlexGrid. The sections below will provide you details on applying all the required customizations.

Merging Cells

FlexGrid provides built-in support for merging cells across rows or columns, provided the adjacent cells have identical content. We can customize the default merging behavior of FlexGrid by inheriting the GridMergeManager class to define custom logic for merging cells across rows and columns.

For this implementation, we would need to define a custom MergeManager which would merge a pre-defined list of cells in FlexGrid to render the appropriate presentation of cells for an Investment Calculator. The code below merges the required cells in FlexGrid:

//Define custom MergeManager class to merge cell ranges based on custom logic
public class CustomMergeManager : GridMergeManager
{
   public override GridCellRange GetMergedRange(GridCellType cellType, GridCellRange range)
   {
      //Merge cells containing Calculator title
      if (cellType == GridCellType.Cell && (range.Row == 0 && range.Column >= 0 && range.Column <= 5))
      {
         GridCellRange range1 = new GridCellRange(0, 0, 0, 5);
         return range1;
      }

      //Merge cells containing calculator description
      if (cellType == GridCellType.Cell && range.Column >= 1 && range.Column <= 2)
      {
         if (range.Row == 2 || range.Row == 3 || range.Row == 5 || range.Row == 6 || range.Row == 8 || range.Row == 9)
         {
            GridCellRange range2 = new GridCellRange(range.Row, 1, range.Row, 2);
            return range2;
         }
      }

      //Merge cells containing calculator field labels
      if (cellType == GridCellType.Cell && range.Column >= 3 && range.Column <= 4)
      {
         if (range.Row == 2 || range.Row == 3 || range.Row == 4 || range.Row == 6 || range.Row == 7 || range.Row == 8 || range.Row == 10 || range.Row == 11)
         {
            GridCellRange range3 = new GridCellRange(range.Row, 3, range.Row, 4);
            return range3;
         }
      }

       return base.GetMergedRange(cellType, range);

      }
   }
}
Adding Field Labels

In the code below, we populate the investment calculator field labels into the appropriate cells of unbound FlexGrid:

//Override AfterRender method to populate grid for Calculator fields
protected override void OnAfterRender(bool firstRender)
{
   if (firstRender)
      GenerateCalculator();
}

// Fill unbound grid to showcase calculator fields and results
private void GenerateCalculator()
{
    //Populate calculator field labels
    grid[0, 0] = "Investment Calculator";

    grid[2, 1] = "Calculate your investment ";
    grid[3, 1] = "returns.";
    grid[5, 1] = "Enter values into the yellow";
    grid[6, 1] = " boxes.";
    grid[8, 1] = "Results will be shown in the ";
    grid[9, 1] = "green boxes.";

    grid[2, 3] = "Initial Investment Amount:";
    grid[3, 3] = "Annual Rate of Return:";
    grid[4, 3] = "Deposit Amount per Period:";

    grid[6, 3] = "Duration of Investment (In Years):";
    grid[7, 3] = "Number of Deposits Per Year:";
    grid[8, 3] = "Total Number of Periods (Upto 360):";

    grid[10, 3] = "Total Interest Income:";
    grid[11, 3] = "Ending Balance(FV):";

    grid[15, 1] = "Period";
    grid[15, 2] = "Initial Balance";
    grid[15, 3] = "Interest Earned";
    grid[15, 4] = "New Deposit";
    grid[15, 5] = "New Balance";

    //Populate initial values for initial investment Amount, Return rate and deposit amount per period
    grid[2, 5] = 5000;
    grid[3, 5] = Convert.ToString(10) + "%";
    grid[4, 5] = 100;

    //Populate initial values for Investment duration(in years), number of deposits per year
    grid[6, 5] = Convert.ToString(30);
    grid[7, 5] = Convert.ToString(12);

    //Invoke method to calculate investment return
    CalculateReturn();
}
Apply Cell Styling

We have added all the required labels in the appropriate merged cells. Now, let's apply styling to the cells to enhance the look and feel of the investment calculator and make it look more realistic. To apply styling to the cells in FlexGrid, inherit GridCellFactory class to create a custom CellFactory class that lets you style each cell individually. You can style the cell by applying background color, forecolor, border, font, etc.

The code below defines a custom CellFactory and styles all the cells in FlexGrid:

public override void PrepareCellStyle(GridCellType cellType, GridCellRange range, C1Style style, C1Thickness internalBorders)
{
    base.PrepareCellStyle(cellType, range, style, internalBorders);

    //Style Calculator border
    if (cellType == GridCellType.Cell)
    {
       if (range.Column == 0 && range.Row >= 1 && range.Row <= 376)
       {
          style.BorderColor = C1Color.Black;
          style.BorderLeftWidth = new C1Thickness(2);
       }

       if (range.Column == 6 && range.Row >= 1 && range.Row <= 376)
       {
          style.BorderColor = C1Color.Black;
          style.BorderRightWidth = new C1Thickness(2);
       }

       if (range.Row == 0)
       {
          style.BorderColor = C1Color.Black;
          style.BorderBottomWidth = new C1Thickness(2);
       }

       if (range.Row == 376)
       {
         style.BorderColor = C1Color.Black;
         style.BorderBottomWidth = new C1Thickness(2);
       }
    }

    //Style calculator title
    if (cellType == GridCellType.Cell && range.Column >= 0 && range.Column <= 6 && range.Row == 0)
    {
       style.BackgroundColor = C1Color.FromARGB(255, 112, 173, 70); ;
       style.FontSize = 32;
       style.FontWeight = "Arial";
       style.Color = C1Color.White;
    }

    //Style calculator description
    if (cellType == GridCellType.Cell && range.Column == 0 && range.Row == 3)
    {
       style.FontSize = 10;
       style.FontWeight = "Arial";
    }

    //Style Calculator fields labels and inputs
    if (cellType == GridCellType.Cell && range.Column >= 3 && range.Column <= 4)
    {
       if (range.Row >= 2 && range.Row <= 11)
       {
          if (range.Row != 5 && range.Row != 9)
          {
             style.BorderColor = C1Color.Black;
             style.BorderWidth = new C1Thickness(1);
             style.BackgroundColor = C1Color.FromARGB(255, 112, 173, 70);
             style.Color = C1Color.White;
             style.JustifyContent = C1StyleJustifyContent.FlexEnd;
          }
      }
      if (range.Row == 12 && range.Column >= 3 && range.Column <= 4)
      {
         style.BorderColor = C1Color.Black;
         style.BorderTopWidth = new C1Thickness(1);
      }
    }

    if (cellType == GridCellType.Cell && range.Column == 5)
    {
       if (range.Row >= 2 && range.Row <= 7)
       {
          if (range.Row != 5)
          {
             style.BorderColor = C1Color.Black;
             style.BorderWidth = new C1Thickness(1);
             style.BackgroundColor = C1Color.White;                       
             style.JustifyContent = C1StyleJustifyContent.FlexEnd;
          }
       }

       if (range.Row >= 8 && range.Row <= 11)
       {
          if (range.Row != 9)
          {
             style.BorderColor = C1Color.Black;
             style.BorderWidth = new C1Thickness(1);
             style.BackgroundColor = C1Color.FromARGB(255, 226, 239, 219);                       
             style.JustifyContent = C1StyleJustifyContent.FlexEnd;
          }
       }

       if (range.Row == 12)
       {
          style.BorderColor = C1Color.Black;
          style.BorderTopWidth = new C1Thickness(1);
       }
    }

    //Style investment return table
    if (cellType == GridCellType.Cell && range.Column >= 1 && range.Column <= 5)
    {
       if (range.Row >= 15 && range.Row <= 375)
       {                   
          if (range.Row == 15)
          {
             style.BackgroundColor = C1Color.FromARGB(255, 112, 173, 70);
             style.Color = C1Color.White;
             style.JustifyContent = C1StyleJustifyContent.Center;
          }
          else
          {
             if (range.Row % 2 == 0)
                 style.BackgroundColor = C1Color.FromARGB(255, 226, 239, 219);
             else
                 style.BackgroundColor = C1Color.FromARGB(255, 255, 255, 255);
             style.JustifyContent = C1StyleJustifyContent.FlexEnd;
          }

          if (range.Column == 1)
          {
             style.JustifyContent = C1StyleJustifyContent.Center;
          }
       }

       if (range.Row == 376)
       {
          style.BorderColor = C1Color.Black;
          style.BorderTopWidth = new C1Thickness(1);
       }
     }
   }
}

Here is a quick look of a FlexGrid control, designed as an investment calculator after following all the above mentioned steps:

Image3

Implement Investment Calculator Calculations

The Investment Calculator designed above has three color tones, the dark green color is used to specify cells containing field labels which are static values, the white-colored cells are input cells wherein the user inputs the required values to perform the calculation, and the light green color is used to symbolize the cells showing the calculated values which are actually the result of all the calculations performed in this calculator and hence the investment return. Among all these cells, only the white cells will be editable, as they require user input.

In this section, we will define a method to perform all the calculations to calculate investment returns. The method below calculates the investment return for each investment period, total interest earned, and the future value of the investment. Few of the calculations have been done using the basic operators add, subtract, multiply and divide. To calculate the future value of the investment, we need to use the financial function FV.

The Microsoft.VisualBasic package must be installed to invoke the financial functions in C#.Net. There are different financial functions available in the Financial class of Microsoft.VisualBasic namespace. In the code below, we have used the FV financial function from the Financial class.

Refer to the code below to understand how various calculations are implemented in C# to get the calculator working and populate the cells with the appropriate investment return values.

//Method to calculate investment return
public async Task<bool> CalculateReturn()
{
   //Fetch initial investment amount
   int initialAmt = Convert.ToInt32(grid[2, 5]);

   //Fetch Rate of return by removing percentage sign
   string rate = (string)grid[3, 5];
   int ror = Convert.ToInt32(rate.Replace("%", " "));

   //Fetch deposit amount
   int depositAmt = Convert.ToInt32(grid[4, 5]);

   //Fetch total duration of investment(in years)
   int investmentYears = Convert.ToInt32(grid[6, 5]);

   //Fetch number of deposists in an year
   int numDeposits = Convert.ToInt32(grid[7, 5]);

   //Calculate total number of periods and assign to respective grid cell
   int totalPeriods = investmentYears * numDeposits;

   //Make sure total number of periods is not more than 360
   if (totalPeriods <= 360)
   {
       grid[8, 5] = Convert.ToString(totalPeriods);
   }
   else
   {
      grid[8, 5] = null;
      await JsRuntime.InvokeVoidAsync("alert", "Please make sure total number of periods is upto 360 !!");
      return false;
   }

   //Calculate investment return for each period in investment duration
   for (int period = 1, row = 16; row <= 375; row++, period++)
   {
      if (period <= totalPeriods)
      {
        grid[row, 1] = period;
        if (row == 16)
        {
           grid[row, 2] = initialAmt;
        }
        else
        {
           grid[row, 2] = grid[row - 1, 5];
        }
        grid[row, 3] = (((Convert.ToDouble(ror) / Convert.ToDouble(numDeposits)) * Convert.ToInt32(grid[row, 2])) / 100);
        grid[row, 4] = depositAmt;
        grid[row, 5] = Convert.ToInt32(grid[row, 2]) + Convert.ToDouble(grid[row, 3]) + Convert.ToInt32(grid[row, 4]);
      }
      else
      {
         grid[row, 1] = grid[row, 2] = grid[row, 3] = grid[row, 4] = grid[row, 5] = null;
      }
   }

   //Calculate Future Value of investment/Ending Balance
   double Rate = Convert.ToDouble(ror) / (Convert.ToDouble(numDeposits) * 100);
   double NPer = Convert.ToDouble(totalPeriods);
   double Pmt = Convert.ToInt32(depositAmt);
   double PV = Convert.ToInt32(initialAmt);
   double fv = -(Financial.FV(Rate, NPer, Pmt, PV));
   grid[11, 5] = fv;

   //Calculate total interest income
   double endingBal = fv - initialAmt - (depositAmt * totalPeriods);
   grid[10, 5] = endingBal;

   return true;
}

Customize UI Interaction

Since the Investment Calculator has been created using a FlexGrid, the default behavior of the FlexGrid related to editing and selection must be handled to meet the behavior of a calculator. This section describes all the FlexGrid events that must be handled to alter the user interaction behavior for the calculator.

First, we would need to handle the CellEditEnded event of FlexGrid to make sure that whenever a user changes any of the input values in the calculator (i.e., rate of return, initial investment amount, deposit amount, or the duration of investment), the calculator must recalculate all the investment return values.

The code below implements the mentioned behavior:

//Handle Flexgrid's CellEditEdited event to recalcuate investment return
//when either of the values Rate of Return, Deposit Amount etc. are changed
public async void OnCellEditEnded(object sender, GridCellRangeEventArgs e)
{
   //Parse string input value to int and assign to cell
   if (e.CellRange.Row == 2 || e.CellRange.Row == 4)
   {
      grid[e.CellRange.Row, e.CellRange.Column] = Convert.ToInt32((string)grid[e.CellRange.Row, e.CellRange.Column]);
   }

   //Add percentage sign to Rate of Return
   if (e.CellRange.Row == 3)
   {
       grid[e.CellRange.Row, e.CellRange.Column] = (string)grid[e.CellRange.Row, e.CellRange.Column] + "%";
   }

   //Invoke method to reclaculate investment return based on new values.
   await CalculateReturn();
}

Next, we handle the BeginningEdit event of FlexGrid to restrict editing in FlexGrid. As discussed above, all the cells in FlexGrid should not be editable. The user should be able to edit only those cells which require an input value from the user.

Hence, the code below handles the BeginningEdit event to implement the said behavior:

//Handle Flexgrid's BeginningEdit event to cancel editing for cells.
public void OnBeginningEdit(object sender, GridCellRangeEventArgs e)
{
   if (e.CellRange.Row >= 8 && e.CellRange.Row <= 375)
         e.Cancel = true;
}

Last, we handle the SelectionChanging event of FlexGrid to make sure that the user is able to select only the editable cells in FlexGrid:

//Handle Flexgrid's SelectionChanging event to disable selection of non editable cells.
public void OnSelectionChanging(object sender, GridCellRangeEventArgs e)
{
   if (!(e.CellRange.Row >= 2 && e.CellRange.Row <= 7))
   {
      if (e.CellRange.Row != 5)
            e.Cancel = true;
   }
   else if (e.CellRange.Column >= 1 && e.CellRange.Column <= 4)
   {
      e.Cancel = true;
   }
}

Here is a GIF showcasing the Investment Calculator in action:

Gif

You can download the sample implementing the described scenario here, and follow the same approach of applying formatting and calculations, to design other types of Investment Calculators.

Try ComponentOne Studio

Download the latest version of ComponentOne Studio Enterprise

Download Now!