Export to Excel with filterable columns does not work?

4 posts, 1 answers
  1. Vladimir
    Vladimir avatar
    8 posts
    Member since:
    Feb 2017

    Posted 01 Feb 2018 Link to this post

    Hello!

    Installed products are Telerik UI for ASP.NET + Kendo UI for jQuery. Version R1 2018 (Version number: 2018.1.117)

    I'm trying to use embedded Export to Excel function and... possible I'm wrong but it looks like this function does not support columns filtering in the right way.

    I have not so simple grid, ny grid has dynamic columns, as we can see below 'columns' - is incoming parameter for grid creation.

    You can see my examplebelow (with most important functions)

    function createGrid(columns) {
        $("#contactsGrid").empty();
     
        var gridElement = $("#contactsGrid");
        var pagingIncrement = 20;
        var scrollbarWidth = kendo.support.scrollbar();
     
        SetFieldTypesForDefaultColumns(columns); 
        var gridModel = generateModel(columns);
        var gridColumns = generateGridColumns(columns);
         
        gridElement.kendoGrid({
            dataSource: {
                type: (function () {
                    if (kendo.data.transports['aspnetmvc-ajax']) {
                        return 'aspnetmvc-ajax';
                    } else {
                        throw new Error('The kendo.aspnetmvc.min.js script is not included.');
                    }
                })(),
                transport: {
                    read: {
                        url: "contacts/getcontacts",
                        data: additionalData,
                        prefix: ""
                    }
                },
                requestEnd: function (e) {
                    if (e.response) {
                        var rows = e.response.Data;
                        for (var rowIndex = 0; rowIndex < rows.length; rowIndex++) {
                            convertTimeColumnsToDate(rows[rowIndex]);
                        }
                        manageButtonsVisibility(gridColumns.length > 0 && rows && rows.length > 0);
                    } else {
                        manageButtonsVisibility(false);
                    }
                },
                pageSize: pagingIncrement,
                serverPaging: true,
                serverSorting: true,
                sort: ({ field: "UpdatedDate", dir: "desc" }),
                schema: {
                    model: gridModel,
                    data: "Data",
                    total: "Total",
                    errors: "Errors"
                }
            },
            resizable: true,
            pageable: {
                refresh: false,
                pageSizes: [10, 20, 50, 100],
                buttonCount: 20
            },
            sortable: {
                mode: "single",
                allowUnsort: false
            },
            dataBound: function () {
                 
            },
            columns: gridColumns,
            columnMenu: {
                sortable: false,
                filterable: true,
                columns: false,
                messages: {
                    settings: "Filter",
                    filter: "Apply Filter",
                }
            },
            filterable: {
                mode: "menu"
            },
            reorderable: true,
            excel: {
                allPages: true,
                filterable: true
            },
            excelExport: function (e) {
                e.workbook.fileName = getExcelFileName();
            },
            messages: {
                commands: {
                    excel: "Export All"
                }
            },
            toolbar: ["excel",
                { template: kendo.template($("#gridColumnsTemplate").html()) }
            ],
        });
     
        createGridTooltips(gridElement.data("kendoGrid"));     // standard 'grid.thead.kendoTooltip'
        columnMenuManage(gridColumns.length > 0);     // just visibility of 'kendoColumnMenu' which hides/shows columns
        clearFiltersManage(false);     // just visibility of template button which clean all applied columns filters
        gridFilterEventHandler();     // the same but when filter is applied
        gridColumnFilterInit();     // some additions for my fields of types date/time/dateTime
    }

     

    function generateModel(columns) {
        var model = {};
        model.id = "ContactID";
        var fields = {};
        for (var index in columns) {
            if (columns[index].Visible) {
                var columnName = columns[index].Name;
                var typeName = columns[index].FieldTypeName;
     
                if (typeName == "Currency" || typeName == "Number") {
                    fields[columnName] = { type: "number" };
                } else if (typeName == "Boolean") {
                    fields[columnName] = { type: "boolean" };
                } else if (typeName == "Date" || typeName == "Time" || typeName == "Date and Time") {
                        fields[columnName] = { type: "date" };
                } else {
                    fields[columnName] = { type: "string" };
                }
            }
        }
        model.fields = fields;
        return model;
    }

     

    function generateGridColumns(columns) {
        var result = []
     
        if (columns.length == 0) return result;
     
        var i = 0;
        for (var index in columns) {
            if (columns[index].Visible) {
                var columnTitle = columns[index].DisplayName;
                var columnName = columns[index].Name;
                var columnType = columns[index].Type;
     
                var column = {};
                column.title = columnTitle;
                column.field = columnName;
                column.encoded = true;
                column.headerAttributes = {
                    "data-field": columnName,
                    "data-title": columnTitle
                };
     
                if (columns[index].FieldTypeName === "Date")
                {
                    column.format = "{0:dd/MM/yyyy}";
                    column.filterable = {
                        ui: function (element) {
                            element.kendoDatePicker({
                                format: "dd/MM/yyyy",
                                parseFormats: [ "dd/MM/yyyy", "ddMMyyyy", "dd MM yyyy" ]
                            });
                        }
                    };
                }
     
                if (columns[index].FieldTypeName === "Date and Time") {
                    column.format = "{0:dd/MM/yyyy HH:mm}";
                    column.filterable = {
                        ui: function (element) {
                            element.kendoDateTimePicker({
                                format: "dd/MM/yyyy HH:mm",
                                parseFormats: [ "dd/MM/yyyy", "ddMMyyyy", "dd MM yyyy" ]
                            });
                        }
                    };
                }
     
                if (columns[index].FieldTypeName === "Time")
                {
                    column.format = "{0:HH:mm}";
                    column.filterable = {
                        ui: function (element) {
                            element.kendoTimePicker({
                                format: "HH:mm",
                                parseFormats: [ "HH:mm", "HHmm", "HH mm" ]
                            });
                        }
                    };
                }
     
                if (columns[index].FieldTypeName === "Currency") {
                    column.format = "{0:n2}";
                }
     
                if (columns[index].FieldTypeName === "Number") {
                    column.format = "{0:n0}";
                }
     
                if (columns[index].FieldTypeName === "Boolean") {
                    var boolTmpl = function (dataItem) {
                        var val = dataItem[this.column];
                        var isEmpty = val === undefined || val === null || val === "";
                        return isEmpty ? "" : dataItem[this.column] ? "True" : "False";
                    }.bind({ column: columns[index].Name });
     
                    column.template = boolTmpl;
                }
     
                column.width = 125;
                result[i++] = column;
            }
        }
     
        var column = {};
        column.title = "";
     
        // 'actions' column there on the last position
     
        result[i] = column
     
        return result;
    }

     

    And when I have grid data and trying to apply just some simple filter, for example - by simple text field 'Surname', grid filters my records and this is absolutly Ok.

    But when I press 'Export All' button - generated file is not correct. It generates either just only header or quite 'random' result, but not the correct one ... which is presented on UI.

    Can you suggest something?

     

    ps I've attached 2 images to do my question more clear.

    1st: if I play with columns visibility and try to export -> all is ok

    2nd: if I play with column filter and try to export -> unexpected result

  2. Answer
    Stefan
    Admin
    Stefan avatar
    2652 posts

    Posted 05 Feb 2018 Link to this post

    Hello, Vladimir,

    Thank you for providing the details.

    After inspecting the code I was able to reproduce the same results on my end. The issue occurs, because the serverPaging is true, and the filtering is on the client. Please have in mind that we recommend using all operations either on the server or on the client as different unexpected issues like this one may occur when mixing the operations:

    https://docs.telerik.com/kendo-ui/framework/datasource/overview#mixed-data-operations-mode

    I hope this will help fixing the issue.

    Regards,
    Stefan
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Vladimir
    Vladimir avatar
    8 posts
    Member since:
    Feb 2017

    Posted 06 Feb 2018 Link to this post

    Hello, Stefan! Thank you for your answer.

    I think it sounds like a valid reason of described problem with export. I will try to look for solution...

     

  4. Vladimir
    Vladimir avatar
    8 posts
    Member since:
    Feb 2017

    Posted 18 Apr 2018 Link to this post

    I've a bit forgotten about this topic, but going to post a solution)

    so, into js code for gridElement.kendoGrid

    have to declare serverFiltering: true

     

    and on server side something like this:

    public ActionResult GetContacts([DataSourceRequest]DataSourceRequest request)

    {

    ....

    // prepare filter string

    string filter = GetFilter(request.Filters);

    // and transfer prepared string to sql stored procedure with another parameters we need

    var contacts = service.GetByListId(listId, pager, filter, out total);

    ...

    }

Back to Top