Spreadsheet Mail Merge

This demo shows how to use a mail merge to generate detailed reports for customer orders with the help of the Spreadsheet Document API. Select an Order ID to view order details. Click Download to save the report to your computer.

Options
Order Id
File Format
@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;
using Microsoft.Extensions.Logging;

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