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.
Feel free to share demo-related thoughts here.
If you have technical questions, please create a support ticket in the DevExpress Support Center.
If you have technical questions, please create a support ticket in the DevExpress Support Center.
Thank you!
We appreciate your feedback.
We appreciate your feedback.
@using DevExtreme.MVC.Demos.Models
@model IEnumerable<Order>
@section ExternalDependencies {
<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>
}
@(Html.DevExtreme().DataGrid<Order>()
.ID("gridContainer")
.DataSource(Model)
.Selection(s => s.Mode(SelectionMode.Multiple))
.Export(e => e.Enabled(true).AllowExportSelectedData(true))
.ShowBorders(true)
.GroupPanel(groupPanel => groupPanel.Visible(true))
.Grouping(grouping => grouping.AutoExpandAll(true))
.SortByGroupSummaryInfo(i => i.Add().SummaryItem("count"))
.OnCellPrepared("cellPrepared")
.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');
/*
The 'DevExpress.excelExporter.exportDataGrid' function uses the ExcelJS library.
For more information about ExcelJS, see:
- https://github.com/exceljs/exceljs#contents
- https://github.com/exceljs/exceljs#browser
*/
DevExpress.excelExporter.exportDataGrid({
component: e.component,
worksheet: worksheet,
topLeftCell: { row: 7, column: 1 },
customizeCell: function(options) {
/*
The 'options.cell' field contains an ExcelJS object that describes an Excel cell.
Refer to the following topics for more details about its members:
- value and type - https://github.com/exceljs/exceljs#value-types
- alignment - https://github.com/exceljs/exceljs#alignment
- border - https://github.com/exceljs/exceljs#borders
- fill - https://github.com/exceljs/exceljs#fills
- font - https://github.com/exceljs/exceljs#fonts
- numFmt - https://github.com/exceljs/exceljs#number-formats
The 'options.gridCell' object fields are described in https://js.devexpress.com/Documentation/ApiReference/Common/Object_Structures/ExcelDataGridCell/
*/
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 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>
using DevExtreme.MVC.Demos.Models;
using DevExtreme.MVC.Demos.Models.DataGrid;
using DevExtreme.MVC.Demos.Models.SampleData;
using System;
using System.Linq;
using System.Web.Mvc;
namespace DevExtreme.MVC.Demos.Controllers {
public class DataGridController : Controller {
public ActionResult ExcelJS() {
return View(SampleData.Orders);
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace DevExtreme.MVC.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.MVC.Demos.Models.SampleData {
public partial class SampleData {
public static readonly IEnumerable<Order> Orders = new[] {
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;
}