Removing Aggregate title from Excel export

10 posts, 0 answers
  1. Kevin
    Kevin avatar
    36 posts
    Member since:
    Jul 2018

    Posted 20 Mar 2019 Link to this post

    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.

     

  2. Tsvetina
    Admin
    Tsvetina avatar
    2481 posts

    Posted 22 Mar 2019 Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  3. Kevin
    Kevin avatar
    36 posts
    Member since:
    Jul 2018

    Posted 26 Jun 2019 Link to this post

    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.

  4. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 28 Jun 2019 Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  5. Kevin
    Kevin avatar
    36 posts
    Member since:
    Jul 2018

    Posted 28 Jun 2019 Link to this post

    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"))

  6. Kevin
    Kevin avatar
    36 posts
    Member since:
    Jul 2018

    Posted 28 Jun 2019 Link to this post

    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.

  7. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 02 Jul 2019 Link to this post

    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>
            </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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  8. Michael
    Michael avatar
    4 posts
    Member since:
    Mar 2020

    Posted 30 Mar 2020 in reply to Alex Hajigeorgieva Link to this post

    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?

  9. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 01 Apr 2020 Link to this post

    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:

    Regards,
    Alex Hajigeorgieva
    Progress Telerik

    Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
    Our thoughts here at Progress are with those affected by the outbreak.
  10. Michael
    Michael avatar
    4 posts
    Member since:
    Mar 2020

    Posted 01 Apr 2020 in reply to Alex Hajigeorgieva Link to this post

    Ding Ding Ding!

    Thanks a lot

Back to Top