Your search did not match any results.

Advanced Document Customization

DevExtreme provides a utility that integrates its widgets with ExcelJS (an Excel document customization library). In this demo, this utility is applied in the onExporting event handler and used to add a custom header and footer to an Excel document exported from the DataGrid widget.

NOTE: This functionality is at the Community Technology Preview (CTP) development stage: it is available for testing, but its concept, design, and behavior can be reconsidered and changed without notice.

@using DevExtreme.NETCore.Demos.Models @model IEnumerable<Order> @(Html.DevExtreme().DataGrid<Order>() .ID("gridContainer") .DataSource(Model) .ShowBorders(true) .GroupPanel(groupPanel => groupPanel.Visible(true)) .Grouping(grouping => grouping.AutoExpandAll(true)) .SortByGroupSummaryInfo(i => i.Add().SummaryItem("count")) .OnCellPrepared("cellPrepared") .Export(e => e.Enabled(true)) .OnExporting("exporting") .Columns(columns => { columns.AddFor(m => m.Employee) .GroupIndex(0); columns.AddFor(m => m.OrderNumber) .Width(130); columns.AddFor(m => m.OrderDate) .Width(160); columns.AddFor(m => m.CustomerStoreCity) .GroupIndex(1); columns.AddFor(m => m.CustomerStoreState); columns.AddFor(m => m.SaleAmount) .Alignment(HorizontalAlignment.Right) .Format(Format.Currency) .SortOrder(SortOrder.Desc); }) .Summary(s => s .GroupItems(groupItems => { groupItems.AddFor(m => m.OrderNumber) .SummaryType(SummaryType.Count) .DisplayFormat("{0} orders") .AlignByColumn(false); groupItems.AddFor(m => m.SaleAmount) .SummaryType(SummaryType.Max) .DisplayFormat("Max: {0}") .ValueFormat(Format.Currency) .AlignByColumn(true) .ShowInGroupFooter(false); groupItems.AddFor(m => m.SaleAmount) .SummaryType(SummaryType.Sum) .DisplayFormat("Sum: {0}") .ValueFormat(Format.Currency) .AlignByColumn(true) .ShowInGroupFooter(true); }) .TotalItems(totalItems => { totalItems.AddFor(m => m.SaleAmount) .SummaryType(SummaryType.Sum) .DisplayFormat("Total Sum: {0}") .ValueFormat(Format.Currency); }) ) ) <script> function exporting(e) { var workbook = new ExcelJS.Workbook(); var worksheet = workbook.addWorksheet('Main sheet'); DevExpress.excelExporter.exportDataGrid({ component: e.component, worksheet: worksheet, topLeftCell: { row: 7, column: 1 }, customizeCell: function (options) { var gridCell = options.gridCell; var excelCell = options.cell; if (gridCell.rowType === 'data') { if (gridCell.data.OrderDate < new Date(2014, 2, 3)) { excelCell.font = { color: { argb: 'AAAAAA' } }; } if (gridCell.data.SaleAmount > 15000) { if (gridCell.column.dataField === 'SaleAmount') { Object.assign(excelCell, { font: { color: { argb: '000000' } }, fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFBB00' } } }); } } } if (gridCell.rowType === 'group') { var nodeColors = ['BEDFE6', 'C9ECD7']; Object.assign(excelCell, { font: { bold: true }, fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: nodeColors[gridCell.groupIndex] } } }); } if (gridCell.rowType === 'groupFooter') { excelCell.font = { italic: 'true', bold: true }; } if (gridCell.rowType === 'totalFooter') { excelCell.font = { bold: true }; } } }).then(function (dataGridRange) { customizeHeader(worksheet); customizeFooter(worksheet, dataGridRange); return Promise.resolve(); }).then(function () { workbook.xlsx.writeBuffer().then(function (buffer) { saveAs(new Blob([buffer], { type: "application/octet-stream" }), "DataGrid.xlsx"); }); }); e.cancel = true; } function cellPrepared(e) { if (e.rowType === 'data') { if (e.data.OrderDate < new Date(2014, 2, 3)) { e.cellElement.css({ color: '#AAAAAA' }); } if (e.data.SaleAmount > 15000) { if (e.column.dataField === 'OrderNumber') { e.cellElement.css({ 'font-weight': 'bold' }); } if (e.column.dataField === 'SaleAmount') { e.cellElement.css({ 'background-color': '#FFBB00', 'color': '#000' }); } } } if (e.rowType === 'group') { var nodeColors = ['#BEDFE6', '#C9ECD7']; e.cellElement.css({ 'background-color': nodeColors[e.row.groupIndex], 'color': '#000' }); e.cellElement.children().css({ 'color': '#000' }); } if (e.rowType === 'groupFooter') { e.cellElement.css({ 'font-style': 'italic' }); } } function customizeHeader(worksheet) { var generalStyles = { font: { bold: true }, fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'D3D3D3' }, bgColor: { argb: 'D3D3D3' } }, alignment: { horizontal: 'left' } }; for (var columnIndex = 1; columnIndex < 5; columnIndex++) { worksheet.getColumn(columnIndex).width = 22; } for (var rowIndex = 1; rowIndex < 6; rowIndex++) { worksheet.mergeCells(rowIndex, 1, rowIndex, 2); worksheet.mergeCells(rowIndex, 3, rowIndex, 4); Object.assign(worksheet.getRow(rowIndex).getCell(1), generalStyles); Object.assign(worksheet.getRow(rowIndex).getCell(3), generalStyles); } worksheet.getRow(1).height = 20; worksheet.getRow(1).getCell(1).font = { bold: true, size: 16 }; worksheet.getRow(1).getCell(3).numFmt = "d mmmm yyyy"; worksheet.getRow(1).getCell(3).font = { bold: true, size: 16 }; worksheet.getColumn(1).values = ["Sale Amounts:", "Company Name:", "Address:", "Phone:", "Website:"]; worksheet.getColumn(3).values = [new Date(), "K&S Music", "1000 Nicllet Mall Minneapolis Minnesota", "(612) 304-6073", "www.nowebsitemusic.com"]; } function customizeFooter(worksheet, dataGridRange) { let currentRowIndex = dataGridRange.to.row + 2; for (var rowIndex = 0; rowIndex < 3; rowIndex++) { worksheet.mergeCells(currentRowIndex + rowIndex, 1, currentRowIndex + rowIndex, 4); Object.assign(worksheet.getRow(currentRowIndex + rowIndex).getCell(1), { font: { bold: true }, alignment: { horizontal: 'right' } }); }; worksheet.getRow(currentRowIndex).getCell(1).value = "If you have any questions, please contact John Smith."; currentRowIndex++; worksheet.getRow(currentRowIndex).getCell(1).value = "Phone: +111-111"; currentRowIndex++; worksheet.getRow(currentRowIndex).getCell(1).value = "For demonstration purposes only"; worksheet.getRow(currentRowIndex).getCell(1).font = { italic: true }; } </script> <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/1.7.0/exceljs.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/7.4.0/polyfill.min.js"></script>
using DevExtreme.NETCore.Demos.Models; using DevExtreme.NETCore.Demos.Models.DataGrid; using DevExtreme.NETCore.Demos.Models.SampleData; using Microsoft.AspNetCore.Mvc; using System.Linq; namespace DevExtreme.NETCore.Demos.Controllers { public class DataGridController : Controller { public ActionResult ExcelJS() { return View(SampleData.Orders); } } }
using System; using System.ComponentModel.DataAnnotations; namespace DevExtreme.NETCore.Demos.Models { public class Order { public int ID { get; set; } [Display(Name = "Invoice Number")] public int OrderNumber { get; set; } public DateTime OrderDate { get; set; } public int SaleAmount { get; set; } public string Terms { get; set; } public int TotalAmount { get; set; } [Display(Name = "State")] public string CustomerStoreState { get; set; } [Display(Name = "City")] public string CustomerStoreCity { get; set; } public string Employee { get; set; } } }
using System; using System.Collections.Generic; namespace DevExtreme.NETCore.Demos.Models.SampleData { public partial class SampleData { public static List<Order> Orders { get { return new List<Order> { new Order { ID = 1, OrderNumber = 35703, OrderDate = DateTime.Parse("2014/04/10"), SaleAmount = 11800, Terms = "15 Days", TotalAmount = 12175, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 4, OrderNumber = 35711, OrderDate = DateTime.Parse("2014/01/12"), SaleAmount = 16050, Terms = "15 Days", TotalAmount = 16550, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 5, OrderNumber = 35714, OrderDate = DateTime.Parse("2014/01/22"), SaleAmount = 14750, Terms = "15 Days", TotalAmount = 15250, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 7, OrderNumber = 35983, OrderDate = DateTime.Parse("2014/02/07"), SaleAmount = 3725, Terms = "15 Days", TotalAmount = 3850, CustomerStoreState = "Colorado", CustomerStoreCity = "Denver", Employee = "Todd Hoffman" }, new Order { ID = 9, OrderNumber = 36987, OrderDate = DateTime.Parse("2014/03/11"), SaleAmount = 14200, Terms = "15 Days", TotalAmount = 14800, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 11, OrderNumber = 38466, OrderDate = DateTime.Parse("2014/03/01"), SaleAmount = 7800, Terms = "15 Days", TotalAmount = 8200, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 14, OrderNumber = 39420, OrderDate = DateTime.Parse("2014/02/15"), SaleAmount = 20500, Terms = "15 Days", TotalAmount = 9100, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 15, OrderNumber = 39874, OrderDate = DateTime.Parse("2014/02/04"), SaleAmount = 9050, Terms = "30 Days", TotalAmount = 19100, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 18, OrderNumber = 42847, OrderDate = DateTime.Parse("2014/02/15"), SaleAmount = 20400, Terms = "30 Days", TotalAmount = 20800, CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 19, OrderNumber = 43982, OrderDate = DateTime.Parse("2014/05/29"), SaleAmount = 6050, Terms = "30 Days", TotalAmount = 6250, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 29, OrderNumber = 56272, OrderDate = DateTime.Parse("2014/02/06"), SaleAmount = 15850, Terms = "30 Days", TotalAmount = 16350, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 30, OrderNumber = 57429, OrderDate = DateTime.Parse("2013/12/31"), SaleAmount = 11050, Terms = "30 Days", TotalAmount = 11400, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" }, new Order { ID = 32, OrderNumber = 58292, OrderDate = DateTime.Parse("2014/05/13"), SaleAmount = 13500, Terms = "15 Days", TotalAmount = 13800, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 36, OrderNumber = 62427, OrderDate = DateTime.Parse("2014/01/27"), SaleAmount = 23500, Terms = "15 Days", TotalAmount = 24000, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 39, OrderNumber = 65977, OrderDate = DateTime.Parse("2014/02/05"), SaleAmount = 2550, Terms = "15 Days", TotalAmount = 2625, CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 40, OrderNumber = 66947, OrderDate = DateTime.Parse("2014/03/23"), SaleAmount = 3500, Terms = "15 Days", TotalAmount = 3600, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 42, OrderNumber = 68428, OrderDate = DateTime.Parse("2014/04/10"), SaleAmount = 10500, Terms = "15 Days", TotalAmount = 10900, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 43, OrderNumber = 69477, OrderDate = DateTime.Parse("2014/03/09"), SaleAmount = 14200, Terms = "15 Days", TotalAmount = 14500, CustomerStoreState = "California", CustomerStoreCity = "Anaheim", Employee = "Harv Mudd" }, new Order { ID = 46, OrderNumber = 72947, OrderDate = DateTime.Parse("2014/01/14"), SaleAmount = 13350, Terms = "30 Days", TotalAmount = 13650, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 47, OrderNumber = 73088, OrderDate = DateTime.Parse("2014/03/25"), SaleAmount = 8600, Terms = "30 Days", TotalAmount = 8850, CustomerStoreState = "Nevada", CustomerStoreCity = "Reno", Employee = "Clark Morgan" }, new Order { ID = 50, OrderNumber = 76927, OrderDate = DateTime.Parse("2014/04/27"), SaleAmount = 9800, Terms = "30 Days", TotalAmount = 10050, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 51, OrderNumber = 77297, OrderDate = DateTime.Parse("2014/04/30"), SaleAmount = 10850, Terms = "30 Days", TotalAmount = 11100, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" }, new Order { ID = 56, OrderNumber = 84744, OrderDate = DateTime.Parse("2014/02/10"), SaleAmount = 4650, Terms = "30 Days", TotalAmount = 4750, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 57, OrderNumber = 85028, OrderDate = DateTime.Parse("2014/05/17"), SaleAmount = 2575, Terms = "30 Days", TotalAmount = 2625, CustomerStoreState = "Nevada", CustomerStoreCity = "Reno", Employee = "Clark Morgan" }, new Order { ID = 59, OrderNumber = 87297, OrderDate = DateTime.Parse("2014/04/21"), SaleAmount = 14200, Terms = "30 Days", CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 60, OrderNumber = 88027, OrderDate = DateTime.Parse("2014/02/14"), SaleAmount = 13650, Terms = "30 Days", TotalAmount = 14050, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 65, OrderNumber = 94726, OrderDate = DateTime.Parse("2014/05/22"), SaleAmount = 20500, Terms = "15 Days", TotalAmount = 20800, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 66, OrderNumber = 95266, OrderDate = DateTime.Parse("2014/03/10"), SaleAmount = 9050, Terms = "15 Days", TotalAmount = 9250, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 69, OrderNumber = 98477, OrderDate = DateTime.Parse("2014/01/01"), SaleAmount = 23500, Terms = "15 Days", TotalAmount = 23800, CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 70, OrderNumber = 99247, OrderDate = DateTime.Parse("2014/02/08"), SaleAmount = 2100, Terms = "15 Days", TotalAmount = 2150, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 78, OrderNumber = 174884, OrderDate = DateTime.Parse("2014/04/10"), SaleAmount = 7200, Terms = "30 Days", TotalAmount = 7350, CustomerStoreState = "Colorado", CustomerStoreCity = "Denver", Employee = "Todd Hoffman" }, new Order { ID = 81, OrderNumber = 188877, OrderDate = DateTime.Parse("2014/02/11"), SaleAmount = 8750, Terms = "30 Days", TotalAmount = 8900, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" }, new Order { ID = 82, OrderNumber = 191883, OrderDate = DateTime.Parse("2014/02/05"), SaleAmount = 9900, Terms = "30 Days", TotalAmount = 10150, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 83, OrderNumber = 192474, OrderDate = DateTime.Parse("2014/01/21"), SaleAmount = 12800, Terms = "30 Days", TotalAmount = 13100, CustomerStoreState = "California", CustomerStoreCity = "Anaheim", Employee = "Harv Mudd" }, new Order { ID = 84, OrderNumber = 193847, OrderDate = DateTime.Parse("2014/03/21"), SaleAmount = 14100, Terms = "30 Days", TotalAmount = 14350, CustomerStoreState = "California", CustomerStoreCity = "San Diego", Employee = "Harv Mudd" }, new Order { ID = 85, OrderNumber = 194877, OrderDate = DateTime.Parse("2014/03/06"), SaleAmount = 4750, Terms = "30 Days", TotalAmount = 4950, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 86, OrderNumber = 195746, OrderDate = DateTime.Parse("2014/05/26"), SaleAmount = 9050, Terms = "30 Days", TotalAmount = 9250, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 87, OrderNumber = 197474, OrderDate = DateTime.Parse("2014/03/02"), SaleAmount = 6400, Terms = "30 Days", TotalAmount = 6600, CustomerStoreState = "Nevada", CustomerStoreCity = "Reno", Employee = "Clark Morgan" }, new Order { ID = 88, OrderNumber = 198746, OrderDate = DateTime.Parse("2014/05/09"), SaleAmount = 15700, Terms = "30 Days", TotalAmount = 16050, CustomerStoreState = "Colorado", CustomerStoreCity = "Denver", Employee = "Todd Hoffman" }, new Order { ID = 91, OrderNumber = 214222, OrderDate = DateTime.Parse("2014/02/08"), SaleAmount = 11050, Terms = "30 Days", TotalAmount = 11250, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" } }; } } } }
#gridContainer { height: 440px; }