Excel导入SQLServer的三种方案

1、方案一：通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到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文件里获取数据/stringquery=SELECTclassDate,classPlace,classTeacher,classTitle,classIDFROM+tableName;stringquery=SELECT日期,开课城市,讲师,课程名称,持续时间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文件获得数据后，插入记录到SQLServer

5、的数据表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持续时间;dataTable1.Rows.Add(dataRow1);Console.WriteLine(新插入+dataTable1.Rows.Count.ToString()+条记录);s

7、qlDA1.Update(dataTable1);oleDbConnection.Close();catch(Exceptionex)Console.WriteLine(ex.ToString();方案二：直接通过SQL语句执行SQLServer的功能函数将Excel文件转换到SQLServer数据库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数据到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