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

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 2

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>