ExcelExport: Creating header to grouped grid

Question

Hello.

I can't add header to exported excel file if grid is grouped. I tried to add code from "advanced-export" sample to "grouping" sample but it isn't working.

Other question: Where can I see a documentation of the JSON-template for ExcelExport? The sample contain very small information.
Thanks, Andrew.

Pavithra Subramaniyam · Answer

Hi Kabanets, 

Query#1: I can't add header to exported excel file if grid is grouped. 

We have analyzed your query and we have confirmed  “Exporting Grouped Grid with Header not working ” as a defect and logged a report for the same. The fix will be available in our December 22, 2017 release.  

Query#2: Where can I see a documentation of the JSON-template for ExcelExport?  
 
Could you please confirm whether you want to use your custom header and footer template for ExcelExport or custom datasource for exporting? This information will be helpful to provide a better solution as early as possible. 

Regards, 
Pavithra S.

Kabanets · Answer

Hi Pavithra,Thank you for the answer. I often have to need to export grids (like as report) to Excel for further analysis. A user wants to see a details information about this grid and conditions of data filtering in the header. For example:Table about something #1Period: 01.01.2017 - 31.12.2017Filter: Category 1, Category 2As you can see, the parameters (period and filter) should be got in the export from something model, the model of parameters maybe or from the part of the common model.Then, we need compose the main grid of data. There we will want translate groups. This is important. I would like to have a description of properties for localization. How can I localize buttons in the toolbar of the grid?The footer doesn't have unusual functions.Thank you for the fast support. I'm starting to use EJS2 in my current project and hope to collaborate. EJS2 should become the main tool for me.Regards, Andrew Kabanets.

Kabanets · Answer

There's another good example.

I have the column type of Date, contains first days of the month for a righted sorting, but output only month and year:

{ field: 'Mon', headerText: 'Month', template: "${Mon.getMonth()}.${Mon.getFullYear()}" }

How can I tune the export to Excel for this column?
Apparently, the export should take the template from the grid.

See attach file...

Regards, Andrew Kabanets.

Attachment: Archive_a471c07a.zip

Pavithra Subramaniyam · Answer

Hi Kabanets, 

Quer#1: I often have to need to export grids (like as report) to Excel for further analysis 
 
We have analyzed your query and you can achieve your requirement by passing `exportProperties` on `excelExport` method. We have prepared a simple sample for your reference. In that sample, we have passed the filtering details to header. Please refer the following code example and sample link. 
 




    let grid: Grid = new Grid({ 
        dataSource: productData.splice(0, 20), 
        .   .   . 
        columns: [ 
            .   .  . 
        ] 
    }); 
    grid.appendTo('#Grid'); 
    grid.toolbarClick = (args: ClickEventArgs) => { 
        if (args.item.id === 'Grid_excelexport') { 
            grid.excelExport(getExcelExportProperties()); 
        } 
    }; 
}; 
function getExcelExportProperties(): any { 
    return { 
        header: { 
            headerRows: 7, 
            rows: [ 
                { 
                    index: 3, //row index 
                    cells: [ 
                        { index: 1, colSpan: 2, value: 'Grid Action', style: { fontColor: '#C67878', fontSize: 15, bold: true } },  
                        { index: 5, value: 'Filter Type', style: { fontColor: '#C67878', bold: true }, width: 150 } 
                    ] 
                }, 
                { 
                    index: 4, //row index 
                    cells: [ 
                        { index: 1, colSpan: 2, value: "Filtering"},  
                        { index: 5, value: grid.filterSettings.type, width: 150 } //cell index, value and width 
                    ] 
                }, 
                
            ] 
        } 
        } 
    }; 
} 

Sample Link: http://plnkr.co/edit/pElg2z6bUcvcSVkE3JyY?p=preview  

Query#2: How can I localize buttons in the toolbar of the grid? 
               
You can Localize the toolbar items by `locale` property. Please refer the code example and documentation link.  
 




import { L10n } from '@syncfusion/ej2-base'; 
 
L10n.load({ 
    'de-DE': { 
        'grid': { 
            'Add': 'add', 
            'Edit': 'edit', 
            'Cancel': 'cancel', 
            'Delete': 'Delete', 
            'Update': 'update', 
            'Excelexport':'excel', 
            'Pdfexport':'pdf', 
            'Csvexport':'csv' 
            
        }, 
        'pager': { 
        } 
    } 
}); 
    let grid: Grid = new Grid( 
        { 
            dataSource: orderData.splice(0, 200), 
            locale: 'de-DE', 
            toolbar: ['add','edit','delete','excelexport', 'pdfexport', 'csvexport'], 
            pageSettings: { pageCount: 5 }, 
            columns: [ 
                  .  .  .                
            ] 
        }); 
    grid.appendTo('#Grid');  
 
Documentation Link: http://ej2.syncfusion.com/15.4.17/documentation/grid/globalization-and-localization.html?lang=typescript  
 
Query#3: How can I  tune the export to Excel for this format column?  

You can format the cell value by using `excelQueryCellInfo` event which will be triggered before exporting each cell to Excel file. Please refer the following code example and sample link. 





    let grid: Grid = new Grid( 
        { 
            dataSource: orderData.splice(0, 200), 
            allowExcelExport: true, 
            toolbar: ['excelexport', 'pdfexport', 'csvexport'], 
            columns: [ 
                .  .  . 
            ], 
             excelQueryCellInfo : customiseCell 
        }); 
    grid.appendTo('#Grid'); 
    grid.toolbarClick = (args: ClickEventArgs) => { 
        if (args.item.id === 'Grid_excelexport') {      
         grid.excelExport(); 
        }   
    }; 
    function customiseCell(args: ExcelQueryCellInfoEventArgs ) { 
      if(args.column.field === "OrderDate"){ 
        args.value=args.data.OrderDate.getMonth().toString(); 
      } 
} 


Sample Link: http://plnkr.co/edit/VwCwt4eexLSr3GHIjkEZ?p=preview 

Regards, 
Pavithra S.

Kabanets · Answer

Thank you so much! I will try...Regards, Andrew.

Pavithra Subramaniyam · Answer

Hi Kabanets,  
  
Thanks for your update.  
  
We will wait until we hear from you.  
  
Regards,  
Pavithra S.

Hariharan J V · Answer

Hi Kabanets, 
 
We are glad to announce that our Essential Studio Volume 4, Service Pack 1, 2017 (v 15.4.23) is rolled out successfully and In that release, we have added the fix for “Exporting Grouped Grid with Header not working” issue. 
 
Regards, 
Hariharan