Help us shape the 2021 DevExtreme Roadmap.
Take our 5 minute survey.
Cell Customization
The customizeCell function allows you to modify cell data and value formatting in exported worksheets. You can access and change the following attributes:
- Font properties
- Cell background
- Cell values
- Text alignment
- Formatting properties
The customizeCell function also allows you to identify row types. For example, this demo changes the background color and font weight for cells with the "group" rowType.
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.
var DemoApp = angular.module('DemoApp', ['dx']);
DemoApp.controller('DemoController', function DemoController($scope) {
$scope.gridOptions = {
dataSource: companies,
showBorders: true,
groupPanel: {
visible: true
},
grouping: {
autoExpandAll: true,
},
sortByGroupSummaryInfo: [{
summaryItem: "count"
}],
columns: [{
dataField: "Name",
width: 190
}, {
dataField: "Address",
width: 200
},
"City",
{
dataField: "State",
groupIndex: 0
}, {
dataField: "Phone",
format: function(value) {
var USNumber = value.match(/(\d{3})(\d{3})(\d{4})/);
return "(" + USNumber[1] + ") " + USNumber[2] + "-" + USNumber[3];
}
}, {
dataField: "Website",
caption: "",
alignment: "center",
width: 100,
cellTemplate: function(container, options) {
return $("<a>", { "href": options.value, "target": "_blank" }).text('Website');
}
}],
summary: {
totalItems: [{
column: "Name",
summaryType: "count",
displayFormat: "Total count: {0} companies" }
]
},
export: {
enabled: true
},
onExporting: function(e) {
var workbook = new ExcelJS.Workbook();
var worksheet = workbook.addWorksheet('Companies');
worksheet.columns = [
{ width: 5 }, { width: 30 }, { width: 25 }, { width: 15 }, { width: 25 }, { width: 40 }
];
DevExpress.excelExporter.exportDataGrid({
component: e.component,
worksheet: worksheet,
keepColumnWidths: false,
topLeftCell: { row: 2, column: 2 },
customizeCell: function(options) {
var gridCell = options.gridCell;
var excelCell = options.excelCell;
if(gridCell.rowType === "data") {
if(gridCell.column.dataField === 'Phone') {
excelCell.value = parseInt(gridCell.value);
excelCell.numFmt = '[<=9999999]###-####;(###) ###-####';
}
if(gridCell.column.dataField === 'Website') {
excelCell.value = { text: gridCell.value, hyperlink: gridCell.value };
excelCell.font = { color: { argb: 'FF0000FF' }, underline: true };
excelCell.alignment = { horizontal: 'left' };
}
}
if(gridCell.rowType === "group") {
excelCell.fill = { type: 'pattern', pattern:'solid', fgColor: { argb: "BEDFE6" } };
}
if(gridCell.rowType === "totalFooter" && excelCell.value) {
excelCell.font.italic = true;
}
}
}).then(function() {
workbook.xlsx.writeBuffer().then(function(buffer) {
saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Companies.xlsx");
});
});
e.cancel = true;
}
};
});
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" >
<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" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>window.jQuery || document.write(decodeURIComponent('%3Cscript src="js/jquery.min.js"%3E%3C/script%3E'))</script>
<link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/20.2.5/css/dx.common.css" />
<link rel="stylesheet" type="text/css" href="https://cdn3.devexpress.com/jslib/20.2.5/css/dx.light.css" />
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.16/angular.min.js"></script>
<script>window.angular || document.write(decodeURIComponent('%3Cscript src="js/angular.min.js"%3E%3C/script%3E'))</script>
<script src="https://cdn3.devexpress.com/jslib/20.2.5/js/dx.all.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/7.4.0/polyfill.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/3.3.1/exceljs.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
<script src="data.js"></script>
<link rel="stylesheet" type="text/css" href="styles.css" />
<script src="index.js"></script>
</head>
<body class="dx-viewport">
<div class="demo-container" ng-app="DemoApp" ng-controller="DemoController">
<div id="gridContainer" dx-data-grid="gridOptions"></div>
</div>
</body>
</html>
#gridContainer {
height: 436px;
}
var companies = [{
"ID": 1,
"Name": "Super Mart of the West",
"Address": "702 SW 8th Street",
"City": "Bentonville",
"State": "Arkansas",
"Zipcode": 72716,
"Phone": "8005552797",
"Fax": "(800) 555-2171",
"Website": "http://nowebsitesupermart.com"
}, {
"ID": 2,
"Name": "Electronics Depot",
"Address": "2455 Paces Ferry Road NW",
"City": "Atlanta",
"State": "Georgia",
"Zipcode": 30339,
"Phone": "8005953232",
"Fax": "(800) 595-3231",
"Website": "http://nowebsitedepot.com"
}, {
"ID": 3,
"Name": "K&S Music",
"Address": "1000 Nicllet Mall",
"City": "Minneapolis",
"State": "Minnesota",
"Zipcode": 55403,
"Phone": "6123046073",
"Fax": "(612) 304-6074",
"Website": "http://nowebsitemusic.com"
}, {
"ID": 4,
"Name": "Tom's Club",
"Address": "999 Lake Drive",
"City": "Issaquah",
"State": "Washington",
"Zipcode": 98027,
"Phone": "8009552292",
"Fax": "(800) 955-2293",
"Website": "http://nowebsitetomsclub.com"
}, {
"ID": 5,
"Name": "E-Mart",
"Address": "3333 Beverly Rd",
"City": "Hoffman Estates",
"State": "Illinois",
"Zipcode": 60179,
"Phone": "8472862500",
"Fax": "(847) 286-2501",
"Website": "http://nowebsiteemart.com"
}, {
"ID": 6,
"Name": "Walters",
"Address": "200 Wilmot Rd",
"City": "Deerfield",
"State": "Illinois",
"Zipcode": 60015,
"Phone": "8479402500",
"Fax": "(847) 940-2501",
"Website": "http://nowebsitewalters.com"
}, {
"ID": 7,
"Name": "StereoShack",
"Address": "400 Commerce S",
"City": "Fort Worth",
"State": "Texas",
"Zipcode": 76102,
"Phone": "8178200741",
"Fax": "(817) 820-0742",
"Website": "http://nowebsiteshack.com"
}, {
"ID": 8,
"Name": "Circuit Town",
"Address": "2200 Kensington Court",
"City": "Oak Brook",
"State": "Illinois",
"Zipcode": 60523,
"Phone": "8009552929",
"Fax": "(800) 955-9392",
"Website": "http://nowebsitecircuittown.com"
}, {
"ID": 9,
"Name": "Premier Buy",
"Address": "7601 Penn Avenue South",
"City": "Richfield",
"State": "Minnesota",
"Zipcode": 55423,
"Phone": "6122911000",
"Fax": "(612) 291-2001",
"Website": "http://nowebsitepremierbuy.com"
}, {
"ID": 10,
"Name": "ElectrixMax",
"Address": "263 Shuman Blvd",
"City": "Naperville",
"State": "Illinois",
"Zipcode": 60563,
"Phone": "6304387800",
"Fax": "(630) 438-7801",
"Website": "http://nowebsiteelectrixmax.com"
}, {
"ID": 11,
"Name": "Video Emporium",
"Address": "1201 Elm Street",
"City": "Dallas",
"State": "Texas",
"Zipcode": 75270,
"Phone": "2148543000",
"Fax": "(214) 854-3001",
"Website": "http://nowebsitevideoemporium.com"
}, {
"ID": 12,
"Name": "Screen Shop",
"Address": "1000 Lowes Blvd",
"City": "Mooresville",
"State": "North Carolina",
"Zipcode": 28117,
"Phone": "8004456937",
"Fax": "(800) 445-6938",
"Website": "http://nowebsitescreenshop.com"
}];