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就可以了