How to move Excel data into Database in C#

Just recently, one of my project requires me to move all data from Excel format into the database, since the solution was really simple, I thought I’d share it.

Using the already provided library from Microsoft Office , we can easily manipulate the data inside each cell.

First, we need to add reference to the Excel Interop DLL (Microsoft.Office.Interop.Excel.DLL) , if you can’t find this , simply browse thru your microsoft office folder, and add excel.exe it already includes that DLL. Now you’re ready.

Add before the namespace: Using Microsoft.Office.Interop.Excel

Declare some private variables for later use:

private static Excel.Workbook MyBook = null; 
private static Excel.Application MyApp = null;
private static Excel.Worksheet MySheet = null;
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open("c:\worksheet.xls");
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; // how many rows

Then the easy part. Reading the contents of your spreadsheet.

List<Person> person new List<Person>();
for (int index = 2; index <= lastRow; index++)
{
    System.Array MyValues = (System.Array)MySheet.get_Range("A" + 
       index.ToString(), "D" + index.ToString()).Cells.Value;
    person.Add(new Person{
      Name = MyValues.GetValue(1,1).ToString(),
      Employment = MyValues.GetValue(1,2).ToString(),
      Email = MyValues.GetValue(1,3).ToString(),
      Phone= MyValues.GetValue(1,4).ToString()
    });
}

Then the inserting it into a database is much easier, since you now have the object filled with data (Person object) you can either pass this to a method that processes your CRUD or simple use it here if you don’t have any db layer. Here’s an example:

using (var db = DBContext())
{
   foreach (var p in person) {

   TBL_PERSON _person = new TBL_PERSON
   {
     Name = p.Name,
     Employment = p.Employment,
     Email = p.Email,
     Phone = p.Phone
    }
};

   db.TBL_PERSON.InsertOnSubmit(_person);
}

db.SubmitChanges();
}

Hope this helps out anyone who has the same task.

Solution adapted from: Nanda Kumar Chimtan
Working-with-Excel-Using-Csharp

Leave a Reply

Your email address will not be published. Required fields are marked *