How to read Excel file in c# by connection string?

Question

Hi How to read the excel file and show its data in grid view ? I tried the ODBC provider its working but, it is happening win "Dns=EXCELF", how it will possible with connection sring ? I generate my Excel sheet connection string as :

Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Data Source=Excel Files;Initial Catalog=D:\Parallelminds\Tryouts\sample.xlsx

Is that wrong ? please guide me. which connection string i have to give there...?

Answer 1

It varies somewhat by version of Excel, but if this is pre-2007, you can find what you need here: http://connectionstrings.com/excel

Otherwise, browse http://connectionstrings.com/. It's there somewhere, I promise :).

Answer 2

This Excel Data Provider is very handy. I recently used it on one of my client's websites with a few minor customizations. If you look through the code you should be able to get a solid idea of how to query Excel from C#.

Just a warning: if Excel is not installed on the deployment machine then you will be restricted to parsing standard XLS files (Office thru 2003), and will not be able to read XLSX (Office 2007).

Answer 3

public string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders){
      return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;
      Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
       Filename.Replace("'", "''"),FirstRowContainsHeaders ? "Yes" : "No");
}

public string BuildExcel2007ConnectionString(string Filename, bool FirstRowContainsHeaders){
      return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source={0};Extended Properties=\"Excel 12.0;HDR={1}\";",
         Filename.Replace("'", "''"),FirstRowContainsHeaders ? "Yes" : "No");

}

private void ReadExcelFile(){
  string connStr = BuildExcel2007ConnectionString(@"C:\Data\Spreadsheet.xlsx", true);
  string query = @"Select * From [Sheet1$] Where Row = 2";
  System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr);

  conn.Open();
  System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn);
  System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();
  DataTable dt = new DataTable();
  dt.Load(dr);
  dr.Close();
  conn.Close(); 
}