Exporting Grid.MVC data to Excel

Question

I need to export the data in Grid.MVC to Excel. I used the solution in this link.

http://www.codeproject.com/Articles/325103/MVC-Grid-to-Excel-file-download?msg=5161340#xx5161340xx

it is working but I have 2 problems. first it is working in chrome but it is not working in IE. It gives me an error (file cannot be read)in IE. Second problem is when I filter the Grid the exported data in Excel is still shows all the data not the filtered data.

if that is not a good solution please provide me example for exporting Grid.MVC data to excel.

Answer 1

I might be late to the party here but I have found a solution that will work using the inbuilt filtering options of the GridMvc plugin. This solution DOES not require any client side manipulations or custom filter applying. This is how you go about it.

When you install the plugin, it generates a partial view called: _Grid.cshtml which is found at: ~/Views/Shared/_Grid.cshtml location.

Now I had observed that everytime you invoke an event on the grid, this partial view is called and I then debugged into the flow and found that the Model in question has different properties like RenderOptions, ItemsToDisplay etc.

So I found that the filtered data is also stored but not directly in a property. There is a property called: ItemsToDisplay but unfortunately if you apply paging on the grid then it will only store the first n items where n is the paging limit.

Then finally I had to resort to reflection to get the entire filtered list which impacts the performance of the grid but not very much and it will get the job done.

_Grid.cshtml:

@using GridMvc.Columns
@using System.Reflection;
@model GridMvc.IGrid
@{ 
    try
    {
        Type t = typeof(GridMvc.Html.GridHtmlOptions<MyModel>);
        FieldInfo[] fi = t.GetFields(BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic);
        foreach (FieldInfo info in fi)
        {
            if (info.Name == "_source")
            {
                object _source = info.GetValue(Model);
                PropertyInfo gridItemsProperty = _source.GetType().GetProperty("GridItems");
                object gridItemsData = gridItemsProperty.GetValue(_source);
                //Set the filtered list here in Session
                Session["CurrentFilteredGridForExcelExport"] = (IEnumerable<MyModel>)(gridItemsData);
            }
        }
    }
    catch
    {

    }
}

@if (Model.RenderOptions.RenderRowsOnly)
{
    @RenderGridBody();
}
else
{
    <div class="grid-mvc" data-lang="@Model.Language" data-gridname="@Model.RenderOptions.GridName" data-selectable="@Model.RenderOptions.Selectable.ToString().ToLower()" data-multiplefilters="@Model.RenderOptions.AllowMultipleFilters.ToString().ToLower()">
        <div class="grid-wrap">
            <table class="table table-striped grid-table">
                @* Draw grid header *@
                <thead>
                    @RenderGridHeader()
                </thead>
                <tbody>
                    @RenderGridBody()
                    @RenderGridFooter()
                </tbody>
            </table>
            @RenderGridFooter()
        </div>
    </div>
}
@helper RenderGridBody()
{
    if (!Model.ItemsToDisplay.Any())
    {
        <tr class="grid-empty-text">
            <td colspan="@Model.Columns.Count()">
                @Model.EmptyGridText
            </td>
        </tr>
    }
    else
    {
        foreach (object item in Model.ItemsToDisplay)
        {
            <tr class="grid-row @Model.GetRowCssClasses(item)">
                @foreach (IGridColumn column in Model.Columns)
                {
                    @column.CellRenderer.Render(column, column.GetCell(item))
                }
            </tr>
        }
    }
}
@helper RenderGridHeader()
{
    if (Model.RenderOptions.ShowGridItemsCount)
    {
            <div class="grid-itemscount">
                <span class="grid-itemscount-label">@Model.RenderOptions.GridCountDisplayName:</span>
                <span class="grid-itemscount-caption">@Model.ItemsCount</span>
            </div>
    }

    <tr>
        @foreach (IGridColumn column in Model.Columns)
        {
            @column.HeaderRenderer.Render(column)
        }
    </tr>
}
@helper RenderGridFooter()
{
    <div class="grid-footer">
        @if (Model.EnablePaging && Model.Pager != null)
        {
            @Html.Partial(Model.Pager.TemplateName, Model.Pager)
        }
    </div>
}

