Extract Data from Excel File and Store in SQL Server database

Question

I am looking for advice on the best way to parse a Microsoft Excel file and update/store the data into a given SQL Server database. I using ASP.NET MVC so I plan on having a page/view take in an Excel spreadsheet and using that user given file I will need to use C# to parse the data from the columns and update the database based on matches with the spreadsheet column that contains the key column of the database table. The spreadsheet will always be in the same format so I will only need to handle on format. It seems like this could be a pretty common thing I am just looking for the best way to approach this before getting started. I am using Entity Framework in my current application but I don't have to use it.

I found this solution which seems like it could be a good option:

public IEnumerable<MyEntity> ReadEntitiesFromFile( IExcelDataReader reader, string filePath )
{
   var myEntities = new List<MyEntity>();
   var stream = File.Open( filePath, FileMode.Open, FileAccess.Read );

   using ( var reader = ExcelReaderFactory.CreateOpenXmlReader( stream ) )
   {
     while ( reader.Read() )
     {
        var myEntity = new MyEntity():
        myEntity.MyProperty1 = reader.GetString(1);
        myEntity.MyProperty2 = reader.GetInt32(2);

        myEntites.Add(myEntity);
      }
   }

   return myEntities;
}

Here is an example of a what a file might look like (Clock# is the key)

So given a file in this format I want to match the user to the data table record using the clock # and update the record with each of the cells information. Each of the columns in the spreadsheet have a relatable column in the data table. All help is much appreciated.

Solution

You can use the classes in the namespace Microsoft.Office.Interop.Excel, which abstracts all the solution you found. Instead of me rewriting it, you can check out this article: http://www.codeproject.com/Tips/696864/Working-with-Excel-Using-Csharp.

Better yet, why not bypass the middle man? You can use an existing ETL tool, such as Pentaho, or Talend, or something to go straight from Excel to your database. These types of tools often offer a lot of customization, and are fairly straightforward to use. I've used Pentaho quite a lot for literally what you're describing, and it saved me the head ache of writing the code myself. Unless you want to/need to write it yourself, I think the latter is the best approach.