How to get filtered value when exporting excel

[Adil]

Hi All

I just want to learn when i do custom excel export i want to get filtered data from ajax request

I tried .view() but it just return data on screen like if i have 20 data and show just 10 of them i get just 10 of them

on the other hand i tried .data() function but that is returning all data and i have really huge data like 15k data

 

How to get just filtered data?

 

here my grid and js functon and controller

@( Html.Kendo().Grid<Orpac.Controllers.DashboardsController.ATGAlerts>()
                 .Name("grdProductAlert").AutoBind(false)
                 .HtmlAttributes("width: 100%;cellpadding:0;")
                 .DataSource(d => d.Ajax().Read(r => r.Action("GridProductAlertBinding", "Dashboards").Type(HttpVerbs.Get)).ServerOperation(false))
                  .Columns(columns =>
                  {
                      columns.Bound(e => e.BelIdent).Title("Id").Width("auto");
                      columns.Bound(e => e.StationCode).Title("Station Code").Width("auto");
                      columns.Bound(e => e.StationName).Title("Station Name").Width("auto");
                      columns.Bound(e => e.BelTarih).Title("Date Time").ClientTemplate("#= kendo.toString(BelTarih, 'MM/dd/yyyy') #").ClientGroupHeaderTemplate("DateTime" + ": #=  kendo.toString(value, 'MM/dd/yyyy') #").Width("auto");
                      columns.Bound(e => e.BelInsTime).Title("Alert Time").ClientTemplate("#= kendo.toString(BelInsTime, 'MM/dd/yyyy HH:mm tt') #").ClientGroupHeaderTemplate("DateTime" + ": #=  kendo.toString(value, 'MM/dd/yyyy HH:mm tt') #").Width("auto");
                      columns.Bound(e => e.ProductCode).Title("Product Code").Width("auto");
                      columns.Bound(e => e.BelAlarm).Title("Alarm").Width("auto");
                      columns.Bound(e => e.BelLevelPerc).Title("Level %").Width("auto");
                      columns.Bound(e => e.BelTotCapacity).Title("Total Capacity").Width("auto");
                      columns.Bound(e => e.BelFuelVolume).Title("Available Volume").Width("auto");
                  }
                )
                .ToolBar(toolBar =>
                {
                    toolBar.Custom().Text(" ").HtmlAttributes(new { @class = "btnexcel", href = "#", onclick= "testexcel()" });
                    toolBar.Custom().Text((string)ViewData["ClearFilter"]).HtmlAttributes(new { @class = "k-button", id = "cleargrid", href = "#", onclick = "clearFiltersFuelLevel()" });
                }
                )
                .Selectable()
                .Sortable()
                .Pageable(pageable => pageable
                .Refresh(true)
                .PageSizes(true)
                .ButtonCount(5))
                .Filterable(filterable => filterable
                .Extra(false)
                .Operators(operators => operators
                .ForString(str => str.Clear()
                .StartsWith((string)ViewData["Startswith"])
                .Contains((string)ViewData["Contains"])
                ))
                )
                .Groupable()
                .Scrollable(scrolling => scrolling.Height("100%"))
                .Resizable(config =>
                {
                    config.Columns(true);
                })
                .Reorderable(config =>
                {
                    config.Columns(true);
                })
                .ColumnMenu()
                )

here my js

function testexcel() {
    var grid = $("#grdProductAlert").data('kendoGrid');

    var viewdata = {
        data: grid.dataSource.view()
    };



    var url = '../Dashboards/ExportCsvList';
    $.ajax({
        type: "POST",
        url: url,
        traditional:true,
        data: { request: viewdata.data },
        success: function (msg) {

        }
    });
}

 

here my conroller

