HelpBot Assistant

How can I help you?

Excel Export in Angular Grid Component

19 Mar 202624 minutes to read

The Excel export feature in the Angular Grid component enables exporting Grid data to Excel or CSV documents. This capability is helpful when sharing or analyzing data in spreadsheet format is required.

Enable Excel export

Excel export configuration in the Grid component requires the following setup:

Module injection: Inject the ExcelExportService module into the providers array. This module provides the necessary services for exporting data to Excel or CSV formats.

Enable export: Set the allowExcelExport property to true. This property activates the Excel and CSV export options in the Grid.

Trigger export: Use the excelExport method to export Grid data to an Excel document. For CSV export, use the csvExport method instead.

The following example demonstrates performing the Excel export action in the grid by adding the ExcelExport item to the grid toolbar and invoking the excelExport method inside the toolbarClick event.

import { data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportService, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [GridModule ],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<ejs-grid #grid id='Grid' [dataSource]='data' [toolbar]='toolbarOptions' 
               height='272px' [allowExcelExport]='true' (toolbarClick)='toolbarClick($event)'>
                <e-columns>
                    <e-column field='OrderID' headerText='Order ID' textAlign='Right' 
                    width=120></e-column>
                    <e-column field='CustomerID' headerText='Customer ID' width=150>
                    </e-column>
                    <e-column field='ShipCity' headerText='Ship City' width=150></e-column>
                    <e-column field='ShipName' headerText='Ship Name' width=150></e-column>
                </e-columns>
                </ejs-grid>`
})
export class AppComponent implements OnInit {

    public data?: object[]; 
    public toolbarOptions?: ToolbarItems[];
    @ViewChild('grid') public grid?: GridComponent;

    ngOnInit(): void {
        this.data = data;
        this.toolbarOptions = ['ExcelExport', 'CsvExport'];
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') { 
            // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
            (this.grid as GridComponent).excelExport();
        }
        else if (args.item.id === 'Grid_csvexport') { 
            // 'Grid_csvexport' -> Grid component id + _ + toolbar item name
            (this.grid as GridComponent).csvExport();
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Show spinner while exporting

Showing a spinner during the export operation enhances usability by giving a clear visual indicator of progress, helping to understand that the export is actively processing.

To show or hide a spinner while exporting the grid, utilize the showSpinner and hideSpinner methods within the toolbarClick event.

The toolbarClick event is triggered when a toolbar item in the Grid is clicked. Within the event handler, the code checks whether the clicked item corresponds to Excel export by examining its ID, which typically combines the Grid’s ID with either “_excelexport”. If a match is found, the showSpinner method is invoked to display the spinner before invoking the excelExport method.

To hide the spinner after the exporting is completed, bind the excelExportComplete event and use the hideSpinner method on the grid instance to hide the spinner.

The following example demonstrates displaying the spinner during Excel export in the grid:

import { data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [ GridModule],
    providers: [ExcelExportService, ToolbarService],
    standalone: true,
    selector: 'app-root',
    template: `<ejs-grid #grid id='Grid' [dataSource]='data'
               [toolbar]='toolbarOptions' height='272px'  [allowExcelExport]='true'
                (excelExportComplete)='excelExportComplete()' 
                (toolbarClick)='toolbarClick($event)'>
                    <e-columns>
                        <e-column field='OrderID' headerText='Order ID' textAlign='Right' 
                        width='90'></e-column>
                        <e-column field='ProductName' headerText='Product Name' width='100'>
                        </e-column>
                        <e-column field='ProductID' headerText='Product ID' textAlign='Right' 
                        width='80'></e-column>
                        <e-column field='CustomerName' headerText='Customer Name' width='120'>
                        </e-column>
                    </e-columns>
                </ejs-grid>`
})
export class AppComponent implements OnInit {

    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    @ViewChild('grid')  public grid?: GridComponent;

    ngOnInit(): void {
        this.data = data;
        this.toolbarOptions = ['ExcelExport'];
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') {
            (this.grid as GridComponent).showSpinner();
            (this.grid as GridComponent).excelExport();
        }
    }
    excelExportComplete(): void {
        (this.grid as GridComponent).hideSpinner();
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Bind custom data source while exporting

The Grid component provides a convenient way to export data to Excel or CSV format. With the Excel or CSV export feature, a custom data source can be defined while exporting. This allows exporting data that is not necessarily bound to the grid, which can be generated or retrieved based on application logic.

Export custom data to Excel or CSV even if not currently used in the Grid by specifying the dataSource property in the excelExportProperties object.

import { data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportProperties, ExcelExportService, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [ GridModule ],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<ejs-grid #grid id='Grid' [dataSource]='data' [toolbar]='toolbarOptions' 
               height='272px' [allowExcelExport]='true' 
               (toolbarClick)='toolbarClick($event)'>
                <e-columns>
                    <e-column field='OrderID' headerText='Order ID' textAlign='Right' 
                    width=120></e-column>
                    <e-column field='CustomerID' headerText='Customer ID' width=150>
                    </e-column>
                    <e-column field='ShipCity' headerText='Ship City' width=150></e-column>
                    <e-column field='ShipName' headerText='Ship Name' width=150></e-column>
                </e-columns>
                </ejs-grid>`
})

