We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Export Excel from a Dynamic Grid

Thread ID:

Created:

Updated:

Platform:

Replies:

147327 Sep 5,2019 11:59 AM UTC Sep 13,2019 05:36 AM UTC ASP.NET MVC 6
loading
Tags: Grid
Cholet
Asked On September 5, 2019 11:59 AM UTC

Hello,
I have a view with a Grid, that grid is filled dynamically and the columns depends on some options in my view.
Everything work well, but when I want to export in excel I have some issues.

I create my grid dynamically in my view like this :

function Statistiques_Edit() {
    var idFonction = obj_Variables[0].VARIABLE.IDFONCTION;
    var parametre = [];
    var i = 0;
    var cols = [];
    var ligne;
    var lignes = [];
    var myNode = document.getElementById("divFicheStatistiquesEditionsVariables");
    document.getElementById("statistiquesEditionsResultGrid").style.display = "block";
    for (var index in obj_Variables) {
        var idvariable = obj_Variables[index].VARIABLE.IDVARIABLE;
        parametre.push(obj_Variables[index].VARIABLE.CODE + ":" + document.getElementById("StatistiquesEditions_variable" + idvariable).value);
    }

    $.ajax({
        type: 'POST',
        contentType: 'application/json',
        url: url_executerFonction,
        data: JSON.stringify({ idFonction: idFonction, parametre: parametre }),
        enableCaching: true,
        cachingPageSize: 10,
        timeTillExpiration: 120000,
        complete: function () {                  
            $("#statistiquesEditionsResultGrid").ejGrid({
                dataSource: lignes,
                columns: cols
            }); 
            $('#statistiquesEditionsResultGrid').ejGrid('instance').ignoreOnExport.splice($('#statistiquesEditionsResultGrid').ejGrid('instance').ignoreOnExport.indexOf('dataSource'), 1);
        },
        success: function (data) {
            console.log(data);      
            lignes = [];
            data.COLUMNS.forEach(function (element) {
                cols.push({ field: element,width:200 }); 
                //$("#statistiquesEditionsResultGrid").ejGrid("columns", element, "add");
            });   
           
            data.ROWS.forEach(function (element) {    
                ligne = {};
                    i = 0;              
                    element.ROW.forEach(function (elmnt) {   
                        ligne[data.COLUMNS[i]] = elmnt;
                        i = i + 1;
                    });     
                    lignes.push(ligne);
            });                 
                        
        },
        fail: function (data) { }
    });
}

In my controller I'v try somethiong like this :

public void ExcelExport(string GridModel)
        {
            GridProperties gridProperty = ConvertGridObject(GridModel);            
            ExcelExport exp = new ExcelExport();                      
            List<Object> collection = new List<Object>((IEnumerable<Object>)gridProperty.DataSource);

            dynamic expando = new ExpandoObject();
            var dataGrid = (IDictionary<string, object>)expando;

            List<dynamic> dynamicObject = new List<dynamic>();

            foreach (Object ligne in collection)
            {
                var resultat = ((IEnumerable)ligne).Cast<object>().ToList();
                foreach (var cellule in resultat)
                {
                    string cle = cellule.GetType().GetProperty("Key").GetValue(cellule).ToString();
                    string valeur = cellule.GetType().GetProperty("Value").GetValue(cellule).ToString();
                    dataGrid.Add(cle, valeur);                    
                }
                dynamicObject.Add(dataGrid.Cast<object>().ToList());
                expando = new ExpandoObject();
                dataGrid = (IDictionary<string, object>)expando;
            }
           
            System.Collections.IEnumerable Data = dynamicObject.Cast<object>().ToList();           
            exp.Export(obj, Data, "ExportStatistique.xlsx", Syncfusion.XlsIO.ExcelVersion.Excel2007, false, true, ExportTheme.FlatAzure);
        }

But it seems that the data IEnumerable are not in the good format in the exp.Export function.

Could you help me.

(Sorry for my poor english)

Best regards.




Padmavathy Kamalanathan [Syncfusion]
Replied On September 8, 2019 05:52 AM UTC

Hi Cholet, 

Thanks for contacting Syncfusion Forums. 

QUERY: Export Excel from a dynamic Grid 
 
You have reported data are not in good format in export function.  Please let us know what do you mean by that. We have created sample and checked. We are unable to reproduce the issue. In the below sample we have created dynamic grid on button click with expand object data. 

Please refer the below code, 

 
@Html.EJ().Button("GridButton").Text("RenderGrid").ClientSideEvents(eve => eve.Click("GridUpdateHandler")) 
 
<div id="Grid"></div> 
<script> 
    function GridUpdateHandler(args) { 
        $.ajax({ 
            url: '@Url.Action("CustomGridpartialView", "Grid")', 
            type: 'GET', 
            success: function (data) { 
            var data1 = ej.parseJSON(data); 
            $("#Grid").ejGrid({ 
 
            columns: [ 
            { field: "OrderID", headerText: "Order ID" }, 
            { field: "CustomerID", headerText: "CustomerID", } 
            ], 
            dataSource: data1, 
            dataBound: function (args) { 
            this.ignoreOnExport.splice(this.ignoreOnExport.indexOf('dataSource'), 1); 
            }, 
            toolbarClick: function (args) { 
              this.exportGrid = this["export"]; 
              if (args.itemName == "Excel Export") { 
                 this.exportGrid('/Grid/ExportToExcel') 
                 args.cancel = true; 
               } 
            }, 
            }); 
            }, 
            }); 
 
            }             
