How to Import Data from a Excel Sheet into a DataTable

Below method will import data from a excel sheet into a DataTable. You need to import three name space.

System.Data.SqlClient;
System.Data;
System.Data.OleDb;

We are passing two parameters.

TabName - The Tab name in Excel sheet.
excelConnStr - OLEDB connection string. Following connection works for Office 2007.

string FilePath = "~/Files/MyFile.xlsx";
String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Server.MapPath(FilePath) + ";Extended Properties=Excel 12.0;";

public static DataTable GetExcelData(string TabName, string excelConnStr)
{
   using (OleDbConnection excelConn = new OleDbConnection(excelConnStr))
{
  excelConn.Open();
  string query = string.Empty;
  if (TabName == "Management$")
   {
    query = "select * from [" + TabName + "]";
   }
  else if (TabName == "Business$")
   {
    query = "select * from [" + TabName + "]";
   }
  else if (TabName == "Design$")
  {
    query = "select * from [" + TabName + "]";
  }
  using (OleDbCommand excelCommand = new OleDbCommand(query, excelConn))
  {
    using (OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter())
   {

   DataTable dtPatterns = new DataTable();
   excelDataAdapter.SelectCommand = excelCommand;
   excelDataAdapter.Fill(dtPatterns);
   return dtPatterns;
   }
  }
 }

}