1. Home
  2. Forum
  3. ASP.NET MVC
  4. Excel export only filtered and/or sorted data of the grid

Excel export only filtered and/or sorted data of the grid

I'm trying to export data from the grid....both filtered and not filtered.
My controller and view files are in the attachment. First, when I try to export without filter I got this:



and when I filter the grid, the message is:



So, I't simple...I load the grid with some data (filter it in the grid) and I want to export it, without loading the data again.
Is there some example like that?

I'm using



Thanks!
B.

Attachment: B_acd36e3f.zip

7 Replies
BJ Bernard Jurlina April 25, 2019 09:46 PM UTC

I left only two columns in the grid and in the datasource and exporting is working when I don't set any filter in the grid.

But, if I set it like this



The error is



I'm using OnToolbarClick function to get the filtered data:



And to export it in the controller



Any idea?
Thanks!

Bernard.

VN Vignesh Natarajan Syncfusion Team April 26, 2019 02:04 PM UTC

Hi Bernard, 
 
Thanks for contacting Syncfusion Support. 
 
Issue:-1 #Without Filter:- System.IndexOutofRangeException:- 
 
From your screenshot we understand that you are facing issue while exporting the Grid without applying filter. We have prepared a sample as per your code example but we are unable to reproduce your reported problem at our end. Kindly refer the below the sample Link 
 
 
The reported issue may occur while defining the anchor tag in the format property of Column. We suggest you achieve your requirement using column template feature of ejGrid. You can also export the Grid with column template.    
Please refer to the documentation Link for template column and its exporting 
 
Column Template 
 
 
 
Column Template Exporting 
 
 
 
After following the above solution still facing the issue, please share us the following details. 
 
  1. StackTrace of the issue.
       2.    Stringified model of the Grid on server side while on Exporting as like below screenshot and copy it into clipboard. 
 
  1. If possible replicate the issue in the above sample and revert us back.
 
 
Issue#2:- With Filter:- System.Argument.NullException:- 
 
We are able to reproduce the reported problem at our end by preparing sample as per your screenshot. In your code example, you have handled filtered data separately on server end. By default Grid will be exported with filtered data when Grid is exported after filtering a column.So it is not necessary to handle the filtered data on server side externally. We suggest you to remove the filtered data externally(ignoreonExport). 
 
Please get back to us if you need further assistance. 
 
Regards, 
Vignesh Natarajan. 
 


BJ Bernard Jurlina April 28, 2019 02:46 PM UTC

Hi Vignesh!

I cannot get this IgnoreOnExport to work. Is there maybe a chance to make me some example with the grid which is bounded to the data with the ajax on button click and then to export the data to excel? Filtered and unfiltered?

Everything I try is not wotking to me. Where I have to put ignoreOnExport.splice if I'm setting the grid's datasource from ajax on button click?
In the grid I'm not using column templates...I have only two columns, int and string.

Thanks!
Bernard.

BJ Bernard Jurlina April 28, 2019 04:42 PM UTC

So, this is quite simple example.

Controller:
public ActionResult Index()
        {
            using (var db = new BilijonEntities())
            {
                userscontext = new ApplicationDbContext();
                UserId = System.Web.HttpContext.Current.User.Identity.GetUserId();

                var userStore = new UserStore<ApplicationUser>(userscontext);
                var userManager = new UserManager<ApplicationUser>(userStore);

                var evidencija = from e in db.Evidencija
                                 join u in db.AspNetUsers on e.DjelatId equals u.Id
                                 join p in db.Proizvod on e.ProizvodID equals p.ProizvodID
                                 join pg in db.ProizvodGrupa on p.GrupaID equals pg.GrupaID
                                 join st in db.Stranka on e.StrankaID equals st.StrankaID
                                 join s in db.AspNetUsers on e.SuradID equals s.Id into sur
                                 from surad in sur.DefaultIfEmpty()
                                 join pol in db.Polica on e.EvidID equals pol.EvidID into pol1
                                 from pol2 in pol1.DefaultIfEmpty()
                                 orderby e.Datum descending
                                 select new
                                 {
                                     e.EvidID,
                                     e.EvidBroj,
                                     e.Datum,
                                     Stranka = st.Prezime + ", " + st.Ime,
                                     Djelatnik = u.Prezime + " " + u.Ime,
                                     Suradnik = surad.Prezime + " " + surad.Ime,
                                     ProizvodNaziv = pg.Naziv + " - " + p.Naziv,
                                     UserId = u.Id,
                                     BrojPolice = pol2.BrojPolice
                                 };

                if (!userManager.IsInRole(UserId, "Administrator"))
                {
                    evidencija = evidencija.Where(e => e.UserId == UserId);
                }

                ViewBag.dsEvidencija = evidencija.ToList();
                return View();
            }
        }

[ValidateInput(false)]
        public void ExportToExcel(string GridModel)
        {
            ExcelExport exp = new ExcelExport();
            GridProperties obj = ConvertGridObject(GridModel);
            exp.Export(obj, (IEnumerable)obj.DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");
        }

        private GridProperties ConvertGridObject(string gridProperty)
        {
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable));
            GridProperties gridProp = new GridProperties();


            foreach (KeyValuePair<string, object> datasource in div)
            {
                var property = gridProp.GetType()
                    .GetProperty(datasource.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
                if (property != null)
                {
                    Type type = property.PropertyType;
                    string serialize = serializer.Serialize(datasource.Value);
                    object value = serializer.Deserialize(serialize, type);
                    property.SetValue(gridProp, value, null);
                }
            }
            return gridProp;
        }

View:

@(Html.EJ().Grid<object>("gridEvid")
            .Datasource((IEnumerable<object>)ViewBag.dsEvidencija)
            .AllowPaging()
            .ToolbarSettings(toolbar =>
            {
                toolbar.ShowToolbar().ToolbarItems(items =>
                {
                    items.AddTool(ToolBarItems.ExcelExport);
                    items.AddTool(ToolBarItems.Search);
                });

            })
            .EnableRowHover(false)
            .AllowSelection(false)
            .IsResponsive(true)
            .Locale("hr-HR")
            .AllowSorting(true)
            .AllowFiltering(true)
            .EnableTouch(true)
            .FilterSettings(filter => { filter.FilterType(FilterType.Excel); })
            .Columns(col =>
            {
                col.Field("EvidID").HeaderText("EvidID").IsPrimaryKey(true).Add();
                col.Field("EvidBroj").HeaderText("Broj").Width(55).Add();
                col.Field("UserId").HeaderText("UserId").Width(75).Priority(3).Add();
            }).ClientSideEvents(ev => ev.Load("load"))
)

function load(args) { 
        toastr.info("Učitavam...", "Grid");
        this.ignoreOnExport.splice(this.ignoreOnExport.indexOf('dataSource'), 1); 
    } 

But when I want to export



Any ideas how to solve this?
I tried to load only those three columns in the dsEvidencija but with the same error.

Thanks!
Bernard.


VN Vignesh Natarajan Syncfusion Team April 29, 2019 11:33 AM UTC

Hi Bernard, 

Thanks for the sharing the information. 

Query#1:-  Is there maybe a chance to make me some example with the grid which is bounded to the data with the ajax on button click and then to export the data to excel? Filtered and unfiltered? 

We have prepared sample as per your requirement such that we have bound the dataSource for Grid on AJAX post and export the data using Grid toolbar click. As per previous update, By default Grid will be exported with filtered data when Grid is exported after filtering a column. So it is not necessary to handle the filtered data on server side externally.  


Please refer to the code example:- 

<button type="button" onclick="Data()">BindDataSource</button> 
@(Html.EJ().Grid<object>("Grid") 
          .AllowPaging() 
           .Columns(col =>{ 
              col.Field("OrderID").HeaderText("EvidID").IsPrimaryKey(true).Width(55).Add(); 
              col.Field("CustomerID").HeaderText("Broj").Width(55).Add(); 
                  .      .    .                              
    }) 
 
) 
<script type="text/javascript"> 
   function Data() { 
        $.ajax({ 
            type: "GET", 
            url: "/Grid/Data", 
            contentType: "application/json; charset=utf-8", 
            dataType: "json", 
            success: function (data) { 
                var gridObj = $("#Grid").ejGrid("instance"); 
               gridObj.dataSource(data);//dataSource method 
 
            } 
        }); 
    } 
 
</script> 

