Use the Spreadsheet Document API to Create a Loan Amortization Schedule within a Blazor Server App

Sep 19, 2023
12 minutes to read

This topic describes how to create a Blazor Server application that uses the Spreadsheet Document API to build a loan amortization schedule.

View Example: Create a Loan Amortization Schedule within a Blazor Server App

The image below demonstrates the resulting application. It allows users to enter the following loan information: loan amount, repayment period in years, annual interest rate, and start date. Once data is entered, the Spreadsheet recalculates loan payments and updates data on the application page. Users can save the result in XLSX and PDF formats.

Resulting application

Prerequisites

Visual Studio 2022 version 17.0 or higher with the ASP.NET and web development workload
.NET 6.0 SDK

Create a Blazor Server App

Create a new project in Visual Studio and select the Blazor Server App template. Specify the project name and location in the next window.

Create a new Blazor Server App project

In the Additional information window, set the target framework to .NET 6.0 and click Create.

Set the target framework to .NET 6.0

Install DevExpress NuGet Packages

Visit nuget.devexpress.com, log in to your account, and click Obtain Feed URL. Open the NuGet Package Manager in Visual Studio and register the DevExpress NuGet feed as the package source. Install the following packages:

DevExpress.Document.Processor: This package includes the Spreadsheet Document API and other Office File API libraries. You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this package in production code.
DevExpress.Blazor: Contains DevExpress UI Components for Blazor. This product line is available as part of the DevExpress Universal, DXperience, or ASP.NET Subscription.

If you are new to NuGet packages, see the following installation guide for assistance: Install DevExpress Packages Using NuGet Package Manager.

Design the Application UI

Register the DevExpress.Blazor namespace in the _Imports.razor file.
- Razor
```
@using DevExpress.Blazor
```
Call the AddDevExpressBlazor method in the Program.cs file.
- C#
```
builder.Services.AddDevExpressBlazor();
```
Apply the DevExpress Blazing Berry theme to the app as described in this help topic: Apply a DevExpress Bootstrap Theme. Open the Pages/_Layout.cshtml file and add the following line to the end of the HEAD section:
- CSHTML
```
<head>
    @* ... *@
    <link href="_content/DevExpress.Blazor.Themes/blazing-berry.bs5.css" rel="stylesheet" />
</head>
```
Add the following DevExpress Blazor UI components to the application:

DxFormLayout

A Form Layout component that allows you to construct responsive and auto-aligned edit forms.

DxSpinEdit<T>

A Spin Edit component. Our application contains four editors. Use the Value property with the @bind attribute to bind editor values to the properties that store loan information. The MinValue and MaxValue properties specify the minimum and maximum values for Spin Edit components. The Increment option specifies the step by which a value in the editor changes when a user clicks the up or down arrow.

DxButton

A button. Use the Text property to define the button’s text. Create two buttons to export a loan amortization schedule to XLSX and PDF.

This example also uses an inline frame (defined by the <iframe> tag) to display the generated loan amortization schedule on the page.

Open the Index.razor file and change its code as follows:

Razor

@page "/"

