项目中用到Excel上传到Sql数据库
项目中经常会用到Excel上传到数据库中的功能或者读取一个Excel在GridView中显示出来的情况。
基本原理就是获取Excel的连接，读取Excel的数据到DataSet。 根据DataSet的结果来显示内容或者把DataSet的结果更新到数据库中。
以下就贴出代码并加上简单的注释。
Imports System.Data
Imports System.Data.SqlClient '连接SQLSERVER数据库
Imports System.Data.OleDb '连接Excel工作表
Partial Class Default_Excel
Inherits System.Web.UI.Page
Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
Dim fup As FileUpload
Dim strPath As String = ""
Dim i As Integer = 0
Dim dataTable As New DataTable
'获取上传控件
fup = Me.form1.FindControl("FileUpload1")
strPath = fup.PostedFile.FileName
If strPath = "" Then
Response.Write(" <script> alert( '请先选择正确的Excel文件导入！ ') </script> ")
Response.End()
End If
'定义Excel连接字符串
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表
dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
'获取sheet名，其中(0)(1)...(N): 按名称排列的表单元素
'固定是读取一个工作表
Dim tableName As String = dataTable.Rows(0)(2).ToString().Trim()
tableName = "[" & tableName.Replace(" ' ", " ") & "]"
'以下SELECT 中的字段名要和Excel工作表中的一致
Dim query As String = "SELECT 字段名1,字段名2, 字段名3,字段名4,字段名5 FROM " & tableName
Dim dataset As DataSet = New DataSet()
Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)
oleAdapter.Fill(dataset, "Rwb")
'连接SQLSERVER数据库
Dim sqlcon As New SqlConnection(SqlOpe.ConnStr)
sqlcon.Open()
'从excel文件获得数据后，插入记录到SQL Server的数据表
Dim dataTable1 As DataTable = New DataTable()
'以下字段名是数据库中 表中的字段名称
Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT YEARS, PERIOD, WERKS, MATNR, BISMT,VERSION , REQQTY FROM ProdS_ForeCast WHERE 1<>1 ", sqlcon)
Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)
sqlDA1.Fill(dataTable1)
Dim dataRow11 As DataRow
For Each dataRow11 In dataset.Tables("Rwb").Rows
'sql里数据dataRow1
Dim dataRow1 As DataRow = dataTable1.NewRow()
dataRow1("YEARS") = dataRow11("字段名1")
dataRow1("PERIOD") = dataRow11("字段名2")
dataRow1("WERKS") = dataRow11("字段名3")
dataRow1("MATNR") = dataRow11("字段名4")
dataRow1("BISMT") = dataRow11("字段名5")
dataTable1.Rows.Add(dataRow1)
Next
Response.Write(" <script> alert( '一共导入 " & dataset.Tables("Rwb").Rows.Count.ToString() & " 条记录 ') </script> ")
sqlDA1.Update(dataTable1)
oleDbConnection.Close()
Catch ex As Exception
Console.WriteLine(ex.ToString())
End Try
End Sub
End Class
如果需要在GridView中显示将DataSet的结果绑定到GridView的DataSource就可以了
