This is a migrated thread and some comments may be shown as answers.

Export to Excel with filterable columns does not work?

3 Answers 579 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Vladimir
Top achievements
Rank 1
Vladimir asked on 01 Feb 2018, 01:27 PM

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

3 Answers, 1 is accepted

Sort by
0
Accepted
Stefan
Telerik team
answered on 05 Feb 2018, 06:55 AM
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.
0
Vladimir
Top achievements
Rank 1
answered on 06 Feb 2018, 04:01 PM

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...

 

0
Vladimir
Top achievements
Rank 1
answered on 18 Apr 2018, 06:45 AM

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

...

}

Tags
General Discussions
Asked by
Vladimir
Top achievements
Rank 1
Answers by
Stefan
Telerik team
Vladimir
Top achievements
Rank 1
Share this question
or