c# - Export html table to excel in asp.net MVC2

Question

Hi am looking for the best way on how export to excel does in ASP.NET MVC

Now i got this one from billsternberger.net

Export to Excel or CSV from ASP.NET MVC with C#

        //Export to excel
        public ActionResult Download()
        {

            List<Lookup> lookupList = data,GetLookupList();
            var grid = new System.Web.UI.WebControls.GridView();

            grid.DataSource = lookupList;
            grid.DataBind();

            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=YourFileName.xlsx");
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            grid.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();

            return View();
        }

which is working from binding to datagrid and export to excel.

Now what i need to do is get the my html table and export it to excel where i used jquery datatable on manipulating table data so it will be more light weight because it is done on client side.

I tried using jquery and ajax where i pass my html table to my entities on my controller

function  Export()  
{    

    var details = {};                                        
    details.LookupName = $("#tblLookup").html();

    //Validate details

    var url_ = generateURL("/Home/Download");                    //Call Save Controller  and pass details entities  

    $.ajax({
        type: "POST",
        url: url_,
        data: details,                                            //details will act as the Entities Model
        traditional: true,
        success: function(data) {

        },
        error: function(XMLHttpRequest, textStatus, errorThrown) {
                alert("error: " + XMLHttpRequest.responseText);
        },
        dataType: 'json'
    });

};

But it throws me A potentially dangerous Request.Form value was detected from the client etc,..

How is it done on MVC? I already look for some similar topic but it always drop me to my first working sample.

Thanks in Regards

Answer 1

The easiest solution would be to export the HTML table as CSV file and send to the server. Let's take an example. Suppose that we have defined a view model:

public class ExportViewModel
{
    [AllowHtml]
    public string Csv { get; set; }
}

and a controller:

public class HomeController : Controller
{
    public ActionResult Index()
    {
        return View(new ExportViewModel());
    }

    [HttpPost]
    public ActionResult Export(ExportViewModel model)
    {
        var cd = new ContentDisposition
        {
            FileName = "YourFileName.csv",
            Inline = false
        };
        Response.AddHeader("Content-Disposition", cd.ToString());
        return Content(model.Csv, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    }
}

Now inside the corresponding view we suppose that we have generated some <table> (the way this table is generated is really not interesting here):

@model ExportViewModel

<table id="myTable">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
            <td>Foo</td>
        </tr>
        <tr>
            <td>2</td>
            <td>Bar</td>
        </tr>
    </tbody>
</table>

@using (Html.BeginForm("Export", null, FormMethod.Post, new { id = "export" }))
{
    @Html.HiddenFor(x => x.Csv)
    <button type="submit">Export to Excel</button>
}

<script type="text/javascript" src="http://www.kunalbabre.com/projects/table2CSV.js"></script>
<script type="text/javascript">
    $('#export').submit(function () {
        $('#Csv').val($('#myTable').table2CSV({ delivery: 'value' }));
    });
</script>

We are using the table2CSV jQuery plugin to convert the HTML table into a CSV format. The resulting CSV will then be stored inside a hidden field just before the form is submitted to the server.

If you want to build native XLSX files you will have to use the OpenXML SDK on the server. You cannot just take an HTML table and turn it into a native Excel file. This solution will be more difficult to put into practice as you will have to send only the data to the server but it will allow you far greater customization over the resulting Excel file.