如何通过连接字符串读取c#中的Excel文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1373522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read Excel file in c# by connection string?
提问by Red Swan
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 :
如何读取 excel 文件并在网格视图中显示其数据？我尝试了 ODBC 提供程序的工作，但是它正在发生，赢得“Dns=EXCELF”，连接 sring 怎么可能？我将 Excel 工作表连接字符串生成为：
Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Data Source=Excel Files;Initial Catalog=D:\Parallelminds\Tryouts\sample.xlsx
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...?
错了吗？请指导我。我必须在那里提供哪个连接字符串...？
采纳答案by jvenema
It varies somewhat by version of Excel, but if this is pre-2007, you can find what you need here: http://connectionstrings.com/excel
它因 Excel 版本而有所不同，但如果这是 2007 年之前的版本，您可以在此处找到所需内容：http: //connectionstrings.com/excel
Otherwise, browse http://connectionstrings.com/. It's there somewhere, I promise :).
否则，浏览http://connectionstrings.com/。它在某处，我保证:)。
回答by Nathan Taylor
This Excel Data Provideris 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#.
这个Excel 数据提供程序非常方便。我最近在我客户的一个网站上使用了它，并进行了一些小的自定义。如果您查看代码，您应该能够对如何从 C# 查询 Excel 有一个清晰的了解。
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).
只是一个警告：如果部署计算机上未安装 Excel，那么您将被限制为解析标准 XLS 文件（Office 到 2003），并且将无法读取 XLSX（Office 2007）。
回答by adatapost
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();
}
