Your search did not match any results.
Data Grid

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.

Copy to CodePen
Apply
Reset
var DemoApp = angular.module('DemoApp', ['dx']); DemoApp.controller('DemoController', function DemoController($scope) { $scope.gridOptions = { dataSource: orders, showBorders: true, export: { enabled: true }, groupPanel: { visible: true, }, grouping: { autoExpandAll: true, }, sortByGroupSummaryInfo: [{ summaryItem: "count" }], columns: [ { dataField: "Employee", groupIndex: 0, }, { dataField: "OrderNumber", caption: "Invoice Number", width: 130, }, { dataField: "OrderDate", dataType: "date", width: 160 }, { caption: "City", dataField: "CustomerStoreCity", groupIndex: 1 }, { caption: "State", dataField: "CustomerStoreState" }, { dataField: "SaleAmount", alignment: "right", format: "currency", sortOrder: "desc" } ], summary: { groupItems: [{ column: "OrderNumber", summaryType: "count", displayFormat: "{0} orders", }, { column: "SaleAmount", summaryType: "max", displayFormat: "Max: {0}", valueFormat: "currency", alignByColumn: true, showInGroupFooter: false }, { column: "SaleAmount", summaryType: "sum", displayFormat: "Sum: {0}", valueFormat: "currency", alignByColumn: true, showInGroupFooter: true }], totalItems: [{ column: "SaleAmount", summaryType: "sum", displayFormat: "Total Sum: {0}", valueFormat: "currency" }] }, onExporting: function(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; }, onCellPrepared: 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 }; }; });
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title>DevExtreme Demo</title> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0" /> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script>window.jQuery || document.write(decodeURIComponent('%3Cscript src="js/jquery.min.js"%3E%3C/script%3E'))</script> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.1.5/css/dx.common.css" /> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.1.5/css/dx.light.css" /> <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.2/jszip.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.16/angular.min.js"></script> <script>window.angular || document.write(decodeURIComponent('%3Cscript src="js/angular.min.js"%3E%3C\/script%3E'))</script> <script src="https://cdn3.devexpress.com/jslib/19.1.5/js/dx.all.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/7.4.0/polyfill.min.js"></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="data.js"></script> <link rel="stylesheet" type="text/css" href="styles.css" /> <script src="index.js"></script> </head> <body class="dx-viewport"> <div class="demo-container" ng-app="DemoApp" ng-controller="DemoController"> <div id="gridContainer" dx-data-grid="gridOptions"></div> </div> </body> </html>
#gridContainer { height: 440px; }
var orders = [{ "ID" : 1, "OrderNumber" : 35703, "OrderDate" : new Date(2014, 3, 10), "SaleAmount" : 11800, "Terms" : "15 Days", "TotalAmount" : 12175, "CustomerStoreState" : "California", "CustomerStoreCity" : "Los Angeles", "Employee" : "Harv Mudd" }, { "ID" : 4, "OrderNumber" : 35711, "OrderDate" : new Date(2014, 0, 12), "SaleAmount" : 16050, "Terms" : "15 Days", "TotalAmount" : 16550, "CustomerStoreState" : "California", "CustomerStoreCity" : "San Jose", "Employee" : "Jim Packard" }, { "ID" : 5, "OrderNumber" : 35714, "OrderDate" : new Date(2014, 0, 22), "SaleAmount" : 14750, "Terms" : "15 Days", "TotalAmount" : 15250, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 7, "OrderNumber" : 35983, "OrderDate" : new Date(2014, 1, 7), "SaleAmount" : 3725, "Terms" : "15 Days", "TotalAmount" : 3850, "CustomerStoreState" : "Colorado", "CustomerStoreCity" : "Denver", "Employee" : "Todd Hoffman" }, { "ID" : 9, "OrderNumber" : 36987, "OrderDate" : new Date(2014, 2, 11), "SaleAmount" : 14200, "Terms" : "15 Days", "TotalAmount" : 14800, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 11, "OrderNumber" : 38466, "OrderDate" : new Date(2014, 2, 1), "SaleAmount" : 7800, "Terms" : "15 Days", "TotalAmount" : 8200, "CustomerStoreState" : "California", "CustomerStoreCity" : "Los Angeles", "Employee" : "Harv Mudd" }, { "ID" : 14, "OrderNumber" : 39420, "OrderDate" : new Date(2014, 1, 15), "SaleAmount" : 20500, "Terms" : "15 Days", "TotalAmount" : 9100, "CustomerStoreState" : "California", "CustomerStoreCity" : "San Jose", "Employee" : "Jim Packard" }, { "ID" : 15, "OrderNumber" : 39874, "OrderDate" : new Date(2014, 1, 4), "SaleAmount" : 9050, "Terms" : "30 Days", "TotalAmount" : 19100, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 18, "OrderNumber" : 42847, "OrderDate" : new Date(2014, 1, 15), "SaleAmount" : 20400, "Terms" : "30 Days", "TotalAmount" : 20800, "CustomerStoreState" : "Wyoming", "CustomerStoreCity" : "Casper", "Employee" : "Todd Hoffman" }, { "ID" : 19, "OrderNumber" : 43982, "OrderDate" : new Date(2014, 4, 29), "SaleAmount" : 6050, "Terms" : "30 Days", "TotalAmount" : 6250, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 29, "OrderNumber" : 56272, "OrderDate" : new Date(2014, 1, 6), "SaleAmount" : 15850, "Terms" : "30 Days", "TotalAmount" : 16350, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 30, "OrderNumber" : 57429, "OrderDate" : new Date(2013, 11, 31), "SaleAmount" : 11050, "Terms" : "30 Days", "TotalAmount" : 11400, "CustomerStoreState" : "Arizona", "CustomerStoreCity" : "Phoenix", "Employee" : "Clark Morgan" }, { "ID" : 32, "OrderNumber" : 58292, "OrderDate" : new Date(2014, 4, 13), "SaleAmount" : 13500, "Terms" : "15 Days", "TotalAmount" : 13800, "CustomerStoreState" : "California", "CustomerStoreCity" : "Los Angeles", "Employee" : "Harv Mudd" }, { "ID" : 36, "OrderNumber" : 62427, "OrderDate" : new Date(2014, 0, 27), "SaleAmount" : 23500, "Terms" : "15 Days", "TotalAmount" : 24000, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 39, "OrderNumber" : 65977, "OrderDate" : new Date(2014, 1, 5), "SaleAmount" : 2550, "Terms" : "15 Days", "TotalAmount" : 2625, "CustomerStoreState" : "Wyoming", "CustomerStoreCity" : "Casper", "Employee" : "Todd Hoffman" }, { "ID" : 40, "OrderNumber" : 66947, "OrderDate" : new Date(2014, 2, 23), "SaleAmount" : 3500, "Terms" : "15 Days", "TotalAmount" : 3600, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 42, "OrderNumber" : 68428, "OrderDate" : new Date(2014, 3, 10), "SaleAmount" : 10500, "Terms" : "15 Days", "TotalAmount" : 10900, "CustomerStoreState" : "California", "CustomerStoreCity" : "Los Angeles", "Employee" : "Harv Mudd" }, { "ID" : 43, "OrderNumber" : 69477, "OrderDate" : new Date(2014, 2, 9), "SaleAmount" : 14200, "Terms" : "15 Days", "TotalAmount" : 14500, "CustomerStoreState" : "California", "CustomerStoreCity" : "Anaheim", "Employee" : "Harv Mudd" }, { "ID" : 46, "OrderNumber" : 72947, "OrderDate" : new Date(2014, 0, 14), "SaleAmount" : 13350, "Terms" : "30 Days", "TotalAmount" : 13650, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 47, "OrderNumber" : 73088, "OrderDate" : new Date(2014, 2, 25), "SaleAmount" : 8600, "Terms" : "30 Days", "TotalAmount" : 8850, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Reno", "Employee" : "Clark Morgan" }, { "ID" : 50, "OrderNumber" : 76927, "OrderDate" : new Date(2014, 3, 27), "SaleAmount" : 9800, "Terms" : "30 Days", "TotalAmount" : 10050, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 51, "OrderNumber" : 77297, "OrderDate" : new Date(2014, 3, 30), "SaleAmount" : 10850, "Terms" : "30 Days", "TotalAmount" : 11100, "CustomerStoreState" : "Arizona", "CustomerStoreCity" : "Phoenix", "Employee" : "Clark Morgan" }, { "ID" : 56, "OrderNumber" : 84744, "OrderDate" : new Date(2014, 1, 10), "SaleAmount" : 4650, "Terms" : "30 Days", "TotalAmount" : 4750, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 57, "OrderNumber" : 85028, "OrderDate" : new Date(2014, 4, 17), "SaleAmount" : 2575, "Terms" : "30 Days", "TotalAmount" : 2625, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Reno", "Employee" : "Clark Morgan" }, { "ID" : 59, "OrderNumber" : 87297, "OrderDate" : new Date(2014, 3, 21), "SaleAmount" : 14200, "Terms" : "30 Days", "TotalAmount" : 0, "CustomerStoreState" : "Wyoming", "CustomerStoreCity" : "Casper", "Employee" : "Todd Hoffman" }, { "ID" : 60, "OrderNumber" : 88027, "OrderDate" : new Date(2014, 1, 14), "SaleAmount" : 13650, "Terms" : "30 Days", "TotalAmount" : 14050, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 65, "OrderNumber" : 94726, "OrderDate" : new Date(2014, 4, 22), "SaleAmount" : 20500, "Terms" : "15 Days", "TotalAmount" : 20800, "CustomerStoreState" : "California", "CustomerStoreCity" : "San Jose", "Employee" : "Jim Packard" }, { "ID" : 66, "OrderNumber" : 95266, "OrderDate" : new Date(2014, 2, 10), "SaleAmount" : 9050, "Terms" : "15 Days", "TotalAmount" : 9250, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 69, "OrderNumber" : 98477, "OrderDate" : new Date(2014, 0, 1), "SaleAmount" : 23500, "Terms" : "15 Days", "TotalAmount" : 23800, "CustomerStoreState" : "Wyoming", "CustomerStoreCity" : "Casper", "Employee" : "Todd Hoffman" }, { "ID" : 70, "OrderNumber" : 99247, "OrderDate" : new Date(2014, 1, 8), "SaleAmount" : 2100, "Terms" : "15 Days", "TotalAmount" : 2150, "CustomerStoreState" : "Utah", "CustomerStoreCity" : "Salt Lake City", "Employee" : "Clark Morgan" }, { "ID" : 78, "OrderNumber" : 174884, "OrderDate" : new Date(2014, 3, 10), "SaleAmount" : 7200, "Terms" : "30 Days", "TotalAmount" : 7350, "CustomerStoreState" : "Colorado", "CustomerStoreCity" : "Denver", "Employee" : "Todd Hoffman" }, { "ID" : 81, "OrderNumber" : 188877, "OrderDate" : new Date(2014, 1, 11), "SaleAmount" : 8750, "Terms" : "30 Days", "TotalAmount" : 8900, "CustomerStoreState" : "Arizona", "CustomerStoreCity" : "Phoenix", "Employee" : "Clark Morgan" }, { "ID" : 82, "OrderNumber" : 191883, "OrderDate" : new Date(2014, 1, 5), "SaleAmount" : 9900, "Terms" : "30 Days", "TotalAmount" : 10150, "CustomerStoreState" : "California", "CustomerStoreCity" : "Los Angeles", "Employee" : "Harv Mudd" }, { "ID" : 83, "OrderNumber" : 192474, "OrderDate" : new Date(2014, 0, 21), "SaleAmount" : 12800, "Terms" : "30 Days", "TotalAmount" : 13100, "CustomerStoreState" : "California", "CustomerStoreCity" : "Anaheim", "Employee" : "Harv Mudd" }, { "ID" : 84, "OrderNumber" : 193847, "OrderDate" : new Date(2014, 2, 21), "SaleAmount" : 14100, "Terms" : "30 Days", "TotalAmount" : 14350, "CustomerStoreState" : "California", "CustomerStoreCity" : "San Diego", "Employee" : "Harv Mudd" }, { "ID" : 85, "OrderNumber" : 194877, "OrderDate" : new Date(2014, 2, 6), "SaleAmount" : 4750, "Terms" : "30 Days", "TotalAmount" : 4950, "CustomerStoreState" : "California", "CustomerStoreCity" : "San Jose", "Employee" : "Jim Packard" }, { "ID" : 86, "OrderNumber" : 195746, "OrderDate" : new Date(2014, 4, 26), "SaleAmount" : 9050, "Terms" : "30 Days", "TotalAmount" : 9250, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Las Vegas", "Employee" : "Harv Mudd" }, { "ID" : 87, "OrderNumber" : 197474, "OrderDate" : new Date(2014, 2, 2), "SaleAmount" : 6400, "Terms" : "30 Days", "TotalAmount" : 6600, "CustomerStoreState" : "Nevada", "CustomerStoreCity" : "Reno", "Employee" : "Clark Morgan" }, { "ID" : 88, "OrderNumber" : 198746, "OrderDate" : new Date(2014, 4, 9), "SaleAmount" : 15700, "Terms" : "30 Days", "TotalAmount" : 16050, "CustomerStoreState" : "Colorado", "CustomerStoreCity" : "Denver", "Employee" : "Todd Hoffman" }, { "ID" : 91, "OrderNumber" : 214222, "OrderDate" : new Date(2014, 1, 8), "SaleAmount" : 11050, "Terms" : "30 Days", "TotalAmount" : 11250, "CustomerStoreState" : "Arizona", "CustomerStoreCity" : "Phoenix", "Employee" : "Clark Morgan" }];