how to export kendo grid to excel file with filtered data

Question

i would like to know how to export grid to excel file with filtered data.

and then choose whether to export the single page or all the pages, always with the filtered data.

This is my component:

<kendo-grid
    [data]="gridData"
    [pageSize]="state.take"
    [skip]="state.skip"
    [sort]="state.sort"
    [filter]="state.filter"
    [sortable]="true"
    [pageable]="true"
    [filterable]="true"
    [selectable]="true"
    (dataStateChange)="dataStateChange($event)">
    <ng-template kendoGridToolbarTemplate>
        <button type="button" kendoGridExcelCommand icon="file-excel">Export To Excel</button>
    </ng-template>
    <kendo-grid-column field="id" [hidden]=true></kendo-grid-column>    
    <kendo-grid-column field="name"></kendo-grid-column>
    <kendo-grid-column field="surname"></kendo-grid-column>
    <kendo-grid-column field="birth_date"></kendo-grid-column>

    <kendo-grid-excel fileName="MyFile.xlsx" [fetchData]="allData"></kendo-grid-excel>
</kendo-grid>

TS COMPONENT:

constructor() { 
  this.allData = this.allData.bind(this);
}
    
ngOnInit() {
  this.getGridData();
}

public allData(): ExcelExportData {
  const result: ExcelExportData =  {
    data: process(this.products, {sort: [{ field: 'name', dir: 'asc' }] }).data
  };
  return result;
}

getGridData(){
  this.MyService.getDataGrid().subscribe(data => {
    data = JSON.parse(data);;
    this.products= data;
    this.gridData = process(data, this.state)
  });
}

Thank you,

best regards

score 2 · Accepted Answer · answered May 05 '21 at 04:50

From Telerik Documentation By default, the Grid exports its current data. To export data that is different from the current Grid data, specify a custom fetchData function (which you have done). It returns an ExcelExportData value or array. The allData() method takes a collection of data—typically, but not mandatory, the same collection to which the Grid is bound—processes it (for example, applies or removes paging, filtering, sorting, and so on), and uses the resulting object to create the workbook.

So, basically once you tell the grid that you want customized set of data, it's on you to query the data for your needs using Data Query component.

import { Component } from '@angular/core';
import { process } from '@progress/kendo-data-query';
import { ExcelExportData } from '@progress/kendo-angular-excel-export';
import { products } from './products';

@Component({
    selector: 'my-app',
    template: `
        <kendo-grid [kendoGridBinding]="products" [height]="400" [group]="group" [pageable]="true" [pageSize]="10">
            <ng-template kendoGridToolbarTemplate>
                <button type="button" kendoGridExcelCommand icon="file-excel">Export to Excel</button>
            </ng-template>
            <kendo-grid-column field="ProductID" title="Product ID" [width]="200">
            </kendo-grid-column>
            <kendo-grid-column field="ProductName" title="Product Name">
            </kendo-grid-column>
            <kendo-grid-column field="Category.CategoryName" title="Category" [hidden]="true">
            </kendo-grid-column>
        <kendo-grid-excel fileName="Products.xlsx" [fetchData]="allData"></kendo-grid-excel>
      </kendo-grid>
    `
})
export class AppComponent {
    public products: any[] = products;

    public group: any[] = [{
        field: 'Category.CategoryName'
    }];

    // Bind 'this' explicitly to capture the execution context of the component.
    constructor() {
        this.allData = this.allData.bind(this);
    }

    public allData(): ExcelExportData {
        const result: ExcelExportData =  {
            data: process(products, { group: this.group, sort: [{ field: 'ProductID', dir: 'asc' }] }).data,
            group: this.group
        };

        return result;
    }
}

The process function is from Kendo Data Query component that you have to install. You can provide skip and take parameters to to get to your page data.

const result = process(data, {
    skip: 10,
    take: 20,
    group: [{
      field: 'category.categoryName',
            aggregates: [
                  { aggregate: "sum", field: "unitPrice" },
                  { aggregate: "sum", field: "unitsInStock" }
            ]
      }],
    sort: [{ field: 'productName', dir: 'desc' }],
    filter: {
        logic: "or",
        filters: [
          { field: "discontinued", operator: "eq", value: true },
          { field: "unitPrice", operator: "lt", value: 22 }
        ]
    }
});

how to export kendo grid to excel file with filtered data

1 Answers1