Export filtererd data to Excel

May 4, 2015 at 8:22 PM
Hi,

I have built a MVC app that are using GridMvc.
I need to add the possibility to export the filtered data to excel. But i don't now how to access the data when i have applied filter to the grid? I only want to export the filtered data.
May 21, 2015 at 2:19 PM
Edited May 21, 2015 at 2:20 PM
Hi,
I looked for it a lot and I found this after some effort. First I have implemented a custom grid class which inherits from GridMvc.Grid<T> class;
public class CustomGrid:Grid<MyModel>
    {
        public HttpResponseBase Response { get; set; }
        public CustomGrid(List<MyModel> items,HttpResponseBase response) : base(items)
        {
            Response = response;  

        }


        protected override IEnumerable<MyModel> GetItemsToDisplay()
        {
            EnablePaging = false;
            var list= base.GetItemsToDisplay();

            ExportToExcel(list.ToList());
            return base.GetItemsToDisplay();
        }

        public void ExportToExcel(List<MyModel> items)
        {
            GridView gv = new GridView();
            gv.DataSource = items;
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=Marklist.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

    }
Then call this from controller method;
var customgrid = new CustomGrid(list,Response); // Response is HttpResponseBase property 
return View("GridExport", customgrid);
May 22, 2015 at 4:27 PM
@Bani90 - I have not tried [srht]'s suggestion, but for my own I found it simplest to export all of the Data in my Model using the EPPlus library and then let me users Filter/Sort/etc. through Excel instead of worrying about it via code.
  • Install EPPlus via Nuget (I'm using VS2013 - may be different for you)
  • Reference in Controller as 'using OfficeOpenXml;'
For my use, I then have an Export View with a ListBoxFor(...) which is populated via a ViewModel:

ViewModel:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Project.Models
{
    public class ExportViewModel
    {
        public Dictionary<int, string> ListOfExportFields { get; set; }
        public int[] SelectedFields { get; set; }
        
        public ExportViewModel() {
            ListOfExportFields = new Dictionary<int, string>() {
                {1, "Model_Description"},
                {2, "Manufacturer_Description"},
                {3, "Type_Description"},
                {4, "Location_Dept"},
                {5, "Location_Room"},
                {6, "Vendor_Name"},
                {7, "Status_Description"},
                // etc.
                {10, "NOTE"},
                {11, "Owner_Name"},
                {12, "COST"},
                {21, "CREATED_DATE"},
                {22, "CREATED_BY"},
                {23, "MODIFIED_DATE"},
                {24, "MODIFIED_BY"},
            };
        }
    }
}
Export View:
<h2>Export</h2>

<p>Please select which fields to Export to Excel:</p>

@using (Html.BeginForm("ExportUsingEPPlus", "Export", FormMethod.Post))
{
    @Html.ListBoxFor(m => m.SelectedFields, new MultiSelectList(Model.ListOfExportFields, "Key", "Value"), new { @class = "form-control", style = "height: 250px;" })
    <br />
    <input type="submit" value="Export Specified Asset Fields" />
}
This allows user to specify which of the Data Fields in the ViewModel to export to Excel (note, the fields must be spelled EXACTLY as they are referenced in your regular model). Then on my ExportController, I pass in the selected fields from the ViewModel and use EPPlus to Export to Excel:

[HttpPost]
        public ActionResult ExportUsingEPPlus(ExportViewModel model)
        {
            ExcelPackage package = new ExcelPackage();
            var ws = package.Workbook.Worksheets.Add("TestExport");

            var exportFields = new List<string>();
            foreach (var selectedField in model.SelectedFields)
            {
                // Adds selected fields to [exportFields] List<string>
                exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
            }

            // Create array of which fields are to be shown based on user selection.
            var membersToShow = typeof(MODEL_Entity).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();
            
            // Output entire MODEL_Entity model to Excel (including only those fields specified by user).
            ws.Cells["A1"].LoadFromCollection(_db.MODEL_Entity.ToList(), true, TableStyles.None, BindingFlags.Default, membersToShow);
            ws.Cells["A1:ZZ1"].Style.Font.Bold = true;
            ws.Cells["A1:ZZ1"].AutoFitColumns();

            var memoryStream = new MemoryStream();
            package.SaveAs(memoryStream);

            string fileName = "Exported-ModelFields-" + DateTime.Now + ".xlsx";
            string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            memoryStream.Position = 0;
            return File(memoryStream, contentType, fileName);
        }
    }
It may not apply all the filtering and sorting capability you are looking for at the time of Export, but this does allow my users to specify only the fields they wish to have exported from the Model, and then they can perform more robust filtering/sorting/etc. within Excel instead of dealing with it in code.

Hope it helps!
May 25, 2015 at 5:35 PM
@srht thanks for the suggestion but that method GetItemsToDisplay() returns all the items, not a filtered collection.
Jun 9, 2015 at 3:56 PM
Hello!
The GetItemsToDisplay works only with no pagination grid.
Otherwise, it returns only the records on the first page.
Do you have other propositions for that case?
Jun 9, 2015 at 9:19 PM
Hi,
I forgot to mention an important part for the solution. You must use the same grid for exporting all rows you used on paged gridview. You may create a view with the same grid but with customgrid model. Pass a CustomGrid object to grid.mvc grid as parameter.
Jun 9, 2015 at 11:20 PM
Thank you for replay,
Any code illustration? I think i'm no more following you!
For information, I spent a lot of time to analyse that problem. The issu is that the _Grid.cshtml uses an IGrid as model (containing all rows), and the _GridPager does contain only the items to display on the first page. The link between the all rows and the filtered rows is between the two steps.
The solution is to know how the filtering is applyed to the grids rows. Personnally, that feature demotivates me to continue with gridMVC after a year of use.
Aug 24, 2015 at 10:55 AM
Hi, it would be nice if you can elaborate a bit more with the code especially when invoking the custom grid from the controller as we are beginers :(
Sep 7, 2016 at 5:32 PM
@srht: How can I pass the CustomGrid from the view to the controller?
When I tried to, I get a message saying that the CustomGrid class needs a constructor with no parameters.
Once I added it, I always get a CustomGrid instance set to null.
Please help!
Sep 8, 2016 at 10:51 AM
@srht: How can I pass the CustomGrid from the view to the controller?
When I tried to, I get a message saying that the CustomGrid class needs a constructor with no parameters.
Once I added it, I always get a CustomGrid instance set to null.
Please help!
Sep 10, 2016 at 1:57 PM
Edited Sep 10, 2016 at 1:58 PM
@Guzman: You can create a method for grid result which gets a parameter indicates the request is an excel export or not. If it's a export operation use the custom grid class that I mentioned earlier in this subject.
 public ActionResult Index(string searchKeyword = "", bool export = false)
        {
           if (export)
                {
                 
                    var customExportGridDataList = new CustomGrid(DataListe,Response);
                    
                    return View("GridExport", customExportGridDataList);
                }
                else
                {
                    return View("Index", gridDataList);
                }
       }