Export to excel filtered data

[Rob ONeill]

Hi,

When using the Grid with angularjs, how do you pass filtered data across to the server. All the examples I see show the data being created on the server side. None of the examples show the data being passed from the grid to the server?

Cheers ... Rob. 

[Rob ONeill]

Hi,

The reason I need the data passed from Grid to Server is that at the server end we have no idea of what grid is being exported, as we send multiple amounts of data down to the client to show in a dashboard, the user can click on a dashboard item which populates the grid, and then we want to be able to export that data. Does the public export() method allow parameters to be sent through to the web api ?

Cheers ... Rob.

[Ragavee U S]

Hi Rob,

We have discussed this topic in our Knowledge Base. Please refer the below Knowledge base link for more information.

Knowledge Base link: http://www.syncfusion.com/kb/5441/how-to-pass-additional-parameter-to-server-while-exporting

In the above link, we have discussed about passing additional parameters to the server side with an example of passing records count value.

Similarly, we can obtain the filtered records using the getFilteredRecords() method and can store it to a variable in gridModel. The filteredRecords thus obtained at the server side can be processed and be passed to the Export method of the grid rather than passing the actual dataSource. Please refer the below code example.

@(Html.EJ().Grid<object>("FlatGrid")
        .Datasource((IEnumerable<object>)ViewBag.datasource)      
        
.ClientSideEvents(eve=>eve.ActionComplete("complete"))

)

<script type="text/javascript">
   
    function complete(args) {
        if (args.requestType == "filtering")
            args.model.filteredRecords = $("#FlatGrid").ejGrid("getFilteredRecords");   // getting the filtered records and storing it in grid model        
    }

</script>

[CS]
private GridProperties ConvertGridObject(string gridProperty)
        {
            . . . .
            foreach (KeyValuePair<string, object> ds in div)
            {
                if (ds.Key == "filteredRecords")
                {
                    string serialize = serializer.Serialize(ds.Value);
                    List<OrdersView> value = serializer.Deserialize<List<OrdersView>>(serialize);//serializing the filteredRecords stored in the gridmodel
                    gridProp.DataSource = value ;//assigning the serialized data to the gridmodel dataSource
                }
                . . . . .
           }
            return gridProp;
        }
