DevExtreme ExcelJS library allows you to customize worksheets outside the exported cell area. This demo uses this functionality to add a header (a title before exported data) and a footer (a note after exported data).
Review the onExporting handler to see the data export code. The functions that create header and footer sections utilize the following customization features:
@using DevExtreme.MVC.Demos.Models
@model IEnumerable<CountryPopulationAndGDP>
@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>
}
<div class="long-title"><h3>Country Area, Population, and GDP Structure</h3></div>
@(Html.DevExtreme().DataGrid<CountryPopulationAndGDP>()
.ID("gridContainer")
.DataSource(Model, "ID")
.Export(e => e.Enabled(true))
.ShowBorders(true)
.OnExporting("exporting")
.Columns(c =>
{
c.AddFor(m => m.Country);
c.AddFor(m => m.Area);
c.Add()
.Caption("Population")
.Columns(a =>
{
a.AddFor(m => m.Population_Total)
.Format(Format.FixedPoint);
a.AddFor(m => m.Population_Urban)
.Format(Format.Percent);
});
c.Add()
.Caption("Nominal GDP")
.Columns(a =>
{
a.AddFor(m => m.GDP_Total)
.Format(Format.FixedPoint)
.SortOrder(SortOrder.Desc);
a.Add()
.Caption("By Sector")
.Columns(b =>
{
b.AddFor(m => m.GDP_Agriculture)
.Width(95)
.Format(f => f.Type(Format.Percent).Precision(1));
b.AddFor(m => m.GDP_Industry)
.Width(80)
.Format(f => f.Type(Format.Percent).Precision(1));
b.AddFor(m => m.GDP_Services)
.Width(85)
.Format(f => f.Type(Format.Percent).Precision(1));
});
});
})
)
<script>
function exporting(e) {
var workbook = new ExcelJS.Workbook();
var worksheet = workbook.addWorksheet('CountriesPopulation');
DevExpress.excelExporter.exportDataGrid({
component: e.component,
worksheet: worksheet,
topLeftCell: { row: 4, column: 1 }
}).then(function (dataGridRange) {
// header
var headerRow = worksheet.getRow(2);
headerRow.height = 30;
worksheet.mergeCells(2, 1, 2, 8);
headerRow.getCell(1).value = 'Country Area, Population, and GDP Structure';
headerRow.getCell(1).font = { name: 'Segoe UI Light', size: 22 };
headerRow.getCell(1).alignment = { horizontal: 'center' };
// footer
var footerRowIndex = dataGridRange.to.row + 2;
var footerRow = worksheet.getRow(footerRowIndex);
worksheet.mergeCells(footerRowIndex, 1, footerRowIndex, 8);
footerRow.getCell(1).value = 'www.wikipedia.org';
footerRow.getCell(1).font = { color: { argb: 'BFBFBF' }, italic: true };
footerRow.getCell(1).alignment = { horizontal: 'right' };
}).then(function () {
workbook.xlsx.writeBuffer().then(function (buffer) {
saveAs(new Blob([buffer], { type: 'application/octet-stream' }), 'CountriesPopulation.xlsx');
});
});
}
</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 ExcelJSHeaderAndFooter() {
return View(SampleData.CountriesPopulationAndGDP);
}
}
}using System.Collections.Generic;
namespace DevExtreme.MVC.Demos.Models.SampleData {
public partial class SampleData {
public static readonly IEnumerable<CountryPopulationAndGDP> CountriesPopulationAndGDP = new[] {
new CountryPopulationAndGDP {
ID = 1,
Country = "Brazil",
Area = 8515767,
Population_Urban = 0.85,
Population_Rural = 0.15,
Population_Total = 205809000,
GDP_Agriculture = 0.054,
GDP_Industry = 0.274,
GDP_Services = 0.672,
GDP_Total = 2353025 },
new CountryPopulationAndGDP {
ID = 2,
Country = "China",
Area = 9388211,
Population_Urban = 0.54,
Population_Rural = 0.46,
Population_Total = 1375530000,
GDP_Agriculture = 0.091,
GDP_Industry = 0.426,
GDP_Services = 0.483,
GDP_Total = 10380380 },
new CountryPopulationAndGDP {
ID = 3,
Country = "France",
Area = 675417,
Population_Urban = 0.79,
Population_Rural = 0.21,
Population_Total = 64529000,
GDP_Agriculture = 0.019,
GDP_Industry = 0.183,
GDP_Services = 0.798,
GDP_Total = 2846889 },
new CountryPopulationAndGDP {
ID = 4,
Country = "Germany",
Area = 357021,
Population_Urban = 0.75,
Population_Rural = 0.25,
Population_Total = 81459000,
GDP_Agriculture = 0.008,
GDP_Industry = 0.281,
GDP_Services = 0.711,
GDP_Total = 3859547 },
new CountryPopulationAndGDP {
ID = 5,
Country = "India",
Area = 3287590,
Population_Urban = 0.32,
Population_Rural = 0.68,
Population_Total = 1286260000,
GDP_Agriculture = 0.174,
GDP_Industry = 0.258,
GDP_Services = 0.569,
GDP_Total = 2047811 },
new CountryPopulationAndGDP {
ID = 6,
Country = "Italy",
Area = 301230,
Population_Urban = 0.69,
Population_Rural = 0.31,
Population_Total = 60676361,
GDP_Agriculture = 0.02,
GDP_Industry = 0.242,
GDP_Services = 0.738,
GDP_Total = 2147952 },
new CountryPopulationAndGDP {
ID = 7,
Country = "Japan",
Area = 377835,
Population_Urban = 0.93,
Population_Rural = 0.07,
Population_Total = 126920000,
GDP_Agriculture = 0.012,
GDP_Industry = 0.275,
GDP_Services = 0.714,
GDP_Total = 4616335 },
new CountryPopulationAndGDP {
ID = 8,
Country = "Russia",
Area = 17098242,
Population_Urban = 0.74,
Population_Rural = 0.26,
Population_Total = 146544710,
GDP_Agriculture = 0.039,
GDP_Industry = 0.36,
GDP_Services = 0.601,
GDP_Total = 1857461 },
new CountryPopulationAndGDP {
ID = 9,
Country = "United States",
Area = 9147420,
Population_Urban = 0.81,
Population_Rural = 0.19,
Population_Total = 323097000,
GDP_Agriculture = 0.0112,
GDP_Industry = 0.191,
GDP_Services = 0.797,
GDP_Total = 17418925 },
new CountryPopulationAndGDP {
ID = 10,
Country = "United Kingdom",
Area = 244820,
Population_Urban = 0.82,
Population_Rural = 0.18,
Population_Total = 65097000,
GDP_Agriculture = 0.007,
GDP_Industry = 0.21,
GDP_Services = 0.783,
GDP_Total = 2945146
}
};
}
}
#gridContainer sup {
font-size: 0.8em;
vertical-align: super;
line-height: 0;
}
.long-title h3 {
font-family: 'Segoe UI Light', 'Helvetica Neue Light', 'Segoe UI', 'Helvetica Neue', 'Trebuchet MS', Verdana;
font-weight: 200;
font-size: 28px;
text-align: center;
margin-bottom: 20px;
}