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

<template> <div> <DxDataGrid id="gridContainer" :data-source="orders" :show-borders="true" @cell-prepared="cellPrepared" > <DxGroupPanel :visible="true"/> <DxGrouping :auto-expand-all="true"/> <DxSortByGroupSummaryInfo summary-item="count"/> <DxColumn :group-index="0" data-field="Employee" /> <DxColumn :width="130" data-field="OrderNumber" caption="Invoice Number" /> <DxColumn :width="160" data-field="OrderDate" data-type="date" /> <DxColumn :group-index="1" data-field="CustomerStoreCity" caption="City" /> <DxColumn data-field="CustomerStoreState" caption="State" /> <DxColumn data-field="SaleAmount" alignment="right" format="currency" sort-order="desc" /> <DxExport :enabled="true" :customize-excel-cell="customizeExcelCell" /> <DxSummary> <DxGroupItem :align-by-column="false" column="OrderNumber" summary-type="count" display-format="{0} orders" /> <DxGroupItem :align-by-column="true" :show-in-group-footer="false" column="SaleAmount" summary-type="max" display-format="Max: {0}" value-format="currency" /> <DxGroupItem :align-by-column="true" :show-in-group-footer="true" column="SaleAmount" summary-type="sum" display-format="Sum: {0}" value-format="currency" /> <DxTotalItem column="SaleAmount" summary-type="sum" display-format="Total Sum: {0}" value-format="currency" /> </DxSummary> </DxDataGrid> </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.2.7/css/dx.common.css" /> <link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/19.2.7/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"> </div> </div> </body> </html>

System.config({ transpiler: 'plugin-babel', meta: { '*.vue': { loader: 'vue-loader' }, }, paths: { 'npm:': 'https://unpkg.com/' }, map: { 'vue': 'npm:vue@2.6.3/dist/vue.esm.browser.js', 'vue-loader': 'npm:dx-systemjs-vue-browser@latest/index.js', 'devextreme': 'npm:devextreme@19.2', 'devextreme-vue': 'npm:devextreme-vue@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@0.1.54', 'devexpress-gantt': 'npm:devexpress-gantt@0.0.29', 'plugin-babel': 'npm:systemjs-plugin-babel@0/plugin-babel.js', 'systemjs-babel-build': 'npm:systemjs-plugin-babel@0/systemjs-babel-browser.js' }, packages: { 'devextreme-vue': { main: 'index.js' }, 'devextreme': { defaultExtension: 'js' }, }, babelOptions: { sourceMaps: false, stage0: true } });