Search results

Excel Export

The excel export allows exporting Grid data to Excel document. You need to use the ExcelExport method for exporting. To enable Excel export in the grid, set the AllowExcelExport as true.

To use excel export, You need to define the ExcelExport in inbuild toolbar and define the toolbarClick event for exporting the Grid.

razor
excel-export.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

    <script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            gridObj.excelExport();
        }
    }
    </script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Multiple Grid exporting

The excel export provides an option to export multiple grid data in the same excel file.

Same sheet

The excel export provides support to export multiple grids in same sheet. To export in same sheet, define multipleExport.type as AppendToSheet in exportProperties. It have an option to provide blank rows between grids. These blank rows count can be defined by using themultipleExport.blankRows.

razor
same-sheet.cs
@Html.EJS().Grid("FirstGrid").DataSource((IEnumerable<object>)ViewBag.FirstGridData).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

    @Html.EJS().Grid("SecondGrid").DataSource((IEnumerable<object>)ViewBag.SecondGridData).AllowExcelExport().Columns(col =>
{
    col.Field("EmployeeID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("FirstName").Width("140").Add();
    col.Field("BirthDate").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("City").Width("120").Add();
    col.Field("Country").Width("140").Add();

}).AllowPaging().Render()

    <script>
   function toolbarClick(args) {
        if(args.item.id === 'FirstGrid_excelexport'){
            var firstGrid = document.getElementById("FirstGrid").ej2_instances[0];
            var secondGrid = document.getElementById("SecondGrid").ej2_instances[0];
            var appendExcelExportProperties = {
                multipleExport: { type: 'AppendToSheet', blankRows: 2 }
            };

            var firstGridExport = firstGrid.excelExport(appendExcelExportProperties, true);
            firstGridExport.then((fData) => {
                secondGrid.excelExport(appendExcelExportProperties, false, fData);
            });
        }
    }
    </script>
public IActionResult Index()
{
    ViewBag.FirstGridData = OrderDetails.GetAllRecords();
    ViewBag.SecondGridData = EmployeeDetails.GetAllRecords();
    return View();
}

By default, multipleExport.blankRows value is 5.

New Sheet

Excel exporting provides support to export multiple grids in new sheet. To export in new sheet, define multipleExport.type as NewSheet in exportProperties.

razor
new-sheet.cs
@Html.EJS().Grid("FirstGrid").DataSource((IEnumerable<object>)ViewBag.FirstGridData).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

        @Html.EJS().Grid("SecondGrid").DataSource((IEnumerable<object>)ViewBag.SecondGridData).AllowExcelExport().Columns(col =>
{
    col.Field("EmployeeID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("FirstName").Width("140").Add();
    col.Field("BirthDate").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("City").Width("120").Add();
    col.Field("Country").Width("140").Add();

}).AllowPaging().Render()

<script>
   function toolbarClick(args) {
        if(args.item.id === 'FirstGrid_excelexport'){
            var firstGrid = document.getElementById("FirstGrid").ej2_instances[0];
            var secondGrid = document.getElementById("SecondGrid").ej2_instances[0];
            var appendExcelExportProperties = {
                multipleExport: { type: 'NewSheet' }
            };

            var firstGridExport = firstGrid.excelExport(appendExcelExportProperties, true);
            firstGridExport.then((fData) => {
                secondGrid.excelExport(appendExcelExportProperties, false, fData);
            });
        }
    }
</script>
public IActionResult Index()
{
    ViewBag.FirstGridData = OrderDetails.GetAllRecords();
    ViewBag.SecondGridData = EmployeeDetails.GetAllRecords();
    return View();
}

To customize excel export

The excel export provides an option to customize mapping of the grid to excel document.

Export current page

The excel export provides an option to export the current page into excel. To export current page, define exportType to currentpage.

razor
export-current.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

    <script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            var excelExportProperties = {
                exportType: 'CurrentPage'
            };
            gridObj.excelExport(excelExportProperties);
        }
    }
    </script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Export hidden columns

The excel export provides an option to export hidden columns of grid by defining includeHiddenColumn as true.

