Click here to Skip to main content
Click here to Skip to main content

MVC Grid to Excel file download

, 8 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Convert the results of a ASP.NET MVC grid to a downloadable Excel file

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.

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();

        }

    } 

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);
        }

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. 
            }
        }


In the view you place the following download link

    @Html.ActionLink("Download File", "Download")

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ritesh Ramesh
Architect Infosolvex Solutions Inc
Australia Australia
Ritesh is an IT consultant with over ten years of experience in the IT industry varying from consultation, architecture, design, development to technical management. He has a strong background in solutions and applications architecture with a focus on Microsoft’s .Net platform. His area of expertise spans design and implementation of client/server, database and web-based systems. He has worked with C#, ASP.NET 1.1 and 2.0, ADO.NET, Web Services and SQL technology on several enterprise class projects.
 

 

Freedom is not worth having if it does not include the freedom to make mistakes.
Mahatma Gandhi

Comments and Discussions

 
GeneralThanks! Pinmembersteven718-Nov-14 0:41 
QuestionHow do I generate multiple Sheets Pinmembersumitp1715-May-14 21:40 
QuestionIt solved PinmemberNanda Rengasamy24-Sep-13 3:25 
GeneralMy vote of 1 PinmemberJaydeep Jadav8-Mar-13 23:38 
Questioncode is working in google chrome browser but not in ie Pinmembermagy11-Feb-13 20:43 
QuestionLog Kuchh bhi dalate hai.. PinmemberMember 862979031-Oct-12 7:10 
GeneralMy vote of 4 Pinmemberandy(-RKO)1-May-12 5:13 
GeneralMy vote of 3 PinmemberxExTxCx7-Feb-12 8:37 
QuestionNot real XLS file PinmemberRobert Hutch5-Feb-12 23:03 
AnswerRe: Not real XLS file PinmemberRitesh Ramesh8-Feb-12 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 8 Feb 2012
Article Copyright 2012 by Ritesh Ramesh
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid