Using LINQ to Excel - Easy Examples, Easy to Follow

I just wanted to demonstrate how easy and efficient using LINQ to Excel is, when compared to the old fashioned OLEDB way of querying data from a spreadsheet that was uploaded.

Whenever you have a need to upload an Excel file (any kind of Excel file, for that matter), LINQ to Excel makes the whole process extremely easy and efficient. All you have to do is either install LINQ to Excel using Nuget in Visual Studio, OR just manually add the LinqToExcel.dll library to your references from Github (https://github.com/paulyoder/LinqToExcel#welcome-to-the-linqtoexcel-project).  This link is the official “go-to” for LINQ to Excel.  It contains all of the documentation, several examples that I didn’t include, and several tutorials.  For anything that you can’t find in this post, please visit that page.

Once you have this added to your solution, you’re ready to start.  Here’s some sample code on how to work with spreadsheets in ASP.NET MVC4.  Please note that this code is a very simple implementation of the library, but this will give you a solid baseline.

1. The first thing you have to do is create your file upload control in your view.  It’s important that you wrap your control in a BeginForm method using Razor syntax (see below).  This tells this particular page that we want to post data back to the server, in the form of a file.
   
   @using (Html.BeginForm("ReadExcel", "Upload", FormMethod.Post, new { enctype = "multipart/form-data" })) { <br style="clear: both;" /> <input id="excelUpload" type="file" name="excelUpload" /> <input type="submit" name="Submit" /> }

   1 2 3 4 5 6

   @using (Html.BeginForm("ReadExcel", "Upload", FormMethod.Post, new { enctype = "multipart/form-data" })) {     <br style="clear: both;" />     <input id="excelUpload" type="file" name="excelUpload" />     <input type="submit" name="Submit" /> }

   
   Notice the BeginForm method.  The first parameter is the Action Name, the second is the Controller Name, the third is the FormMethod (POST, GET, etc.), and the last are any HTML attributes.  We have to have the enctype defined here as multipart/form-data since we’re uploading an Excel file.
2. Now, we go to the ReadExcel Action Method specified in the BeginForm method above.  This is in your Controller.
   
   public ActionResult ReadExcel() { HttpPostedFileBase hpf = this.Request.Files[0]; UploadContext.ImportExcelXLS(hpf); return RedirectToAction("Confirm", "Upload"); }

   1 2 3 4 5 6

   public ActionResult ReadExcel() {     HttpPostedFileBase hpf = this.Request.Files[0];     UploadContext.ImportExcelXLS(hpf);     return RedirectToAction("Confirm", "Upload"); }

   
   Notice that I must use the type “HttpPostedFileBase”.  Request.Files is a very old way to retrieve posted files from the client, but it works.  So, we’re creating an HttpPostedFileBase, and then passing that into my method “ImportExcelXLS(HttpPostedFileBase hpf)”.
3. Finally, navigating to the final piece of this puzzle, I’ll take you to the ImportExcelEXL method.
   
   public static void ImportExcelXLS(HttpPostedFileBase file) { var exc = new ExcelQueryFactory(file.FileName); var data = from c in excel.Worksheet("All Injuries") select c; }

   1 2 3 4 5

   public static void ImportExcelXLS(HttpPostedFileBase file) {     var exc = new ExcelQueryFactory(file.FileName);     var data = from c in excel.Worksheet("All Injuries") select c; }

   
   It’s important to note that you need the
   
   using LinqToExcel;

   1	using LinqToExcel;

   
   reference at the top of your file.  This part is easy to forget, but simple to resolve.  But, once you have this, you are ready to exploit the full functionality magic.  file.FileName is the local path of the file that is being uploaded.  So, if you’re uploading from your local machine, the path will look something like “C:\\BlahBlahBlah\file.xlsx”.  We create an ExcelQueryFactory called “exc” and pass in the file name.  Then, we can query on the “exc” object, as shown in the second line.  The possibilities are endless.  If you have any questions, please feel free to visit the site I listed in the beginning of this post, as that’s the official code repository for LINQ to Excel.