And finallllllyy, you can export this filtered grid on your Controller side like this:

public void ExportToExcel()
{
    List<MyModel> mymodel= new List<MyModel>();
    if(Session["CurrentFilteredGridForExcelExport"] != null)
    {
        var datasetFromSession = (IEnumerable<MyModel>)(Session["CurrentFilteredGridForExcelExport"]);
        mymodel = datasetFromSession.ToList();
    }
    
    //Use your export logic with this dataset   
}

Hope this helps someone who is still using this grid plugin. Cheers!

Answer 2

This is a Server-side solution

In this case, the client-side component isn't important. Any data you pass for export, it exports it to excel file and download it. When I want to make data to export, I use the latest filter and retrieve the same data that are showing.

I hope it helps you.

• I used GridView to show my data.
• And also I used a simple data table. You can retrieve data from DataBase or wherever.
• DataView just helps me to filter data.
• You need to add ClosedXML to your project. Use this NuGet command: Install-Package ClosedXML

aspx

<form id="form1" runat="server">
<div>
    <asp:TextBox ID="TextBox1" runat="server" placeholder="Name or Family" ></asp:TextBox>
    <asp:Button ID="ButtonFilter" runat="server" Text="Filter" OnClick="ButtonFilter_Click" />
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    <br />
    <br />
    <asp:Button ID="ButtonExport" runat="server" Text="Export" OnClick="ButtonExport_Click" />
</div>
</form>

C# code

private DataTable dt = new DataTable();
private DataView dv;

private void Page_Load(object sender, System.EventArgs e)
{
    dt.Columns.Add("Id");
    dt.Columns.Add("EmployeeName");
    dt.Columns.Add("EmployeeFamily");

    for (int i = 0; i < 10; i++)
    {
        var r1 = dt.NewRow();
        r1["Id"] = i + 100;
        r1["EmployeeName"] = "Name " + i.ToString();
        r1["EmployeeFamily"] = "Family " + i.ToString();
        dt.Rows.Add(r1);
    }

    dv = new DataView(dt);

    GridView1.DataSource = dv;
    GridView1.DataBind();
}


private MemoryStream GetStream(XLWorkbook excelWorkbook)
{
    MemoryStream fs = new MemoryStream();
    excelWorkbook.SaveAs(fs);
    fs.Position = 0;
    return fs;
}


protected void ButtonFilter_Click(object sender, EventArgs e)
{
    dv.RowFilter = $"EmployeeName LIKE '%{TextBox1.Text}%' OR EmployeeFamily LIKE '%{TextBox1.Text}%'";

    GridView1.DataSource = dv;
    GridView1.DataBind();
}


protected void ButtonExport_Click(object sender, EventArgs e)
{
    dv = new DataView(dt);
    dv.RowFilter = $"EmployeeName LIKE '%{TextBox1.Text}%' OR EmployeeFamily LIKE '%{TextBox1.Text}%'";

    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dv.ToTable(), "Employees");
        string myName = HttpContext.Current.Server.UrlEncode("Employees.xlsx");
        MemoryStream stream = GetStream(wb);
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + myName);
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.BinaryWrite(stream.ToArray());
        HttpContext.Current.Response.End();
    }
}

---

Update: GridMvc version

In this version, I used GridMvc but still I make Excel file in server-side

ASPX

@model IList<GridMvcExportToExcel.Controllers.EmployeeModel>
@using GridMvc.Html

@{
    ViewBag.Title = "Home Page";
}

<script type="text/javascript">
    function exportToExcel() {
        debugger;
        var txtFilter = $('#txtFilter').val(); // get the textbox value
        var url = 'http://localhost:54312/Home/ExportToExcel?txtFilter=' + txtFilter;
        location.href = url; // redirect
        return false; // cancel default redirect
    };

</script>

