Retrieve Data From Excel file Using C#

In this blog you will learn how to retrieve data from Excel file using C#.

Two different types are there to fetch the data from the Excel file.

1. Using OLeDb Connection
2. Using Excel Reference Object

Also we will define, what are the advantages and disadvantages of both the processes.

1. Using OleDb Connection
   

   string cs=@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\ContactDetailsofGICEmployee.xls';Extended Properties=Excel 8.0;";
   DataTable EmployeeTable=new DataTable();
   OleDbConnection con=new OleDbConnection(cs);
   OleDbCommand cmd=new OleDbCommand("Select * from [sheet1$]",con);
   OleDbDataAdapter oda=new OleDbDataAdapter(cmd);
   oda.Fill(EmployeeTable);
   return EmployeeTable;

   Advantages: Simple to use and same like SQL coding.
   
   Disadvantages: Excel should contain the data in table format for Insert and Update commands to use.
   
   
2. Using Excel Reference Object
   

   Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
   Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"D:\ContactDetailsofGICEmployee.xls", 0, true, 5, "", "", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
   Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
   Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)theWorkbook.Worksheets.get_Item(1);
   Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange;
   int r=range.Rows.Count;
   dt.Columns.Add("EmpCode");
   dt.Columns.Add("EmpName");
   dt.Columns.Add("Email");
   dt.Columns.Add("ContactNo");
   for(int i=1;i<=r;i++)
   {
      DataRow dr=dt.NewRow();
      dr["EmpCode"]= Convert.ToString((worksheet.Cells[i, 1] as Microsoft.Office.Interop.Excel.Range).Value2);
      dr["EmpName"]= Convert.ToString((worksheet.Cells[i, 2] as Microsoft.Office.Interop.Excel.Range).Value2);
      dr["Email"]= Convert.ToString((worksheet.Cells[i, 3] as Microsoft.Office.Interop.Excel.Range).Value2);
      dr["Cont"]= Convert.ToString((worksheet.Cells[i, 4] as Microsoft.Office.Interop.Excel.Range).Value2);
      dt.Rows.Add(dr);
   }
   return dt;

   Advantages: Dynamic Row and Column Index can be accessed i.e no need to have data in excel in tabular format.
   
   Disadvantages: Coding is difficult and requires to create the reference for many types thus memory allocation will be high.