<div class="container">
    <DxFormLayout>
        <DxFormLayoutGroup Caption="Loan Amortization Schedule" ColSpanMd="11">
            <DxFormLayoutItem ColSpanMd="12">
                <Template>
                    <p>
                        This example uses the Spreadsheet Document API
                        to create a loan amortization schedule.
                        Specify the loan amount, loan period in years,
                        annual interest rate, and start date to calculate
                        your loan payments. Click <b>Export to XLSX</b>
                        or <b>Export to PDF</b> to save the result as XLSX or PDF.
                    </p>
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem Caption="Loan Amount:" ColSpanMd="5">
                <Template>
                    <DxSpinEdit @bind-Value="LoanAmount"
                                DisplayFormat="c"
                                Increment="100"
                                MinValue="100"
                                MaxValue="1000000" />
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem Caption="Period in Years:" ColSpanMd="5">
                <Template>
                    <DxSpinEdit @bind-Value="PeriodInYears"
                                Increment="1"
                                MinValue="1"
                                MaxValue="100" />
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem ColSpanMd="2">
                <Template>
                    <DxButton CssClass="btn-block" Text="Export to XLSX" />
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem Caption="Interest Rate:" ColSpanMd="5">
                <Template>
                    <DxSpinEdit @bind-Value="InterestRate"
                                DisplayFormat="p"
                                Increment="0.01"
                                MinValue="0.001"
                                MaxValue="100" />
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem Caption="Start Date of Loan:" ColSpanMd="5">
                <Template>
                    <DxDateEdit @bind-Date="StartDate"></DxDateEdit>
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem ColSpanMd="2">
                <Template>
                    <DxButton CssClass="btn-block" Text="Export to PDF" />
                </Template>
            </DxFormLayoutItem>

            <DxFormLayoutItem ColSpanMd="12">
                <Template>
                    <iframe class="col p-0 preview" height="500" />
                </Template>
            </DxFormLayoutItem>
        </DxFormLayoutGroup>
    </DxFormLayout>
</div>

@code{ 
    double loanAmount = 19000;
    int periodInYears = 2;
    double interestRate = 0.055d;
    DateTime startDate = DateTime.Now;

    double LoanAmount
    {
        get => loanAmount;
        set { loanAmount = value; UpdateValue(); }
    }
    int PeriodInYears
    {
        get => periodInYears;
        set { periodInYears = value; UpdateValue(); }
    }
    double InterestRate
    {
        get => interestRate;
        set { interestRate = value; UpdateValue(); }
    }
    DateTime StartDate
    {
        get => startDate;
        set { startDate = value; UpdateValue(); }
    }

    protected override async Task OnInitializedAsync()
    {
        await base.OnInitializedAsync();
    }

    void UpdateValue() =>
        InvokeAsync(StateHasChanged);
}

Generate a Loan Amortization Schedule Based on a Document Template

Create a Loan Amortization Schedule template. You can download the following document to proceed: LoanAmortizationScheduleTemplate.xltx. Add this file to the Data folder of your project.
Add a new Code folder to the project and create a DocumentGenerator.cs file within the folder.

Implement a LoanAmortizationScheduleGenerator class, as shown below. This class calculates loan payments based on the specified loan information (loan amount, repayment period in years, annual interest rate, and start date) and generates an amortization table.

The following built-in Spreadsheet functions are used to create a loan payment schedule:

PMT — Returns the periodic payment on a loan.
PPMT — Returns the principal paid on a loan for a given period.
IPMT — Returns the interest paid on a loan for a given period.

Expand to Show Class Implementation

using System;
using System.Drawing;
using DevExpress.Spreadsheet;

namespace BlazorApp_SpreadsheetDocumentAPI
{
    public class LoanAmortizationScheduleGenerator
    {
        IWorkbook workbook;

        public LoanAmortizationScheduleGenerator(IWorkbook workbook)
        {
            this.workbook = workbook;
        }
        Worksheet Sheet { get { return workbook.Worksheets[0]; } }
        DateTime LoanStartDate
        {
            get { return Sheet["E8"].Value.DateTimeValue; }
            set { Sheet["E8"].Value = value; }
        }
        double LoanAmount
        {
            get { return Sheet["E4"].Value.NumericValue; }
            set { Sheet["E4"].Value = value; }
        }
        double InterestRate
        {
            get { return Sheet["E5"].Value.NumericValue; }
            set { Sheet["E5"].Value = value; }
        }
        int PeriodInYears
        {
            get { return (int)Sheet["E6"].Value.NumericValue; }
            set { Sheet["E6"].Value = value; }
        }
        int ActualNumberOfPayments
        {
            get { return (int)Math.Round(Sheet["I6"].Value.NumericValue); }
        }
        int ScheduledNumberOfPayments
        {
            get { return (int)Math.Round(Sheet["I5"].Value.NumericValue); }
        }
        string ActualLastRow
        {
            get { return (11 + ActualNumberOfPayments).ToString(); }
        }

