Spreadsheet Mail Merge

@model AspNetCoreDemos.OfficeFileAPI.SpreadsheetMailMergeModel
@using DevExtreme.AspNet.Mvc

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

<script type="text/javascript">
    function UpdatePreview() {
        var orderIdValue = $("#OrderBox").dxSelectBox('instance').option('value');
        var params = "orderId=" + encodeURIComponent(orderIdValue);
        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">Order Id</div>
        @(Html.DevExtreme().SelectBoxFor(m => m.OrderId)
                           .ID("OrderBox")
                           .DataSource(new object[] {
                                new { Value = 10294, Text = "10294" },
                                new { Value = 10295, Text = "10295" },
                                new { Value = 10296, Text = "10296" }})
                           .ValueExpr("Value")
                           .DisplayExpr("Text")
                           .OnValueChanged("UpdatePreview")
        )
    </div>
    @await Html.PartialAsync("SpreadsheetDocumentDownloaderPartial", Model)
</div>

@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;

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

        public IActionResult SpreadsheetMailMerge() {
            SpreadsheetMailMergeModel model = new SpreadsheetMailMergeModel();
            return View(model);
        }

    }
}

using System.Collections.Generic;
using System.Globalization;
using System.IO;
using DevExpress.Spreadsheet;
using Microsoft.AspNetCore.Mvc;

namespace AspNetCoreDemos.OfficeFileAPI {
    public partial class MailMergeController {
        const string spreadsheetMailMergeDefaultFile = "/Documents/CustomerOrderDetails.xlsx";

        public IActionResult SpreadsheetMailMergeExportTo(SpreadsheetMailMergeModel model) {
            IWorkbook workbook = CreateSpreadsheetMailMergeWorkbook(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 SpreadsheetMailMergePreview(SpreadsheetMailMergeModel model) {
            SpreadsheetPreviewModel previewModel = model.PreviewModel;
            previewModel.Workbook = CreateSpreadsheetMailMergeWorkbook(model);
            return GenerateHtmlPreview(previewModel);
        }

        IWorkbook CreateSpreadsheetMailMergeWorkbook(SpreadsheetMailMergeModel model) {
            IWorkbook mailMergeTemplate = new Workbook();

            string filePath = HostingEnvironment.ContentRootPath + spreadsheetMailMergeDefaultFile;
            mailMergeTemplate.LoadDocument(filePath);

            List<OrderDetail> orderDetails = new List<OrderDetail>();
            OrderDetailsProvider.FillWithData(orderDetails, model.OrderId);
            mailMergeTemplate.MailMergeDataSource = orderDetails;
            mailMergeTemplate.MailMergeParameters["Order"].Value = model.OrderId;

            IList<IWorkbook> mailMergeDocuments = mailMergeTemplate.GenerateMailMergeDocuments();
            IWorkbook workbook = mailMergeDocuments[0];
            workbook.Options.Culture = new CultureInfo("en-US");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.SetPrintRange(sheet.GetDataRange());
            sheet.PrintOptions.FitToPage = true;
            sheet.PrintOptions.FitToWidth = 1;
            sheet.PrintOptions.FitToHeight = 0; // automatic
            return workbook;
        }
    }
}

namespace AspNetCoreDemos.OfficeFileAPI {
    public class SpreadsheetMailMergeModel : SpreadsheetModelBase {
        public SpreadsheetMailMergeModel() {
            PreviewModel.PreviewDocumentAction = "SpreadsheetMailMergePreview";
            PreviewModel.ControllerName = "MailMerge";
            OrderId = 10296;
        }

        public int OrderId { 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; }
    }
}