public void ExcelExport(string GridModel)
        {
           
            GridProperties gridPropert = ConvertGridObject(GridModel);

            ExcelExport exp = new ExcelExport();

            exp.Export(gridPropert, (IEnumerable)gridPropert.DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-azure-dark");//passing the filtered data in the export method

        }

Regards,
Ragavee U S.

[Rob ONeill]

Thank you for all the assistance, I now have the client to server working great.

But there is now a problem on the actual Export() method. We are getting an exception : 'dateTime' is not a member of type 'System.Collections.Generic.Dictionary`2[System.String,System.Object]'

Our grid we have created is a dynamically created grid, so we don't know what (or how many) columns are going to be generated. The datasource we send down to the client grid is an IEnumerable<Dictionary<string, object>>.

When returning with the GridModel for export we deserialize this back in the ienumerable dictionary as so.

var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
if (property != null)
{
    Type type = property.PropertyType;
    if (ds.Key == "dataSource")
    {
        var dataSource = serializer.Deserialize<IEnumerable<Dictionary<string, object>>>(serialize);
        gridProp.DataSource = dataSource;
        continue;
    }
    object value = serializer.Deserialize(serialize, type);
    property.SetValue(gridProp, value, null);
}

But when we try to Export the data we get the error message.

Any thoughts on what we are doing wrong ?

Cheers ... Rob.

[Rob ONeill]

Hi,

I have tried now to create a dynamic object (ExpandoObject) so that the column field names should be actual properties of the DataSource

                    if (ds.Key == "dataSource")
                    {
                        dynamic dynamicDataSource = new List<dynamic>();
                        var dataSource = serializer.Deserialize<IEnumerable<Dictionary<string, object>>>(serialize);
                        foreach (var dictionary in dataSource)
                        {
                            dynamic rowObject = new ExpandoObject();
                            foreach (var keyValuePair in dictionary)
                            {
                                ((IDictionary<String, Object>)rowObject).Add(keyValuePair.Key, keyValuePair.Value);
                            }
                            dynamicDataSource.Add((dynamic)rowObject);
                        }
                        gridProp.DataSource = dynamicDataSource;
                    continue;
                    }

and then in the Export

            ExcelExport exp = new ExcelExport();
            exp.Export(gridProperty, (dynamic)gridProperty.DataSource, excelTitle + ".xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");

But still get the error : 'dateTime' is not a member of type 'System.Dynamic.ExpandoObject'

Cheers ... Rob.

[Rob ONeill]

Hi,

To clarify each dynamic row (dynamic rowObject = new ExpandoObject();) would have members like this

rowObject.dateTime = "2015-10-27T08:00:00"

rowObject.instance = "CPU Network"

rowObject.metric1 = 25.3

rowObject.metric2 = 34.5

And in our GridProperties.Columns there are 4 columns with corresponding Fields, "dateTime", "instance", "metric1", "metric2"

Then in the Export() method we get the error :   'dateTime' is not a member of type 'System.Dynamic.ExpandoObject'

The reason we have a dynamic object is that we don't know how many metrics there can be, as these are selected by the user.

Cheers ... Rob.

[Ragavee U S]

Hi Rob,

We have analyzed your requirement to export grid with dynamic dataSource.

We are sorry to let you know that we don’t have support to export Grid with dynamic dataSource. But we can achieve your requirement using TypeBuilder. Please refer to the below link for more information.

http://stackoverflow.com/questions/18467235/creating-dynamic-type-from-typebuilder-with-a-base-class-and-additional-fields-g

For your convenience, we have created a sample which can be downloaded from the below location.

Sample Link: http://www.syncfusion.com/downloads/support/forum/120915/ze/Dynamic_Type_Export726378867847370221

In the above sample, we have obtained the dynamic table type by using the GetTableType method and have created its instance as Array of objects then serialized the data. Please refer to the below code example.

public void ExcelExport()

{

string gridModel = HttpContext.Current.Request.Params["GridModel"];

JavaScriptSerializer serializer = new JavaScriptSerializer();

GridProperties gridPropert = ConvertGridObject(gridModel); // Get the grid properties

Type test = GetTableType(gridPropert); // Get the dynamic table type

var inst = Array.CreateInstance(test, 1); // Create instance of Array to the dynamic table type

var data = serializer.Deserialize(this.ExportData, inst.GetType()) as object[]; // serialize the export data as object array

ExcelExport exp = new ExcelExport();

exp.Export(gridPropert, data, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-azure-dark"); 
}

// Get dynamic table type from here

public static Type GetTableType(GridProperties gridProperty)
{
--------------
--------------
} 

Regards,
Ragavee U S.

[Rob ONeill]

Hi,

Thank you for the example, and the use of reflection typebuilder.

But when trying filtered records this does not work - on your example too ... get an error within the Export() method ... Object reference not set to an instance of an object

 at Syncfusion.JavaScript.DataSources.DataOperations.PerformFiltering(IEnumerable dataSource, List`1 filteredColumns)
   at Syncfusion.JavaScript.DataSources.DataOperations.Execute(IEnumerable dataSource, GridProperties property)
   at Syncfusion.EJ.Export.GridExcelExport.ExecuteResult(GridProperties GridModel, IEnumerable dataSource)
   at Syncfusion.EJ.Export.GridExcelExport.Export(GridProperties gridModel, IEnumerable dataSource, Boolean multipleExport)
   at Syncfusion.EJ.Export.ExcelExport.Export(GridProperties gridmaodel, IEnumerable datasource, String excelname, ExcelVersion excelversion, Boolean isHideColumnIncude, Boolean isTemplateColumnIclude, String theme)
   at MVCApp_13_2.Controllers.HomeController.ExcelExport(String GridModel) in d:\VSProjects\Export-MVC\MVCApp_13_2\MVCApp_13_2\Controllers\HomeController.cs:line 68
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ActionMethodDispatcher.<>c__DisplayClass1.<WrapVoidAction>b__0(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.ActionInvocation.InvokeSynchronousActionMethod()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__36(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
   at System.Web.Mvc.Async.AsyncResultWrapper.End[TResult](IAsyncResult asyncResult, Object tag)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3c()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass45.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3e()

Cheers ... Rob.

[Ragavee U S]

Hi Rob,

We considered this query “Exception while exporting dynamic data after filtering operation” as a bug and a support incident has been created under your account to track the status of this issue. Please log on to our support website to check for further updates.
https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents

Regards,
Ragavee U S.