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)

 

Advertisements

11 Responses to Export To Excel in MVC

  1. Robert says:

    Great write-up!!

  2. Ala says:

    where exactly do i add :

    RegionTableAdapter regionTableAdapter = new RegionTableAdapter();
    Northwind.RegionDataTable regionDataTable = regionTableAdapter.GetData();

    I’ve placed it everywhere but i keep getting the excel file at step 4.

  3. jamie dixon says:

    Put it in the region controller. Here is the full function:
    public ExcelResult GetExcelData()
    {
    RegionTableAdapter regionTableAdapter = new RegionTableAdapter();
    Northwind.RegionDataTable regionDataTable = regionTableAdapter.GetData();

    string xmlStream;
    using (StringWriter sw = new StringWriter())
    {
    regionDataTable.WriteXml(sw);
    xmlStream = sw.ToString();
    }

    return new ExcelResult
    {
    FileName = “sample.xls”,
    XMLStream = xmlStream
    };

    }

  4. Stha says:

    Thank you for he example…Quick question, how do you hook up .xml to open as excel??

    • jamie dixon says:

      You don’t have to do anything. When you try and open an .XML file on the clinet – Excel should automatically load and open the file. If the client hasn’t already assoicated with Excel yet, you will get the “Choose Program to Open” dialog.

  5. suresh kumar says:

    Save dialog is not coming for me

  6. Graham says:

    Nice article.
    I am using Excel 2010 and I get as far as you did with the Warning error and just a 1 line string in the 1st row, although I followed the code example.
    Any ideas.

  7. Dan Cary says:

    Jamie, This blog posting helped me tremendously.

    Since my data is already provided to my controller in a view model, I implemented getExcelData() a little differently. I am just not that familiar with datatables and readers, so I found this example using a gridview. I thought it might help others. Please feel free if there is something horribly wrong with this method.

    public ExcelResult GetExcelData()
    {
    //note myDataViewModel is returned as a List;
    var viewModels = _provider.getMyDataViewModel(myParameter1, myParameter2);
    var myFileName = “MyFileNameBlah”;

    var gridView= new GridView { DataSource = viewModels.ToList() };
    gridView.DataBind();
    var stringWriter = new StringWriter();
    var htmlTextWriter = new HtmlTextWriter(stringWriter);
    gridView.RenderControl(htmlTextWriter);

    return new ExcelResult
    {
    FileName = fileName,
    XmlStream = sw.ToString()
    };
    }

    Thanks again.
    Dan

  8. Kalid Pareja says:

    Hi everyone.
    Excuse me, How can I change the name of my headers before export to Excel? Thanks.

  9. Akhilesh dhakar says:

    Hi Everyone

    I have changed .xml but still getting xml fromat data in excel Please help me

  10. carlos says:

    hello, Good day,
    how can I do it from Ajax? help me. please

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: