Export search results to excel in MVC 4

2020-05-03 10:38发布

问题:

I have following controller. When i search for a specific grade i get a list. What i want is to export this result to excel. I have my export button works fine. But the only thing is that it tries to export everything in db to excel when i click on it. What i want is to export only the results. Any idea?

public ActionResult Index(string searchBy, string search)
    {
        if (!String.IsNullOrEmpty(searchBy) && !String.IsNullOrEmpty(search))
        {
            if (searchBy == "ID")
            {
                return View(db.students.Where(x => x.id==search).ToList());
            }
            else if (searchBy == "grade")
            {
                return View(db.students.Where(x => x.grade == search).ToList());
            }
        else
        {
            return View(db.students.Take(0));
        }
    }

     public ActionResult ExportData()
    {
        GridView gv = new GridView();
        gv.DataSource = db.students.ToList();
        gv.DataBind();
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment; filename=students.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();

        return RedirectToAction("Home");
    }

And this is the part in my index view:

  @using (Html.BeginForm("ExportData", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
            {
                <table>
                    <tr>
                        <td></td>
                        <td>
                            <input type="submit" name="Export" id="Export" value="Export" />
                        </td>
                    </tr>

                </table>
            }

回答1:

Your ExportData() action is just retrieving every result from db.students, it doesn't know what is in your view.
Assuming your list of students has all the data you want, you could either post the entire list to your action, e.g.

public ActionResult ExportData(List<Student> students)
{
    GridView gv = new GridView();
    gv.DataSource = students;
    gv.DataBind();
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=students.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();

    return RedirectToAction("Home");
}  

Or you could pass your search parameters and filter the database accordingly, e.g.

public ActionResult ExportData(string searchBy, string search)
{

    GridView gv = new GridView();
    if (!String.IsNullOrEmpty(searchBy) && !String.IsNullOrEmpty(search))
    {
        if (searchBy == "ID")
        {
            gv.DataSource = db.students.Where(x => x.id==search).ToList();
        }
        else if (searchBy == "grade")
        {
            gv.DataSource = db.students.Where(x => x.grade == search).ToList();
        }
    }
    gv.DataBind();
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=students.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();

    return RedirectToAction("Home");
}

And don't forget to post the necessary data from your view.