How to read Excel file in C # by connection string?
How to read an excel file and show its data in a grid view? I tried working with ODBC provider but it happens by winning "Dns = EXCELF", how is this possible when connecting sring? I am creating an Excel sheet connection string as:
Provider = MSDASQL.1; Persist Security Info = False; User ID = admin; Data source = Excel files; Start Directory = D: \ Parallelminds \ Tryouts \ sample.xlsx
It is not right? please guide me. what connection string should I give there ...?
source to share
It depends a bit on the version of Excel, but if it's before 2007 you can find what you need here: http://connectionstrings.com/excel
Otherwise, check out http://connectionstrings.com/ . Out there somewhere, I promise :).
source to share
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();
}
source to share
This Excel Data Provider is very handy. I recently used it on one of my client sites with a few minor tweaks. If you look at the code, you should be able to get a clear idea of how to query Excel from C #.
Just a warning: if Excel is not installed on the deployment machine, you will not be able to parse standard XLS files (Office thru 2003) and will not be able to read XLSX (Office 2007).
source to share