razor
export-hidden.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").Visible(false).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

        <script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            var excelExportProperties = {
                includeHiddenColumn: true
            };
            gridObj.excelExport(excelExportProperties);
        }
    }
        </script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Theme

The excel export provides an option to include theme for exported excel document.

To apply theme in exported Excel, define the theme in exportProperties .

razor
theme.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

    <script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            var excelExportProperties = {
                theme:{
                    header: { fontName: 'Segoe UI', fontColor: '#666666' },
                    record: { fontName: 'Segoe UI', fontColor: '#666666' },
                    caption: { fontName: 'Segoe UI', fontColor: '#666666' }
                }
            };
            gridObj.excelExport(excelExportProperties);
        }
    }
    </script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

By default, material theme is applied to exported excel document.

The excel export provides an option to include header and footer content for exported excel document.

razor
header-footer.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

<script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            var excelExportProperties = {
                header: {
                    headerRows: 7,
                    rows: [
                        { cells: [{ colSpan: 4, value: "Northwind Traders", style: { fontColor: '#C67878', fontSize: 20, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, value: "2501 Aerial Center Parkway", style: { fontColor: '#C67878', fontSize: 15, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, value: "Suite 200 Morrisville, NC 27560 USA", style: { fontColor: '#C67878', fontSize: 15, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, value: "Tel +1 888.936.8638 Fax +1 919.573.0306", style: { fontColor: '#C67878', fontSize: 15, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, hyperlink: { target: 'https://www.northwind.com/', displayText: 'www.northwind.com' }, style: { hAlign: 'Center' } }] },
                        { cells: [{ colSpan: 4, hyperlink: { target: 'mailto:support@northwind.com' }, style: { hAlign: 'Center' } }] },
                    ]
                },
                footer: {
                    footerRows: 4,
                    rows: [
                        { cells: [{ colSpan: 4, value: "Thank you for your business!", style: { hAlign: 'Center', bold: true } }] },
                        { cells: [{ colSpan: 4, value: "!Visit Again!", style: { hAlign: 'Center', bold: true } }] }
                    ]
                },
            };
            gridObj.excelExport(excelExportProperties);
        }
    }
</script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

File Name for Exported document

You can assign the file name for the exported document by defining fileName property in ExcelExportProperties.

razor
export-filename.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

<script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            var excelExportProperties = {
                 fileName:"new.xlsx"
            };
            gridObj.excelExport(excelExportProperties);
        }
    }
</script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Custom data source

The excel export provides an option to define datasource dynamically before exporting. To export data dynamically, define the dataSource in exportProperties.

razor
custom-data.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

    <script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            var data = [
                { OrderID: "100", CustomerID: "Vinet", Freight: "2.00", OrderDate: new Date() },
                { OrderID: "101", CustomerID: "Hanar", Freight: "2.01", OrderDate: new Date() },
                { OrderID: "102", CustomerID: "Mega", Freight: "4.48", OrderDate: new Date() },
                { OrderID: "103", CustomerID: "Sam", Freight: "19.23", OrderDate: new Date() }
            ];
            var excelExportProperties = {
                dataSource: data
            };
            gridObj.excelExport(excelExportProperties);
        }
    }
    </script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Exporting grouped records

The excel export provides outline option for grouped records which hides the detailed data for better viewing. In grid, we have provided the outline option for the exported document when the data’s are grouped.

razor
excel-group.cs
@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).AllowExcelExport().ToolbarClick("toolbarClick").Columns(col =>
   {
       col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
       col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
       col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
       col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

   }).AllowGrouping().GroupSettings(group => { group.Columns(new string[] { "CustomerID" }); }).AllowPaging().Toolbar(new List<string>() { "ExcelExport" }).Render()

    <script>
   function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            gridObj.excelExport();
        }
    }
    </script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Limitations

  • Microsoft Excel permits up to seven nested levels in outlines. So that in the grid we can able to provide only up to seven nested levels and if it exceeds more than seven levels then the document will be exported without outline option. Please refer the Microsoft Limitation