excel导入问题.doc

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=YES;IMEX=1excel 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 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);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 时为“连结模式”，这个模式开启的 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 Source = + 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; try myExcelConn = new OleDbConnection(strExcelConn); catch (Exception ex) Console.WriteLine(Error: Failed to create a database connection. n0, ex.Message); return null; try OleDbCommand myExcelCommand = new OleDbCommand(strExcelSelect, myExcelConn); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myExcelCommand); myExcelConn.Open(); myDataAdapter.Fill(myDataSet, sheet1$); catch (Exception ex) Console.WriteLine(Error: Failed to retrieve the required data from the DataBase.n0, ex.Message); return null; finally myExcelConn.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.WriteLine(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 information on / a specific table by name from the dataset: Console.WriteLine(0 rows in Categories table, myDataSet.Tablessheet1$.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.Tablessheet1$.Columns.Count); DataColumnCollection drc = myDataSet.Tablessheet1$.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 =