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.
Unfortunately, activation email could not send to your email. Please try again.

export filtered grid to excel

Thread ID:

Created:

Updated:

Platform:

Replies:

119826 Aug 5,2015 08:15 AM Aug 11,2015 08:33 AM ASP.NET MVC 5
loading
Tags: Grid
Manolo
Asked On August 5, 2015 08:15 AM

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 [Syncfusion]
Replied On August 6, 2015 08:29 AM

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
Replied On August 7, 2015 07:02 AM

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 [Syncfusion]
Replied On August 8, 2015 11:44 AM

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: http://helpjs.syncfusion.com/js/api/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
Replied On August 10, 2015 07:24 AM

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 [Syncfusion]
Replied On August 11, 2015 08:33 AM

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



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.

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.

;