Subscribe to Sergio Tapia - Lost in the GC.        RSS Feed
***** 2 Votes

C# and MVC3 - Uploading and parsing an Excel document is easier than it seems.

Icon 6 Comments
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:

Posted Image

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!

Posted Image

6 Comments On This Entry

Page 1 of 1

grimpirate Icon

25 February 2011 - 01:22 PM
I thought this was a blog about Marvel Vs. Capcom 3. I am now officially disappointed.
-1

Sergio Tapia Icon

25 February 2011 - 01:47 PM
That game is amazing. I love playing as Spiderman.
0

grimpirate Icon

25 February 2011 - 05:05 PM
There is no character greater than Captain America. Except maybe for the red Captain America.
0

Denis1 Icon

01 March 2011 - 12:57 PM

grimpirate, on 26 February 2011 - 12:05 AM, said:

There is no character greater than Captain America. Except maybe for the red Captain America.


OMG YESSH CAP AMERICA = OWNAGE
0

Munawwar Icon

17 April 2011 - 03:35 AM
Thanks sergio for this blog.
I initially thought that office 2007 is required for this. But after searching for many alternatives and reading a lot, I found that 2007 Office System Driver - Data Connectivity Components are available for download.
0

tylrwb Icon

26 June 2012 - 07:34 PM
Sergio,
I have implemented this in a site i am doing. It works great. however i need the data that is parsed to go into a database table. It worked and then when i went to the view to look at the data it wasn't there. Am i missing something? It shows up on the page but does not get put in the data table.

thanks much!
1
Page 1 of 1

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

2 user(s) viewing

2 Guests
0 member(s)
0 anonymous member(s)

About Me

Posted Image


Bienvenidos! I'm a USA ex-pat living in Bolivia for the past 10 years. Web development is my forte with a heavy lean for usability and optimization. I'm fluent in both English and Spanish. I guest write for the popular Python website Python Central. Visit my website.

Categories