<div>
    @using (Html.BeginForm("Index", "Home"))
    {
        @Html.TextBox("txtFilter", "", new { id = "txtFilter" })
        <button type="submit">Filter</button>
    }
    <br />

    @Html.Grid(Model).Columns(c =>
    {
        c.Add(x=>x.Id).Titled ("Employee Id");
        c.Add(x=>x.Name).Titled ("First Name").Filterable(false);
        c.Add(x=>x.Family).Titled ("Last Name").Filterable(true);
    }).WithPaging(50)

    <input type="button" id="exportToExcel" value="Export to Excel" onclick="exportToExcel()" />
</div>

C#

public class EmployeeModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
}

public class HomeController : Controller
{
    private IList<EmployeeModel> employees = new List<EmployeeModel>();

    public HomeController()
    {
        for (int i = 0; i < 20; i++)
        {
            employees.Add(new EmployeeModel()
            {
                Id = i + 1,
                Name = "Name " + (i + 1).ToString(),
                Family = "Family " + (i + 1).ToString(),
            });
        }
    }

    public ActionResult Index(string txtFilter)
    {
        txtFilter = txtFilter ?? "";
        var result = employees.Where(x => x.Name.Contains(txtFilter) || x.Family.Contains(txtFilter) || x.Id.ToString() == txtFilter);
        return View(result.ToList());
    }

    public void ExportToExcel(string txtFilter)
    {
        txtFilter = txtFilter ?? "";
        var result = employees.Where(x => x.Name.Contains(txtFilter) || x.Family.Contains(txtFilter) || x.Id.ToString() == txtFilter).ToList();

        DataTable table = new DataTable();
        using (var reader = ObjectReader.Create(result))
        {
            table.Load(reader);
        }

        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(table, "Employees");
            string myName = HttpContext.Server.UrlEncode("Employees.xlsx");
            MemoryStream stream = GetStream(wb);
            HttpContext.Response.Clear();
            HttpContext.Response.Buffer = true;
            HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + myName);
            HttpContext.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Response.BinaryWrite(stream.ToArray());
            HttpContext.Response.End();
        }
    }

    private MemoryStream GetStream(XLWorkbook excelWorkbook)
    {
        MemoryStream fs = new MemoryStream();
        excelWorkbook.SaveAs(fs);
        fs.Position = 0;
        return fs;
    }
}

Answer 3

Set the following header in your response, it will be downloaded with proper type

data:application/vnd.ms-excel

Answer 4

You have to take the parameters from url. Then build your own service on back end, which gets the parameters and exports to excel

let params = new URLSearchParams(document.location.search);
let allParams = params.getAll('grid-filter');

Answer 5

I have an javascript/jquery solution that works for me.

When you use grid.mvc it's add some classes to the thead and tbody, this classes needs to be removed for an correct exportation/visualization on your generated excel file. I'm using grid.mvc too, and this code export to excel, please let me know if this is working for you.

<script>

    $("#btnExport").click(function (e) {
        $('.grid-wrap').find('table').removeAttr('class');
        $('.grid-header').removeAttr('class');
        $('.grid-row').removeAttr('class');

        $('.grid-cell').removeAttr('data-name');
        $('.grid-cell').removeAttr('class');


        window.open('data:application/vnd.ms-excel,' + $('.grid-wrap').html());

       
       //MakeAnyFunctionToReloadThePageToGetTheClassesAgain();
        e.preventDefault();
    });

</script>

@Html.Grid(Model).Columns(columns =>
{
    
    columns.Add(foo => foo.Date).Sortable(true).Filterable(true);
    columns.Add(foo => foo.User).Sortable(true).Filterable(true);
    columns.Add(foo => foo.Controller).Sortable(true).Filterable(true);
    columns.Add(foo => foo.Action).Sortable(true).Filterable(true);
    columns.Add(foo => foo.ActionType).Sortable(true).Filterable(true);
    columns.Add(foo => foo.JsonObject).Sortable(true).Filterable(true);
}).WithMultipleFilters()

<button type="button" class="btn btn-danger" id="btnExport">export csv</button>