1. Home
  2. Forum
  3. Vue
  4. Vue Data Grid exportExcel: Multiple grids (more then 2) to single excel sheet.

Vue Data Grid exportExcel: Multiple grids (more then 2) to single excel sheet.

Hello there,

I am having trouble export more than 2 grids into a single excel file. below is my sample code. As noted in the code. Only the third grid's data are exported.

    let exportProperties = {
        multipleExport: { type: 'AppendToSheet', blankRows: 3 }
    };

    let promise = this.$refs.grid1.excelExport(exportProperties, true);
    let promise2 = promise.then((wkbk)=>{
        this.$refs.grid2.excelExport(exportProperties, true, wkbk);
    });
    promise2.then((wkbk)=>{
        // wkbk here is undefined. causing the excel file only contains grid3's data
        this.$refs.grid3.excelExport(exportProperties, false, wkbk);
    });


Then, I tried to remember the workbook from the first call. Now I only get grid1 and grid3
    var workbook;
    let promise = this.$refs.grid1.excelExport(exportProperties, true);
    let promise2 = promise.then((wkbk)=>{
        workbook = wkbk;
        this.$refs.grid2.excelExport(exportProperties, true, workbook);
    });
    promise2.then(()=>{
        // now I only get grid1 and grid3's data
        this.$refs.grid3.excelExport(exportProperties, false, workbook);
    });
Thanks,

John


12 Replies 1 reply marked as answer
AG Ajith Govarthan Syncfusion Team July 1, 2020 11:32 AM UTC

Hi John, 

Thanks for contacting Syncfusion support. 

Based on your requirement you want to export all the grids in a same sheet. Based on this we have prepared sample in that we have used the toolbarClick event and enabled the excel export for all the grids. 

And when you click the ExcelExport toolbar item. We have called the excelExport method of first grid and in the then function of first grid we have called the excelExport method of second grid. Similarly we have done for all the grids. In the excel export method we have passed the isMultipleExport property as true for all the grids except the last one. 

For your convenience we have attached the cod example and the documentation so please refer them for your reference. 

Code example: 
App.vue 

  if (args.item.id === 'Grid_excelexport') { 
             let appendExcelExportProperties: ExcelExportProperties = { 
            multipleExport: { type: 'AppendToSheet', blankRows: 2 } 
        }; 

        let firstGridExport = grid.excelExport(appendExcelExportProperties, true);  
        firstGridExport.then((fData) => { 
           let secondGrid = grid2.excelExport(appendExcelExportProperties, true, fData);   // pass first grid argument fData and set isMultipleExport as true 
            secondGrid.then((sData)=>{ 
            grid3.excelExport(appendExcelExportProperties,false,sData); 
            }); 

        }); 

        } 


Regards, 
Ajith G. 


Marked as answer
JC John Chen July 2, 2020 04:03 PM UTC

Hi Ajith,

Thanks for the quick response. your solution worked.

Now I have a new question.

Actually in my case, I only have ONE grid. The grid contents (columns and dataSource) changes based on the selection a list.
But when exporting, I want to export all, i.e. go through each selection and export them one-by-one.

I tried set the columns and dataSource of the exportProperties before calling excelExport(). But I got empty result.
please advise how can I achieve this. A sample is greatly appreciated.

Thanks,

John



AG Ajith Govarthan Syncfusion Team July 3, 2020 01:02 PM UTC

Hi John, 

Thanks for the update. 

We have checked your requirement and you have mentioned that you are changing the dataSource and columns based on the selection list and also you want to export all.   

When you click on the PDF export icon in the grid toolbar you need to export all the records not only the records binded in the Grid. 

1. Share the complete grid code example. 

2. Share the screenshot or video demonstration of your requirement. 

3. Share the Syncfusion package version. 

Regards, 
Ajith G. 



AG Ajith Govarthan Syncfusion Team July 3, 2020 01:11 PM UTC

Hi John, 

Please ignore previous update. 

We have checked your requirement and you have mentioned that you are changing the dataSource and columns based on the selection list and also you want to export all.   

When you click on the Excel export icon in the grid toolbar you need to export all the records not only the records binded in the Grid. 

1. Share the complete grid code example. 

2. Share the screenshot or video demonstration of your requirement. 

3. Share the Syncfusion package version. 

