C# .NET OLEDB 访问Excel2000-2003, Excel2007的不同连接字符串格式

来源：互联网发布：苏大网络缴费平台编辑：程序博客网时间：2024/05/17 19:59

连接串范例

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="D:\My Works\AN45S01283.xls";Persist Security Info=False;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"

excel 2000 ~ 2003的 OleDb 连接串的格式如下：
Provider=Microsoft.Jet.OleDb.4.0;Data Source='excel文件路径';Extended Properties='Excel 8.0;HDR=YES'

excel 2007 的 OleDb 的连接串的格式如下：

Provider=Microsoft.Ace.OleDb.12.0;Data Source='excel文件路径';Extended Properties='Excel 12.0;HDR=YES'

Excel2000-2003:

OleDbConnection ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + FilePath + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");

Excel2007:
OleDbConnection ExcelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FilePath + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'");

完整代码示例：

public void Method2()

{

string cStr = "Provider=Microsoft.ACE.OLEDB.12.0; "

+ "Data Source=C:\\users\\<yourname>\\documents\\Book1.xlsx; "

+ "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;ISAM=1;\"";

OleDbConnection conn = new OleDbConnection(cStr);

conn.Open();

// Select the data from Sheet1 of the workbook.

string stmt = "select * from [Sheet1$]";

OleDbDataAdapter ada = new OleDbDataAdapter(stmt, conn);

DataSet ds = new DataSet();

ada.Fill(ds);

dataGridView1.DataSource = ds.Tables[0].DefaultView;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

conn.Close();

}

相关链接：http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx