How to get filtered value when exporting excel

2 posts, 0 answers
  1. Adil
    Adil avatar
    26 posts
    Member since:
    Mar 2014

    Posted 28 May 2020 Link to this post

    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

  2. Nikolay
    Admin
    Nikolay  avatar
    328 posts

    Posted 01 Jun 2020 Link to this post

    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.
Back to Top