由Excel作为数据源导入SQL

引用Excel组件，完成导入功能

 Dim cn As New SqlConnection("server=localhost;database=****;uid=****;pwd=****;")
        Dim cmdInsert As New SqlCommand
        Dim cmdselect As New SqlCommand

        ' Add the Insert command
        cmdInsert.CommandText = "INSERT Storage(ST_ItemID,ST_Company,ST_CType,ST_CName,ST_PType,ST_INum,ST_IPrice,ST_ITotal,ST_ONum,ST_OPrice,ST_OTotal,ST_TNum,ST_TPrice,ST_TTotal,ST_MCin,ST_MCout,ST_MTin,ST_MTout,ST_Date,User_Names) VALUES(@ST_ItemID,@ST_Company,@ST_CType,@ST_CName,@ST_PType,@ST_INum,@ST_IPrice,@ST_ITotal,@ST_ONum,@ST_OPrice,@ST_OTotal,@ST_TNum,@ST_TPrice,@ST_TTotal,@ST_MCin,@ST_MCout,@ST_MTin,@ST_MTout,@ST_Date,@User_Names)"
        cmdInsert.Parameters.Add("@ST_ItemID", SqlDbType.VarChar, 20, "ST_ItemID")
        cmdInsert.Parameters.Add("@ST_Company", SqlDbType.VarChar, 100, "ST_Company")
        cmdInsert.Parameters.Add("@ST_CType", SqlDbType.VarChar, 50, "ST_CType")
        cmdInsert.Parameters.Add("@ST_CName", SqlDbType.VarChar, 100, "ST_CName")
        cmdInsert.Parameters.Add("@ST_PType", SqlDbType.VarChar, 50, "ST_PType")
        cmdInsert.Parameters.Add("@ST_INum", SqlDbType.BigInt, 8, "ST_INum")
        cmdInsert.Parameters.Add("@ST_IPrice", SqlDbType.Money, 8, "ST_IPrice")
        cmdInsert.Parameters.Add("@ST_ITotal", SqlDbType.Money, 8, "ST_ITotal")
        cmdInsert.Parameters.Add("@ST_ONum", SqlDbType.BigInt, 8, "ST_ONum")
        cmdInsert.Parameters.Add("@ST_OPrice", SqlDbType.Money, 8, "ST_OPrice")
        cmdInsert.Parameters.Add("@ST_OTotal", SqlDbType.Money, 8, "ST_OTotal")
        cmdInsert.Parameters.Add("@ST_TNum", SqlDbType.BigInt, 8, "ST_TNum")
        cmdInsert.Parameters.Add("@ST_TPrice", SqlDbType.Money, 8, "ST_TPrice")
        cmdInsert.Parameters.Add("@ST_TTotal", SqlDbType.Money, 8, "ST_TTotal")
        cmdInsert.Parameters.Add("@ST_MCin", SqlDbType.Money, 8, "ST_MCin")
        cmdInsert.Parameters.Add("@ST_MCout", SqlDbType.Money, 8, "ST_MCout")
        cmdInsert.Parameters.Add("@ST_MTin", SqlDbType.Money, 8, "ST_MTin")
        cmdInsert.Parameters.Add("@ST_MTout", SqlDbType.Money, 8, "ST_MTout")
        cmdInsert.Parameters.Add("@ST_Date", SqlDbType.DateTime, 8, "ST_Date")
        cmdInsert.Parameters.Add("@User_Names", SqlDbType.VarChar, 50, "User_Names")
      
        'add the select command
        cmdselect.CommandText = "SELECT ST_Date,ST_ItemID,ST_Company,ST_CType,ST_CName,ST_PType,ST_INum,ST_IPrice,ST_ITotal,ST_ONum,ST_OPrice,ST_OTotal,ST_TNum,ST_TPrice,ST_TTotal,ST_MCin,ST_MCout,ST_MTin,ST_MTout,User_Names FROM Storage"

        Dim da As New SqlDataAdapter
        cmdselect.Connection = cn
        cmdInsert.Connection = cn
        da.SelectCommand = cmdselect
        da.InsertCommand = cmdInsert
        'da.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da.AcceptChangesDuringFill = True
        Dim myapp As New Excel.Application
        Dim xlsbook1 As Excel.Workbook
        xlsbook1 = myapp.Workbooks.Open("c:\2.xls")
        Dim xlssheet1 As Excel.Worksheet
        xlssheet1 = xlsbook1.Sheets("sheet1")

        Dim ds As New DataSet
        da.Fill(ds, "Storage")
        Dim mytable As System.Data.DataTable = ds.Tables("Storage")

        Dim l As Integer = 1

        Dim flag As Boolean = True

        'While (Convert.ToString(xlssheet1.Cells(l, 1).Value)) <> ""
        '    l += 1
        'End While

        'activesheet.usedrange.row(+activesheet.usedrange.rows.count - 1)

        l = xlssheet1.UsedRange.Row + xlssheet1.UsedRange.Rows.Count - 1


        Dim m As Integer = 1
        While flag And m < l + 1
            If (Convert.ToString(xlssheet1.Cells(m, 1).Value)) = "" Or (Convert.ToString(xlssheet1.Cells(m, 2).Value)) = "" Or (Convert.ToString(xlssheet1.Cells(m, 3).Value)) = "" Then
                flag = False
            End If
            m += 1
        End While

        If flag Then
            Dim i As Integer
            For i = 1 To l - 1
                Dim mydatarow As DataRow = mytable.NewRow

                mydatarow.Item(0) = xlssheet1.Cells(i, 1).Value
                mydatarow.Item(1) = xlssheet1.Cells(i, 2).Value
                mydatarow.Item(2) = xlssheet1.Cells(i, 3).Value

                If xlssheet1.Cells(i, 4).Value <> "" Then
                    mydatarow.Item(3) = xlssheet1.Cells(i, 4).Value
                Else
                    mydatarow.Item(3) = ""
                End If

                If xlssheet1.Cells(i, 5).Value <> "" Then
                    mydatarow.Item(4) = xlssheet1.Cells(i, 5).Value
                Else
                    mydatarow.Item(4) = ""
                End If

               
                If xlssheet1.Cells(i, 6).Value <> "" Then
                    mydatarow.Item(5) = xlssheet1.Cells(i, 6).Value
                Else
                    mydatarow.Item(5) = ""
                End If

                If xlssheet1.Cells(i, 7).Value Then
                    mydatarow.Item(6) = xlssheet1.Cells(i, 7).Value
                Else
                    mydatarow.Item(6) = 0
                End If

                If xlssheet1.Cells(i, 8).Value Then
                    mydatarow.Item(7) = xlssheet1.Cells(i, 8).Value
                Else
                    mydatarow.Item(7) = 0
                End If

                If xlssheet1.Cells(i, 9).Value Then
                    mydatarow.Item(8) = xlssheet1.Cells(i, 9).Value
                Else
                    mydatarow.Item(8) = 0
                End If

                If xlssheet1.Cells(i, 10).Value Then
                    mydatarow.Item(9) = xlssheet1.Cells(i, 10).Value
                Else
                    mydatarow.Item(9) = 0
                End If

                If xlssheet1.Cells(i, 11).Value Then
                    mydatarow.Item(10) = xlssheet1.Cells(i, 11).Value
                Else
                    mydatarow.Item(10) = 0
                End If

                If xlssheet1.Cells(i, 12).Value Then
                    mydatarow.Item(11) = xlssheet1.Cells(i, 12).Value
                Else
                    mydatarow.Item(11) = 0
                End If

                If xlssheet1.Cells(i, 13).Value Then
                    mydatarow.Item(12) = xlssheet1.Cells(i, 13).Value
                Else
                    mydatarow.Item(12) = 0
                End If

                If xlssheet1.Cells(i, 14).Value Then
                    mydatarow.Item(13) = xlssheet1.Cells(i, 14).Value
                Else
                    mydatarow.Item(13) = 0
                End If

                If xlssheet1.Cells(i, 15).Value Then
                    mydatarow.Item(14) = xlssheet1.Cells(i, 15).Value
                Else
                    mydatarow.Item(14) = 0
                End If

                If xlssheet1.Cells(i, 16).Value Then
                    mydatarow.Item(15) = xlssheet1.Cells(i, 16).Value
                Else
                    mydatarow.Item(15) = 0
                End If

                If xlssheet1.Cells(i, 17).Value Then
                    mydatarow.Item(16) = xlssheet1.Cells(i, 17).Value
                Else
                    mydatarow.Item(16) = 0
                End If

                If xlssheet1.Cells(i, 18).Value Then
                    mydatarow.Item(17) = xlssheet1.Cells(i, 18).Value
                Else
                    mydatarow.Item(17) = 0
                End If

                If xlssheet1.Cells(i, 19).Value Then
                    mydatarow.Item(18) = xlssheet1.Cells(i, 19).Value
                Else
                    mydatarow.Item(18) = 0
                End If


                mydatarow.Item(19) = "AutoFill"

                mytable.Rows.Add(mydatarow)
            Next
        Else
            ShowMessage("添加未成功,请检查Excel文件格式是否有错误!")
        End If
        da.Update(ds, "Storage")

Tips:1赋予Excel组件IIS足够的启动权限
2赋予Excel文件IIS足够的访问权限