Excel導(dǎo)入SQLServer的三種方案

1、方案一：通過OleDB方式獲取Excel文件的數(shù)據(jù)，然后通過DataSet中轉(zhuǎn)到SQLServeropenFileDialog=newOpenFileDialog();openFileDialog.Filter=Excelfiles(*.xls)|*.xls;if(openFileDialog.ShowDialog()=DialogResult.OK)FileInfofileInfo=newFileInfo(openFileDialog.FileName);stringfilePath=fileInfo.FullName;stringconnExcel=Provider=Microsoft.J

2、et.OLEDB.4.0;DataSource=+filePath+;ExtendedProperties=Excel8.0;tryOleDbConnectionoleDbConnection=newOleDbConnection(connExcel);oleDbConnection.Open();/獲取excel表DataTabledataTable=oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);/獲取sheet名，其中01.N:按名稱排列的表單元素stringtableName=dataTable.Rows

3、02.ToString().Trim();tableName=+tableName.Replace(,)+;利用SQL語句從Excel文件里獲取數(shù)據(jù)/stringquery=SELECTclassDate,classPlace,classTeacher,classTitle,classIDFROM+tableName;stringquery=SELECT日期,開課城市,講師,課程名稱,持續(xù)時(shí)間FROM+tableName;dataSet=newDataSet();/OleDbCommandoleCommand=newOleDbCommand(query,oleDbConnection);/Ol

4、eDbDataAdapteroleAdapter=newOleDbDataAdapter(oleCommand);OleDbDataAdapteroleAdapter=newOleDbDataAdapter(query,connExcel);oleAdapter.Fill(dataSet,gch_Class_Info);/dataGrid1.DataSource=dataSet;/dataGrid1.DataMember=tableName;dataGrid1.SetDataBinding(dataSet,gch_Class_Info);從excel文件獲得數(shù)據(jù)后，插入記錄到SQLServer

5、的數(shù)據(jù)表DataTabledataTable1=newDataTable();SqlDataAdaptersqlDA1=newSqlDataAdapter(SELECTclassID,classDate,classPlace,classTeacher,classTitle,durativeDateFROMgch_Class_Info,sqlConnection1);SqlCommandBuildersqlCB1=newSqlCommandBuilder(sqlDA1);sqlDA1.Fill(dataTable1);foreach(DataRowdataRowindataSet.Tablesg

6、ch_Class_Info.Rows)DataRowdataRow1=dataTable1.NewRow();dataRowlclassDate=dataRow日期;dataRowlclassPlace=dataRow開課城市;dataRow1classTeacher=dataRow講師;dataRow1classTitle=dataRow課程名稱;dataRow1durativeDate=dataRow持續(xù)時(shí)間;dataTable1.Rows.Add(dataRow1);Console.WriteLine(新插入+dataTable1.Rows.Count.ToString()+條記錄);s

7、qlDA1.Update(dataTable1);oleDbConnection.Close();catch(Exceptionex)Console.WriteLine(ex.ToString();方案二：直接通過SQL語句執(zhí)行SQLServer的功能函數(shù)將Excel文件轉(zhuǎn)換到SQLServer數(shù)據(jù)庫OpenFileDialogopenFileDialog=newOpenFileDialog();openFileDialog.Filter=Excelfiles(*.xls)|*.xls;SqlConnectionsqlConnection1=null;if(openFileDialog.Sho

8、wDialog()=DialogResult.OK)stringfilePath=openFileDialog.FileName;sqlConnection1=newSqlConnection();sqlConnection1.ConnectionString=server=(local);integratedsecurity=SSPI;initialcatalog=Library;/importexcelintoSQLServer2000/*stringimportSQL=SELECT*intolive41FROMOpenDataSource+(Microsoft.Jet.OLEDB.4.0

9、,DataSource=+E:022n.xls+;UserID=;Password=;Extendedproperties=Excel5.0).Sheet1$;*/exportSQLServer2000intoexcelstringexportSQL=EXECmaster.xp_cmdshellbcpLibrary.dbo.live41out+filePath+-c-q-S+-U+-P+;trysqlConnection1.Open();/SqlCommandsqlCommand1=newSqlCommand();/sqlCommand1.Connection=sqlConnection1;/

10、sqlCommand1.CommandText=importSQL;/sqlCommand1.ExecuteNonQuery();/MessageBox.Show(importfinish!);SqlCommandsqlCommand2=newSqlCommand();sqlCommand2.Connection=sqlConnection1;sqlCommand2.CommandText=exportSQL;sqlCommand2.ExecuteNonQuery();MessageBox.Show(exportfinish!);catch(Exceptionex)MessageBox.Sho

11、w(ex.ToString();if(sqlConnection1!=null)sqlConnection1.Close();sqlConnection1=null;方案三：通過到入Excel的VBAdll,通過VBA接口獲取Excel數(shù)據(jù)到DataSetOpenFileDialogopenFile=newOpenFileDialog();openFile.Filter=Excelfiles(*.xls)|*.xls;ExcelIOexcelio=newExcelIO();if(openFile.ShowDialog()=DialogResult.OK)if(excelio!=null)exc

12、elio.Close();excelio=newExcelIO(openFile.FileName);object,range=excelio.GetRange();excelio.Close();DataSetds=newDataSet(xlsRange);intx=range.GetLength(0);inty=range.GetLength(1);DataTabledt=newDataTable(xlsTable);DataRowdr;DataColumndc;ds.Tables.Add(dt);for(intc=1;c=y;c+)dc=newDataColumn();dt.Columns.Add(dc);objecttemp=ne