export filtered grid to excel

[Manolo]

Hi,

I've a filtered section and a grid.



I need export the data grid, but in the examples, when the export function is called, the datasource is load, but then, I don't have the filters.

How can I export the data grid filtered to excel?

Bye

[Isuriya Rajan]

Hi Manolo,

Thanks for using the Syncfusion product.

We suspect that you are using the external filter for grid. If so we need to pass filtered record to Export method.

But by default grid has inbuilt filtering support where you need not to pass filtered data to Export method.

Please find the code example to pass filtered data to export method.
  

Controller

public void ExportToExcel(string GridModel)

{

ExcelExport exp = new ExcelExport();

var DataSource = new NorthwindDataContext().OrdersViews.ToList();

GridProperties obj = ConvertGridObject(GridModel);

exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "bootstrap-theme");

}

We have created a simple sample with inbuilt filter support and it can be downloaded from the below location:

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/119826/Exporting-715826328.zip

Please let us know if you require any further assistance on this.

Regards,

Isuriya R

[Manolo]

Hi,

I fill the grid with a store procedure. Its parameters are defined by my filters's section, so I need it.

On the other hand, I need grid's filter for user. I'm testing your example, and when I use the grid's filter (Ship country or customer ID),  the export function to excel no return values. The employee ID filter exportation works fine.

Thanks

[Isuriya Rajan]

Hi Manolo,

We can achieve  your requirement by using filterColumn method of the grid. In filterColumn  method we have to pass fieldname ,operator,value and comparer . we can apply filtering to grid through filterColumn method and we can get filtered column details in controller after exporting.

Please refer the below code snippets.

 

function click() {

        var obj = $("#FlatGrid").ejGrid('instance')

        obj.filterColumn("CustomerID", "equal", "ALFKI", "and", true);

     

    }

 

As in the below screenshot ,while filtering the column using filterColumn method we can get the filtered details in the grid controller itself which can be passed to ExportToExcel method

  

While doing the filtering operation we must mention the allowFiltering property as a true.

We have created a sample and the same can be downloaded from the following location:

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/119826/Exporting-1900867735.zip

Please refer below  the documentation link for how we can do filtering  for grid columns.

Online  Link: https://help.syncfusion.com/api/js/ejgrid#methods:filtercolumn

Please try the above sample and let us know if it helps. If we misunderstood your query, please provide us clear information regarding your requirements. It will help us to provide the prompt solution.

Regards,

Isuriya R

[Manolo]

Hi,

I've the initial problem. I post my code to understand:

View

 <div class="row">
            @using (Html.BeginForm())
            {
                <div class="col-md-2">
                    @Html.EJ().DatePicker("FechaInforme").Value(new DateTime(2015, 7, 17)).DateFormat("dd/MM/yyyy")
                </div>
                <div class="col-md-2">
                        @Html.EJ().DropDownList("TipoOperacion").Datasource((IEnumerable<EntitiesInforme.Informe.TipoOS>)ViewBag.DSTiposOperacion).DropDownListFields(ddl => ddl.ID("ID").Text("Nombre").Value("ID"))
                </div>
                <div class="col-md-2">
                        @Html.EJ().DropDownList("EstadoOperacion").Datasource((IEnumerable<EntitiesInforme.Informe.EstadoOperacion>)ViewBag.DSEstadosOperacion).DropDownListFields(ddl => ddl.ID("ID").Text("Nombre").Value("ID"))
                </div>
                <div class="col-md-2">
                    <button type="submit" class="btn btn-default">Generar Informe</button>
                </div>
            }
        </div>
