Your search did not match any results.
DataGrid

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.

@using DevExtreme.MVC.Demos.Models @model IEnumerable<Order> @(Html.DevExtreme().DataGrid<Order>() .ID("gridContainer") .DataSource(Model) .Export(e => e.Enabled(true).CustomizeExcelCell("customizeExcelCell")) .ShowBorders(true) .OnCellPrepared("cellPrepared") .Columns(columns => { columns.AddFor(m => m.OrderNumber) .Width(130); columns.AddFor(m => m.OrderDate) .Width(160); columns.AddFor(m => m.Employee); columns.AddFor(m => m.CustomerStoreCity); columns.AddFor(m => m.CustomerStoreState); columns.AddFor(m => m.SaleAmount) .Alignment(HorizontalAlignment.Right) .Format(Format.Currency); }) ) <script> function cellPrepared(e) { if(e.rowType === 'data') { if(e.data.OrderDate < new Date(2014, 2, 3)) { e.cellElement.addClass('oldOrder'); } if(e.data.SaleAmount > 15000) { if(e.column.dataField === 'Employee') { e.cellElement.addClass('highAmountOrder_employee'); } if(e.column.dataField === 'SaleAmount') { e.cellElement.addClass('highAmountOrder_saleAmount'); } } } } function customizeExcelCell(options) { if(options.gridCell.rowType === 'data') { if(options.gridCell.data.OrderDate < new Date(2014, 2, 3)) { options.font.color = '#AAAAAA'; } if(options.gridCell.data.SaleAmount > 15000) { if(options.gridCell.column.dataField === 'Employee') { options.font.bold = true; } if(options.gridCell.column.dataField === 'SaleAmount') { options.backgroundColor = '#FFBB00'; options.font.color = '#000000'; } } } } </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 ExcelCellCustomization() { return View(SampleData.Orders); } } }
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Linq; using System.Web; namespace DevExtreme.MVC.Demos.Models { public class Order { public int ID { get; set; } [Display(Name = "Invoice Number")] public int OrderNumber { get; set; } public DateTime OrderDate { get; set; } public int SaleAmount { get; set; } public string Terms { get; set; } public int TotalAmount { get; set; } [Display(Name = "State")] public string CustomerStoreState { get; set; } [Display(Name = "City")] public string CustomerStoreCity { get; set; } public string Employee { get; set; } } }
using System; using System.Collections.Generic; namespace DevExtreme.MVC.Demos.Models.SampleData { public partial class SampleData { public static List<Order> Orders { get { return new List<Order> { new Order { ID = 1, OrderNumber = 35703, OrderDate = DateTime.Parse("2014/04/10"), SaleAmount = 11800, Terms = "15 Days", TotalAmount = 12175, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 4, OrderNumber = 35711, OrderDate = DateTime.Parse("2014/01/12"), SaleAmount = 16050, Terms = "15 Days", TotalAmount = 16550, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 5, OrderNumber = 35714, OrderDate = DateTime.Parse("2014/01/22"), SaleAmount = 14750, Terms = "15 Days", TotalAmount = 15250, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 7, OrderNumber = 35983, OrderDate = DateTime.Parse("2014/02/07"), SaleAmount = 3725, Terms = "15 Days", TotalAmount = 3850, CustomerStoreState = "Colorado", CustomerStoreCity = "Denver", Employee = "Todd Hoffman" }, new Order { ID = 9, OrderNumber = 36987, OrderDate = DateTime.Parse("2014/03/11"), SaleAmount = 14200, Terms = "15 Days", TotalAmount = 14800, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 11, OrderNumber = 38466, OrderDate = DateTime.Parse("2014/03/01"), SaleAmount = 7800, Terms = "15 Days", TotalAmount = 8200, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 14, OrderNumber = 39420, OrderDate = DateTime.Parse("2014/02/15"), SaleAmount = 20500, Terms = "15 Days", TotalAmount = 9100, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 15, OrderNumber = 39874, OrderDate = DateTime.Parse("2014/02/04"), SaleAmount = 9050, Terms = "30 Days", TotalAmount = 19100, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 18, OrderNumber = 42847, OrderDate = DateTime.Parse("2014/02/15"), SaleAmount = 20400, Terms = "30 Days", TotalAmount = 20800, CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 19, OrderNumber = 43982, OrderDate = DateTime.Parse("2014/05/29"), SaleAmount = 6050, Terms = "30 Days", TotalAmount = 6250, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 29, OrderNumber = 56272, OrderDate = DateTime.Parse("2014/02/06"), SaleAmount = 15850, Terms = "30 Days", TotalAmount = 16350, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 30, OrderNumber = 57429, OrderDate = DateTime.Parse("2013/12/31"), SaleAmount = 11050, Terms = "30 Days", TotalAmount = 11400, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" }, new Order { ID = 32, OrderNumber = 58292, OrderDate = DateTime.Parse("2014/05/13"), SaleAmount = 13500, Terms = "15 Days", TotalAmount = 13800, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 36, OrderNumber = 62427, OrderDate = DateTime.Parse("2014/01/27"), SaleAmount = 23500, Terms = "15 Days", TotalAmount = 24000, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 39, OrderNumber = 65977, OrderDate = DateTime.Parse("2014/02/05"), SaleAmount = 2550, Terms = "15 Days", TotalAmount = 2625, CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 40, OrderNumber = 66947, OrderDate = DateTime.Parse("2014/03/23"), SaleAmount = 3500, Terms = "15 Days", TotalAmount = 3600, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 42, OrderNumber = 68428, OrderDate = DateTime.Parse("2014/04/10"), SaleAmount = 10500, Terms = "15 Days", TotalAmount = 10900, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 43, OrderNumber = 69477, OrderDate = DateTime.Parse("2014/03/09"), SaleAmount = 14200, Terms = "15 Days", TotalAmount = 14500, CustomerStoreState = "California", CustomerStoreCity = "Anaheim", Employee = "Harv Mudd" }, new Order { ID = 46, OrderNumber = 72947, OrderDate = DateTime.Parse("2014/01/14"), SaleAmount = 13350, Terms = "30 Days", TotalAmount = 13650, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 47, OrderNumber = 73088, OrderDate = DateTime.Parse("2014/03/25"), SaleAmount = 8600, Terms = "30 Days", TotalAmount = 8850, CustomerStoreState = "Nevada", CustomerStoreCity = "Reno", Employee = "Clark Morgan" }, new Order { ID = 50, OrderNumber = 76927, OrderDate = DateTime.Parse("2014/04/27"), SaleAmount = 9800, Terms = "30 Days", TotalAmount = 10050, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 51, OrderNumber = 77297, OrderDate = DateTime.Parse("2014/04/30"), SaleAmount = 10850, Terms = "30 Days", TotalAmount = 11100, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" }, new Order { ID = 56, OrderNumber = 84744, OrderDate = DateTime.Parse("2014/02/10"), SaleAmount = 4650, Terms = "30 Days", TotalAmount = 4750, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 57, OrderNumber = 85028, OrderDate = DateTime.Parse("2014/05/17"), SaleAmount = 2575, Terms = "30 Days", TotalAmount = 2625, CustomerStoreState = "Nevada", CustomerStoreCity = "Reno", Employee = "Clark Morgan" }, new Order { ID = 59, OrderNumber = 87297, OrderDate = DateTime.Parse("2014/04/21"), SaleAmount = 14200, Terms = "30 Days", CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 60, OrderNumber = 88027, OrderDate = DateTime.Parse("2014/02/14"), SaleAmount = 13650, Terms = "30 Days", TotalAmount = 14050, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 65, OrderNumber = 94726, OrderDate = DateTime.Parse("2014/05/22"), SaleAmount = 20500, Terms = "15 Days", TotalAmount = 20800, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 66, OrderNumber = 95266, OrderDate = DateTime.Parse("2014/03/10"), SaleAmount = 9050, Terms = "15 Days", TotalAmount = 9250, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 69, OrderNumber = 98477, OrderDate = DateTime.Parse("2014/01/01"), SaleAmount = 23500, Terms = "15 Days", TotalAmount = 23800, CustomerStoreState = "Wyoming", CustomerStoreCity = "Casper", Employee = "Todd Hoffman" }, new Order { ID = 70, OrderNumber = 99247, OrderDate = DateTime.Parse("2014/02/08"), SaleAmount = 2100, Terms = "15 Days", TotalAmount = 2150, CustomerStoreState = "Utah", CustomerStoreCity = "Salt Lake City", Employee = "Clark Morgan" }, new Order { ID = 78, OrderNumber = 174884, OrderDate = DateTime.Parse("2014/04/10"), SaleAmount = 7200, Terms = "30 Days", TotalAmount = 7350, CustomerStoreState = "Colorado", CustomerStoreCity = "Denver", Employee = "Todd Hoffman" }, new Order { ID = 81, OrderNumber = 188877, OrderDate = DateTime.Parse("2014/02/11"), SaleAmount = 8750, Terms = "30 Days", TotalAmount = 8900, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" }, new Order { ID = 82, OrderNumber = 191883, OrderDate = DateTime.Parse("2014/02/05"), SaleAmount = 9900, Terms = "30 Days", TotalAmount = 10150, CustomerStoreState = "California", CustomerStoreCity = "Los Angeles", Employee = "Harv Mudd" }, new Order { ID = 83, OrderNumber = 192474, OrderDate = DateTime.Parse("2014/01/21"), SaleAmount = 12800, Terms = "30 Days", TotalAmount = 13100, CustomerStoreState = "California", CustomerStoreCity = "Anaheim", Employee = "Harv Mudd" }, new Order { ID = 84, OrderNumber = 193847, OrderDate = DateTime.Parse("2014/03/21"), SaleAmount = 14100, Terms = "30 Days", TotalAmount = 14350, CustomerStoreState = "California", CustomerStoreCity = "San Diego", Employee = "Harv Mudd" }, new Order { ID = 85, OrderNumber = 194877, OrderDate = DateTime.Parse("2014/03/06"), SaleAmount = 4750, Terms = "30 Days", TotalAmount = 4950, CustomerStoreState = "California", CustomerStoreCity = "San Jose", Employee = "Jim Packard" }, new Order { ID = 86, OrderNumber = 195746, OrderDate = DateTime.Parse("2014/05/26"), SaleAmount = 9050, Terms = "30 Days", TotalAmount = 9250, CustomerStoreState = "Nevada", CustomerStoreCity = "Las Vegas", Employee = "Harv Mudd" }, new Order { ID = 87, OrderNumber = 197474, OrderDate = DateTime.Parse("2014/03/02"), SaleAmount = 6400, Terms = "30 Days", TotalAmount = 6600, CustomerStoreState = "Nevada", CustomerStoreCity = "Reno", Employee = "Clark Morgan" }, new Order { ID = 88, OrderNumber = 198746, OrderDate = DateTime.Parse("2014/05/09"), SaleAmount = 15700, Terms = "30 Days", TotalAmount = 16050, CustomerStoreState = "Colorado", CustomerStoreCity = "Denver", Employee = "Todd Hoffman" }, new Order { ID = 91, OrderNumber = 214222, OrderDate = DateTime.Parse("2014/02/08"), SaleAmount = 11050, Terms = "30 Days", TotalAmount = 11250, CustomerStoreState = "Arizona", CustomerStoreCity = "Phoenix", Employee = "Clark Morgan" } }; } } } }
#gridContainer { height: 440px; } .oldOrder { color: #AAAAAA; } .highAmountOrder_saleAmount { background-color: #FFBB00; color: #000000; } .highAmountOrder_employee { font-weight: bold; }