Excel的数据导入到sql中

Question

之前是用DTS包做的，现在想将包实现的功能用vb.net实现。。

Answer 1

 Dim name As String = "size_" & DateTime.Now.ToString("ddHHmmss") + "." & hz
                Dim filename As String = Server.MapPath("xls/" + name)
                File1.PostedFile.SaveAs(filename)
                File1.PostedFile.ContentType.ToString()                '建立EXCEL连接，读入数据  
                Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & filename & "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"
                Dim da As New Data.OleDb.OleDbDataAdapter("SELECT 款号,规格,数量 FROM [Sheet1$]", strConn)
                Dim ds As New Data.DataSet
                ds = New Data.DataSet
                da.Fill(ds)
                Dim tb As New Data.DataTable
                tb = ds.Tables(0)                If tb.Rows.Count > 0 Then                    Dim i As Integer
                    For i = 0 To tb.Rows.Count - 1                        Try
                            Dim bm_cj, size As String
                            Dim quantity As Integer
                            bm_cj = tb.Rows.Item(i).Item(0)
                            size = tb.Rows.Item(i).Item(1)
                            quantity = tb.Rows.Item(i).Item(2)                            If quantity <= 0 Then
                                quantity = 0
                            End If                            code.Data_updatefw("Insert Into A_Product_Size_linshi(A_bm_cj,A_Size,a_quantity) values('" & bm_cj & "','" & size & "','" & quantity & "')")‘此行是插入到数据库的代码                        Catch ex As Exception
                        End Try
                    Next                    File.Delete(filename)‘删除上传的文件

Answer 2

我自己在网上学的，现在拿出来分享下吧！
    Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strpath & "; Extended Properties=Excel 8.0;"
        Dim oleDbConnection As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Try
            oleDbConnection.Open()
            conn.Open()
            Dim sheetdataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim tableName As String = sheetdataTable.Rows(3)(2).ToString.Trim()
            tableName = "[" & tableName.Replace(" ' ", " ") & "]"
            Dim query As String = "select Country,Comp_ID,BankName,Facility,Role,ParticipationAmt,Curr,Amount,CurrAlt,AmountAlt,IssueDate,MaturityDate,CloseOutDate,ContNo,TranType,BUnit,TMISBankCode from" & tableName
            Dim exceldataset As DataSet = New DataSet()
            Dim exceloleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)
            exceloleAdapter.Fill(exceldataset, "intUploadFacility")            Dim dataTable As DataTable = New DataTable()
            Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("select UploadFacilityID,UserID,Facility,Curr,TotalAmount,MaturityDate,IssueDate,Curr_Alt,TotalAmount_Alt,Comp_ID,Role,ParticipationAmt,IntStatus,ContNo from intUploadFacility", conn)
            Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)
            sqlDA1.Fill(dataTable)            Dim exceldataRow As DataRow
            Dim UploadFacilityID As Int32 = 0
            For Each exceldataRow In exceldataset.Tables("intUploadFacility").Rows
                Dim dataRow As DataRow = dataTable.NewRow()
                UploadFacilityID = UploadFacilityID + 1
                dataRow("UploadFacilityID") = UploadFacilityID                dataRow("UserID") = Me.UserID
                dataRow("Facility") = exceldataRow("Facility")
                dataRow("Curr") = exceldataRow("Curr")
                dataRow("TotalAmount") = exceldataRow("Amount")
                dataRow("MaturityDate") = exceldataRow("MaturityDate")
                dataRow("IssueDate") = exceldataRow("IssueDate")                If Not exceldataRow("CurrAlt") Is DBNull.Value Then
                    dataRow("Curr_Alt") = exceldataRow("CurrAlt")
                Else
                    dataRow("Curr_Alt") = ""
                End If
                If Not exceldataRow("AmountAlt") Is DBNull.Value Then
                    dataRow("TotalAmount_Alt") = Convert.ToDouble(exceldataRow("AmountAlt"))
                Else
                    dataRow("TotalAmount_Alt") = 0
                End If                If Not exceldataRow("Comp_ID") Is DBNull.Value Then
                    dataRow("Comp_ID") = exceldataRow("Comp_ID")
                Else
                    dataRow("Comp_ID") = 0
                End If                dataRow("Role") = exceldataRow("Role")
                ' Dim particpationAmt As String = Convert.ToString(dataRow11("ParticipationAmt") + "")                If Not exceldataRow("ParticipationAmt") Is DBNull.Value Then
                    dataRow("ParticipationAmt") = Convert.ToDouble(exceldataRow("ParticipationAmt"))
                Else
                    dataRow("ParticipationAmt") = 0
                End If
                dataRow("IntStatus") = ""
                dataRow("ContNo") = exceldataRow("ContNo")
                dataTable.Rows.Add(dataRow)
            Next  
            sqlDA1.Update(dataTable)