<div class="row">
    <div class="col-md-12">
        @(Html.EJ().Grid<EntitiesInforme.SP.InformeDiarioProcesado>("FlatGrid")
            .Locale("es-ES")
            .Datasource((IEnumerable<object>)ViewBag.DSInforme)
            .SelectionType(SelectionType.Multiple)
            .Mappers(map => map.ExportToExcelAction("Informes/ExcelExport"))
            .ToolbarSettings(toolBar => toolBar.ShowToolbar().ToolbarItems(items =>
            {
                items.AddTool(ToolBarItems.ExcelExport);
            }))
            .AllowFiltering()
                //.FilterSettings(filter => { filter.FilterType(FilterType.Excel); })
            .FilterSettings(filter => { filter.ShowFilterBarStatus().FilterBarMode(FilterBarMode.OnEnter); })

            .SummaryRow(row =>
                {
                    row.Title("Total").SummaryColumns(col =>
                        {
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango0").DataMember("Rango0").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango1").DataMember("Rango1").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango2").DataMember("Rango2").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango3").DataMember("Rango3").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango4").DataMember("Rango4").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango5").DataMember("Rango5").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango6").DataMember("Rango6").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Rango7").DataMember("Rango7").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("TotalDias").DataMember("TotalDias").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("PromedioDias").DataMember("PromedioDias").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("OperacionesSuperioresLimite").DataMember("OperacionesSuperioresLimite").Add();
                            col.SummaryType(SummaryType.Sum).DisplayColumn("Total").DataMember("Total").Add();
                        }).Add();
                })

            .AllowTextWrap()
            .AllowSorting()
            .AllowPaging()
            .AllowScrolling()
            .AllowGrouping()
            .GroupSettings(group => { group.EnableDropAreaAnimation(true); group.ShowDropArea(true); })
            .Columns(col =>
            {
                col.Field("CodigoOS").HeaderText("Tipo").TextAlign(TextAlign.Center).Add();
                col.Field("CodigoCentro").HeaderText("Centro").Add();
                col.Field("Contrata").HeaderText("Contrata").Width(120).Add();
                col.Field("Rango0").HeaderText("<= 4 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango1").HeaderText("5-10 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango2").HeaderText("11-20 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango3").HeaderText("21-30 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango4").HeaderText("31-40 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango5").HeaderText("41-50 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango6").HeaderText("51-60 días").TextAlign(TextAlign.Center).Add();
                col.Field("Rango7").HeaderText("> 60 días").TextAlign(TextAlign.Center).Add();
                col.Field("TotalDias").HeaderText("Total días").TextAlign(TextAlign.Center).Add();
                col.Field("PromedioDias").HeaderText("Promedio Dias").TextAlign(TextAlign.Center).Width(90).Add();
                col.Field("OperacionesSuperioresLimite").HeaderText("Op. Sup. Límite").TextAlign(TextAlign.Center).Width(90).Add();
                col.Field("Total").TextAlign(TextAlign.Center).Add();

            })
            .ClientSideEvents(eve =>
            {
                eve.RowSelected("onRowSelected");
                eve.ActionComplete("onActionComplete");
            })

        )
    </div>
</div>

Controller:

private DMInformes db = new DMInformes();
 // GET: Informes
        public ActionResult Index(DateTime? fechaInforme, int? tipoOperacion, int? estadoOperacion)
        {
            ViewBag.DSTiposOperacion = db.GetTiposOS().ToList<TipoOS>();
            ViewBag.DSEstadosOperacion = db.GetEstadosOperacion().ToList<EstadoOperacion>();

            if (fechaInforme != null && tipoOperacion != null && estadoOperacion != null)
            {
                ViewBag.DSInforme = db.GetInformeDiario((DateTime)fechaInforme, (int)tipoOperacion, (int)estadoOperacion);
                ViewBag.GridTitle = ((DateTime)fechaInforme).ToString("dd/MM/yyyy") + " - " + db.GetTiposOS().Where(x => x.ID == tipoOperacion).Select(x => x.Nombre).Single() ;
            }
           
            return View();
        }

DMInformes:

 public IEnumerable<InformeDiarioProcesado> GetInformeDiario(DateTime fecha, int idTipoOS, int idEstadoOperacion)
        {
            InformesContext db = new InformesContext();

            SqlParameter p1 = new SqlParameter("@fecha", System.Data.SqlDbType.Date);
            p1.Value = fecha;

            SqlParameter p2 = new SqlParameter("@tipoOS", System.Data.SqlDbType.Int);
            p2.Value = idTipoOS;

            SqlParameter p3 = new SqlParameter("@estadoOperacion", System.Data.SqlDbType.Int);
            p3.Value = idEstadoOperacion;

            string spName = "spGetInformeDiario";

            List<InformeDiarioProcesado> resultados = db.Database.SqlQuery<InformeDiarioProcesado>("EXEC " + spName + " @fecha, @tipoOS, @estadoOperacion", new object[] { p1, p2, p3 }).ToList<InformeDiarioProcesado>();

            return resultados;
        }

I fill the datasource with store procedure, with params in my param's section.

So, I don't know how set the store procedure parameters in ExcelExportFunction to get the datasource

[Isuriya Rajan]

Hi Manolo,

Thanks for using Syncfusion products.

By default, while exporting we ignore to pass the datasource with Grid model to Export action. Whereas we need to pass filter data in export function while using external filter. To avoid that, we are passing the datasource with model to Export action and directly exporting the Grid datasource. Please find the code example.

index.cshtml

function create(args) {

//ignore the dataSource property

var index = $.inArray("dataSource", ej.Grid.prototype.ignoreOnExport);

ej.Grid.prototype.ignoreOnExport.splice(index, 1);

}

Controller

public void ExportToExcel(string GridModel)

{

ExcelExport exp = new ExcelExport();

var DataSource = new NorthwindDataContext().OrdersViews.ToList();

GridProperties obj = (GridProperties)ConvertGridObject(GridModel);

// filter dataSource

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)

{

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);

if (ds.Key == "dataSource")

{

List<OrdersView> value1 = serializer.Deserialize<List<OrdersView>>(serialize);

property.SetValue(gridProp, value1, null);

continue;

}

object value = serializer.Deserialize(serialize, type);

property.SetValue(gridProp, value, null);

}

}

return gridProp;

}

We have created a sample and the same can be downloaded from the following location:

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/119826/Grid-1285788659.zip

Please try the above sample and let us know if it helps. If we misunderstood your query, please provide us clear information regarding your requirements. It will help us to provide the prompt solution.

Regards,

Isuriya R