Export GridPanel (with Paging and filters) Data to Excel

  1. #1

    Export GridPanel (with Paging and filters) Data to Excel

    Hi,

    I have a gridpanel with paging and filters. Whenever I try and export a file, it only includes the current page from the gridpanel on the export file. I need all filtered data from the GridPanel. How can I do this?


     @(Html.X().GridPanel()
            .ID("GridPanel1")
            .Title("Records")
            .Width(1100)
            .Height(350)
            .Store(Html.X().Store()
                .RemoteSort(true)
                .PageSize(20)
                .Proxy(
                    Html.X().AjaxProxy()
                    .Url(Url.Action("ReadData"))
                    .ActionMethods(actions =>
                        {
                        actions.Read = HttpMethod.POST;
                        }) 
                    .Reader(
                        Html.X().JsonReader().Root("data")
                    )
                )            
                .ID("Store1")
                .Model(Html.X().Model()
                    .IDProperty("id")
                    .Fields(
                        new ModelField("Name", ModelFieldType.String),
                        new ModelField("DateString", ModelFieldType.Date, "MM/dd/yyyy"),
                        new ModelField("Location", ModelFieldType.String),
                        new ModelField("Code", ModelFieldType.String),
                        new ModelField("Amount", ModelFieldType.Float),
                        new ModelField("Status", ModelFieldType.String),
                        new ModelField("Email", ModelFieldType.String),
                        new ModelField("TimeStampString", ModelFieldType.Date, "MM/dd/yyyy HH:mm tt"),
                        new ModelField("LastUpdated", ModelFieldType.Date, "MM/dd/yyyy HH:mm tt"),
                        new ModelField("PaymentRecord", ModelFieldType.String)
                    )
                ) 
                .Sorters(
                    Html.X().DataSorter().Property("Name").Direction(Ext.Net.SortDirection.ASC)
                ) 
            )
            .ColumnModel(
                Html.X().Column().Text("Event Name").DataIndex("Name").Renderer("NameRenderer").Width(145),
                Html.X().DateColumn().Text("Date").DataIndex("DateString").Align(Alignment.Center).Format("MM/dd/yyyy").Width(135),
                Html.X().Column().Text("Location").DataIndex("Location").Width(140),
                Html.X().Column().Text("Code").DataIndex("Code").Width(130),
                Html.X().Column().Text("Amount").DataIndex("Amount").Renderer("Amount").Width(130),
                Html.X().Column().Text("Status").DataIndex("Status").Width(124),
                Html.X().Column().Text("Contact").DataIndex("Email").Width(135),
                Html.X().DateColumn().Text("Created").DataIndex("TimeStampString").Align(Alignment.Center).Format("MM/dd/yyyy HH:mm").Width(135),
                Html.X().DateColumn().Text("Last Updated").DataIndex("LastUpdated").Align(Alignment.Center).Format("MM/dd/yyyy HH:mm").Width(135)
             )
             .Features(
                 Html.X().GridFilters()
                 .Filters(
                     Html.X().StringFilter().DataIndex("Name"),
                     Html.X().DateFilter().DataIndex("DateString")
                         .DatePickerOptions(options =>
                         {
                            options.TodayText = "Now";
                         }),
                     Html.X().StringFilter().DataIndex("Location"),
                     Html.X().StringFilter().DataIndex("Code"),
                     Html.X().StringFilter().DataIndex("Amount"),
                     Html.X().ListFilter().DataIndex("Status").Options("Paid, Cancelled"),
                     Html.X().StringFilter().DataIndex("Email"),               
                     Html.X().DateFilter().DataIndex("TimeStampString")
                         .DatePickerOptions(options =>
                         {
                            options.TodayText = "Now";
                         }),
                     Html.X().DateFilter().DataIndex("LastUpdated")
                         .DatePickerOptions(options =>
                         {
                            options.TodayText = "Now";
                         }),
                    Html.X().StringFilter().DataIndex("PaymentRecord")
                 )
             )
             .TopBar(
                 Html.X().Toolbar().Items(
                     Html.X().ToolbarFill(),
                     Html.X().Button().ID("btnXML").Text("To XML").Icon(Icon.PageCode)
                     .Listeners(btn => btn.Click.Fn = "submitDataXML"),
                     Html.X().Button().ID("btnExcel").Text("To Excel").Icon(Icon.PageExcel)
                     .Listeners(btn => btn.Click.Fn = "submitDataExcel"),
                     Html.X().Button().ID("btnCSV").Text("To CSV").Icon(Icon.PageAttach)
                     .Listeners(btn => btn.Click.Fn = "submitDataCSV")
                 )
             )
             .BottomBar(
                Html.X().PagingToolbar()
             )
    Here is the Javascript code
        var submitDataExcel = function (evt, t, o) {
            var url = '@Url.Action("ToExcel")';
            submitData(url);
        };
    
        var submitData = function (url) {
            var dataToSubmit = Ext.encode(App.GridPanel1.getRowsValues({ selectedOnly: false }));
    
            Ext.net.DirectMethod.request({
                url: url,
                cleanRequest: true,
                isUpload: true,
                params: {
                    json: dataToSubmit
                }
            });
        };
    Here is the controller:
     public ActionResult ReadData(StoreRequestParameters parameters)
    {
    
                List<object> data = GridFiltersModel.Data;
    
                FilterConditions fc = parameters.GridFilters;
    
                //-- start filtering ------------------------------------------------------------
                if (fc != null)
                {
                    foreach (FilterCondition condition in fc.Conditions)
                    {
                        Comparison comparison = condition.Comparison;
                        string field = condition.Field;
                        FilterType type = condition.Type;
                        
                        object value;
                        switch (condition.Type)
                        {
                            case FilterType.Boolean:
                                value = condition.Value<bool>();
                                break;
                            case FilterType.Date:
                                value = condition.Value<DateTime>();
                                break;
                            case FilterType.List:
                                value = condition.List;
                                break;
                            case FilterType.Numeric:
                                if (data.Count > 0 && data[0].GetType().GetProperty(field).PropertyType == typeof(int))
                                {
                                    value = condition.Value<int>();
                                }
                                else
                                {
                                    value = condition.Value<double>();
                                }
    
                                break;
                            case FilterType.String:
                                value = condition.Value<string>();
                                break;
                            default:
                                throw new ArgumentOutOfRangeException();
                        }
                       
                        data.RemoveAll(
                            item =>
                            {
                                object oValue = item.GetType().GetProperty(field).GetValue(item, null);
                                IComparable cItem = oValue as IComparable;
    
                                DateTime date1 = new DateTime();
                              
                                if (condition.Field.Contains("Date"))
                                {
                                    if (cItem.ToString().Count() < 1)
                                    {
                                         date1 = condition.Value<DateTime>();
                                    }
                                    else if (condition.Field.Contains("EventDateString"))
                                    {
                                        date1 = DateTime.Parse(cItem.ToString());
                                    }
                                    else
                                    {
                                        date1 = DateTime.ParseExact(cItem.ToString(), "MM/dd/yyyy HH:mm tt", null);
                                    }
                                }                         
                                
                                switch (comparison)
                                {
                                    case Comparison.Eq:
    
                                        switch (type)
                                        {
                                            case FilterType.List:
                                                return !(value as List<string>).Contains(oValue.ToString());
                                            case FilterType.String:
                                                return !oValue.ToString().StartsWith(value.ToString());
                                            default:
                                                return !cItem.Equals(value);
                                        }
    
                                    case Comparison.Gt:
                                        return date1.CompareTo(value) < 1;                                   
                                       
                                    case Comparison.Lt:
                                            return date1.CompareTo(value) > -1;   
                                    default:
                                        throw new ArgumentOutOfRangeException();
                                }
                            }
                        );
                    }
                }
                //-- end filtering ------------------------------------------------------------
    
               
                //-- start sorting ------------------------------------------------------------
                
              
                if (parameters.Sort.Length > 0)
                {
                    DataSorter sorter = parameters.Sort[0];
                    data.Sort(delegate(SalesPaymentRecord x, SalesPaymentRecord y)
                    {
                        object a;
                        object b;
    
                        int direction = sorter.Direction == Ext.Net.SortDirection.DESC ? -1 : 1;
    
                        a = x.GetType().GetProperty(sorter.Property).GetValue(x, null);
                        b = y.GetType().GetProperty(sorter.Property).GetValue(y, null);
                        return CaseInsensitiveComparer.Default.Compare(a, b) * direction;
                    });
                }
                //-- end sorting ------------------------------------------------------------
    
    
                //-- start paging ------------------------------------------------------------
                int limit = parameters.Limit;
    
                if ((parameters.Start + parameters.Limit) > data.Count)
                {
                    limit = data.Count - parameters.Start;
                }
    
                List<SalesPaymentRecord> rangeData = (parameters.Start < 0 || limit < 0) ? data : data.GetRange(parameters.Start, limit);
                //-- end paging ------------------------------------------------------------
    
                return this.Store(rangeData, data.Count);
            }
    
    public ActionResult ToExcel(string json)
    {
               
               StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
               XmlNode xml = eSubmit.Xml;
    
                XslCompiledTransform xtExcel = new XslCompiledTransform();
    
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                xtExcel.Load(Server.MapPath("/Excel.xsl"));
                xtExcel.Transform(xml, null, ms);
                return File(ms.GetBuffer(), "application/vnd.ms-excel", "excelfile.xls");
            }
    I appreciate the help. Thanks in advance!
  2. #2

    Has this been solved for you.

    I'm trying to get the data out of a filter grid panel in ext.net mvc. I can't figure it out.

Similar Threads

  1. Replies: 0
    Last Post: Feb 12, 2014, 12:19 PM
  2. [CLOSED] Export data to excel
    By immenso in forum 2.x Legacy Premium Help
    Replies: 4
    Last Post: Dec 16, 2013, 4:42 AM
  3. How can Export data from GridPanel to Excel
    By delta in forum 2.x Help
    Replies: 2
    Last Post: Nov 14, 2012, 1:07 AM
  4. [CLOSED] Export Data To Excel in Remote Paging GridPanel
    By pdcase in forum 1.x Legacy Premium Help
    Replies: 3
    Last Post: Oct 11, 2010, 8:49 PM
  5. [CLOSED] Export all data from gridpanel if paging
    By Sharon in forum 1.x Legacy Premium Help
    Replies: 2
    Last Post: Jul 28, 2009, 10:01 AM

Tags for this Thread

Posting Permissions