</script> 
 
In server side 
 
       public static List<ExpandoObject> order = new List<ExpandoObject>(); 
        public ActionResult GridFeatures() 
        { 
            if (order.Count() == 0) 
                BindDataSource(); 
            return View(); 
        } 
        public ActionResult CustomGridpartialView() 
        { 
            var data = order; 
            return Json(data, JsonRequestBehavior.AllowGet); 
        } 
 
 
        public void BindDataSource() 
        { 
            int code = 10000; 
            for (int i = 1; i < 10; i++) 
            { 
                order.Add(new ExpandoObject(code + 1, ""); 
                order.Add(new ExpandoObject(code + 2, "ANATR"); 
                order.Add(new ExpandoObject(code + 3, "ANTON"); 
                order.Add(new ExpandoObject(code + 4, "BLONP"); 
                order.Add(new ExpandoObject(code + 5, "BOLID"); 
                code += 5; 
            } 
        } 
 
        public class ExpandoObject 
        { 
            public ExpandoObject() 
            { 
 
            } 
            public ExpandoObject(int OrderID, string CustomerId,) 
            { 
                this.OrderID = OrderID; 
                this.CustomerID = CustomerId; 
            } 
 
            public int? OrderID { get; set; } 
            public string CustomerID { get; set; } 
        } 
 
        public void ExportToExcel(string GridModel) 
        { 
            ExcelExport exp = new ExcelExport(); 
            GridProperties gridProperty = ConvertGridObject(GridModel); 
            List<Object> collection = new List<Object>((IEnumerable<Object>)gridProperty.DataSource); 
            exp.Export(gridProperty, collection, "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> ds in div) 
            { 
                if (ds.Key == "dataSource") 
                { 
                    string serialize = serializer.Serialize(ds.Value); 
                    gridProp.DataSource = serializer.Deserialize<List<ExpandoObject>>(serialize); 
                } 
                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; 
        } 



Also Please share us below details, 

  1. Whether you are able to export or not
  2. Is the exported file is empty?
  3. Screenshots of error you are getting when you try to export (if any)
  4. Complete grid rendering code.
  5. share us the gridModel (refer below image) from the ExportToExcel as word file by following the below steps,
 
   
 
   
 
click the Text Visualizer and copy the whole content in Value, paste it in word file and share us.  
 
If you have further queries, please get back to us. 

Regards, 
Padmavathy Kamalanathan 



Cholet
Replied On September 9, 2019 11:59 AM UTC

Hello,
Thanks for your reply, but in your exemple you know that there is two fields in your grid OrderID and CustomerID :

public class ExpandoObject
        {
            public ExpandoObject()
            {

            }
            public ExpandoObject(int OrderID, string CustomerId)
            {
                this.OrderID = OrderID;
                this.CustomerID = CustomerId;
            }

            public int? OrderID { get; set; }
            public string CustomerID { get; set; }

        }

In my case, I don't know in advance the name of my columns, because my grid is dynamic in my view , so I have to build my ExpandoObject and I don't know how to do that, I've try something like this :

List<object> collection = new List<object>((IEnumerable<object>)gridProperty.DataSource);

 foreach (Object ligne in collection)
            {
                var resultat = ((IEnumerable)ligne).Cast<object>().ToList();
                foreach (var cellule in resultat)
                {
                    string cle = cellule.GetType().GetProperty("Key").GetValue(cellule).ToString();
                    string valeur = cellule.GetType().GetProperty("Value").GetValue(cellule).ToString();
                    dataGrid.Add(cle, valeur);
                }
                dynamicObject.Add(dataGrid.Cast<object>().ToList());
                expando = new ExpandoObject();
                dataGrid = (IDictionary<string, object="">)expando;
            }

            System.Collections.IEnumerable Data = dynamicObject.Cast<object>().ToList();

But it doesnt' work, could you show me a good way to do that ?

I'v got this error :

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error: 

Line 128:            //Génération de excel
Line 129:            GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel);
Line 130:            exp.Export(obj, Data, "ExportStatistique.xlsx", Syncfusion.XlsIO.ExcelVersion.Excel2007, false, true, ExportTheme.FlatAzure);
Line 131:        }
Line 132:

Thx



Cholet
Replied On September 9, 2019 12:19 PM UTC

My grid model :

Attachment: gridModel_52f26078.zip

Padmavathy Kamalanathan [Syncfusion]
Replied On September 10, 2019 01:02 PM UTC

Hi Cholet, 
 
Thanks for your update. 
 
QUERY: I don't know in advance the name of my columns, because my grid is dynamic in my view 
 
From your query we understand that your grid has been rendered dynamically and you don’t know the column names prior to rendering your grid. Since the columns are unknown you are unable to deserialize it in server side when you try to export the grid. 
 
In order to export dynamic list we suggest you to follow the below Knowledge Base documentation, 
 
Also the error you have mentioned occurs when the columns bound to the Grid are not defined in the Grid dataSource. We have already discussed the same issue in following knowledge base doucument.  
 
If you have further queries, please get back to us. 
 
Regards, 
Padmavathy Kamalanathan 


Cholet
Replied On September 12, 2019 02:13 PM UTC


Padmavathy Kamalanathan [Syncfusion]
Replied On September 13, 2019 05:36 AM UTC

Hi Cholet, 

We are happy to hear that your issue has been resolved. 

Feel free to get back to us if you need further assistance. 

Regards, 
Padmavathy Kamalanathan 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon