Spreadsheet Document Template

This demo uses the Spreadsheet Document API to generate a loan amortization schedule template. Specify the loan amount, loan period in years, annual interest rate, and start date to calculate your loan information. Click Download to save the resulting document to your computer.

Options
Loan Amount
Period in Years
Interest Rate
Start Date of Loan
File Format
Download
@model AspNetCoreDemos.OfficeFileAPI.SpreadsheetDocumentTemplateModel
@using DevExtreme.AspNet.Mvc

@{ Html.BeginForm("SpreadsheetDocumentTemplateExportTo", "MailMerge", FormMethod.Post); }

<script type="text/javascript">
    function UpdatePreview() {
        var LoanAmountValue = $("#LoanAmount").dxNumberBox('instance').option('value');
        var PeriodInYearsValue = $("#PeriodInYears").dxNumberBox('instance').option('value');
        var InterestRateValue = $("#InterestRate").dxNumberBox('instance').option('value');
        const options = { year: 'numeric', month: '2-digit', day: '2-digit' };
        const dateFormat = new Intl.DateTimeFormat('en-US', options).format;
        var StartDateOfLoanValue = dateFormat(new Date($("#StartDateOfLoan").dxDateBox('instance').option('value')));
        var params = "LoanAmount=" + encodeURIComponent(LoanAmountValue) +
            "&PeriodInYears=" + encodeURIComponent(PeriodInYearsValue) +
            "&InterestRate=" + encodeURIComponent(InterestRateValue) +
            "&StartDateOfLoan=" + encodeURIComponent(StartDateOfLoanValue);
        SpreadsheetPreview.Update(params);
    }
</script>

<div class="demo-view-container">
    @await Html.PartialAsync("SpreadsheetPreviewPartial", Model.PreviewModel)
</div>

<div class="options">
    <div class="caption">Options</div>
    <div class="option">
        <div class="label">Loan Amount</div>
        @(Html.DevExtreme().NumberBoxFor(m => m.LoanAmount)
                           .ID("LoanAmount")
                           .Min(100)
                           .Step(100)
                           .ShowSpinButtons(true)
                           .OnValueChanged("function(s,e) { UpdatePreview(); }")
        )
    </div>
    <div class="option">
        <div class="label">Period in Years</div>
        @(Html.DevExtreme().NumberBoxFor(m => m.PeriodInYears)
                           .ID("PeriodInYears")
						   .Min(1)
                           .Step(1)
                           .ShowSpinButtons(true)
                           .OnValueChanged("function(s,e) { UpdatePreview(); }")
        )
    </div>
    <div class="option">
        <div class="label">Interest Rate</div>
        @(Html.DevExtreme().NumberBoxFor(m => m.InterestRate)
                           .ID("InterestRate")
                           .Format("#0.00%")
                           .Step(0.01)
                           .ShowSpinButtons(true)
                           .OnValueChanged("function(s,e) { UpdatePreview(); }")
        )
    </div>
    <div class="option">
        <div class="label">Start Date of Loan</div>
        @(Html.DevExtreme().DateBoxFor(m => m.StartDateOfLoan)
                           .ID("StartDateOfLoan")
                           .Type(DateBoxType.Date)
                           .Width("100%")
                           .OnValueChanged("function(s,e) { UpdatePreview(); }")
        )
    </div>
    @await Html.PartialAsync("SpreadsheetDocumentDownloaderPartial", Model)
</div>
@{ Html.EndForm(); }
@model AspNetCoreDemos.OfficeFileAPI.SpreadsheetPreviewModel

<iframe id="previewFrame" src="@Url.Action(Model.PreviewDocumentAction, Model.ControllerName)" class="demo-preview-border" style="width: 100%; height: @Model.HeightInPixels;box-sizing:border-box"></iframe>

<script type="text/javascript">
    SpreadsheetPreview = {
        basePath: '@Url.Action(Model.PreviewDocumentAction, Model.ControllerName)',
        Update: function (param) {
            var iframeElementName = "previewFrame";
            var iframeElement = document.getElementById(iframeElementName);
            if (!iframeElement)
                return;
            var additionalParams = "&" + new Date().valueOf();
            if (param)
                additionalParams = param;
            iframeElement.src = this.basePath + "?" + additionalParams;
        }
    };