Regards, 
Ajith G. 


JC John Chen July 9, 2020 04:45 AM UTC

Hi Ajith,

I made a non-working sample (below) just to demo what I am going to do.

1. a dropdown box so the user can select.
2. the grid switch the dataSource and columns based on user's selection.
3. When user click the export button, I want to export not just current selected but all into one excel sheet.



Thanks,

John


<template>
    <div class="dlg" > 
        <div class="grayBar">
            <div class="btnExportToExcel" title="Export to Excel"
                @click.prevent.stop="onExportToExcelClick">
                <img class="img" :src="iconExportToExcel" />   
            </div> 
            <div class="mydropdown">
                <MyDropdown :source="dataTypes" :selectionChanged="onDataTypeChanged" />
            </div>

        </div>

        <!-- shared ejs-grid for export -->
        <ejs-grid ref="grid" v-show="false"
            :enableVirtualization="true"  :allowExcelExport="true" 
            :dataSource="gridData" 
            :columns="gridColumns"
            >
        </ejs-grid>

    </div>
</template>
<script>


import Vue from "vue";
import { GridPlugin, VirtualScroll, ExcelExport, Resize } from "@syncfusion/ej2-vue-grids";

Vue.use(GridPlugin);

export default {
    name: 'MyExcelReport',
    mixins: [langmixin, dlgmixin, formmixin],
    components:{  


    },
    provide: {
        grid: [VirtualScroll, ExcelExport, Resize]
    },
    data(){
        return {
            
            dataTypes: [],  
            gridData: [],
            gridColumns: [],

        }
    },        
    props: {  
       
    },  

    created(){ 
        this.dataTypes = getDataTypes();


    },
    methods:{ 
        getDataTypes() {
            var list = ['A', 'B', 'C', 'D'];

            return list;
        },
        
        onDataTypeChanged(val){
            this.gridData = getGridData(val);
            this.gridColumns = getGridColumns(val);
        },

        onExportToExcelClick() {


            var exportProperties = {
                multipleExport: { type: 'AppendToSheet', blankRows: 3 },
                fileName: 'MyExcelExport.xlsx'
            };

            // below just show what I intended to do. Probably need do it recursively.
            // but I cannot even get the first one.
            exportProperties.dataSource = getGridData(this.dataTypes[0]);
            exportProperties.columns = getGridColumns(this.dataTypes[0])
            let xlExport = this.$refs.grid.excelExport(exportProperties, true);
            xlExport.then((wkbk)=>{
                exportProperties.dataSource = getGridData(this.dataTypes[1]);
                exportProperties.columns = getGridColumns(this.dataTypes[1]);
                let xlExport = this.$refs.grid.excelExport(exportProperties, true, wkbk);
                xlExport.then((wkbk) => {
                    exportProperties.dataSource = getGridData(this.dataTypes[1]);
                    exportProperties.columns = getGridColumns(this.dataTypes[1]);
                    this.$refs.grid.excelExport(exportProperties, false, wkbk);
                });
            });
        }
    }

 }
</script>

<style lang = "scss" scoped> 
.dlg{
    width: 800px;
    height: 800px; 
    resize: both;
    overflow: auto;    
}
.grayBar{
    padding-left: 10px;
    padding-top: 2px;
    margin-bottom: 2px;
    margin-right: 99px;

    position: relative;
    width: 100%;
    height: 24px;   
    display: flex;    
    font-size: 13px;
    background-color: #F5F5F5; 
}

.btnExportToExcel{
     position: absolute;
     top: 4px;
     left: 20px;
     width: 20px;
     height: 20px;
     z-index: 1;
     &:hover {
        background-color:rgba(200,200,255,0.9);
     }
 }

.img{
    padding-top: 2px; 
    padding-left: 2px;
    padding-right:2px;
    padding-bottom: 2px;
 }

</style>

AG Ajith Govarthan Syncfusion Team July 10, 2020 02:03 PM UTC

Hi John, 

Thanks for the update. 

Based on your query we found that you have rendered single grid and based on the dropdown selection you have binded data and the columns for the Grid. In this you want to export the all the columns and data not only the current selected data. 

By default in EJ2 Grid using the single grid Instance you can call excelExport method only onetime and you cannot call excelExport method multiple times to export multiple grid data in a single sheet. So it is not feasible to export the single grid with multiple times with different dataSource and columns inside the promise. 