Serverside:- 

           public object Data() 
        { 
            BindDataSource(); 
            IEnumerable Data = order.Take(10).ToList(); 
            return Json(Data, JsonRequestBehavior.AllowGet); 
        } 
        [ValidateInput(false)] 
        public void ExportToExcel(string GridModel) 
        { 
                BindDataSource(); 
                ExcelExport exp = new ExcelExport(); 
                var DataSource = order.ToList().Take(10); 
                GridProperties obj = ConvertGridObject(GridModel); 
                exp.Export(obj,DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "bootstrap-theme"); 
            } 
 
                .     .    . 
            private GridProperties ConvertGridObject(string gridProperty) 
            { 
                JavaScriptSerializer serializer = new JavaScriptSerializer(); 
                IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable)); 
                GridProperties gridProp = new GridProperties(); 
                foreach (KeyValuePair<string, object> ds in div) 
                { 
                var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase); 
                if (property != null) 
                { 
                    Type type = property.PropertyType; 
                    string serialize = serializer.Serialize(ds.Value); 
                    object value = serializer.Deserialize(serialize, type); 
                    property.SetValue(gridProp, value, null); 
                } 
              } 
            return gridProp; 
        } 


Query#2:- System.NullReferenceException:Object reference not set to an instance of an object 

We can reproduce the reported (above mentioned query) problem while using your code example. While using IgnoreOnExport method to splice dataSource, we need to handle the dataSource at server side. But in your code example, you doesn’t handle the dataSource at server end. For your convenience we have created sample to splice the grid dataSource property on ‘ignoreOnExport’ method then handle the grid model dataSource in server side. 

Refer to the sample Link 



Refer to the code example 

@(Html.EJ().Grid<object>("Grid") 
      .Datasource((IEnumerable<object>)ViewBag.datasource) 
       .Columns(col =>{ 
                                              col.Field("OrderID").HeaderText("EvidID").IsPrimaryKey(true).Width(55).Add(); 
                               
                              
}) 
 
) 
<script type="text/javascript"> 
    function load(args) { 
        this.ignoreOnExport.splice(this.ignoreOnExport.indexOf('dataSource'), 1);  
    } 
</script> 
 
    
Serverside:- 
 
  [ValidateInput(false)] 
        public void ExportToExcel(string GridModel) 
        { 
                BindDataSource(); 
                ExcelExport exp = new ExcelExport(); 
                var DataSource = order.ToList().Take(10); 
                GridProperties obj = ConvertGridObject(GridModel); 
                exp.Export(obj,(IEnumerable)obj.DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "bootstrap-theme"); 
            } 
 
            
            private GridProperties ConvertGridObject(string gridProperty) 
            { 
                JavaScriptSerializer serializer = new JavaScriptSerializer(); 
                IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable)); 
                GridProperties gridProp = new GridProperties(); 
                foreach (KeyValuePair<string, object> ds in div) 
                { 
                if (ds.Key == "dataSource") 
                { 
                    string serialize = serializer.Serialize(ds.Value); 
                    gridProp.DataSource = serializer.Deserialize<List<Orders>>(serialize);//here we need to deserialize the gridDatasource  
                } 
                else 
                { 
                    var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase); 
                    if (property != null) 
                    { 
                        Type type = property.PropertyType; 
                        string serialize = serializer.Serialize(ds.Value); 
                        object value = serializer.Deserialize(serialize, type); 
                        property.SetValue(gridProp, value, null); 
                    } 
                } 
            } 
            return gridProp; 
        } 
 
 
 


Please get back to us if you have further queries. 

Regards, 
Vignesh Natarajan. 

 


BJ Bernard Jurlina May 1, 2019 08:26 PM UTC

Excellent Vignesh,

thanks for the examples, you helped me a lot. The thing is that I had to create a model class for the grid data.
When I changed 
gridProp.DataSource = serializer.Deserialize<List<object>>(serialize) 
to
gridProp.DataSource = serializer.Deserialize<List<EvidencijaLista>>(serialize)
the export is working fine...and with filtered data too.

Thanks again!
Regards.
Bernard.

VN Vignesh Natarajan Syncfusion Team May 2, 2019 03:51 AM UTC

Hi Bernard,  
 
Thanks for the update.  
 
We are  glad to hear that your query has been resolved by our solution.  
 
Please get back to us if you have further queries.  
 
Regards, 
Vignesh Natarajan. 


Loader.
Up arrow icon