#region 连接并查询Excel2003数据库 /// <summary> /// 连接并查询Excel2003数据库 /// </summary> protected void btnExcel2003_Click(object sender, EventArgs e) { string connStr = "provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|//Excel2003.xls;extended properties=excel 8.0"; OleDbConnection conn = new OleDbConnection(connStr); try { conn.Open(); lblExcel2003.Text = "成功打开Excel2003数据库!"; /* 在代码中引用工作表时,应将表名表示为“[工作表名$]”,遇到字段为数据库保留关键字时,给该字段名加上[]以示区别, 如定义select 语句时:string connStr=”select * from [login$] where username=’abc’ and [password]=’abc123’ ”; 如果在数据表中用数字作为文本类型数据时,则应在数字前加单引号将默认的数值强行设定为文本类型。 */ string sql = "select * from [UserInfo$] where UserName='abc'and [password]=123"; OleDbCommand cmd = new OleDbCommand(sql, conn); OleDbDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); lblExcel2003.Text += "--并且成功读取第1条记录:UserNaem=" + dr["UserName"] + " Pwd=" + dr["password"].ToString(); } else { lblExcel2003.Text += "--但没有读取到任何数据!"; } } catch (OleDbException ex) { lblExcel2003.Text = "打开Excel2003数据库文件失败!"; Response.Write("-----------------------------------------------------------------------------------------<br/>" + ex.StackTrace + "<br/>----------------------------------------------------------------------------------------------<br/>"); } finally { if (conn.State == ConnectionState.Open) conn.Close(); } } #endregion #region 连接并查询Excel2007数据库 /// <summary> /// 连接并查询Excel2007数据库 /// </summary> protected void btnExcel2007_Click(object sender, EventArgs e) { string connStr = "provider=Microsoft.Ace.Oledb.12.0;Data Source=|DataDirectory|//Excel2007.xlsx;extended properties=excel 12.0"; OleDbConnection conn = new OleDbConnection(connStr); try { conn.Open(); lblExcel2007.Text = "成功打开Excel2007数据库!"; /* 在代码中引用工作表时,应将表名表示为“[工作表名$]”,遇到字段为数据库保留关键字时,给该字段名加上[]以示区别, 如定义select 语句时:string connStr=”select * from [login$] where username=’abc’ and [password]=’abc123’ ”; 如果在数据表中用数字作为文本类型数据时,则应在数字前加单引号将默认的数值强行设定为文本类型。 */ string sql = "select * from [UserInfo$] where UserName='abc'and pwd='abc123'"; OleDbCommand cmd = new OleDbCommand(sql, conn); OleDbDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); lblExcel2007.Text += "--并且成功读取第1条记录:UserNaem=" + dr["UserName"] + " Pwd=" + dr["Pwd"].ToString(); } else { lblExcel2007.Text += "--但没有读取到任何数据!"; } } catch (OleDbException ex) { lblExcel2007.Text = "打开Excel2007数据库文件失败!"; Response.Write("-----------------------------------------------------------------------------------------<br/>" + ex.StackTrace + "<br/>----------------------------------------------------------------------------------------------<br/>"); } finally { if (conn.State == ConnectionState.Open) conn.Close(); } } #endregion