注意:1、电脑需要下载相关的驱动如:2007 Office system 驱动程序:数据连接组件AccessDatabaseEngine,csdn下载资源处可以找到。
2003、2007、2010的EXCEL版本定义稍有区别如下:
string connectString2003 = string.Format("provider = microsoft.jet.oledb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", fName);//2003
string connectString2007 = string.Format("provider = microsoft.ace.oledb.12.0;data source={0};Extended Properties='Excel 12.0;HDR=No;IMEX=1'", fName);//2007
string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", fName);//2010
List<string> arratlist = new List<string>();
DataSet ds = new DataSet();//设备唯一标识excel表
DataTable tb1 = new DataTable();//获取excel表格
#region excel文件导入
OleDbConnection myConn = null;
openFileDialog1.Filter = "xls files(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.Title = "选择目标文件";
try
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
if (arratlist != null)
{
arratlist.Clear();
}
string fName = openFileDialog1.FileName;//获取文件名
OleDbDataAdapter myCommand = null;
//创建一个数据链接
string connectString2003 = string.Format("provider = microsoft.jet.oledb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", fName);//2003
string connectString2007 = string.Format("provider = microsoft.ace.oledb.12.0;data source={0};Extended Properties='Excel 12.0;HDR=No;IMEX=1'", fName);//2007
string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", fName);//2010
if (fName.ToLower().Contains(".xlsx"))
{
myConn = new OleDbConnection(strCon);
}
else
{ myConn = new OleDbConnection(connectString2003); }
string strExcel = "select * from [sheet1$]";
myConn.Open();
myCommand = new OleDbDataAdapter(strExcel, myConn);
myCommand.Fill(ds, "table1");
myConn.Close();
tb1 = ds.Tables[0];
tbequ.Text = tb1.Rows[row]["DSN1"].ToString();// 读取表格内容,row为第几行的值,"DSN1"为列名
}
}
#endregion