Live Razor Page Samples

Amortization Calculator

This sample extends the Simple Loan Calculator with an amortization table which is displayed in an HTML Table. Of particular interest is the use of the defined name, AmortizationTableForNPer, which uses the OFFSET() worksheet function to return a dynamically sized range.

Supporting Files

The following file is utilized by this sample:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Calculations
{
    public partial class AmortizationCalculatorModel : PageModel
    {
        [BindProperty]
        public string LoanAmount { get; set; }

        [BindProperty]
        public string InterestRate { get; set; }

        [BindProperty]
        public string Periods { get; set; }

        public string PaymentAmount { get; set; }

        public string LastPayment { get; set; }

        public string TotalInterest { get; set; }

        // This range will be used to populate the header row in the HTML table.
        public SpreadsheetGear.IRange HeaderRange { get; set; }

        // This range will be used to populate the data HTML table.
        public SpreadsheetGear.IRange DataRange { get; set; }


        public void OnGet()
        {
            LoanAmount = "$15,000";
            InterestRate = "7.25%";
            Periods = "24";
        }

        public void OnPost()
        {
            // Open the workbook
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/amortization.xlsx");

            // Get IRange for cells from defined names.
            SpreadsheetGear.IRange pv = workbook.Names["pv"].RefersToRange;
            SpreadsheetGear.IRange rate = workbook.Names["rate"].RefersToRange;
            SpreadsheetGear.IRange nper = workbook.Names["nper"].RefersToRange;

            // Copy the form values to the worksheet.
            pv.Formula = LoanAmount;
            rate.Formula = InterestRate;
            nper.Formula = Periods;

            // Display payment and interest details
            PaymentAmount = workbook.Names["Payment"].RefersToRange.Text;
            LastPayment = workbook.Names["LastPayment"].RefersToRange.Text;
            TotalInterest = workbook.Names["TotalInterest"].RefersToRange.Text;

            // Return the IRange used for the specified defined name, in this case the populated portion of the
            // amortization table, including the header row.
            SpreadsheetGear.IRange table = workbook.Names["AmortizationTableForNPer"].RefersToRange;

            // Provide specific IRanges for the header and data areas of the table for easier consumption by the view.
            HeaderRange = table[0, 0, 0, table.ColumnCount - 1];
            DataRange = table[1, 0, table.RowCount - 1, table.ColumnCount - 1];
        }
    }
}
@page
@model AmortizationCalculatorModel
@{ Layout = "_SamplesLayout";}

<div class="row">
  <div class="col-sm-12 col-md-8 mb-3">
    <div class="card">
      <h2 class="card-header">Loan Details</h2>
      <div class="card-body">
        <!-- Input Form -->
        <form asp-page="AmortizationCalculator" method="post">
          <div class="mb-2">
            <label asp-for="LoanAmount" class="form-label">Loan Amount (pv)</label>
            <input asp-for="LoanAmount" class="form-control" />
          </div>

          <div class="mb-2">
            <label asp-for="InterestRate" class="form-label">Interest Rate (rate)</label>
            <input asp-for="InterestRate" class="form-control" />
          </div>

          <div class="mb-2">
            <label asp-for="Periods" class="form-label">Total # of Periods (Nper)</label>
            <input asp-for="Periods" class="form-control" />
          </div>

          <div class="text-end">
            <button class="btn btn-primary">Calculate</button>
          </div>
        </form>
      </div>
    </div>
  </div>
  <div class="col-sm-12 col-md-4 mb-3">
    <div class="card">
      <h2 class="card-header">Results</h2>
      <div class="card-body">
        <dl class="row">
          <dd class="col-6">Payment</dd>
          <dt class="col-6">@Model.PaymentAmount</dt>

          <dd class="col-6">Last Payment</dd>
          <dt class="col-6">@Model.LastPayment</dt>

          <dd class="col-6">Total Interest</dd>
          <dt class="col-6">@Model.TotalInterest</dt>
        </dl>
      </div>
    </div>
  </div>
</div>

@if (Model.HeaderRange != null)
{
  <table class="table table-striped table-hover">
    <thead class="table-dark">
      <tr>
        <th colspan="@Model.HeaderRange.ColumnCount"><h3>Amortization Table</h3></th>
      </tr>
      <tr>
        <!-- Loop over each cell within HeaderRange -->
        @foreach (SpreadsheetGear.IRange cell in Model.HeaderRange)
        {
          <th>@cell.Text</th>
        }
      </tr>
    </thead>
    <tbody>
      <!-- Loop the DataRange in row chunks-->
      @foreach (SpreadsheetGear.IRange row in Model.DataRange.Rows)
      {
        <tr>
          <!-- Now loop over each column in the row -->
          @foreach (SpreadsheetGear.IRange cell in row.Columns)
          {
            <td>@cell.Text</td>
          }
        </tr>
      }
    </tbody>
  </table>
}