Excel Export

2 Mar 202211 minutes to read

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 property as true.

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

CSHTML
Excel-export.cs
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowExcelExport="true" toolbarClick="toolbarClick" toolbar="@(new List<string>() { "ExcelExport" })" allowPaging="true">
    <e-grid-pagesettings pageCount="5"></e-grid-pagesettings>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" width="150"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" textAlign="Right" width="130"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="120"></e-grid-column>
        <e-grid-column field="ShipCountry" visible="false" headerText="Ship Country" width="150"></e-grid-column>
        <e-grid-column field="ShipCity" visible="false" headerText="Ship City" width="150"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<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();
}

Show spinner while exporting

You can show/ hide spinner component while exporting the grid using showSpinner/ hideSpinner methods. You can use toolbarClick event to show spinner before exporting and hide a spinner in the pdfExportComplete or excelExportComplete event after the exporting.

In the toolbarClick event, based on the parameter args.item.id as Grid_pdfexport or Grid_excelexport we can call the showSpinner method from grid instance.

In the pdfExportComplete or excelExportComplete events, We can call the hideSpinner method.

In the below demo, we have rendered the default spinner component when exporting the grid.

CSHTML
Show-spinner.cs
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPdfExport="true" allowExcelExport="true" toolbarClick="toolbarClick" pdfExportComplete="pdfExportComplete" excelExportComplete="excelExportComplete" toolbar="@(new List<string>() {"PdfExport", "ExcelExport"})" allowPaging="true">
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" visible="false" width="140"></e-grid-column>               
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="120"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" width="140" format="yMd"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<script>
    function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_pdfexport') {
            gridObj.showSpinner();
            gridObj.pdfExport();
        }
        else if (args.item.id === 'Grid_excelexport') {
            gridObj.showSpinner();
            gridObj.excelExport();
        }
    }

    function pdfExportComplete(args) {
        this.hideSpinner();
    }
    
    function excelExportComplete(args) {
        this.hideSpinner();
    }
</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.

CSHTML
Custom-data.cs
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowExcelExport="true" toolbarClick="toolbarClick" toolbar="@(new List<string>() {"ExcelExport"})" allowPaging="true">
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" width="140"></e-grid-column>               
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="120"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="140" ></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<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();
}

Passing additional parameters to the server when exporting

You can pass the additional parameter in the query property by invoking addParams method. In the toolbarClick event, you can define params as key and value pair so it will receive at the server side when exporting.

In the below example, we have passed recordcount as 12 using addParams method.

CSHTML
Addtional-parameter.cs
Addtional-parameter.cs
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPdfExport="true" allowExcelExport="true" toolbarClick="toolbarClick" pdfExportComplete="pdfExportComplete" excelExportComplete="excelExportComplete" toolbar="@(new List<string>() {"PdfExport", "ExcelExport"})" allowPaging="true">
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" visible="false" width="140"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="120"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" width="140" format="yMd"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<script>
    var queryClone;
    function toolbarClick(args) {
        var gridObj = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_pdfexport') {
            queryClone = gridObj.query;
            gridObj.query = new ej.data.Query().addParams("recordcount", "12");
            gridObj.pdfExport();
        }
        else if (args.item.id === 'Grid_excelexport') {
            queryClone = gridObj.query;
            gridObj.query = new ej.data.Query().addParams("recordcount", "12");
            gridObj.excelExport();
        }
    }
    function pdfExportComplete(args) {
        this.query = queryClone;
    }
    function excelExportComplete(args) {
        this.query = queryClone;
    }
</script>
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}
public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}