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.

See more Hide details

Prev Demo Next Demo

Was this demo helpful?

Thank you!

Feel free to share demo-related thoughts here.
If you have technical questions, please create a support ticket in the DevExpress Support Center.

Send Feedback

Thank you!
We appreciate your feedback.

jQuery

Angular

Vue

React

AngularJS

ASP.NET Core

ASP.NET MVC

Copy to CodeSandBox

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 = '"Max: "$0.00'; } } if(gridCell.rowType === 'groupFooter' && gridCell.column.dataField === 'SaleAmount') { options.value = gridCell.value; options.numberFormat = '"Sum: "$0.00'; options.font.italic = true; } if(gridCell.rowType === 'totalFooter' && gridCell.column.dataField === 'SaleAmount') { options.value = gridCell.value; options.numberFormat = '"Total Sum: "$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/20.1.7/css/dx.common.css" /> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/20.1.7/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; }

System.config({ transpiler: 'plugin-babel', meta: { }, 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@20.1', 'devextreme-react': 'npm:devextreme-react@20.1', 'jszip': 'npm:jszip@3.1.3/dist/jszip.min.js', 'quill': 'npm:quill@1.3.7/dist/quill.js', 'devexpress-diagram': 'npm:devexpress-diagram@1.0.14', 'devexpress-gantt': 'npm:devexpress-gantt@1.0.7', // 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' }, 'devextreme/events/utils': { main: 'index' }, 'devextreme/events': { main: 'index' }, }, babelOptions: { sourceMaps: false, stage0: true, react: true } });