Post
Ask Question

How to Convert Excel to DataTable in C#

There are many ways available to convert excel file to DataTable, I found OLEDB is best way to convert excel file to DataTable.

This is the connection String I have used:

  1. OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';")  

 

This function will convert excel file to DataTable. You need to pass Excel file path and it will return DataTable.

  1. public static DataTable ConvertExcelToDataTable(string FileName)  
  2. {  
  3.     DataTable dtResult = null;  
  4.     int totalSheet = 0; //No of sheets on excel file  
  5.     using(OleDbConnection objConn = new OleDbConnection(@  
  6.     "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))  
  7.     {  
  8.         objConn.Open();  
  9.         OleDbCommand cmd = new OleDbCommand();  
  10.         OleDbDataAdapter oleda = new OleDbDataAdapter();  
  11.         DataSet ds = new DataSet();  
  12.         DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  13.         string sheetName = string.Empty;  
  14.         if (dt != null)  
  15.         {  
  16.             var tempDataTable = (from dataRow in dt.AsEnumerable()  
  17.             where!dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")  
  18.             select dataRow).CopyToDataTable();  
  19.             dt = tempDataTable;  
  20.             totalSheet = dt.Rows.Count;  
  21.             sheetName = dt.Rows[0]["TABLE_NAME"].ToString();  
  22.         }  
  23.         cmd.Connection = objConn;  
  24.         cmd.CommandType = CommandType.Text;  
  25.         cmd.CommandText = "SELECT * FROM [" + sheetName + "]";  
  26.         oleda = new OleDbDataAdapter(cmd);  
  27.         oleda.Fill(ds, "excelData");  
  28.         dtResult = ds.Tables["excelData"];  
  29.         objConn.Close();  
  30.         return dtResult; //Returning Dattable  
  31.     }  
  32. }  

 

Note

If you want to host in server, OLEDB driver need to install on server machine.