Bit bored today and decided to finally learn how to interact with an Excel document using C#. Since I also love using MVC3 I just combined the two and this is what came out. Hopefully you guys like it and find it useful. :)

First things first, we need our excel document. Open excel and create something like this:



Let's create a Controller called Upload in our MVC3 solution that will manage all of the uploading for us.

using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Web.Mvc;
using QuimizaReportes.Models;
using System.Collections.Generic;
using System;

namespace QuimizaReportes.Controllers
{
    public class UploadController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase excelFile)
        {
            return View();
        }

        public ActionResult Error()
        {
            return View();
        }
    }
}

We're using the HttpPost decorator to let our routing manager know that we want this specific action to run when it's invoked via POST. Useful because we don't want bots or script kiddies bashing our server.

Right click on your Index() action, not the POST one, and select Add View. This is going to be our default view when someone browses to our /Upload url.

@{
    ViewBag.Title = "Index";
}

<h2>Upload Test</h2>

<p>Please upload an Excel file and we'll try to parse it.</p>

<form action="/Upload/Index" enctype="multipart/form-data" method="post">
    <input type="file" id="excelFile" name="excelFile" />
    <input type="submit" value="Upload!" />
</form>

Cool, now go back to the Controller and let's modify our Error Action:

public ActionResult Error()
{
    return View();
}

This will Action will be invoke when something goes wrong during the upload. Go ahead and create a view with a nice error message for this Action.

Ok, so far so good. Now let's work on the whole point of this blog post. The excel uploading and parsing. :)

Let's create a Person model, right click on the Models folder and create a new class called Person.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace QuimizaReportes.Models
{
    public class Person
    {
        public double? Id { get; set; }
        public string Name { get; set; }
        public string LastName { get; set; }
        public DateTime? DateOfBirth { get; set; }
    }
}

We'll have to modify the POST Index action:

[HttpPost]
public ActionResult Index(HttpPostedFileBase excelFile)
{
    if (excelFile != null)
    {
        //Save the uploaded file to the disc.
        string savedFileName = "~/UploadedExcelDocuments/" + excelFile.FileName;
        excelFile.SaveAs(Server.MapPath(savedFileName));

        //Create a connection string to access the Excel file using the ACE provider.
        //This is for Excel 2007. 2003 uses an older driver.
        var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", Server.MapPath(savedFileName));

        //Fill the dataset with information from the Hoja1 worksheet.
        var adapter = new OleDbDataAdapter("SELECT * FROM [Hoja1$]", connectionString);
        var ds = new DataSet();
        adapter.Fill(ds, "results");
        DataTable data = ds.Tables["results"];

        //Create a new list of People.
        var people = new List<Person>();

        for (int i = 0; i < data.Rows.Count - 1; i++)
        {
            Person newPerson = new Person();
            newPerson.Id = data.Rows[i].Field<double?>("Id");
            newPerson.Name = data.Rows[i].Field<string>("Name");
            newPerson.LastName = data.Rows[i].Field<string>("LastName");
            newPerson.DateOfBirth = data.Rows[i].Field<DateTime?>("DateOfBirth");                    

            people.Add(newPerson);
        }

        return View("UploadComplete", people);
    }
    
    return RedirectToAction("Error", "Upload");                      
}

And here is the UploadComplete view:

@model IEnumerable<QuimizaReportes.Models.Person>

@{
    ViewBag.Title = "UploadComplete";
}

<h2>UploadComplete</h2>

<table>
    <tr>
        <th>ID</th>
        <th>
            Name
        </th>
        <th>
            LastName
        </th>
        <th>
            DateOfBirth
        </th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>@item.Id</td>
        <td>
            @item.Name
        </td>
        <td>
            @item.LastName
        </td>
        <td>
            @String.Format("{0:g}", item.DateOfBirth)
        </td>
    </tr>
}

</table>

We're using the ACE provider to read the information and then placing it into a DataTable. Then it's just a matter of getting the fields and setting them to our Model. Pre-tey good, if I say so myself. I didn't know it was going to be this easy, in fact I had heard horror stories.

That about wraps it up, if you have any question please write in the comments section and I'll try to clarify. Thanks for reading!