How to do Excel filtering for the grouped columns when showGroupedColumn is false

1 min read

How to do Excel filtering for the grouped columns when showGroupedColumns is false.

This Knowledge Base explains the way of how to perform excel filtering for the grouped columns when showGroupedColumn is false.

HTML

                   <div id="Grid"></div>

<script type="text/javascript">
        $(function () {
            $("#Grid").ejGrid({
                // the datasource "window.gridData" is referred from jsondata.min.js
                dataSource: window.gridData,
                allowPaging: true,
                allowGrouping: true,
                 allowFiltering: true,
                filterSettings: { filterType: 'excel'},
                groupSettings: {
                    showGroupedColumn: false,
                    groupedColumns: ["ShipCountry", "ShipCity"]
                },
                columns: [
                         { field: "OrderID", headerText: "Order ID",isPrimaryKey: true, textAlign: ej.TextAlign.Right, width: 65},
                         { field: "CustomerID", headerText: "Customer ID", width: 90 },
                         { field: "ShipCity", headerText: "Ship City", width: 90 },
                         { field: "ShipCountry", headerText: "Ship Country", width: 90 },
                         { field: "EmployeeID", headerText: "Employee ID", width: 90 },
                         ],
               actionComplete: "actioncomplete"
            });
        });
 </script>

Razor

@(Html.EJ().Grid<MVCSampleBrowser.Models.EditableOrder>("Grid")
        .Datasource((IEnumerable<object>)ViewBag.datasource)
        .AllowGrouping().GroupSettings(group => { group.GroupedColumns(col => { col.Add("ShipCountry"); col.Add("ShipCity"); }).ShowGroupedColumn(false); })
        .AllowPaging()
        .AllowFiltering().FilterSettings(filter => filter.FilterType(FilterType.Excel))
        .Columns(col =>
            {
                col.Field("OrderID").HeaderText("OrderID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(65).Add();
                col.Field("CustomerID").HeaderText("Customer ID").Width(90).Add();
                col.Field("ShipCity").HeaderText("Ship City").Width(90).Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Width(90).Add();
                col.Field("EmployeeID").HeaderText("EmployeeID").Width(90).Add();
            })
        .ClientSideEvents(eve => { eve.ActionComplete("actioncomplete"); })
)

namespace Sample.Controllers
{
    public class GridController : Controller
    {
      public ActionResult GridFeatures()
        {
            var DataSource = new NorthwindDataContext().OrdersViews.ToList();
            ViewBag.datasource = DataSource;
            return View();
        }
    }
}

ASPX

<ej:Grid ID="Grid" runat="server" AllowPaging="True" AllowGrouping="true" AllowFiltering="true">
              <GroupSettings GroupedColumns="ShipCountry,ShipCity" ShowGroupedColumn="false" />
              <FilterSettings FilterType="Excel"></FilterSettings>
              <Columns>
                   <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="true" Width="65"></ej:Column>
                   <ej:Column Field="CustomerID" HeaderText="CustomerID" TextAlign="Left" Width="90" />
                   <ej:Column Field="ShipCity" HeaderText="ShipCity" TextAlign="Left" Width="90" />
                   <ej:Column Field="ShipCountry" HeaderText="ShipCountry" Width="90"></ej:Column>
                   <ej:Column Field="EmployeeID" HeaderText="EmployeeID" Width="90"></ej:Column>
              </Columns>
              <ClientSideEvents ActionComplete="actioncomplete" />
           </ej:Grid>

We can achieve this by rendering the filter icon in the Using actionComplete event in grid, render the excel filter icon for groupgrouped columns in group header cell when showGroupedColumns is false using actionComplete event of grid.

When clicking the filter icon for the grouped columns in the group drop area, the corresponding column Excel filter dialog gets rendered opened and we can perform the filtering operations. Through this we can perform filtering action after grouped the particular which is done as like usual Excel filtering.

This filtering process is done for the grouped column when showGroupedColumn is false.

<script type="text/javascript">
     var filtercolumn, target, columnName;        var filtercolumn;
        var target;
        var columnName;
 
        function actioncomplete(args) {          //actionComplete event in grid
            var grid = $("#Grid").ejGrid("instance");
            var flag = false;
            if ((args.requestType == "grouping" || args.requestType == "ungrouping") && this.model.groupSettings.groupedColumns.length && this.model.groupSettings.showGroupedColumn == false) {
                $(".e-groupheadercell").find(".e-ascending").hide();
                if ((this.model.allowFiltering)) {
                                          //Rendering the filter icon in groupheadercell
                    filtercolumn = $('<div class="e-filtericon e-icon e-filterset"/>').appendTo(this.element.find(".e-groupheadercell:not(:has(.e-filterset))")).css({ 'margin-top': '-16px', 'margin-left': '-17px', 'margin-bottom': '-1px', 'margin-right': '94px' })
                    $('.e-filtericon').click(function (e) {         
 
                                   //Rendering the excel filter dialog when clicking the filter icon
 
                        var grid = $("#Grid").ejGrid("instance");
                        var docWidth = $(document).width();
                        var dlgWidth = document.documentElement.clientWidth < 800 ? 200 : 250;
                        var xPos = $(e.target).position().left + 18
                        var yPos = $(e.target).position().top + 2;
                        if ($(e.target).offset().left + 18 + dlgWidth > docWidth)
                            xPos = xPos - dlgWidth;
                        if ((grid.model.filterSettings.filterType == "excel") && grid.model.groupSettings.showGroupedColumn == false) {
                            target = $(e.target);
                            var column = grid.getColumnByField($(e.target).parent().find("div").attr("ej-mappingname"));
                            var options = { field: column.field, displayName: column.headertext, dataSource: grid._dataSource(), position: { X: xPos, Y: yPos }, type: column.type };
                            grid._excelFilter.openXFDialog(options);           //Open the dialog by passing the type, field, dataSource and position as a parameter
                        }
                    })
                }
            }
            if (args.requestType == "filtering") {
                var currentfieldname = target.parent().find("div").attr("ej-mappingname");
                var column = this.getColumnByField(target.parent().find("div").attr("ej-mappingname"));
                var index = $.inArray(this.getColumnByField(target.prev().attr("ej-mappingname")), this.model.columns);
                flag = false;
                $.each(this.model.filterSettings.filteredColumns, function (indx, col) {
                    if (col.field == currentfieldname) {
                        flag = true;
                        return false;
                    }
                    else if (col.isComplex == true) {
                        if (col.predicates[0].field == currentfieldname) {
                            flag = true;
                            return false;
                        }
                    }
                });
                var icon = target;
                if (flag)
                    icon.addClass("e-filteredicon e-filternone");     //changing the filter icon when the column gets filtered
                else
                    icon.removeClass("e-filteredicon e-filternone");   //changing the filter icon when the column is not filtered
 
            }
        }
       
    </script>

$C:\Users\Manisankar.durai\Desktop\sshot-1.png$