This is a migrated thread and some comments may be shown as answers.
How to get filtered value when exporting excel
1 Answer 6 Views
This is a migrated thread and some comments may be shown as answers.
Adil
Top achievements
Rank 1
Veteran
Adil asked on 28 May 2020, 09:19 AM

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

1 Answer, 1 is accepted

Sort by
0
Nikolay
Telerik team
answered on 01 Jun 2020, 08:45 AM

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: 

Let me know if you have any questions.

Regards,
Nikolay
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.
Tags
Grid
Asked by
Adil
Top achievements
Rank 1
Veteran
Answers by
Nikolay
Telerik team
Share this question
or