Please get back to us if you need further assistance. 

Regards, 
Ajith G.  



JC John Chen July 10, 2020 02:48 PM UTC

Hi Ajith,

Thanks for your reply. I will create multiple grids to do this.

More questions?

#1. Is there a limit on the number of nested excelExport() into one single Excel file?

#2. Since I am putting different data in a single Excel.  I like to add section title row so before the grid data. How can I do that?

#3. if #2 cannot be done, I am thinking exporting each grid into it own sheet (instead of all into one single sheet) and have a name for the sheet. Is that possible?

Thanks again for your help.

John 

AG Ajith Govarthan Syncfusion Team July 13, 2020 12:44 PM UTC

Hi John, 

Thanks for the update. 

Query: 1. Is there a limit on the number of nested excelExport() into one single Excel file? 

By default you can use the nested excelExport into single grid to one excel file and there will not be any limits. For your convenience we have attached the documentation so please refer them for your reference. 


Query: 2. Since I am putting different data in a single Excel.  I like to add section title row so before the grid data. How can I do that? 

Based on your requirement you want to export separate headers for all the grids exported in the single page. We have tired to achieve your requirement but it is not feasible to achieve your requirement. 

Query: 3  if #2 cannot be done, I am thinking exporting each grid into it own sheet (instead of all into one single sheet) and have a name for the sheet. Is that possible? 

Based on your requirement we have prepared sample in that we have exported the multiple grids in the different sheets and also set sheet names for the each grids. For your convenience we have attached the code example so please refer them for your reference. 

Code example: 
App.vue 

var names = ["OrderDetail", "EmployeeDetail", "Detail", "CustomerDetail", "ProductDetail"]; // sheet names 