        public void GenerateDocument(double loanAmount, int periodInYears,
            double interestRate, DateTime loanStartDate)
        {
            workbook.BeginUpdate();
            try
            {
                ClearData();
                LoanAmount = loanAmount;
                InterestRate = interestRate;
                PeriodInYears = periodInYears;
                LoanStartDate = loanStartDate;
                GenerateLoanAmortizationTable();
                ApplyFormatting();
                SpecifyPrintOptions();
            }
            finally
            {
                workbook.EndUpdate();
            }
        }

        void ClearData()
        {
            // Clear all data
            // except for rows 1 through 11. 
            var range = Sheet.GetDataRange().Exclude(Sheet["1:11"]);
            if (range != null)
                range.Clear();
            Sheet["I4"].ClearContents();
            Sheet["I6:I8"].ClearContents();
            workbook.DefinedNames.Clear();
        }

        void GenerateLoanAmortizationTable()
        {
            CreateDefinedNames();
            // Calculate loan payment amount.
            Sheet["I4"].FormulaInvariant =
                "=PMT(Interest_Rate_Per_Month,Scheduled_Number_Payments,-Loan_Amount)";
            // Calculate the scheduled number of payments.
            Sheet["I5"].FormulaInvariant = "=Loan_Years*Number_of_Payments_Per_Year";
            // Calculate the actual number of payments.
            Sheet["I6"].FormulaInvariant = "=ROUNDUP(Actual_Number_of_Payments,0)";
            // Recalculate all formulas in the document.
            workbook.Calculate();
            // Calculate the total amount of early payments.
            Sheet["I7"].FormulaInvariant = "=SUM(F12:F" + ActualLastRow + ")";
            // Calculate the total interest paid.
            Sheet["I8"].FormulaInvariant = "=SUM($I$12:$I$" + ActualLastRow + ")";

            if (ScheduledNumberOfPayments == 0)
                return;

            // Populate the "Payment Number" column.
            for (int i = 0; i < ActualNumberOfPayments; i++)
                Sheet["B" + (i + 12).ToString()].Value = i + 1;

            // Populate the "Payment Date" column.
            Sheet["C12:C" + ActualLastRow].FormulaInvariant =
                "=DATE(YEAR(Loan_Start),MONTH(Loan_Start)+(B12)*12/Number_of_Payments_Per_Year,DAY(Loan_Start))";

            // Calculate the beginning balance for each period.
            Sheet["D12"].Formula = "=Loan_Amount";
            if (ScheduledNumberOfPayments > 1)
                Sheet["D13:D" + ActualLastRow].Formula = "=J12";

            // Populate the "Scheduled Payment" column.
            Sheet["E12:E" + ActualLastRow].FormulaInvariant =
                "=IF(D12>0,IF(Scheduled_Payment<D12, Scheduled_Payment, D12),0)";
            // Populate the "Extra Payment" column.
            Sheet["F12:F" + ActualLastRow].FormulaInvariant =
                "=IF(Extra_Payments<>0, IF(Scheduled_Payment<D12, G12-E12, 0), 0)";
            // Calculate total payment amount.
            Sheet["G12:G" + ActualLastRow].FormulaInvariant = "=H12+I12";
            // Calculate the principal part of the payment. 
            Sheet["H12:H" + ActualLastRow].FormulaInvariant =
                "=IF(J12>0,PPMT(Interest_Rate_Per_Month,B12,Actual_Number_of_Payments,-Loan_Amount),D12)";
            // Calculate interest payments for each period.
            Sheet["I12:I" + ActualLastRow].FormulaInvariant =
                "=IF(D12>0,IPMT(Interest_Rate_Per_Month,B12,Actual_Number_of_Payments,-Loan_Amount),0)";
            // Calculate the remaining balance for each period.
            Sheet["J12:J" + ActualLastRow].FormulaInvariant =
                "=IF(D12-PPMT(Interest_Rate_Per_Month,B12,Actual_Number_of_Payments,-Loan_Amount)>0," +
                "D12-PPMT(Interest_Rate_Per_Month,B12,Actual_Number_of_Payments,-Loan_Amount),0)";
            // Calculate the cumulative interest paid on the loan.
            Sheet["K12:K" + ActualLastRow].FormulaInvariant = "=SUM($I$12:$I12)";
            // Recalculate all formulas in the document. 
            workbook.Calculate();
        }

