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.

Apply
Reset
import React from 'react'; import DataGrid, { Column, Export, Selection, Summary, GroupPanel, Grouping, SortByGroupSummaryInfo, GroupItem, TotalItem } from 'devextreme-react/data-grid'; import ExcelJS from 'exceljs'; import saveAs from 'file-saver'; import excelExporter from 'devextreme/exporter/exceljs/excelExporter'; import service from './data.js'; class App extends React.Component { constructor(props) { super(props); this.orders = service.getOrders(); this.onExporting = this.onExporting.bind(this); } render() { return ( <div> <DataGrid id={'gridContainer'} dataSource={this.orders} showBorders={true} onExporting={this.onExporting} onCellPrepared={this.onCellPrepared}> <Selection mode={'multiple'} /> <GroupPanel visible={true} /> <Grouping autoExpandAll={true} /> <SortByGroupSummaryInfo summaryItem={'count'} /> <Column dataField={'Employee'} groupIndex={0} /> <Column dataField={'OrderNumber'} caption={'Invoice Number'} width={130} /> <Column dataField={'OrderDate'} dataType={'date'} width={160} /> <Column dataField={'CustomerStoreCity'} caption={'City'} groupIndex={1} /> <Column dataField={'CustomerStoreState'} caption={'State'} /> <Column dataField={'SaleAmount'} alignment={'right'} format={'currency'} sortOrder={'desc'} /> <Export enabled={true} allowExportSelectedData={true} /> <Summary> <GroupItem column={'OrderNumber'} summaryType={'count'} displayFormat={'{0} orders'} alignByColumn={false} /> <GroupItem column={'SaleAmount'} summaryType={'max'} displayFormat={'Max: {0}'} valueFormat={'currency'} alignByColumn={true} showInGroupFooter={false} /> <GroupItem column={'SaleAmount'} summaryType={'sum'} displayFormat={'Sum: {0}'} valueFormat={'currency'} alignByColumn={true} showInGroupFooter={true} /> <TotalItem column={'SaleAmount'} summaryType={'sum'} displayFormat={'Total Sum: {0}'} valueFormat={'currency'} /> </Summary> </DataGrid> </div> ); } onExporting(e) { var workbook = new ExcelJS.Workbook(); var worksheet = workbook.addWorksheet('Main sheet'); var context = this; /* 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 */ excelExporter.exportDataGrid({ component: e.component, worksheet: worksheet, topLeftCell: { row: 7, column: 1 }, customizeCell: function(options) { /* The 'options.excelCell' 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.excelCell; 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, { fill: { type: 'pattern', pattern:'solid', fgColor: { argb: nodeColors[gridCell.groupIndex] } } }); } if(gridCell.rowType === 'groupFooter' && excelCell.value) { Object.assign(excelCell.font, { italic: true }); } } }).then(function(dataGridRange) { context.customizeHeader(worksheet); context.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; } 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']; } customizeFooter(worksheet, dataGridRange) { var 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 }; } onCellPrepared(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'; } } } export default App;
import React from 'react'; import ReactDOM from 'react-dom'; import App from './App.js'; ReactDOM.render( <App />, document.getElementById('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.2.3/css/dx.common.css" /> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.2.3/css/dx.light.css" /> <link rel="stylesheet" type="text/css" href="styles.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"></div> </div> </body> </html>
#gridContainer { height: 440px; }
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/' }, defaultExtension: 'js', map: { 'react': 'npm:react@16/umd/react.development.js', 'react-dom': 'npm:react-dom@16/umd/react-dom.development.js', 'prop-types': 'npm:prop-types/prop-types.js', 'devextreme': 'npm:devextreme@19.2', 'devextreme-react': 'npm:devextreme-react@19.2', 'jszip': 'npm:jszip@3.1.3/dist/jszip.min.js', 'quill': 'npm:quill@1.3.7/dist/quill.js', 'devexpress-diagram': 'npm:devexpress-diagram', 'devexpress-gantt': 'npm:devexpress-gantt', 'exceljs': 'npm:exceljs@1.7.0/dist/exceljs.js', 'file-saver': 'npm:file-saver@1.3.8/FileSaver.js', // SystemJS plugins 'plugin-babel': 'npm:systemjs-plugin-babel@0/plugin-babel.js', 'systemjs-babel-build': 'npm:systemjs-plugin-babel@0/systemjs-babel-browser.js' }, packages: { 'devextreme': { defaultExtension: 'js' }, 'devextreme-react': { main: 'index.js' } }, babelOptions: { sourceMaps: false, stage0: true, react: true } });