Exporting a HTML Table to Excel from ASP.NET MVC

Question

I am currently working with ASP.NET MVC and I have an action method that displays few reports in the view in table format.

I have a requirement to export the same table to an Excel document at the click of a button in the View.

How can this be achieved? How would you create your Action method for this?

Answer 1

Hi, I'm using component, called Aspose.Cells (http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/).

It's not free, though the most powerful solution I've tried +)

Also, for free solutions, see: http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c

Answer 2

Get data from database using your data access methods in dot net.

Use a loop to get each record.

Now add each record in a variable one by one like this.

Name,Email,Phone,Country
John,[email protected],+12345,USA
Ali,[email protected],+54321,UAE
Naveed,[email protected],+09876,Pakistan

use 'new line' code at the end of each row (For example '\n')

Now write above data into a file with extension .csv (example data.csv)

Now open that file in EXCEL

:)

Answer 3

In your controller action you could add this:

Response.AddHeader("Content-Disposition", "filename=thefilename.xls");
Response.ContentType = "application/vnd.ms-excel";

Then just send the user to the same view. That should work.

Answer 4

in code behind lets take an example a gridview because its rendered as html table

    //***   First you have to prepare your grid view for export
    Literal l = new Literal();
    string s = string.Empty;
    LinkButton lb = new LinkButton();

    for (int i = 0; i < gv.Controls.Count; i++)
    {
        if (gv.Controls[i].GetType() == typeof(LinkButton))
        {
            l.Text = (gv.Controls[i] as LinkButton).Text;
            gv.Controls.Remove(gv.Controls[i]);
            gv.Controls.AddAt(i, l);
        }
        else if (gv.Controls[i].GetType() == typeof(DropDownList))
        {
            l.Text = (gv.Controls[i] as DropDownList).Text;
            gv.Controls.Remove(gv.Controls[i]);
            gv.Controls.AddAt(i, l);
        }
        else if (gv.Controls[i].GetType() == typeof(CheckBox))
         {
             l.Text = (gv.controls[i] as CheckBox).Checked ? "True" : "False";
             gv.Controls.Remove(gv.Controls[i]);
             gv.Controls.AddAt(i, l);
         }
         if (gv.Controls[i].HasControls())
         {
             PrepareGridViewForExport(gv.Controls[i]);
         }


    }
    //***   end
    //*** now lets export the gridview to excell ExportToExcell()

    Response.ClearHeaders();
    string attachment = "attachment; filename="+"ItemReport" +".xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    gridView1.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();