export class AppComponent implements OnInit {

    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    @ViewChild('grid') public grid?: GridComponent;

    ngOnInit(): void {
        this.data = data;
        this.toolbarOptions = ['ExcelExport'];
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') { 
            // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
            const excelExportProperties: ExcelExportProperties = {
                dataSource: data
            };
            (this.grid as GridComponent).excelExport(excelExportProperties);
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Exporting with custom aggregate

Exporting grid data with custom aggregates allows including additional calculated values in the exported file based on specific requirements. This feature is helpful for providing a comprehensive view of the data in the exported file, incorporating specific aggregated information for analysis or reporting purposes.

In order to utilize custom aggregation, specify the type property as Custom and provide the custom aggregate function in the customAggregate property.

Within the customAggregate function, the argument contains the data that has a result property. The function calculates the count of objects in this data where the “Ship Country” field value is equal to “Brazil” and returns the count with a descriptive label.

The following example shows exporting the grid with a custom aggregate that shows the calculation of the “Brazil” count of the “Ship Country” column:

import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { GridModule, ToolbarService, ExcelExportService,AggregateService } from '@syncfusion/ej2-angular-grids'

import { Component, ViewChild } from '@angular/core';
import { data } from './datasource';
import { GridComponent } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-navigations';

@Component({
imports: [GridModule]
providers: [ExcelExportService, ToolbarService,AggregateService],
standalone: true,
  selector: 'app-root',
  template:`<div class="control-section">
              <ejs-grid #grid id="DefaultExport" [dataSource]="data" [toolbar]="toolbar"
                (toolbarClick)="toolbarClick($event)" [allowExcelExport]="true">
                <e-columns>
                  <e-column field='OrderID' headerText='Order ID' textAlign='right' width=120>
                  </e-column>
                  <e-column field='CustomerID' headerText='Customer ID' width=150></e-column>      
                  <e-column field="Freight" headerText="Freight" width="150" format="C2" 
                    textAlign="Right"></e-column>
                  <e-column field="OrderDate" headerText="Order Date" width="150"
                  format="yMd" textAlign="Right"></e-column>
                  <e-column field="ShipCountry"  headerText="Ship Country" width="150">
                  </e-column>
                </e-columns>
                <e-aggregates>
                  <e-aggregate>
                    <e-columns>
                      <e-column columnName="ShipCountry" type="Custom"
                      [customAggregate]="customAggregateFn">
                        <ng-template #footerTemplate let-data> {{ data.Custom }}</ng-template>
                      </e-column>
                    </e-columns>
                  </e-aggregate>
                </e-aggregates>
              </ejs-grid>
            </div>`
})

export class AppComponent {
  
  public data?: Object[];
  public toolbar?: string[];
  @ViewChild('grid')
  public grid?: GridComponent;

  public ngOnInit(): void {
    this.data = data.slice(0, 20);
    this.toolbar = ['ExcelExport'];
  }
  toolbarClick(args: ClickEventArgs): void {
    if (args.item.id=='DefaultExport_excelexport') {
        (this.grid as GridComponent).excelExport();
    }
  }
  public customAggregateFn = (customData: any) => {
      const brazilCount=customData.result ? customData.result.filter(
        (item: object) => (item as any)['ShipCountry'] === 'Brazil'
      ).length:
    customData.filter(
        (item: object) => (item as any)['ShipCountry'] === 'Brazil'
      ).length;
  return `Brazil Count::${brazilCount}`;
  };
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Export with cell and row spanning

Exporting data from the Grid with cell and row spanning enables maintaining cell and row layout in the exported data. This feature is useful when merged cells or rows exist in the Grid and maintaining the same structure in the exported file is desired.

To achieve this, utilize the rowSpan and colSpan properties in the queryCellInfo event of the Grid. This event allows defining the span values for specific cells. Additionally, the appearance of the grid cells during the export can be customized using the excelQueryCellInfo event of the Grid.

The following example demonstrates performing export with cell and row spanning using queryCellInfo and excelQueryCellInfo events of the Grid:

import { columnDataType, data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { Column, ExcelCell, ExcelExportService, ExcelQueryCellInfoEventArgs, FilterService, GridComponent, GridModule, QueryCellInfoEventArgs, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [GridModule ],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<ejs-grid #grid id='Grid'  [dataSource]='data' [toolbar]='toolbarOptions' height='272px' 
               allowExcelExport='true' (toolbarClick)='toolbarClick($event)' 
               (excelQueryCellInfo)="excelQueryCellInfo($event)" (queryCellInfo)="queryCellInfoEvent($event)">
                    <e-columns>
                        <e-column field='OrderID' headerText='Order ID' textAlign='Right' width=90></e-column>
                        <e-column field='CustomerID' headerText='Customer ID' width=100></e-column>
                        <e-column field='Freight' headerText='Freight' width=80></e-column>
                        <e-column field='ShipCity' headerText='Ship City' width=100></e-column>
                        <e-column field='ShipCountry' headerText='Ship Country' width=100></e-column>
                    </e-columns>
                </ejs-grid>`
})
export class AppComponent implements OnInit {

    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    public message?: string;
    @ViewChild('grid') public grid?: GridComponent;

    ngOnInit(): void {
        this.data = data;
        this.toolbarOptions=['ExcelExport'];
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') {
            (this.grid as GridComponent).excelExport();
        }
    }
    queryCellInfoEvent = function (args: QueryCellInfoEventArgs) {

        switch ((args.data as columnDataType).OrderID) {
            case 10248:
                if ((args.column as Column).field === 'CustomerID') {
                    args.rowSpan = 2;
                }
                break;
            case 10250:
                if ((args.column as Column).field === 'CustomerID') {
                    args.colSpan = 2;
                }
                break;
            case 10252:
                if ((args.column as Column).field === 'OrderID') {
                    args.rowSpan = 3;
                }
                break;
            case 10256:
                if ((args.column as Column).field === 'CustomerID') {
                    args.colSpan = 3;
                }
                break;
            case 10261:
                if ((args.column as Column).field === 'Freight') {
                    args.colSpan = 2;
                }
                break;
        }
    }
    excelQueryCellInfo = function ({data,cell,colSpan,column}:ExcelQueryCellInfoEventArgs) {

        switch ((data as columnDataType).OrderID) {
            case 10248:
                if ((column as Column).field === 'CustomerID') {
                   (cell as ExcelCell).rowSpan = 2;
                }
                break;
            case 10250:
                if ((column as Column).field === 'CustomerID') {
                    colSpan = 2;
                }
                break;
            case 10252:
                if ((column as Column).field === 'OrderID') {
                    (cell as ExcelCell).rowSpan  = 3;
                }
                break;
            case 10256:
                if ((column as Column).field === 'CustomerID') {
                    (cell as ExcelCell).colSpan = 3;
                }
                break;
            case 10261:
                if ((column as Column).field === 'Freight') {
                    (cell as ExcelCell).colSpan = 2;
                }
                break;
        }
    };
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

The updateCell method does not support row and column spanning.

Export with custom date format

The exporting functionality in the Syncfusion® Angular Grid allows exporting grid data, including custom date format. This feature is useful when exporting grid data with customized date values is needed.

To apply a custom date format to grid columns during the export, utilize the format property. This property allows defining a custom format using format options.

The following example demonstrates exporting the grid with custom date format. In the example, the “formatOptions” object is set to the format property for the “Order Date” column. This custom date format displays the date in the format of day-of-the-week, month abbreviation, day, and 2-digit year (e.g., Sun, May 8, ‘23):

import { data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportService, FilterService, GridComponent, GridModule, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-navigations';

@Component({
    imports: [ GridModule],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<div class="control-section">
                    <ejs-grid #grid [dataSource]='data' allowPaging='true' 
                    [pageSettings]='pageSettings' [toolbar]='toolbar' 
                    (toolbarClick)='toolbarClick($event)' [allowExcelExport]='true' 
                    [allowPdfExport]='true'>
                        <e-columns>
                            <e-column field='OrderID' headerText='Order ID' width='120' 
                            textAlign='Right' isPrimaryKey='true'></e-column>
                            <e-column field='OrderDate' headerText='Order Date' width='130' 
                            [format]="formatOption" textAlign='Right'></e-column>
                            <e-column field='CustomerID' headerText='Customer ID' width='120'>
                            </e-column>
                            <e-column field='Freight' headerText='Freight' [allowGrouping]="false" 
                            width='120' format='C2' textAlign='Right'></e-column>            
                            <e-column field='ShipCountry' headerText='Ship Country' width='150'>
                            </e-column>
                        </e-columns>
                    </ejs-grid>
                </div>`
})

export class AppComponent  implements OnInit {

    public data?: Object[];
    public toolbar?: string[];
    public pageSettings?: Object;
    public refresh?: Boolean;
    public formatOption?: Object;
    @ViewChild('grid')
    public grid?: GridComponent;

    public ngOnInit(): void {
        this.data = data;
        this.formatOption = { type: 'date', format:"EEE, MMM d, ''yy"};
        this.toolbar = ['ExcelExport'];
        this.pageSettings = { pageCount: 5 };
    }
    toolbarClick(args: ClickEventArgs): void {
         if(args.item.text ==='Excel Export') {
                (this.grid as GridComponent).excelExport();
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Merge duplicate cells on export

Duplicate cells (based on their values) in a specific column of the Syncfusion® Angular Grid can be merged. This can be achieved by utilizing the dataBound event. Additionally, duplicate cells in the specified column can be merged during export by using the excelQueryCellInfo event for Excel and CSV formats. This functionality is useful for improving the readability of data and providing a clearer visual representation.

The following example demonstrates merging duplicate cells in the “Order ID” column in both Grid view and export:

import { data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportCompleteArgs, ExcelExportService, ExcelQueryCellInfoEventArgs, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [GridModule],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<ejs-grid #grid id='Grid' [dataSource]='data' [toolbar]='toolbarOptions' 
               height='272px' [allowExcelExport]='true' (toolbarClick)='toolbarClick($event)'
               (dataBound)="onDataBound()" (excelQueryCellInfo)="excelQueryCellInfo($event)" (excelExportComplete)="excelExportComplete($event)"
               >
                <e-columns>
                    <e-column field='OrderID' headerText='Order ID' textAlign='Right' 
                    width=120></e-column>
                    <e-column field='CustomerID' headerText='Customer ID' width=150>
                    </e-column>
                    <e-column field='City' headerText='Ship City' width=150></e-column>
                </e-columns>
                </ejs-grid>`
})
export class AppComponent implements OnInit {

    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    public gridcells: HTMLElement | null = null;
    public currentOrderID: number | null = null;
    public rowspanCount = 1;

    @ViewChild('grid') public grid?: GridComponent;

    ngOnInit(): void {
        this.data = data;
        this.toolbarOptions = ['ExcelExport'];
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') {
            // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
            (this.grid as GridComponent).excelExport();
        }
    }
    onDataBound() {
        let previousData: number | null = null;
        let startRowIndex: number | null = null;
        let endRowIndex: number | null = null;

        let rows = (this.grid as GridComponent).getRows();
        let data = (this.grid as GridComponent).getCurrentViewRecords();

        for (let i = 0, len = rows.length; i < len; i++) {
            if (!previousData) {
                previousData = (data[i] as Order)['OrderID'];
                startRowIndex = parseInt(((rows[i] as HTMLElement).getAttribute("aria-rowindex") as string));
            }
            else if (previousData === (data[i] as Order)['OrderID']) {
                rows[i].children[0].classList.add('e-hide');
            }
            else if (previousData && previousData !== (data[i] as Order)['OrderID']) {
                if ((this.grid as GridComponent).getRows().length > 0 && (this.grid as GridComponent).getRows().length > (startRowIndex as number)) {
                    endRowIndex = parseInt(((rows[i] as HTMLElement).getAttribute("aria-rowindex") as string), 10);
                    let targetRow = (this.grid as GridComponent).getRows()[(startRowIndex as number)];
                    let currentRowChild = rows[i] && rows[i].children[0];
                    if (targetRow && currentRowChild) {
                        let targetCell = [].slice.call(targetRow.querySelectorAll('.e-rowcell')).filter((cell) =>
                            parseInt(((cell as HTMLElement).getAttribute('aria-colindex') as string), 10) === parseInt(((currentRowChild as HTMLElement).getAttribute('aria-colindex') as string))
                        );
                        if (targetCell[0]) {
                            (targetCell[0] as HTMLElement).setAttribute("rowSpan", ((endRowIndex as number) - (startRowIndex as number)).toString());
                        }
                    }
                    previousData = (data[i] as Order)['OrderID'];
                    startRowIndex = parseInt(((rows[i] as HTMLElement).getAttribute("aria-rowindex") as string), 10);
                }
            }
            if (rows[i].children[0].classList.contains("e-hide") || i < len) {
                endRowIndex = parseInt(((rows[i] as HTMLElement).getAttribute("aria-rowindex") as string), 10);
                if (endRowIndex > 0) {
                    let targetRow = (this.grid as GridComponent).getRows()[(startRowIndex as number)];
                    let currentRowChild = rows[i] && rows[i].children[0];
                    if (targetRow && currentRowChild) {
                        let targetCell = [].slice.call(targetRow.querySelectorAll('.e-rowcell')).filter((cell) =>
                            parseInt(((cell as HTMLElement).getAttribute('aria-colindex') as string), 10) === parseInt(((currentRowChild as HTMLElement).getAttribute('aria-colindex') as string))
                        );
                        if (targetCell.length > 0) {
                            (targetCell[0] as HTMLElement).setAttribute("rowSpan", ((endRowIndex as number) - (startRowIndex as number) + 1).toString());
                        }
                    }
                }
            }
        }

    };
    excelQueryCellInfo(args: ExcelQueryCellInfoEventArgs) {

        if (!this.currentOrderID && args.column.field == "OrderID") {
            this.currentOrderID = (args.data as Order)["OrderID"];
            this.gridcells = (args.cell as HTMLElement);
        }
        else if (this.currentOrderID && args.column.field == "OrderID" && this.currentOrderID == (args.data as Order)["OrderID"]) {
            this.rowspanCount++;
        } else if (this.currentOrderID !== (args.data as Order)["OrderID"] && args.column.field == "OrderID") {
            (this.gridcells as HTMLTableCellElement).rowSpan = this.rowspanCount;
            this.currentOrderID = (args.data as Order)["OrderID"];
            this.gridcells = (args.cell as HTMLElement);
            this.rowspanCount = 1;
        }
    };
    excelExportComplete(args: ExcelExportCompleteArgs) {
        this.currentOrderID = null;
        this.gridcells = null;
        this.rowspanCount = 1;
    }
}

interface Order {
    OrderID: number;
    CustomerID: string;
    City: string;
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Exporting multiple grids

Exporting multiple grids in the Syncfusion® Angular Grid component allows exporting different grids to compare them side by side in external applications on the same or different pages of an Excel document. Each grid is identified by its unique ID. The grids to export can be specified by listing their IDs in the exportGrids property.

Same sheet

Excel exporting provides support for exporting multiple grids on the same page. This feature is particularly useful when combining and organizing data from different grids for a unified view in the exported Excel file is desired.

To achieve this, define the multipleExport.type property as AppendToSheet in the excelExportProperties object. This setting ensures that the data from each grid will be appended to the same Excel sheet.

Additionally, there is an option to include blank rows between the data of each grid to visually separate them in the exported Excel sheet. The number of blank rows to be inserted can be defined using the multipleExport.blankRows property.

The following example demonstrates exporting multiple grids to the same page in an Excel file when a toolbar item is clicked:

import { data, employeeData } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportProperties, ExcelExportService, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [GridModule ],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<p><b>First Grid:</b></p>
               <ejs-grid #grid1 id='FirstGrid' [dataSource]='firstData' 
               [toolbar]='toolbarOptions' [allowExcelExport]='true'
               (toolbarClick)='toolbarClick($event)' [exportGrids]='exportGrids'>
                    <e-columns>
                        <e-column field='OrderID' headerText='Order ID' textAlign='Right' 
                        width=120></e-column>
                        <e-column field='CustomerID' headerText='Customer ID' width=150>
                        </e-column>
                        <e-column field='ShipCity' headerText='Ship City' width=150></e-column>
                        <e-column field='ShipName' headerText='Ship Name' width=150></e-column>
                    </e-columns>
                    </ejs-grid>
                    <p><b>Second Grid:</b></p>
                    <ejs-grid #grid2 id='SecondGrid' [dataSource]='secondData' 
                    [allowExcelExport]='true'>
                    <e-columns>
                        <e-column field='EmployeeID' headerText='Employee ID' textAlign='Right' 
                        width=120></e-column>
                        <e-column field='FirstName' headerText='FirstName' width=150></e-column>
                        <e-column field='LastName' headerText='Last Name' width=150></e-column>
                        <e-column field='City' headerText='City' width=150></e-column>
                    </e-columns>
                </ejs-grid> `
})
export class AppComponent implements OnInit {

    public firstData?: object[];
    public secondData?: object[];
    public toolbarOptions?: ToolbarItems[];
    public exportGrids?: string[];
    @ViewChild('grid1') public firstGrid?: GridComponent;
    @ViewChild('grid2') public secondGrid?: GridComponent;

    ngOnInit(): void {
        this.firstData = data.slice(0, 5);
        this.secondData = employeeData.slice(0, 5);
        this.toolbarOptions = ['ExcelExport'];
        this.exportGrids = ['FirstGrid', 'SecondGrid']
    }
    toolbarClick = (args: ClickEventArgs) => {
        if (args.item.id === 'FirstGrid_excelexport') { 
            // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
            const appendExcelExportProperties: ExcelExportProperties = {
                multipleExport: { type: 'AppendToSheet', blankRows: 2 }
            };
            (this.firstGrid as GridComponent).excelExport(appendExcelExportProperties, true);
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

By default, multipleExport.blankRows value is “5”.

New sheet

Excel export functionality enables the exporting of multiple grids into separate pages (each grid on a new page) within the Excel file.

To achieve this, follow these steps:

  1. Access the excelExportProperties of the Grid component.

  2. Set the multipleExport.type to NewPage.

  3. Trigger the Excel export operation.

The following example demonstrates exporting multiple grids to an Excel file when a toolbar item is clicked:

import { data, employeeData } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportProperties, ExcelExportService, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [GridModule],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<p><b>First Grid:</b></p>
               <ejs-grid #grid1 id='FirstGrid' [dataSource]='firstData' [toolbar]='toolbarOptions' 
               [allowExcelExport]='true' (toolbarClick)='toolbarClick($event)' 
               [exportGrids]='exportGrids'>
                    <e-columns>
                        <e-column field='OrderID' headerText='Order ID' textAlign='Right'
                         width=120></e-column>
                        <e-column field='CustomerID' headerText='Customer ID' width=150>
                        </e-column>
                        <e-column field='ShipCity' headerText='Ship City' width=150></e-column>
                        <e-column field='ShipName' headerText='Ship Name' width=150></e-column>
                    </e-columns>
                    </ejs-grid>
                    <p><b>Second Grid:</b></p>
                    <ejs-grid #grid2 id='SecondGrid' [dataSource]='secondData' 
                    [allowExcelExport]='true'>
                    <e-columns>
                        <e-column field='EmployeeID' headerText='Employee ID' textAlign='Right' 
                        width=120></e-column>
                        <e-column field='FirstName' headerText='FirstName' width=150>
                        </e-column>
                        <e-column field='LastName' headerText='Last Name' width=150>
                        </e-column>
                        <e-column field='City' headerText='City' width=150></e-column>
                    </e-columns>
                </ejs-grid>
                `
})
export class AppComponent implements OnInit {

    public firstData?: object[];
    public secondData?: object[];
    public toolbarOptions?: ToolbarItems[];
    public exportGrids?: string[];
    @ViewChild('grid1') public firstGrid?: GridComponent;
    @ViewChild('grid2') public secondGrid?: GridComponent;

    ngOnInit(): void {
        this.firstData = data.slice(0, 5);
        this.secondData = employeeData.slice(0, 5);
        this.toolbarOptions = ['ExcelExport'];
        this.exportGrids = ['FirstGrid', 'SecondGrid']
    }
    toolbarClick = (args: ClickEventArgs) => {
        if (args.item.id === 'FirstGrid_excelexport') { 
            // 'Grid_excelexport' -> Grid component id + _ + toolbar item name
            const appendExcelExportProperties: ExcelExportProperties = {
                multipleExport: { type: 'NewSheet' }
            };
            (this.firstGrid as GridComponent).excelExport(appendExcelExportProperties, true);
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Limitations

Multiple grids exporting feature is not supported with server side exporting.

Exporting hierarchy grid

Exporting a hierarchy grid in the Syncfusion® Angular Grid component allows generating an Excel or CSV document that includes the parent grid along with its child grids. This feature is useful when exporting hierarchical data with its related details is needed.

To achieve this, the exporting behavior can be customized by using the excelExportProperties.hierarchyExportMode property of the Grid. This property allows specifying the exporting behavior for the hierarchy grid. The following options are available:

Mode Behavior
Expanded Exports the master grid with expanded child grids.
All Exports the master grid with all child grids, expanded or not.
None Exports only the master grid without any child grids.

The following example demonstrates exporting hierarchical grid to Excel document. Also change the excelExportProperties.hierarchyExportMode property by using value property of the DropDownList component:

import { data, employeeData } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { DropDownListAllModule, DropDownListComponent } from '@syncfusion/ej2-angular-dropdowns';
import { DetailRowService, ExcelExportProperties, ExcelExportService, GridComponent, GridModel, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [GridModule,DropDownListAllModule],
    providers: [ExcelExportService, ToolbarService, DetailRowService],
    standalone: true,
    selector: 'app-root',
    template: `
            <div style="display: flex">
                <label style="padding: 10px 10px 26px 0"> 
                Change the hierarchy export mode: </label>
                <ejs-dropdownlist
                style="margin-top:5px"
                #dropDownList
                index="0"
                width="150"
                [dataSource]="dropDownData"></ejs-dropdownlist>
            </div>
            <ejs-grid #grid id='Grid' [dataSource]='data' [toolbar]='toolbarOptions' 
            [childGrid]='childGrid' height='220px' [allowExcelExport]='true' 
            (toolbarClick)='toolbarClick($event)'>
                <e-columns>
                    <e-column field='EmployeeID' headerText='Employee ID' textAlign='Right' 
                    width=90></e-column>
                    <e-column field='FirstName' headerText='FirstName' width=100>
                    </e-column>
                    <e-column field='LastName' headerText='Last Name' width=100>
                    </e-column>
                    <e-column field='City' headerText='City' width=100></e-column>
                </e-columns>
            </ejs-grid>`
})
export class AppComponent implements OnInit {

    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    @ViewChild('grid') public grid?: GridComponent;
    @ViewChild('dropDownList')
    public dropDownList?: DropDownListComponent;
    public dropDownData: object[] = [
        { text: 'None', value: 'None' },
        { text: 'Expanded', value: 'Expanded' },
        { text: 'All', value: 'All' },
    ];
    public childGrid: GridModel = {
        dataSource: data,
        queryString: 'EmployeeID',
        columns: [
            {
                field: 'OrderID',
                headerText: 'Order ID',
                textAlign: 'Right',
                width: 90,
            },
            { field: 'CustomerID', headerText: 'Customer ID', width: 100 },
            { field: 'ShipCity', headerText: 'Ship City', width: 100 },
            { field: 'ShipName', headerText: 'Ship Name', width: 110 },
        ],
    };

    ngOnInit(): void {
        this.data = employeeData;
        this.toolbarOptions = ['ExcelExport'];
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') {
            // 'Grid_Excelexport' -> Grid component id + _ + toolbar item name
            const exportProperties: ExcelExportProperties = {
                hierarchyExportMode: ((this.dropDownList as DropDownListComponent).value as ExcelExportProperties["hierarchyExportMode"] )
            };
            (this.grid as GridComponent).excelExport(exportProperties);
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Format the child grid columns before exporting

The Syncfusion Angular Grid allows customization of the child Grid’s Excel export options, enabling precise control over data formatting before export. This functionality is achieved using the exportDetailDataBound event, which is triggered for each child Grid during the export process. This event provides access to the child Grid instance, allowing modifications to its column formatting before generating the Excel document.

In the following example, the exportDetailDataBound event is used to modify the “Order Date” column of the child Grid. By setting the column’s format property, the date values are formatted as dd/MM/yyyy when exported to Excel document:

import { data, employeeData } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ColumnModel, DetailDataBoundEventArgs, DetailRowService, ExcelExportProperties, ExcelExportService, GridComponent, GridModel, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [ GridModule],
    providers: [ExcelExportService, ToolbarService, DetailRowService],
    standalone: true,
    selector: 'app-root',
    template: `
    <ejs-grid #grid id="Grid" [dataSource]="data" [toolbar]="toolbarOptions" [childGrid]="childGrid" height="220px" [allowExcelExport]="true" (toolbarClick)="toolbarClick($event)" (exportDetailDataBound)="exportDetailDataBound($event)">
        <e-columns>
            <e-column field="EmployeeID" headerText="Employee ID" textAlign="Right" width="90"></e-column>
            <e-column field="FirstName" headerText="FirstName" width="100"></e-column>
            <e-column field="LastName" headerText="Last Name" width="100"></e-column>
            <e-column field="City" headerText="City" width="100"></e-column>
        </e-columns>
    </ejs-grid>`
})
export class AppComponent implements OnInit {
    @ViewChild("grid") public grid?: GridComponent;
    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    public childGrid: GridModel = {
        dataSource: data,
        queryString: "EmployeeID",
        columns: [
            { field: "OrderID", headerText: "Order ID", textAlign: "Right", width: 90 },
            { field: "CustomerID", headerText: "Customer ID", width: 100 },
            { field: "OrderDate", headerText: "Order Date", width: 120, format: { type: 'date', format: 'dd-MM-yyyy' } },
            { field: "ShipCity", headerText: "Ship City", width: 100 },
        ],
    };
    ngOnInit(): void {
        this.data = employeeData;
        this.toolbarOptions = ["ExcelExport"];
    }
    public exportDetailDataBound(args: DetailDataBoundEventArgs): void {
        // Set the date format for the OrderDate column before exporting.
        ((args.childGrid as GridModel).columns as ColumnModel[])[2].format = 'dd\\/MM\\/yyyy';
    }
    public toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === "Grid_excelexport") {
            const exportProperties: ExcelExportProperties = {
                hierarchyExportMode: 'All',
            };
            (this.grid as GridComponent).excelExport(exportProperties);
        }
    }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

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.

Remove header row while exporting

Remove the header row from the exported file by clearing header cells in the excelHeaderQueryCellInfo event and removing the row in the created event.

import { data } from './datasource';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelCell, ExcelExport, ExcelExportService, ExcelHeaderQueryCellInfoEventArgs, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
    imports: [ GridModule ],
    providers: [ExcelExportService, ToolbarService, FilterService],
    standalone: true,
    selector: 'app-root',
    template: `<ejs-grid #grid id='Grid' [dataSource]='data' [toolbar]='toolbarOptions' 
               height='272px' [allowExcelExport]='true' (toolbarClick)='toolbarClick($event)' 
               (excelHeaderQueryCellInfo)="excelHeaderQueryCellInfo($event)" 
               (created)=created()>
                    <e-columns>
                    <e-column field='OrderID' headerText='Order ID' textAlign='Right' 
                    width=90></e-column>
                    <e-column field='CustomerID' headerText='Customer ID' 
                    width=100></e-column>
                    <e-column field='OrderDate' headerText='Order Date' 
                    [format]='formatOptions' width=100></e-column>
                    <e-column field='Freight' headerText='Freight' width=80></e-column>
                    </e-columns>
              </ejs-grid>`

})

export class AppComponent implements OnInit {

    public data?: object[];
    public toolbarOptions?: ToolbarItems[];
    public formatOptions?: object;
    @ViewChild('grid') public grid?: GridComponent;

    ngOnInit(): void {
        this.data = data;
        this.toolbarOptions = ['ExcelExport'];
        this.formatOptions = { type: 'date', format: "yMd" };
    }
    toolbarClick(args: ClickEventArgs): void {
        if (args.item.id === 'Grid_excelexport') {
            (this.grid as GridComponent).excelExport();
        }
    }
    excelHeaderQueryCellInfo(args: ExcelHeaderQueryCellInfoEventArgs) {
        (args.gridCell as ExcelCell).value = '';
      }

    created() {
    //   var gridObj = (document.getElementById("Grid")).ej2_instances[0];
      var processGridExportObject =
          ((this.grid as GridComponent).excelExportModule as any).__proto__.processGridExport;
          ((this.grid as GridComponent).excelExportModule as any).__proto__.processGridExport = function (
           gobj:object,
          props:ExcelExport,
          r:boolean
      ) {
          var rows = processGridExportObject.call(this, gobj, props, r);
          rows.shift();
          rows.forEach((item:any, index:number) => {
              item.index = index + 1;
          });
          return rows;
      };
    }
     
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Add formula for the cell while exporting

The Grid component provides a convenient way to add formulas to cells during the export process. This feature allows performing calculations and applying formulas to specific cells in the exported Excel document. This can be particularly useful when including calculated values or performing complex calculations is needed.

To add formulas to cells during the export process, utilize the valueAccessor method along with the excelQueryCellInfo event.

In the following example, the toolbarClick function handles a toolbar button click event. When the Excel Export button is clicked, it triggers the Excel export process. Inside this function, an excelExportProperties object is defined, specifying that hidden columns should be included in the export. Inside the excelQueryCellInfo event, the valueAccessor method generates formulas for the desired cells and assigns these formulas to the cell’s formula property, ensuring that the calculated values are exported to the Excel document:

import { inventoryData } from './datasource';
import { Component, ViewChild } from '@angular/core';
import { ExcelExportProperties, ExcelExportService, ExcelQueryCellInfoEventArgs, FilterService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { ClickEventArgs } from '@syncfusion/ej2-angular-navigations';

@Component({
  imports: [GridModule],
  providers: [ExcelExportService, ToolbarService, FilterService],
  standalone: true,
  selector: 'app-root',
  template: `
          <ejs-grid #grid id='Grid' [dataSource]='data' [allowPaging]='true' 
            [toolbar]='toolbarOptions' height='272px' [allowExcelExport]='true' 
            (toolbarClick)='toolbarClick($event)' (excelQueryCellInfo)="excelQueryCellInfo($event)">
            <e-columns>
              <e-column field="Inventor" headerText="Inventor Name" width="180" textAlign="Right">
              </e-column>
              <e-column field="NumberofPatentFamilies" headerText="Number of Patent Families" 
              width="180" textAlign="Right"></e-column>
              <e-column field="Country" headerText="Country" width="140" textAlign="Left">
              </e-column>
              <e-column field="Mainfieldsofinvention" headerText="Main fields of invention" 
              width="200" textAlign="Left"></e-column>
              <e-column field="Number of INPADOC patents" headerText="Number of INPADOC patents" 
              width="180" textAlign="Right"></e-column>
              <e-column field="TotalPatents" headerText="Total Patents" [visible]='false' 
              [valueAccessor]="valueAccess" width="120"  textAlign="Right"></e-column>
            </e-columns>
          </ejs-grid>`
})
export class AppComponent {

  public data?: object[];
  public toolbarOptions?: ToolbarItems[];
  @ViewChild('grid') public grid?: GridComponent;

  public ngOnInit(): void {
    this.data = inventoryData;
    this.toolbarOptions = ['ExcelExport'];
  }
  toolbarClick(args: ClickEventArgs): void {
    if (args.item.id === 'Grid_excelexport') {
      // 'Grid_pdfexport' -> Grid component id + _ + toolbar item name
      const excelExportProperties: ExcelExportProperties = {
        includeHiddenColumn: true,
      };
      (this.grid as GridComponent).excelExport(excelExportProperties);
    }

  }
  valueAccess = (field: string, data: Object[]) => {
    const cell = (this.data as Object[]).indexOf(data) + 2;
    return '=E' + cell + '+' + 'B' + cell;
  }
  excelQueryCellInfo(args: ExcelQueryCellInfoEventArgs): void {
    if (args.column.field === 'TotalPatents') {
      args.value = this.valueAccess(args.column.field, (args.data as Object[]));
      (args.cell as CustomExcelCell).formula = args.value;
    }
  } 
} 
interface CustomExcelCell {
  formula: string; 
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Pass additional parameters to the server when exporting

Passing additional parameters to the server when exporting data in the Syncfusion Angular Grid involves providing flexibility to include extra information or customize the export process based on specific requirements.

This can be achieved by utilizing the query property and the toolbarClick event. Within the query property, the addParams method can be invoked to add parameters to the request.

The following example demonstrates to pass additional parameters to the server when Excel exporting within the toolbarClick event. Within the event, the additional parameters, specifically “recordcount” as “15”, are passed using the addParams method and displayed as a message:

import { data } from './datasource';
import { CommonModule } from '@angular/common';
import { Component, OnInit, ViewChild } from '@angular/core';
import { ExcelExportCompleteArgs, ExcelExportService, GridComponent, GridModule, ToolbarItems, ToolbarService } from '@syncfusion/ej2-angular-grids';
import { Query } from '@syncfusion/ej2-data';
import { ClickEventArgs } from '@syncfusion/ej2-navigations';

@Component({
  selector: 'app-root',
  standalone: true,
  imports: [CommonModule, GridModule],
  providers: [ExcelExportService, ToolbarService],
  template: `<p id="message"></p>
             <ejs-grid #grid id='Grid' [dataSource]='data'[toolbar]='toolbarOptions' height='272px' [allowExcelExport]='true' (excelExportComplete)='excelExportComplete($event)' (toolbarClick)='toolbarClick($event)'>
              <e-columns>
                  <e-column field='OrderID' headerText='Order ID' textAlign='Right' width='120'></e-column>
                  <e-column field='CustomerID' headerText='Customer ID' [visible]='false' width='150'></e-column>
                  <e-column field='ShipCity' headerText='Ship City' width='150'></e-column>
                  <e-column field='ShipName' headerText='Ship Name' width='150'></e-column>
              </e-columns>
             </ejs-grid>
             <p id="message"></p>`
})
export class AppComponent implements OnInit {
  @ViewChild('grid') public grid!: GridComponent;
  public data: object[] = [];
  public toolbarOptions: ToolbarItems[] = ['ExcelExport'];
  public queryClone!: Query;
  public message: string = '';

  public ngOnInit(): void {
    this.data = data;
  }

  public toolbarClick(args: ClickEventArgs): void {
    if (args.item.id === 'Grid_excelexport') {
      this.queryClone = this.grid.query;
      this.grid.query = new Query().addParams('recordcount', '15');
      this.message = `Key: ${this.grid.query.params[0].key} and Value: ${this.grid.query.params[0].value} on ${args.item.text}`;
      this.grid.excelExport();
    }
  }

  public excelExportComplete(args: ExcelExportCompleteArgs): void {
    this.grid.query = this.queryClone;
  }
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));

Limitations

A CSV is a plain text format that does not support features such as cell rotation, font and color customization, column and row spanning, or adding formulas. CSV files store raw data without any formatting or styling.

See Also