Removing Aggregate title from Excel export

[Kevin]

Currently I have a grid that groups on a specific column of the data. The display looks just as desired. A template is used to display the grouping "header" like

.ClientGroupHeaderTemplate("Title: #= ...  # <br/>Description: #=... # <br/>Author: #= ... # <span class='badge badge-light'>#= ... #/#= ...  #</span>");I 

 

The problem is that when exporting to Excel this "header" along with the associated HTML is exported as well. I would like to somehow disable these extra header lines from appearing in the exported data? As an added bonus I would also like to remove the duplicates from the exported data.

 

Thank you.

 

[Tsvetina]

Hello Kevin,

To hide the group headers, you can handle the Grid ExcelExport event, traverse the rows and remove those with type "group-header":

.Events(e => e.ExcelExport("onExcelExport"))

function onExcelExport(e){
  var rows = e.workbook.sheets[0].rows;
  for (var i = 0; i < rows.length; i++) {
    if (rows[i].type == "group-header") {
      rows.splice(i, 1);
    }
  }
}

As for exporting only unique records, this isn't possible to do automatically without removing the duplicate records from the Grid. The Excel export functionality exports the Grid data, as provided by the DataSource. You could declare a standalone DataSource, where data is filtered to only unique values and export its data following this example:
DataSource Export

You could cancel the Grid default export when it is triggered (in onExcelExport) and instead run the custom logic for exporting the second DataSource.


Regards,
Tsvetina
Progress Telerik

[Kevin]

This works fine if all I am doing is removing rows but it seems to break when I combine showing and hiding columns like:

        static excelExport(e) {
            if (!Gartner.GlobalAgendaTabGrid.exportFlg) {
                e.preventDefault();
                e.sender.showColumn("Id");
                e.workbook.sheets.forEach(function (sheet) {
                    sheet.rows = sheet.rows.filter(r => r.type != "group-header");
                });
                Gartner.GlobalAgendaTabGrid.exportFlg = true;
                setTimeout(() => {
                    e.sender.saveAsExcel();
                });
            } else {
                e.sender.hideColumn("Id");
                Gartner.GlobalAgendaTabGrid.exportFlg = false;
           }
        }

The Excel file that is exported in this case ignores the filtering that I did to remove the rows and all the rows are included.

[Alex Hajigeorgieva]

Hi, Kevin,

This is most likely because the rows are removed in an inappropriate moment.

I tested it this way and it works well:

Runnable Dojo: https://dojo.telerik.com/@bubblemaster/aVEtEnos

var exportFlag = false;

$("#grid").data("kendoGrid").bind("excelExport", function (e) {

  e.workbook.sheets.forEach(function (sheet) {

    sheet.rows = sheet.rows.filter(r => r.type != "group-header");

  });

  if (!exportFlag) {

    e.sender.hideColumn(1);

    e.preventDefault();

    exportFlag = true;

    setTimeout(function () {

      e.sender.saveAsExcel();

 

    });

  } else {

    e.sender.showColumn(1);

    exportFlag = false;

  }

});


Finally, I noticed that you posted another thread that seems identical to this one here:

https://www.telerik.com/forums/removing-rows-for-export

Please refrain from doing that so we can focus on the topic in a single thread. In our experience it is evident that focused threads lead to faster resolution. Thank you for your understanding in advance.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Kevin]

I noticed that you have two handlers. I am not sure how to specify this. Currently I have

        static excelExport(e) {
            e.workbook.sheets.forEach(function (sheet) {
                sheet.rows = sheet.rows.filter(r => r.type != "group-header");
            });
            // This code is performance intensive.
            if (!Gartner.GlobalAgendaTabGrid.exportFlg) {
                e.preventDefault();
                e.sender.showColumn("Id");
                Gartner.GlobalAgendaTabGrid.exportFlg = true;
                setTimeout(() => {
                    e.sender.saveAsExcel();
                });
            } else {
                e.sender.hideColumn("Id");
                Gartner.GlobalAgendaTabGrid.exportFlg = false;
            }
        }
