Search results

Excel Export in JavaScript (ES5) Grid control

30 Sep 2022 / 5 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 as true.

To use excel export, You need to inject the ExcelExport module in grid.

Source
Preview
index.js
index.html
Copied to clipboard
ej.grids.Grid.Inject(ej.grids.Page, ej.grids.ExcelExport, ej.grids.Toolbar);
var grid = new ej.grids.Grid({
    dataSource: data,
    allowPaging: true,
    allowExcelExport: true,
    toolbar: ['ExcelExport'],
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120, type: 'number' },
        { field: 'CustomerID', width: 140, headerText: 'Customer ID', type: 'string' },
        { field: 'Freight', headerText: 'Freight', textAlign: 'Right', width: 120, format: 'C' },
        { field: 'OrderDate', headerText: 'Order Date', width: 140, format: 'yMd', textAlign: 'Right' }
    ],
    height: 260
});
grid.toolbarClick = function(args){
    if (args['item'].id === 'Grid_excelexport') {
        grid.excelExport();
    }
}
grid.appendTo('#Grid');
Copied to clipboard
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-notifications/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
    <style>
        .e-row[aria-selected="true"] .e-customizedExpandcell {
            background-color: #e0e0e0;
        }
        .e-grid.e-gridhover tr[role='row']:hover {
            background-color: #eee;
        }
        .e-expand::before {
        content: '\e5b8';
    }
    </style>
<script src="https://cdn.syncfusion.com/ej2/20.3.47/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <script id="template" type="text/x-template">
        <input id='${OrderID}' value='${Freight}' class='custemp' type='text' style='width: 100%'>
    </script>
    <div id="container"> 
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

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 event, We can call the hideSpinner method.

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

Source
Preview
index.js
index.html
Copied to clipboard
ej.grids.Grid.Inject(ej.grids.Page, ej.grids.PdfExport, ej.grids.ExcelExport, ej.grids.Toolbar);
var grid = new ej.grids.Grid({
    dataSource: data,
    allowPaging: true,
    allowPdfExport: true,
    allowExcelExport: true,
    toolbar: ['PdfExport', 'ExcelExport'],
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120, type: 'number' },
        { field: 'CustomerID', width: 140, headerText: 'Customer ID', visible: false },
        { field: 'Freight', headerText: 'Freight', textAlign: 'Right', width: 120 },
        { field: 'ShipCity', headerText: 'ShipCity', textAlign: 'Right', width: 140 }
    ],
    height: 260
});
grid.appendTo('#Grid');
grid.toolbarClick = function(args){
    if (args['item'].id === 'Grid_pdfexport') {
        grid.showSpinner();
        grid.pdfExport();
    }
    if (args['item'].id === 'Grid_excelexport') {
        grid.showSpinner();
        grid.excelExport();
    }
}
grid.pdfExportComplete = () => {
        grid.hideSpinner();
    }
grid.excelExportComplete = () => {
        grid.hideSpinner();
    }
Copied to clipboard
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-notifications/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
    <style>
        .e-row[aria-selected="true"] .e-customizedExpandcell {
            background-color: #e0e0e0;
        }
        .e-grid.e-gridhover tr[role='row']:hover {
            background-color: #eee;
        }
        .e-expand::before {
        content: '\e5b8';
    }
    </style>
<script src="https://cdn.syncfusion.com/ej2/20.3.47/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <script id="template" type="text/x-template">
        <input id='${OrderID}' value='${Freight}' class='custemp' type='text' style='width: 100%'>
    </script>
    <div id="container"> 
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Custom data source

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

Source
Preview
index.js
index.html
Copied to clipboard
ej.grids.Grid.Inject(ej.grids.Page, ej.grids.ExcelExport, ej.grids.Toolbar);
var grid = new ej.grids.Grid({
    dataSource: data,
    allowPaging: true,
    allowExcelExport: true,
    toolbar: ['ExcelExport'],
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120, type: 'number' },
        { field: 'CustomerID', width: 140, headerText: 'Customer ID', type: 'string' },
        { field: 'Freight', headerText: 'Freight', textAlign: 'Right', width: 120, format: 'C' },
        { field: 'OrderDate', headerText: 'Order Date', width: 140, format: 'yMd', textAlign: 'Right' }
    ],
    height: 315
});
grid.toolbarClick = function(args) {
    if (args['item'].id === 'Grid_excelexport') {
        var excelExportProperties = {
            dataSource: data
        };
        grid.excelExport(excelExportProperties);
    }
}
grid.appendTo('#Grid');
Copied to clipboard
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-notifications/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
    <style>
        .e-row[aria-selected="true"] .e-customizedExpandcell {
            background-color: #e0e0e0;
        }
        .e-grid.e-gridhover tr[role='row']:hover {
            background-color: #eee;
        }
        .e-expand::before {
        content: '\e5b8';
    }
    </style>
