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.
Feel free to share demo-related thoughts here.
If you have technical questions, please create a support ticket in the DevExpress Support Center.
If you have technical questions, please create a support ticket in the DevExpress Support Center.
Thank you!
We appreciate your feedback.
We appreciate your feedback.
<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 = '"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';
}
}
}
};
</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.6/css/dx.common.css" />
<link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.1.6/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
}
});