firstGrid.toolbarClick = function (args) { 
  if (args['item'].id === 'FirstGrid_excelexport') { 
    var appendExcelExportProperties = { 
      header: { 
        headerRows: 7, 
        rows: [ 
          { cells: [{ colSpan: 4, value: "Syncfusion Software", 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:[email protected]' }, 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 } }] } 
        ] 
      }, 
      multipleExport: { type: 'NewSheet' } 
    }; 
    var Grids = []; 
    for (var i = 0; i < document.querySelectorAll(".e-grid").length; i++) { 
      var grid = document.getElementById(document.querySelectorAll(".e-grid")[i].id).ej2_instances[0]; 
      Grids.push(grid);           // get all grid instances 
    } 
    if (Grids.length > 1) { 
      appendExcelExportProperties.header.rows[0].cells[0].value = names[0]; 
      var firstGridExport = firstGrid.excelExport(appendExcelExportProperties, true).then(function (fData) { 
        fData.worksheets[0].name = names[0]; 
        exportData = fData; 
        for (var j = 1; j < Grids.length - 1; j++) { 
          appendExcelExportProperties.header.rows[0].cells[0].value = names[1]; 
          Grids[j].excelExport(appendExcelExportProperties, true, exportData).then(function (wb) { 
            appendExcelExportProperties.header.rows[0].cells[0].value = names[wb.worksheets.length]; 
            exportData = wb; 
            if (exportData.worksheets.length === (Grids.length - 1)) { 
              for (var k = 0; k < exportData.worksheets.length; k++) { 
                if (!exportData.worksheets[k].name) { 
                  exportData.worksheets[k].name = names[k]; 
                } 
              } 
            } 
          }) 
        } 
        var lastGridExport = Grids[Grids.length - 1].excelExport(appendExcelExportProperties, true, exportData).then(function (wb) { 
          appendExcelExportProperties.header.rows[0].cells[0].value = names[names.length - 1]; 
          wb.worksheets[wb.worksheets.length - 1].name = names[Grids.length - 1]; 
          const book = new ej.excelexport.Workbook(wb, 'xlsx'); 
          book.save('Export.xlsx'); 
        }); 
      }); 
    } 
  } 
} 


Please get back to us if you need further assistance. 

Regards, 
Ajith G. 


JC John Chen July 16, 2020 03:58 AM UTC

Thanks Ajith,

Your sample helped a lot. All worked except at the very end. I got error: 'ej' is not defined.
I am not sure what I need to import.

Thanks,

John

 var lastGridExport = Grids[Grids.length - 1].excelExport(appendExcelExportProperties, true, exportData).then(function (wb) { 
          appendExcelExportProperties.header.rows[0].cells[0].value = names[names.length - 1]; 
          wb.worksheets[wb.worksheets.length - 1].name = names[Grids.length - 1]; 
          const book = new ej.excelexport.Workbook(wb, 'xlsx'); // GOT ERROR ej is not defined.
          book.save('Export.xlsx'); 
        }); 
     

AG Ajith Govarthan Syncfusion Team July 17, 2020 01:08 PM UTC

Hi John, 

Sorry for the inconveniences. 

Based on your requirement we have prepared sample and in that sample we have imported all the necessary modules to export multiple grids in the different sheets.  

For your convenience we have attached the sample and the code example so please refer them for your reference. 

Code example: 
App.vue 
import { Workbook } from "@syncfusion/ej2-excel-export"; 

toolbarClick: function(args) { 
      //debugger; 
      var exportData; 
      var firstGrid = document.getElementById("Grid").ej2_instances[0]; 
      var names = [ 
        "OrderDetail", 
        "EmployeeDetail", 
        "Detail", 
        "CustomerDetail", 
        "ProductDetail" 
      ]; 
      if (args.item.id === "Grid_excelexport") { 
        var appendExcelExportProperties = { 
          header: { 
            headerRows: 7, 
            rows: [ 
              { 
                cells: [ 
                  { 
                    colSpan: 4, 
                    value: "Syncfusion Software", 
                    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:[email protected]" }, 
                    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 } 
                  } 
                ] 
              } 
            ] 
          }, 
          multipleExport: { type: "NewSheet" } 
        }; 
        var Grids = []; 
        for (var i = 0; i < document.querySelectorAll(".e-grid").length; i++) { 
          var grid = document.getElementById( 
            document.querySelectorAll(".e-grid")[i].id 
          ).ej2_instances[0]; 
          Grids.push(grid); // get all grid instances 
        } 
        if (Grids.length > 1) { 
          appendExcelExportProperties.header.rows[0].cells[0].value = names[0]; 
          var firstGridExport = firstGrid 
           .excelExport(appendExcelExportProperties, true) 
            .then(function(fData) { 
              fData.worksheets[0].name = names[0]; 
              exportData = fData; 
              for (var j = 1; j < Grids.length - 1; j++) { 
                appendExcelExportProperties.header.rows[0].cells[0].value = 
                  names[1]; 
                Grids[j] 
                  .excelExport(appendExcelExportProperties, true, exportData) 
                  .then(function(wb) { 
                    appendExcelExportProperties.header.rows[0].cells[0].value = 
                      names[wb.worksheets.length]; 
                    exportData = wb; 
                    if (exportData.worksheets.length === Grids.length - 1) { 
                      for (var k = 0; k < exportData.worksheets.length; k++) { 
                        if (!exportData.worksheets[k].name) { 
                          exportData.worksheets[k].name = names[k]; 
                        } 
                      } 
                    } 
                  }); 
              } 
              var lastGridExport = Grids[Grids.length - 1] 
                .excelExport(appendExcelExportProperties, true, exportData) 
                .then(function(wb) { 
                  appendExcelExportProperties.header.rows[0].cells[0].value = 
                    names[names.length - 1]; 
                  wb.worksheets[wb.worksheets.length - 1].name = 
                    names[Grids.length - 1]; 
                  //debugger; 
                  const book = new Workbook(wb, "xlsx"); 
                  book.save("Export.xlsx"); 
                }); 
            }); 
        } 
      } 
      if (args.item.id === "Grid_pdfexport") { 
        // 'Grid_pdfexport' -> Grid component id + _ + toolbar item name 
        this.$refs.grid.pdfExport(); 
      } 
    } 



Please get back to us if you need further assistance. 

Regards, 
Ajith G. 


JC John Chen July 17, 2020 03:07 PM UTC

Thanks again Ajith,

It works perfectly now.

John

AG Ajith Govarthan Syncfusion Team July 20, 2020 12:32 PM UTC

Hi John, 

Thanks for the update. 

We are happy to hear that your issue has been resolved. 

Please get back to us if you need further assistance. 

Regards, 
Ajith G. 


Loader.
Up arrow icon