Your search did not match any results.
Data Grid

Excel Cell Customization

Documentation

The DataGrid allows you to customize data when exporting it to an Excel file: adjust the font options, specify a cell’s background, modify exported values or specify their alignment and formatting options. In this demo, orders before "March 3, 2014" are displayed gray, and the background of 'Sale Amount' cells with a value greater than 15000 is orange. Click the Export button to see how it looks in Excel.

Apply
Reset
import React from 'react'; import DataGrid, { Column, Export, Summary, GroupPanel, Grouping, SortByGroupSummaryInfo, GroupItem, TotalItem } from 'devextreme-react/data-grid'; import service from './data.js'; class App extends React.Component { constructor(props) { super(props); this.orders = service.getOrders(); } render() { return ( <div> <DataGrid id={'gridContainer'} dataSource={this.orders} showBorders={true} onCellPrepared={this.onCellPrepared}> <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} customizeExcelCell={this.customizeExcelCell} /> <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> ); } 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') { if(e.row.groupIndex === 0) { e.cellElement.style.backgroundColor = '#BEDFE6'; } if(e.row.groupIndex === 1) { e.cellElement.style.backgroundColor = '#C9ECD7'; } e.cellElement.style.color = '#000'; if(e.cellElement.firstChild && e.cellElement.firstChild.style) { e.cellElement.firstChild.style.color = '#000'; } } if(e.rowType === 'groupFooter' && e.column.dataField === 'SaleAmount') { e.cellElement.style.fontStyle = 'italic'; } } customizeExcelCell(options) { var gridCell = options.gridCell; if(!gridCell) { return; } if(gridCell.rowType === 'data') { if(gridCell.data.OrderDate < new Date(2014, 2, 3)) { options.font.color = '#AAAAAA'; } if(gridCell.data.SaleAmount > 15000) { if(gridCell.column.dataField === 'OrderNumber') { options.font.bold = true; } if(gridCell.column.dataField === 'SaleAmount') { options.backgroundColor = '#FFBB00'; options.font.color = '#000000'; } } } if(gridCell.rowType === 'group') { if(gridCell.groupIndex === 0) { options.backgroundColor = '#BEDFE6'; } if(gridCell.groupIndex === 1) { options.backgroundColor = '#C9ECD7'; } if(gridCell.column.dataField === 'Employee') { options.value = `${gridCell.value} (${gridCell.groupSummaryItems[0].value} items)`; options.font.bold = false; } if(gridCell.column.dataField === 'SaleAmount') { options.value = gridCell.groupSummaryItems[0].value; options.numberFormat = '&quot;Max: &quot;$0.00'; } } if(gridCell.rowType === 'groupFooter' && gridCell.column.dataField === 'SaleAmount') { options.value = gridCell.value; options.numberFormat = '&quot;Sum: &quot;$0.00'; options.font.italic = true; } if(gridCell.rowType === 'totalFooter' && gridCell.column.dataField === 'SaleAmount') { options.value = gridCell.value; options.numberFormat = '&quot;Total Sum: &quot;$0.00'; } } } 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', // 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 } });