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.

Copy to CodeSandBox
Apply
Reset
<template> <div> <dx-data-grid id="gridContainer" :data-source="orders" :show-borders="true" @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" :customize-excel-cell="customizeExcelCell" /> <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 service from './data.js'; export default { components: { DxDataGrid, DxColumn, DxExport, DxSummary, DxGroupPanel, DxGrouping, DxGroupItem, DxSortByGroupSummaryInfo, DxTotalItem }, data() { return { orders: service.getOrders() }; }, methods: { 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') { 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'; } } } }; </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.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://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', '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 } });