Excel的数据导入到sql中

[Cosmo]

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

[Cosmo]

木人回答吗？、

[huayy]

 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)‘删除上传的文件

[Cosmo]

我自己在网上学的，现在拿出来分享下吧！
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)