</script>

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;

namespace AspNetCoreDemos.OfficeFileAPI {
    public partial class MailMergeController : OfficeDemoController {

        public IActionResult SpreadsheetDocumentTemplate() {
            SpreadsheetDocumentTemplateModel model = new SpreadsheetDocumentTemplateModel();
            return View(model);
        }

    }
}
using System.Globalization;
using System.IO;
using DevExpress.Spreadsheet;
using Microsoft.AspNetCore.Mvc;

namespace AspNetCoreDemos.OfficeFileAPI {
    public partial class MailMergeController {
        const string spreadsheetDocumentTemplateDefaultFile = "/Documents/LoanAmortizationSchedule_template.xltx";

        public IActionResult SpreadsheetDocumentTemplateExportTo(SpreadsheetDocumentTemplateModel model) {
            IWorkbook workbook = CreateLoanAmortizationScheduleWorkbook(model);
            SpreadsheetFileFormat fileFormat = model.FileFormat;
            DocumentFormat documentFormat = SpreadsheetUtils.ConvertToFormat(fileFormat);
            MemoryStream stream = new MemoryStream();
            if(documentFormat == DocumentFormat.Undefined)
                workbook.ExportToPdf(stream);
            else
                workbook.SaveDocument(stream, documentFormat);
            string contentType = SpreadsheetUtils.ConvertToContentType(fileFormat);
            string fileExtension = SpreadsheetUtils.ConvertToFileExtension(fileFormat);
            return CreateFileStreamResult(stream, contentType, fileExtension);
        }

        public IActionResult SpreadsheetDocumentTemplatePreview(SpreadsheetDocumentTemplateModel model) {
            SpreadsheetPreviewModel previewModel = new SpreadsheetPreviewModel();
            previewModel.Workbook = CreateLoanAmortizationScheduleWorkbook(model);
            return GenerateHtmlPreview(previewModel);
        }

        IWorkbook CreateLoanAmortizationScheduleWorkbook(SpreadsheetDocumentTemplateModel model) {
            IWorkbook workbook = new Workbook();
            workbook.Options.Culture = new CultureInfo("en-US");
            string filePath = HostingEnvironment.ContentRootPath + spreadsheetDocumentTemplateDefaultFile;
            workbook.LoadDocument(filePath);
            LoanAmortizationScheduleDocumentGenerator generator = new LoanAmortizationScheduleDocumentGenerator(workbook);
            generator.GenerateDocument(model.LoanAmount, model.PeriodInYears, model.InterestRate, model.StartDateOfLoan);
            return workbook;
        }
    }
}
using System;

namespace AspNetCoreDemos.OfficeFileAPI {
    public class SpreadsheetDocumentTemplateModel : SpreadsheetModelBase {
        public SpreadsheetDocumentTemplateModel() {
            PreviewModel.PreviewDocumentAction = "SpreadsheetDocumentTemplatePreview";
            PreviewModel.ControllerName = "MailMerge";
            LoanAmount = 19900;
            PeriodInYears = 2;
            InterestRate = 0.055;
            StartDateOfLoan = DateTime.Now;
        }

        public double LoanAmount { get; set; }
        public double InterestRate { get; set; }
        public int PeriodInYears { get; set; }
        public DateTime StartDateOfLoan { get; set; }
        public bool AnnuityPayments { get; set; }
    }
}
namespace AspNetCoreDemos.OfficeFileAPI {
    public class SpreadsheetModelBase {
        public SpreadsheetModelBase() {
            PreviewModel = new SpreadsheetPreviewModel();
            PreviewModel.OwnerPropertyName = "PreviewModel";
            FileFormat = SpreadsheetFileFormat.Xlsx;
        }

        public SpreadsheetFileFormat FileFormat { get; set; }
        public SpreadsheetPreviewModel PreviewModel { get; internal set; }
    }
}