MVC Grid to Excel file download

Introduction

This article is a "how to" on converting a tabulated or grid representation of data from an ASP.NET MVC web application to a downloadable Excel data file.

Background

I was recently building an ASP.NET MVC 3 based reporting application. One of the features was to provide a downloadable Excel file for the report that was being rendered as a grid/table. Rather than recreate an entire module to accomplish this, I wanted to find a way to reuse the data being used to render the view, with the least amount of code possible.

Using the code

I have tried to encapsulate much of the implementation in a custom ActionResult class (DownloadFileActionResult), so you can just pass data in the form of a GridView object and expect a downloadable Excel file with tabulated data.

You can download the complete code from the below link.

• Download MvcGridApp - 494.87 KB

As stated above most of the implementation is contained in the DownloadFileActionResult class.

Firstly the DownloadFileActionResult inherits from the abstract ActionResult class, the constructor accepts two parameters the databound gridview object and the name of the downloadable Excel file.

The ExecuteResult is overridden with the custom implementation that creates a response, then uses a HtmlTextWriter and a StringWriter to pipe the rendering of a gridview to a string. This string is then streamed back to the response to be delivered as an Excel file. Its that simple

public class DownloadFileActionResult : ActionResult
    {

        public GridView ExcelGridView { get; set; }
        public string fileName { get; set; }


        public DownloadFileActionResult(GridView gv, string pFileName)
        {
            ExcelGridView = gv;
            fileName = pFileName;
        }


        public override void ExecuteResult(ControllerContext context)
        {

           //Create a response stream to create and write the Excel file
            HttpContext curContext = HttpContext.Current;
            curContext.Response.Clear();
            curContext.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
            curContext.Response.Charset = "";
            curContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            curContext.Response.ContentType = "application/vnd.ms-excel";

            //Convert the rendering of the gridview to a string representation 
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            ExcelGridView.RenderControl(htw);
            
            //Open a memory stream that you can use to write back to the response
            byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
            MemoryStream s = new MemoryStream(byteArray);
            StreamReader sr = new StreamReader(s, Encoding.ASCII);

            //Write the stream back to the response
            curContext.Response.Write(sr.ReadToEnd());
            curContext.Response.End();

        }

    } 
Colourised in 20ms

Within your controller's Action result where you bind the view with model data you will need to create a gridview, bind the model data to it and store it in a session variable.

public ActionResult Index()
       {
           ViewBag.Message = "Welcome to ASP.NET MVC!";
           CarModels cm = new CarModels();
           List<Cars> model = cm.getAllCars();

           GridView gv = new GridView();
           gv.DataSource = model;
           gv.DataBind();
           Session["Cars"] = gv;

           return View(model);
       }
Colourised in 7ms

In the same controller we need to create a new ActionResult called Download to handle the file download request.

public ActionResult Download()
        {
            if (Session["Cars"] != null)
            {
                return new DownloadFileActionResult((GridView)Session["Cars"], "Cars.xls");
            }
            else 
            {
               //Some kind of a result that will indicate that the view has 
               //not been created yet. I would use a Javascript message to do so. 
            }
        }
Colourised in 10ms

In the view you place the following download link

@Html.ActionLink("Download File", "Download")
Colourised in 0ms

Points of Interest

I hope this article goes to show how you can override the default ActionResult class with a custom implementation to generate a custom response.

I am somehow not too happy about storing the results of the grid in a Session variable and will endeavor to come up with a for a better solution.

Limitation

As this is html being rendered as an Excel sheet you will not be able to make use of most of the Excel functionality. In my case, the client wanted was a downloadable version of the data so it was fine. But in case you want to create a true Excel file you could look at using XSLT to convert your html output to an Excel file.

Reference

http://www.codeshelve.com/code/details/54/export-to-excel-in-asp-net-mvc