This "deleted" rows still appear.

@(Html.Kendo().Grid<GlobalAgendaDashboardGridViewModel>()

.Name(gridName)
. . . .

.Events(events => events.DataBound("function(e) { Gartner.refreshFiltersForGrid('" + gridName + "'); Gartner.buildActionMenu(e);Gartner.GlobalAgendaTabGrid.onDataGridBound(e);}"
).ExcelExport("Gartner.GlobalAgendaTabGrid.excelExport"))

[Kevin]

It seems that I am not calling the saCme handler that you are. Can you try to specify the attributes (mostly the handler) via the grid helpers?

 

Thank you.

[Alex Hajigeorgieva]

Hello, Kevin,

It looks like I pasted the same code twice. I apologize for that, it was not intentional.

I created an example using the TagHelpers and changed the forum to ASP.NET Core. You can just paste the example and see that it is working correctly as it is using one of our services:

@addTagHelper *, Kendo.Mvc

<kendo-grid name="grid" height="550" on-excel-export="onExcelExport">
    <toolbar><toolbar-button name="excel"></toolbar-button> </toolbar>
    <datasource type="DataSourceTagHelperType.Custom" custom-type="odata" page-size="20">
        <transport>
            <read url="https://demos.telerik.com/kendo-ui/service/Northwind.svc/Customers" />
        </transport>
        <groups>
            <group field="Country"></group>
        </groups>
    </datasource>
    <groupable enabled="true" />
    <pageable button-count="5" refresh="true" page-sizes="new int[] { 5, 10, 20 }">
    </pageable>
    <filterable enabled="true" />
    <columns>
        <column field="ContactName" title="Contact Name" width="240" hidden="true"/>
        <column field="ContactTitle" title="Contact Title" />
        <column field="CompanyName" title="Company Name" />
        <column field="Country" title="Country" width="150" />
    </columns>
</kendo-grid>
<script>
    var exportFlag = false;
    function onExcelExport(e) {
       e.workbook.sheets.forEach(function (sheet) {
           sheet.rows = sheet.rows.filter(r => r.type != "group-header");
       });

       if (!exportFlag) {
           e.preventDefault();
           e.sender.showColumn("ContactName");
           exportFlag = true;
           setTimeout(() => {
               e.sender.saveAsExcel();
           });
       } else {
           e.sender.hideColumn("ContactName");
           exportFlag = false;
       }
    }
</script>

Let me know how I can assist you further.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Michael]

Alex,

I have followed you example and got the output excel without the group header.

But in the excel, there is a blank column, first column.  Can you hide or delete this blank column from the excel output?

[Alex Hajigeorgieva]

Hi, Michael,

The sheet contains a two dimensional array of rows and cells. So if you wanted to remove the first column, you would need to splice the first of each rows cells. There are many common ways of handling arrays so you can do it any way you prefer but here is one way:

 e.workbook.sheets.forEach(function (sheet) {
            sheet.rows = sheet.rows.filter(r => r.type != "group-header");
            $.each(sheet.rows, function(i,row){
                 // remove the first cell from each row
                  sheet.rows[i].cells.splice(0,1);
            });
           // remove the first column so it has the correct width 
            sheet.columns.splice(0,1);
            // update the filter range 
            sheet.filter = { from: 0, to: sheet.columns.length };
          });

In the previous example I shared, we were also hiding one column, so the filter is set to the columns.length -1 in it:

https://dojo.telerik.com/ekuDetoX

OOXML relevant API references:

• Cells - https://docs.telerik.com/kendo-ui/api/javascript/ooxml/workbook/configuration/sheets.rows.cells
• Columns - https://docs.telerik.com/kendo-ui/api/javascript/ooxml/workbook/configuration/sheets.columns
• Filter - https://docs.telerik.com/kendo-ui/api/javascript/ooxml/workbook/configuration/sheets.filter

Regards,
Alex Hajigeorgieva
Progress Telerik

[Michael]

Ding Ding Ding!

Thanks a lot