Excel导入SQL数据库

Dim strPath As String = File1.PostedFile.FileName

If strPath = \"\" Then

Response.Write(\" \") Response.End() End If

'定义连接字符串

Dim sConnectionString As String = \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" & strPath & \"; Extended Properties=Excel 8.0;\"

Try

Dim oleDbConnection As OleDbConnection = New OleDbConnection(sConnectionString) oleDbConnection.Open()

'获取excel表

Dim dataTable As DataTable

oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

'获取sheet名，其中(0)(1)...(N): 按名称排列的表单元素 Dim tableName As String = dataTable.Rows(0)(2).ToString().Trim() tableName = \"[\" & tableName.Replace(\" ' \

'利用SQL语句从Excel文件里获取数据

Dim query As String = \"SELECT 部门,全年任务,本年累计完成,上年同期完成,[完成全年任务的%],[可比增长%] FROM \" & tableName Dim dataset As DataSet = New DataSet()

Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)

oleAdapter.Fill(dataset, \"Rwb\")

'SQL数据库连接

Dim sqlcon As SqlClient.SqlConnection =

SqlClient.SqlConnection(\"server=(local);database=test;user id=sa;password=123\") sqlcon.Open()

'从excel文件获得数据后，插入记录到SQL Server的数据表 Dim dataTable1 As DataTable = New DataTable()

Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(\"SELECT 部门,全年任务, 本年累计完成,上年同期完成,[完成全年任务的%],[可比增长%] FROM Rwb \

Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)

=

New

sqlDA1.Fill(dataTable1)

Dim dataRow11 As DataRow

For Each dataRow11 In dataset.Tables(\"qssrzb\").Rows 'sql里数据dataRow1

Dim dataRow1 As DataRow = dataTable1.NewRow() dataRow1(\"部门\") = dataRow11(\"部门\")

dataRow1(\"全年任务\") = dataRow11(\"全年任务\")

dataRow1(\"本年累计完成\") = dataRow11(\"本年累计完成\") dataRow1(\"上年同期完成\") = dataRow11(\"上年同期完成\")

dataRow1(\"完成全年任务的%\") = dataRow11(\"完成全年任务的%\") dataRow1(\"可比增长%\") = dataRow11(\"可比增长%\") dataTable1.Rows.Add(dataRow1) Next

Console.WriteLine(\"新插入 \" & dataTable1.Rows.Count.ToString() & \" 条记录 \") sqlDA1.Update(dataTable1)

oleDbConnection.Close() Catch ex As Exception

Console.WriteLine(ex.ToString()) End Try