CODEWITHSUNDEEP
c#excel
Red Swan
Red Swan

Reputation: 15553

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

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...?

Upvotes: 2

Views: 29049

Answers (3)

KV Prajapati
KV Prajapati

Reputation: 94653

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(); 
}

Upvotes: 2

Nathan Taylor
Nathan Taylor

Reputation: 24606

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).

Upvotes: 0

Jerod Venema
Jerod Venema

Reputation: 44642

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 :).

Upvotes: 4

Related Questions