1、Excel 连接字符串：String strCon = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source =“ + filename + “;Extended Properties=Excel 8.0;HDR=yes;IMEX=1“；中“HDR=yes;IMEX=1”起什么作用？连接串范例Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“D:My WorksAN45S01283.xls“;Persist Security Info=False;Extended Properties=“Excel 8.0;HDR
2、=YES;IMEX=1“excel 2000 2003 的 OleDb 连接串的格式如下： Provider=Microsoft.Jet.OleDb.4.0;Data Source=excel 文件路径;Extended Properties=Excel 8.0;HDR=YESexcel 2007 的 OleDb 的连接串的格式如下：Provider=Microsoft.Ace.OleDb.12.0;Data Source=excel 文件路径;Extended Properties=Excel 12.0;HDR=YESExcel2000-2003:OleDbConnection ExcelC
3、onn = 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;H
4、DR=YES;IMEX=1“);HDR=Yes，这代表第一行是标题，不做为数据使用； IMEX ( IMport EXport mode )设置IMEX 有三种模式：0 is Export mode1 is Import mode2 is Linked mode (full update capabilities)我这里特别要说明的就是 IMEX 参数了，因为不同的模式代表著不同的读写行为：当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“ 写入”用途。当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“ 读取”用途。当 IMEX=2
5、时为“连结模式”，这个模式开启的 Excel 档案可同时支援“ 读取”与“写入”用途。意义如下:0 -输出模式;1-输入模式 ;2-链接模式(完全更新能力)/ / OldDb Excel数据连接类/ class oleConnection/ / 静态连接方法，需传入文件路径参数/ / 文件路径/ public static DataSet Conn(string path)string strExcelSelect = “SELECT * FROM sheet1$“;string strExcelConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Sou
6、rce =“ + path + “;Extended Properties=“Excel 8.0;HDR=yes;IMEX=1“;/ Create the dataset and add the Categories table to it:DataSet myDataSet = new DataSet();OleDbConnection myExcelConn = null;trymyExcelConn = new OleDbConnection(strExcelConn);catch (Exception ex)Console.WriteLine(“Error: Failed to cre
7、ate a database connection. n0“, ex.Message);return null;tryOleDbCommand myExcelCommand = new OleDbCommand(strExcelSelect, myExcelConn);OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myExcelCommand);myExcelConn.Open();myDataAdapter.Fill(myDataSet, “sheet1$“);catch (Exception ex)Console.WriteLi
8、ne(“Error: Failed to retrieve the required data from the DataBase.n0“, ex.Message);return null;finallymyExcelConn.Close();return myDataSet;/*/ A dataset can contain multiple tables, so lets get them/ all into an array:DataTableCollection dta = myDataSet.Tables;foreach (DataTable dt in dta)Console.Wr
9、iteLine(“Found data table 0“, dt.TableName);/ The next two lines show two different ways you can get the/ count of tables in a dataset:Console.WriteLine(“0 tables in data set“, myDataSet.Tables.Count);Console.WriteLine(“0 tables in data set“, dta.Count);/ The next several lines show how to get infor
10、mation on/ a specific table by name from the dataset:Console.WriteLine(“0 rows in Categories table“, myDataSet.Tables“sheet1$“.Rows.Count);/ The column info is automatically fetched from the database,/ so we can read it here:Console.WriteLine(“0 columns in Categories table“, myDataSet.Tables“sheet1$
11、“.Columns.Count);DataColumnCollection drc = myDataSet.Tables“sheet1$“.Columns;int i = 0;foreach (DataColumn dc in drc)/ Print the column subscript, then the columns name/ and its data type:Console.WriteLine(“Column name0 is 1, of type 2“, i+, dc.ColumnName, dc.DataType);DataRowCollection dra = myDataSet.Tables“sheet1$“.Rows;foreach (DataRow dr in dra)/ Print the CategoryID as a subscript, then the CategoryName:Console.WriteLine(“CategoryName0 is 1“, dr0, dr1);*/
