How to export excel with multiple sheets in one grid

[lorryl]

Hi,

I have a grid List page, that will export excel. The exported excel will have two sheets, one sheets contains the current grid list data, another sheets will contains the related detail data.

But I only have one grid in the member list page. How can I achieve the export effect I want.

[lorryl]

Another question:

1. I have 3 grid, and want to export to one excel with 3 sheets. But I find out it exported two excels at the same time in my test .

2. Can the 3 sheets name can change ?

3. Can show the picture in the excel cell?

This is the code:

 this.exporting = true;

        let excelExportProperties: ExcelExportProperties = {

            multipleExport: { type: 'NewSheet' },

            fileName: 'employee.xlsx'

        };

        const employeeGridExport: Promise<any> = this.tecgrid.excelExport(excelExportProperties, true);

        employeeGridExport.then((fData: any) => {

            let departmentData = [

                { DepartmentID: 'D1', Name1: 'd1', Name2: 'd2', Name3: 'd3', Sales: true, Purchases: true, Manufacturing: false, CustomerService: false, Reference1: 'sdf', Reference2: 'ref2', Remark: "re" },

                { DepartmentID: 'D2', Name1: 'd1', Name2: 'd2', Name3: 'd3', Sales: true, Purchases: true, Manufacturing: false, CustomerService: false, Reference1: 'sdf', Reference2: 'ref2', Remark: "re" },

            ];

            excelExportProperties = {

                multipleExport: { type: 'NewSheet' },

                dataSource: departmentData,

                fileName: 'employee.xlsx'

            };

            const departmentGridExport: Promise<any> = this.gridDepartment.excelExport(excelExportProperties, true, fData);

            departmentGridExport.then((fData2: any) => {

                let relationData = [

                    { EmployeeID: 'SALES-001', DepartmentID: 'D1', Position: 0, DefaultSupervisor: false, Name: 'sales' },

                    { EmployeeID: 'SARAL', DepartmentID: 'ALL', Position: 0, DefaultSupervisor: false, Name: 'sara' },

                ];

                excelExportProperties = {

                    multipleExport: { type: 'NewSheet' },

                    dataSource: relationData,

                    fileName: 'employee.xlsx'

                };

                this.gridRelation.excelExport(excelExportProperties, false, fData2);

            });

        });

   

[Thiyagu Subramani]

Hi lorryl, 

Thanks for contacting Syncfusion forum. 

Based on your shared information we suspect that you want to export two sheets in single file. To achieve this requirement we suggest to use below code blocks in toolbarClick event. Using this we can exports multiple number of sheets in single exported file. 

 toolbarClick = (args: ClickEventArgs) => { 

    var names = ["OrderDetail", "EmployeeDetail"]; 

    for (var i = 0; i < document.querySelectorAll(".e-grid").length; i++) {  // you can find all grid controls using this.  

      var grid = (document.getElementById(document.querySelectorAll(".e-grid")[i].id) as any).ej2_instances[0]; 

      this.objGrid.push(grid); 

    } 

    if (args.item.id === 'FirstGrid_excelexport') { // 'Grid_excelexport' -> Grid component id + _ + toolbar item name 

      var exportData; 

      const appendExcelExportProperties: ExcelExportProperties = { multipleExport: { type: 'NewSheet' } }; 

      if (this.objGrid.length > 1) {   

        var firstGridExport = this.objGrid[0].excelExport(appendExcelExportProperties, true).then(function (fData) { 

          fData.worksheets[0].name = names[0]; 

          exportData = fData; 

          for (var j = 1; j < this.objGrid.length - 1; j++) {    // iterate grids here  

            this.objGrid[j].excelExport(appendExcelExportProperties, true, exportData).then(function (wb) { 

              exportData = wb; 

              if (exportData.worksheets.length === (this.objGrid.length - 1)) { 

                for (var k = 0; k < exportData.worksheets.length; k++) { 

                  if (!exportData.worksheets[k].name) { 

                    exportData.worksheets[k].name = names[k]; 

                  } 

                } 

              } 

            }) 

          } 

          var lastGridExport = this.objGrid[this.objGrid.length - 1].excelExport(appendExcelExportProperties, true, exportData).then(function (wb) { 

            wb.worksheets[wb.worksheets.length - 1].name = names[this.objGrid.length - 1]; 

            const book = new Workbook(wb, 'xlsx'); 

            book.save('Export.xlsx'); 

          }.bind(this)); 

        }.bind(this)); 

      } 

    } 

  } 

Sample link:  https://stackblitz.com/edit/angular-vqda89-lcyezb?file=app.component.ts 

UG: https://ej2.syncfusion.com/angular/documentation/grid/excel-exporting/#multiple-grid-exporting 

API: https://ej2.syncfusion.com/angular/documentation/api/grid/#toolbarclick 

Screenshot:  

 

Please get back to us, if you need any further assistance. 

Regards, 

Thiyagu S 

[lorryl]

Thanks for your reply, that's what I want, it works fine.

[Thiyagu Subramani]

Hi lorryl, 

Thanks for your update. 

We are happy to hear that the provided solution works at your end. 

Please get back to us, if you need any further assistance. 

Regards, 

Thiyagu S