public FileResult ExportCsvList(List<ATGAlerts> request)
        {
            //Get the data representing the current grid state - page, sort and filter
            var products = request;

            using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
            {
                /* Create the worksheet. */

                SpreadsheetDocument spreadsheet = Excel.CreateWorkbook(stream);
                Excel.AddBasicStyles(spreadsheet);
                Excel.AddAdditionalStyles(spreadsheet);
                Excel.AddWorksheet(spreadsheet, "ProductAlert");
                Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;

                //create columns and set their widths


                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 1, "Ident", false, false);
                Excel.SetColumnWidth(worksheet, 1, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 2, "Station Code", false, false);
                Excel.SetColumnWidth(worksheet, 2, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 3, "Station  Name", false, false);
                Excel.SetColumnWidth(worksheet, 3, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 4, "Date Time", false, false);
                Excel.SetColumnWidth(worksheet, 4, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 5, "Alert Time", false, false);
                Excel.SetColumnWidth(worksheet, 5, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 6, "Product Code", false, false);
                Excel.SetColumnWidth(worksheet, 6, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 7, "Alarm", false, false);
                Excel.SetColumnWidth(worksheet, 7, 50);


                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 8, "Level %", false, false);
                Excel.SetColumnWidth(worksheet, 8, 50);

                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 9, "Total Capacity", false, false);
                Excel.SetColumnWidth(worksheet, 9, 50);


                Excel.SetColumnHeadingValue(spreadsheet, worksheet, 10, "Available Volume", false, false);
                Excel.SetColumnWidth(worksheet, 10, 50);

                /* Add the data to the worksheet. */

                // For each row of data...
                for (int idx = 0; idx < products.Count; idx++)
                {
                    // Set the field values in the spreadsheet for the current row.
                    Excel.SetCellValue(spreadsheet, worksheet, 1, (uint)idx + 2, products[idx].BelIdent.ToString(), false, false);

                    Excel.SetCellValue(spreadsheet, worksheet, 2, (uint)idx + 2, products[idx].StationCode, false, false);

                    Excel.SetCellValue(spreadsheet, worksheet, 3, (uint)idx + 2, products[idx].StationName, false, false);
                    Excel.SetCellValue(spreadsheet, worksheet, 4, (uint)idx + 2, products[idx].BelTarih.Value, 1, false);
                    Excel.SetCellValue(spreadsheet, worksheet, 5, (uint)idx + 2, products[idx].BelInsTime.Value, 1, false);
                    Excel.SetCellValue(spreadsheet, worksheet, 6, (uint)idx + 2, products[idx].ProductCode, false, false);

                    Excel.SetCellValue(spreadsheet, worksheet, 7, (uint)idx + 2, products[idx].BelAlarm, false, false);

                    Excel.SetCellValue(spreadsheet, worksheet, 8, (uint)idx + 2, products[idx].BelLevelPerc.ToString(), false, false);

                    Excel.SetCellValue(spreadsheet, worksheet, 9, (uint)idx + 2, products[idx].BelTotCapacity.ToString(), false, false);
                    Excel.SetCellValue(spreadsheet, worksheet, 10, (uint)idx + 2, products[idx].BelFuelVolume.ToString(), false, false);

                }

                /* Save the worksheet and store it in Session using the spreadsheet title. */

                worksheet.Save();
                spreadsheet.Close();

                return File(stream.ToArray(),   //The binary data of the XLS file
                "application/vnd.ms-excel", //MIME type of Excel files
                "ProductAlert.xlsx");
            }
        }

 

please help

[Nikolay]

Hello Adil,

The view() method returns the data items which correspond to the current page configuration and will not get the whole filtered data. So I would suggest traversing through all Pages and adding the filtered data to the list.

var filteredRows = [];
          
var dataSource = $("#grid").data("kendoGrid").dataSource;
var filters = dataSource.filter();
            
var totalRowCount = parseInt(dataSource.total().toString());
var totalPages = Math.ceil(totalRowCount / dataSource.pageSize());
PageTraverser(dataSource, 1, totalPages, filters, function () {
     console.log(filteredRows)
});  
function PageTraverser(dataSource, targetPage, totalPages, filters, completionFunction) {
            dataSource.query({
              page: targetPage,
              pageSize: 5,
              filter: filters
            }).then(function () {
              var view = dataSource.view();
              for (var viewItemId = 0; viewItemId < view.length; viewItemId++) {
                var viewItem = view[viewItemId];
                filteredRows.push(viewItem);
              }
              targetPage++;
              if (targetPage <= totalPages) {
                PageTraverser(dataSource, targetPage, totalPages, filters, completionFunction);
              } else {
                completionFunction();
              }
            });
          } 

  The above can be examined live in the following Dojo demo: 

• http://dojo.telerik.com/adAcOYob

Let me know if you have any questions.

Regards,
Nikolay
Progress Telerik