Export To Excel in MVC

I ran into the need to export a table to Excel in a MVC application.  A quick search on Bing showed a couple of different options.  For example, Steven Walther had the most complete solution found here but it had waaay to much code.

My original code in a web form application is much more compact:

        private void ExportTimesToExcel()

        {

            Response.ContentType = "application/vnd.ms-excel";

            Response.Charset = "";

            this.EnableViewState = false;

 

            StringWriter stringWriter = new StringWriter();

            HtmlTextWriter textWriter = new HtmlTextWriter(stringWriter);

 

            HtmlForm form = new HtmlForm();

            this.Controls.Add(form);

            form.Controls.Add(this.SwimmerTimeGridView);

            form.RenderControl(textWriter);

 

            Response.Write(stringWriter.ToString());

            Response.End();

        }

 

So I thought if there was a way to duplicate that in MVC.  I ran across a good starting point here.

public class ExcelResult : ActionResult

{

      public string FileName { get; set; }

      public string Path { get;set; }

 

      public override void ExecuteResult(ControllerContext context)

      {

            context.HttpContext.Response.Buffer = true;

            context.HttpContext.Response.Clear();

            context.HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + FileName);

            context.HttpContext.Response.ContentType = "application/vnd.ms-excel";

            context.HttpContext.Response.WriteFile(context.HttpContext.Server.MapPath(Path));   

      }

}

 

And here is the function to get the File

public ExcelResult GetExcelFile()

{

      return new ExcelResult

                  {

                        FileName = "sample.xls", Path = "~/Content/sample.xls"

                  };

}

 

And here is a MVC View control that calls it

<%= Html.ActionLink("Download Excel", "GetExcelFile", "Home")%>

 

So I needed to meld together some separated parts.

They key is this line:

context.HttpContext.Response.WriteFile(context.HttpContext.Server.MapPath(Path));   

The problem is that I don’t have an Excel file to start with so I can’t use Write File.  I first thought of writing a stream of XML that Excel recognizes.  So what is the fastest way to get XML from a Data Table?  I can’t use the web form trick of this

form.Controls.Add(this.SwimmerTimeGridView);

            form.RenderControl(textWriter);

 

because the controller doesn’t know anything about how the views are rendering the model.  It could use a Table, it could use a series of labels, etc…

I then thought (briefly) about looping through some LINQ  that is hitting the entire model – and realized that I didn’t want to do that (that is what Steve did)

I then thought – wait a second.  <insert second here> ADO.NET has a XML from DataTable.  If I had a model with the data as a DataTable, I can render the stream out as XML and everything should work…

Step #1: Add a ADO.NET DataSet

 

Then

Step #2: Create that Excel Class

    public class ExcelResult : ActionResult

    {

        public string XMLStream { get; set; }

        public string FileName { get; set; }

 

        public override void ExecuteResult(ControllerContext context)

        {

            context.HttpContext.Response.Buffer = true;

            context.HttpContext.Response.Clear();

            context.HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + FileName);

            context.HttpContext.Response.ContentType = "application/vnd.ms-excel";

            context.HttpContext.Response.Write(XMLStream);

        }

    }

 

Step #3: Call it from the Controller

 

        public ExcelResult GetExcelData()

        {

            Northwind northwind = new Northwind();

 

            string xmlStream;

            using (StringWriter sw = new StringWriter())

            {

                northwind.Tables[0].WriteXml(sw);

                xmlStream = sw.ToString();

            }

 

 

            return new ExcelResult

            {

                FileName = "sample.xls",

                XMLStream = xmlStream

            };

 

Step #4: Hook up a link to try it out

    <%= Html.ActionLink("Download Region Info To Excel", "GetExcelData", "Region")%>

 

The first try I got this:

And the result was this:

Ooops – Looks like the DataTable is not like Linq to SQL or Entity Framework – I actually have to code up the loading of it.  I added this code to load the data table

            RegionTableAdapter regionTableAdapter = new RegionTableAdapter();

            Northwind.RegionDataTable regionDataTable = regionTableAdapter.GetData();

 

Getting Closer

I have the data, Excel is launching, but Excel is not recognizing the XML as XML:

So the last thing I have to do is change the extension to .xml

            return new ExcelResult

            {

                FileName = "sample.xml",

                XMLStream = xmlStream

            };

 

and boom goes the dynamite (assuming the user has .xml hooked to open as Excel)