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 CodeSandBox
Apply
Reset
<template> <div> <dx-data-grid id="gridContainer" :data-source="orders" :show-borders="true" @exporting="exporting" @cell-prepared="cellPrepared" > <dx-group-panel :visible="true"/> <dx-grouping :auto-expand-all="true"/> <dx-sort-by-group-summary-info summary-item="count"/> <dx-column :group-index="0" data-field="Employee" /> <dx-column :width="130" data-field="OrderNumber" caption="Invoice Number" /> <dx-column :width="160" data-field="OrderDate" data-type="date" /> <dx-column :group-index="1" data-field="CustomerStoreCity" caption="City" /> <dx-column data-field="CustomerStoreState" caption="State" /> <dx-column data-field="SaleAmount" alignment="right" format="currency" sort-order="desc" /> <dx-export :enabled="true" /> <dx-summary> <dx-group-item :align-by-column="false" column="OrderNumber" summary-type="count" display-format="{0} orders" /> <dx-group-item :align-by-column="true" :show-in-group-footer="false" column="SaleAmount" summary-type="max" display-format="Max: {0}" value-format="currency" /> <dx-group-item :align-by-column="true" :show-in-group-footer="true" column="SaleAmount" summary-type="sum" display-format="Sum: {0}" value-format="currency" /> <dx-total-item column="SaleAmount" summary-type="sum" display-format="Total Sum: {0}" value-format="currency" /> </dx-summary> </dx-data-grid> </div> </template> <script> import { DxDataGrid, DxColumn, DxExport, DxSummary, DxGroupPanel, DxGrouping, DxGroupItem, DxSortByGroupSummaryInfo, DxTotalItem } from 'devextreme-vue/data-grid'; import excelExporter from 'devextreme/exporter/exceljs/excelExporter'; import ExcelJS from 'exceljs'; import saveAs from 'file-saver'; import service from './data.js'; export default { components: { DxDataGrid, DxColumn, DxExport, DxSummary, DxGroupPanel, DxGrouping, DxGroupItem, DxSortByGroupSummaryInfo, DxTotalItem }, data() { return { orders: service.getOrders() }; }, methods: { exporting(e) { var workbook = new ExcelJS.Workbook(); var worksheet = workbook.addWorksheet('Main sheet'); excelExporter.exportDataGrid({ component: e.component, worksheet: worksheet, topLeftCell: { row: 7, column: 1 }, customizeCell: 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; }, cellPrepared(e) { if(e.rowType === 'data') { if(e.data.OrderDate < new Date(2014, 2, 3)) { e.cellElement.style.color = '#AAAAAA'; } if(e.data.SaleAmount > 15000) { if(e.column.dataField === 'OrderNumber') { e.cellElement.style.fontWeight = 'bold'; } if(e.column.dataField === 'SaleAmount') { e.cellElement.style.backgroundColor = '#FFBB00'; e.cellElement.style.color = '#000000'; } } } if(e.rowType === 'group') { var nodeColors = [ '#BEDFE6', '#C9ECD7']; e.cellElement.style.backgroundColor = nodeColors[e.row.groupIndex]; e.cellElement.style.color = '#000'; if(e.cellElement.firstChild && e.cellElement.firstChild.style) e.cellElement.firstChild.style.color = '#000'; } if(e.rowType === 'groupFooter') { e.cellElement.style.fontStyle = '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> <style scoped> #gridContainer { height: 440px; } </style>
import Vue from 'vue'; import App from './App.vue'; new Vue({ el: '#app', components: { App }, template: '<App/>' });
<!DOCTYPE html> <html> <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" /> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.1.4/css/dx.common.css" /> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.1.4/css/dx.light.css" /> <script src="https://unpkg.com/core-js@2.4.1/client/shim.min.js"></script> <script src="https://unpkg.com/systemjs@0.21.3/dist/system.js"></script> <script type="text/javascript" src="config.js"></script> <script type="text/javascript"> System.import('./index.js'); </script> </head> <body class="dx-viewport"> <div class="demo-container"> <div id="app"> <span v-if="false">Loading...</span> </div> </div> </body> </html>
let 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' }]; export default { getOrders() { return orders; } };
System.config({ transpiler: 'plugin-babel', paths: { 'npm:': 'https://unpkg.com/' }, map: { vue: 'npm:vue@2.6.3/dist/vue.esm.browser.js', 'vue-loader': 'npm:systemjs-vue-browser@latest/index.js', 'devextreme': 'npm:devextreme@19.1', 'devextreme-vue': 'npm:devextreme-vue@19.1', jszip: 'npm:jszip@3.1.3/dist/jszip.min.js', 'quill': 'npm:quill@1.3.6/dist/quill.js', 'devexpress-diagram': 'npm:devexpress-diagram', 'exceljs': 'npm:exceljs@1.7.0/dist/exceljs.min.js', 'file-saver': 'npm:file-saver@1.3.8/FileSaver.js', 'plugin-babel': 'npm:systemjs-plugin-babel@0/plugin-babel.js', 'systemjs-babel-build': 'npm:systemjs-plugin-babel@0/systemjs-babel-browser.js' }, meta: { '*.vue': { loader: 'vue-loader' } }, packages: { 'devextreme-vue': { main: 'index.js' }, 'devextreme': { defaultExtension: 'js' } }, babelOptions: { sourceMaps: false, stage0: true } });