<script src="https://cdn.syncfusion.com/ej2/20.3.47/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <script id="template" type="text/x-template">
        <input id='${OrderID}' value='${Freight}' class='custemp' type='text' style='width: 100%'>
    </script>
    <div id="container"> 
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

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.

Source
Preview
index.js
index.html
Copied to clipboard
ej.grids.Grid.Inject(ej.grids.Page, ej.grids.PdfExport, ej.grids.ExcelExport, ej.grids.Toolbar);
var queryClone;
var grid = new ej.grids.Grid({
    dataSource: data,
    allowPaging: true,
    allowPdfExport: true,
    allowExcelExport: true,
    toolbar: ['PdfExport', 'ExcelExport'],
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120, type: 'number' },
        { field: 'CustomerID', width: 140, headerText: 'Customer ID', visible: false },
        { field: 'Freight', headerText: 'Freight', textAlign: 'Right', width: 120 },
        { field: 'ShipCity', headerText: 'ShipCity', textAlign: 'Right', width: 140 }
    ],
    height: 260
});
grid.appendTo('#Grid');
grid.toolbarClick = function(args){
    if (args['item'].id === 'Grid_pdfexport') {
        queryClone = grid.query;
        grid.query = new ej.data.Query().addParams("recordcount", "12");
        grid.pdfExport();
    }
    if (args['item'].id === 'Grid_excelexport') {
        queryClone = grid.query;
        grid.query = new ej.data.Query().addParams("recordcount", "12");
        grid.excelExport();
    }
}
grid.pdfExportComplete = () => {
        grid.query = queryClone;
    }
grid.excelExportComplete = () => {
        grid.query = queryClone;
    }
Copied to clipboard
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-notifications/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/20.3.47/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
    <style>
        .e-row[aria-selected="true"] .e-customizedExpandcell {
            background-color: #e0e0e0;
        }
        .e-grid.e-gridhover tr[role='row']:hover {
            background-color: #eee;
        }
        .e-expand::before {
        content: '\e5b8';
    }
    </style>
<script src="https://cdn.syncfusion.com/ej2/20.3.47/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <script id="template" type="text/x-template">
        <input id='${OrderID}' value='${Freight}' class='custemp' type='text' style='width: 100%'>
    </script>
    <div id="container"> 
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Passing the selected records to the server using ajax request via custom toolbar button click

You can pass the selected records to the server with the help of an ajax request. In the toolbarClick event, you can get the selected records using the getSelectedRecords method and pass the selected records to the server using the data property of the ajax.

Copied to clipboard
import { Grid, Page,Toolbar } from '@syncfusion/ej2-grids';
import { Ajax } from '@syncfusion/ej2-base';

Grid.Inject(Page,Toolbar);
let grid: Grid = new Grid({
    dataSource: data,
    allowSelection: true,
    toolbar: ['Selected'],
    columns: [
        { type: 'checkbox', width: 50, },
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120},
        { field: 'CustomerID', width: 140, headerText: 'CustomerID'},
        { field: 'ShipCity', headerText: 'Ship Country', width: 140 }
    ],
    toolbarClick: function (args) {
        if (args.item.text === 'Selected') {
            var selectedRecord = this.getSelectedRecords();
            var ajax = new Ajax({
                url: "/Home/SelectRecord",
                type: "POST",
                contentType: "application/json",
                data: JSON.stringify(selectedRecord),
                successHandler: function (response) {
                    console.log(JSON.parse(response));
                },
                failure: function (response) {
                    alert(response);
                }
            });
            ajax.send();
        }
    },
});
grid.appendTo('#Grid');

The selected record details are bound to the row parameter. Please refer to the following screenshot.

Selected record

See Also