        void CreateDefinedNames()
        {
            string sheetName = "'" + Sheet.Name + "'";
            char separator = workbook.Options.Culture.TextInfo.ListSeparator[0];

            // Define names for cell ranges and functions
            // used in payment amount calculation.
            DefinedNameCollection definedNames = workbook.DefinedNames;
            definedNames.Add("Loan_Amount", sheetName + "!$E$4");
            definedNames.Add("Interest_Rate", sheetName + "!$E$5");
            definedNames.Add("Loan_Years", sheetName + "!$E$6");
            definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
            definedNames.Add("Loan_Start", sheetName + "!$E$8");
            definedNames.Add("Extra_Payments", sheetName + "!$E$9");
            definedNames.Add("Scheduled_Payment", sheetName + "!$I$4");
            definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
            definedNames.Add("Interest_Rate_Per_Month",
                "=Interest_Rate/Number_of_Payments_Per_Year");
            definedNames.Add("Actual_Number_of_Payments",
                "=NPER(Interest_Rate_Per_Month" + separator + " " +
                sheetName + "!$I$4+Extra_Payments" + separator + " -Loan_Amount)");
        }

        void ApplyFormatting()
        {
            // Format the amortization table.
            CellRange range;
            // Change the color of even rows in the table.
            for (int i = 1; i < ActualNumberOfPayments; i += 2)
            {
                range = Sheet.Range.FromLTRB(1, 11 + i, 10, 11 + i);
                range.Fill.BackgroundColor = Color.FromArgb(217, 217, 217);
            }

            range = Sheet["B11:K" + ActualLastRow];
            Formatting formatting = range.BeginUpdateFormatting();
            try
            {
                // Display vertical inside borders within the table.
                formatting.Borders.InsideVerticalBorders.LineStyle = BorderLineStyle.Thin;
                formatting.Borders.InsideVerticalBorders.Color = Color.White;
                // Center text vertically within table cells.
                formatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            }
            finally
            {
                range.EndUpdateFormatting(formatting);
            }

            Sheet["B12:C" + ActualLastRow].Alignment.Horizontal =
                SpreadsheetHorizontalAlignment.Right;
            // Apply the number format to table columns.
            Sheet["C11:C" + ActualLastRow].NumberFormat = "m/d/yyyy";
            Sheet["D11:K" + ActualLastRow].NumberFormat =
                "_(\\$* #,##0.00_);_(\\$ (#,##0.00);_(\\$* \" - \"??_);_(@_)";
        }

        void SpecifyPrintOptions()
        {
            // Specify print settings
            // for the worksheet.
            Sheet.SetPrintRange(Sheet.GetDataRange());
            Sheet.PrintOptions.FitToPage = true;
            Sheet.PrintOptions.FitToWidth = 1;
            Sheet.PrintOptions.FitToHeight = 0;
        }
    }
}

Add a new DocumentService.cs class file to the Code folder. This class will contain asynchronous methods used to generate a document and export it to various file formats (XLSX, HTML, and PDF).

