Export Excel from a Dynamic Grid

6 Replies
2 Participants

Created by
CH Cholet

Platform
ASP.NET MVC

Platform
ASP.NET MVC

Control
Grid

Created On
Sep 5, 2019 11:59 AM UTC

Last Activity On
Sep 13, 2019 05:36 AM UTC

Want to subscribe?
SIGN IN

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.

6 Replies

PK Padmavathy Kamalanathan Syncfusion Team 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"))

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,

Whether you are able to export or not
Is the exported file is empty?
Screenshots of error you are getting when you try to export (if any)
Complete grid rendering code.
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

CH Cholet 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

CH Cholet September 9, 2019 12:19 PM UTC

My grid model :

Attachment: gridModel_52f26078.zip

PK Padmavathy Kamalanathan Syncfusion Team 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,

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

CH Cholet September 12, 2019 02:13 PM UTC

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

Thx.

PK Padmavathy Kamalanathan Syncfusion Team 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,