Export Excel from a Dynamic Grid

[Cholet]

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]

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]

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]

My grid model :

Attachment: gridModel_52f26078.zip

[Padmavathy Kamalanathan]

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, 

https://www.syncfusion.com/kb/7620/how-to-export-dynamic-list-using-typebuilder-in-grid 

 

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.  

https://www.syncfusion.com/kb/3018/null-exception-while-exporting 

 

If you have further queries, please get back to us. 

 

Regards, 

Padmavathy Kamalanathan 

[Cholet]

It's work fine with this link https://www.syncfusion.com/kb/7620/how-to-export-dynamic-list-using-typebuilder-in-grid 

Thx.

[Padmavathy Kamalanathan]

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