The customizeCell function allows you to modify cell data and value formatting in exported worksheets.
You can access and change the following attributes:
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.
@using DevExtreme.MVC.Demos.Models
@section ExternalDependencies {
<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/7.10.1/polyfill.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/devextreme-exceljs-fork@4.4.4/dist/dx-exceljs-fork.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>
}
@(Html.DevExtreme().DataGrid<Company>()
.ID("gridContainer")
.DataSource(new JS("companies"))
.KeyExpr("ID")
.Width("100%")
.ShowBorders(true)
.GroupPanel(groupPanel => groupPanel.Visible(true))
.Grouping(grouping => grouping.AutoExpandAll(true))
.SortByGroupSummaryInfo(i => i.Add().SummaryItem("count"))
.Export(e => e.Enabled(true))
.OnExporting("exporting")
.Columns(columns =>
{
columns.Add().DataField("Name").Width(190);
columns.Add().DataField("Address").Width(200);
columns.Add().DataField("City");
columns.Add().DataField("State")
.GroupIndex(0);
columns.Add().DataField("Phone")
.Format(new JS("phoneNumberFormat"));
columns.Add().DataField("Website")
.Alignment(HorizontalAlignment.Center)
.Width(100)
.CellTemplate(@<text>
<a href="<%- value %>" target="_blank">
Website
</a>
</text>);
})
.Summary(s => s
.TotalItems(totalItems =>
{
totalItems.AddFor(m => m.Name)
.SummaryType(SummaryType.Count)
.DisplayFormat("Total count: {0} companies");
})
)
)
<script src="~/Scripts/data/companies.js"></script>
<script>
function exporting(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");
});
});
}
function phoneNumberFormat(value) {
var USNumber = value.match(/(\d{3})(\d{3})(\d{4})/);
return "(" + USNumber[1] + ") " + USNumber[2] + "-" + USNumber[3];
}
</script>
using DevExtreme.MVC.Demos.Models;
using DevExtreme.MVC.Demos.Models.DataGrid;
using DevExtreme.MVC.Demos.Models.SampleData;
using System;
using System.Linq;
using System.Web.Mvc;
namespace DevExtreme.MVC.Demos.Controllers {
public class DataGridController : Controller {
public ActionResult ExcelJSCellCustomization() {
return View();
}
}
}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.dx"
}, {
"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.dx"
}, {
"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.dx"
}, {
"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.dx"
}, {
"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.dx"
}, {
"ID": 6,
"Name": "Walters",
"Address": "200 Wilmot Rd",
"City": "Deerfield",
"State": "Illinois",
"Zipcode": 60015,
"Phone": "8479402500",
"Fax": "(847) 940-2501",
"Website": "http://nowebsitewalters.dx"
}, {
"ID": 7,
"Name": "StereoShack",
"Address": "400 Commerce S",
"City": "Fort Worth",
"State": "Texas",
"Zipcode": 76102,
"Phone": "8178200741",
"Fax": "(817) 820-0742",
"Website": "http://nowebsiteshack.dx"
}, {
"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.dx"
}, {
"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.dx"
}, {
"ID": 10,
"Name": "ElectrixMax",
"Address": "263 Shuman Blvd",
"City": "Naperville",
"State": "Illinois",
"Zipcode": 60563,
"Phone": "6304387800",
"Fax": "(630) 438-7801",
"Website": "http://nowebsiteelectrixmax.dx"
}, {
"ID": 11,
"Name": "Video Emporium",
"Address": "1201 Elm Street",
"City": "Dallas",
"State": "Texas",
"Zipcode": 75270,
"Phone": "2148543000",
"Fax": "(214) 854-3001",
"Website": "http://nowebsitevideoemporium.dx"
}, {
"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.dx"
}];
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace DevExtreme.MVC.Demos.Models {
public class Company {
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public int ZipCode { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
public string Website { get; set; }
}
}
#gridContainer {
height: 436px;
}