Jamie Dixon's Home

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 #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)

One of the biggest learning curves for me over the last week is how to pass data from the client to the server using Ajax and Json in an MVC solution.  As I can tell, there are 3 main patterns:

·         Individual parameters

·         Complex object that is serialized

·         Abstract Collection or array

MVC uses:

·         Request.Form[“x”] where x is the name of the property to assign to the object

·         UpdateModel(x) where x is the name of the class you want to update.  Behind the scenes, MVC hooks up the proprieties from the form collection to the proprieties in that object.  Therefore, the name of the property must match exactly between the form and the object

·         Or just pass in the object to the parameter.  Behind the scenes, MVC assigns the values to the properties, serializes the object, and throws it into your parameter

The first option, Request.Form[“x”] where x is the name of the property to assign to the object:

For example:

        [HttpPost]

        public ActionResult Create()

        {

            try

            {

                Region region = new Region();

                region.RegionID = Int32.Parse(Request.Form["regionId"]);

                region.RegionDescription = Request.Form["regionDescription"];

                //Insert Logic Here

                return RedirectToAction("Index");

            }

            catch

            {

                return View();

            }

        }

But you can’t have 2 methods with the same interface, so you can’t have this also:

        public ActionResult Create()

        {

            return View();

        }

So you can drop the one for the Get – but then every get requires an input.  So you are stuck, unless you muck about in the routing.

Alternatively, you can use the default and pass in a variable and not use it – which is what MVC does out of the box with the formCollection.  I passed in an Id for the heck of it:

        [HttpPost]

        public ActionResult Create(int? id)

        {

            try

            {

                Region region = new Region();

                region.RegionID = Int32.Parse(Request.Form["regionId"]);

                region.RegionDescription = Request.Form["regionDescription"];

                //Insert Logic Here

                return RedirectToAction("Index");

            }

            catch

            {

                return View();

            }

        }

And looking at the watch, you can see that the Form collection is being populated:

This is all well and good, but does not help me with a JSON request (and least not directly) because there is no Form Collection to be had. 

Interestingly, when making a method with the class as a parameter, MVC does a Json call behind the scenes.  For example, consider this method that returns a View:

        [HttpPost]

        public ActionResult Create(Region region)

        {

            //Insert Logic Here

            return View();

        }

Check out the base value: JsonSerialization:

So I wondered if MVC can do it, why can’t I?  Can I send the exactly same Json values and have it parse?

I wrote some JQuery with Ajax like so:

    <script type="text/javascript">

        function CallService() {

            var regionId = $("#RegionID").val();

            var regionDescription = $("#RegionDescription").val();

            var postData = { RegionID: regionId, RegionDescription: regionDescription };

            $.ajax({

                type: "POST",

                url: "/Region/Create",

                data: postData,

                dataType: "json"

            });

        }

    </script>

I then fired up Fiddler and find out:

Here is the request/response:

 And Holy Cow – It worked!  Check out a Quick Watch from my controller:

I set up a entity framework in my model for Northwind:

I then created a Region Controller to return all of the regions for the Index() Method

        public ViewResult Index()

        {

            var region = (from r in dataContext.Regions

             select r);

            return View(region.ToList());

        }

 I then set up a Unit Test to check to make sure I am getting 4 regions back (Yes, I know I should be using a stub).

        [TestMethod()]

        public void IndexTest()

        {

            RegionController target = new RegionController();

            List<Region> regions = (List<Region>)target.Index().ViewData.Model;

            int expected = 4;

            int actual = regions.Count;

            Assert.AreEqual(expected, actual);

        }

The thing that surprised me is that the chain I needed to go though to get to the model and the cast.  However, seeing this:

makes it all worthwhile.