Open the Program.cs file and register the DocumentService implementation. Call the AddSingleton method to create a service instance and add it to the app’s service collection. This instance is available throughout the application for all requests.
- C#
```
using BlazorApp_SpreadsheetDocumentAPI;
// ...

builder.Services.AddSingleton<DocumentService>();
```

Implement the following method within the DocumentService.cs class to asynchronously generate a loan amortization schedule based on the document template and loan information entered by users:

public class DocumentService
{
    async Task<Workbook> GenerateDocumentAsync(double loanAmount,
        int periodInYears, double interestRate, DateTime loanStartDate)
    {
        var workbook = new Workbook();
        // Load the document template.
        await workbook.LoadDocumentAsync("Data/LoanAmortizationScheduleTemplate.xltx");
        // Generate a loan amortization schedule
        // based on the template and specified loan information.
        new LoanAmortizationScheduleGenerator(workbook)
            .GenerateDocument(loanAmount, periodInYears,
                interestRate, loanStartDate);
        return workbook;
    }
}

Preview the Loan Amortization Schedule in the App

You need to convert the generated document to HTML to display its content within the application.

Add the following method to the DocumentService.cs class. This method calls DocumentService.GenerateDocumentAsync to build an amortization schedule and export the document to HTML.

public async Task<byte[]> GetHtmlDocumentAsync(double loanAmount,
    int periodInYears, double interestRate, DateTime startDateOfLoan)
{
    // Generate a workbook
    // that contains an amortization schedule.
    using var workbook = await GenerateDocumentAsync(loanAmount, periodInYears,
        interestRate, startDateOfLoan);
    // Export the document to HTML.
    using var ms = new MemoryStream();
    await workbook.ExportToHtmlAsync(ms, workbook.Worksheets[0]);
    return ms.ToArray();
}

Open the Index.razor page and inject an instance of the DocumentService object into the page.
- Razor
```
@page "/"
@inject DocumentService DocumentService
```

Implement the UpdatePreview method. This method calls DocumentService.GetHtmlDocumentAsync to generate an HTML preview of the amortization schedule and displays the result on the page within the <iframe> element. The UpdatePreview method is called each time a user updates loan information in the application.

Razor

<DxFormLayoutItem ColSpanMd="12">
    <Template>
        <iframe class="col p-0 preview" height="500" src="@content"/>
    </Template>
</DxFormLayoutItem>

@* ... *@

@code{
    string content = String.Empty;

    // ...
    protected override async Task OnInitializedAsync()
    {
        await UpdatePreview();
        await base.OnInitializedAsync();
    }

    void UpdateValue() =>
        InvokeAsync(async () =>
        {
            await UpdatePreview();
            StateHasChanged();
        });

    async Task UpdatePreview()
    {
        var document = await DocumentService.GetHtmlDocumentAsync(loanAmount,
            periodInYears, interestRate, startDate);
        content = "data:text/html;base64," + Convert.ToBase64String(document);
    }
}

Download the Loan Amortization Schedule in XLSX and PDF Formats to the Browser

You can use a web API to download the calculated amortization schedule in XLSX and PDF formats on the client side.

Open the DocumentService.cs class and add the following methods to generate and export a loan amortization schedule to XLSX and PDF:

public async Task<byte[]> GetXlsxDocumentAsync(double loanAmount,
        int periodInYears, double interestRate, DateTime startDateOfLoan)
{
    // Generate a workbook
    // that contains an amortization schedule.
    using var workbook = await GenerateDocumentAsync(loanAmount, periodInYears,
        interestRate, startDateOfLoan);
    // Save the document as XLSX.
    return await workbook.SaveDocumentAsync(DocumentFormat.Xlsx);
}

public async Task<byte[]> GetPdfDocumentAsync(double loanAmount,
    int periodInYears, double interestRate, DateTime startDateOfLoan)
{
    // Generate a workbook
    // that contains an amortization schedule.
    using var workbook = await GenerateDocumentAsync(loanAmount, periodInYears,
        interestRate, startDateOfLoan);
    // Export the document to HTML.
    using var ms = new MemoryStream();
    await workbook.ExportToPdfAsync(ms);
    return ms.ToArray();
}

Implement a web API controller. Add a new Controllers folder to the project and create an ExportController.cs class within the folder. This class contains action methods that handle HTTP GET requests and return files in XLSX and PDF formats.

using BlazorApp_SpreadsheetDocumentAPI;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Threading.Tasks;

namespace BlazorApp_SpreadsheetAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ExportController : ControllerBase
    {
        readonly DocumentService documentService;

        public ExportController(DocumentService documentService)
        {
            this.documentService = documentService;
        }

        [HttpGet]
        [Route("[action]")]
        public async Task<IActionResult> Xlsx([FromQuery] double loanAmount, 
            [FromQuery] int periodInYears, [FromQuery] double interestRate, 
            [FromQuery] DateTime loanStartDate)
        {
            var document = await documentService.GetXlsxDocumentAsync(loanAmount, periodInYears, 
                interestRate, loanStartDate);
            return File(document, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
                "output.xlsx");
        }

        [HttpGet]
        [Route("[action]")]
        public async Task<IActionResult> Pdf([FromQuery] double loanAmount, 
            [FromQuery] int periodInYears, [FromQuery] double interestRate, 
            [FromQuery] DateTime loanStartDate)
        {
            var document = await documentService.GetPdfDocumentAsync(loanAmount, periodInYears, 
                interestRate, loanStartDate);
            return File(document, "application/pdf", "output.pdf");
        }
    }
}

Open the Program.cs file. Call the MapControllers method to map incoming HTTP requests to the controller’s action methods.
- C#
```
// ...
app.MapControllers();

app.Run();
```

Open the _Imports.razor file and add the following namespaces:

Razor

@using Microsoft.AspNetCore.WebUtilities
@using System.Globalization

Open the Index.razor page and inject an instance of the IJSRuntime object into the page.
- Razor
```
@inject IJSRuntime JS
```

Handle the Click events of the Export to XLSX and Export to PDF buttons. These buttons call the following methods:

ExportToXlsx - Sends a request to download a loan amortization schedule in XLSX format. The request executes the controller’s Xlsx action method.
ExportToPdf - Sends a request to download a loan amortization schedule in PDF format. The request executes the controller’s Pdf action method.

Razor

<DxFormLayoutItem ColSpanMd="2">
    <Template>
        <DxButton Click="ExportToXlsx" CssClass="btn-block" Text="Export to XLSX" />
    </Template>
</DxFormLayoutItem>

@* ... *@

<DxFormLayoutItem ColSpanMd="2">
    <Template>
        <DxButton Click="ExportToPdf" CssClass="btn-block" Text="Export to PDF" />
    </Template>
</DxFormLayoutItem>

@* ... *@

@code{
    void ExportToXlsx(MouseEventArgs args) => JS.InvokeAsync<object>("open",
        GetQueryString("api/Export/Xlsx"), "_self");

    void ExportToPdf(MouseEventArgs args) => JS.InvokeAsync<object>("open",
        GetQueryString("api/Export/Pdf"), "_self");

    string GetQueryString(string uri)
    {
        var queryParams = new Dictionary<string, string?>() {
            { "loanAmount", loanAmount.ToString() },
            { "periodInYears", periodInYears.ToString() },
            { "interestRate", interestRate.ToString(CultureInfo.InvariantCulture) },
            { "loanStartDate", startDate.ToString("O") },
        };
        return QueryHelpers.AddQueryString(uri, queryParams);
    }
}

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Prerequisites

Create a Blazor Server App

Install DevExpress NuGet Packages

Design the Application UI

Generate a Loan Amortization Schedule Based on a Document Template

Preview the Loan Amortization Schedule in the App

Download the Loan Amortization Schedule in XLSX and PDF Formats to the Browser