YiJyun's 研究小間

Excel匯入到資料表的程式

 Public Overloads Overrides Sub ExceltoSQLServer(ByRef SQLString As String, ByRef ExcelSource As String, ByRef TableName As String)
        Dim cnExcel As System.Data.OleDb.OleDbConnection
        cnExcel = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & ExcelSource & ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""")
        Dim OleDbCmd As New OleDb.OleDbCommand(SQLString, cnExcel)
        Dim dr As Data.OleDb.OleDbDataReader = Nothing
        Dim bc As Data.SqlClient.SqlBulkCopy = Nothing
        Try
            cnExcel.Open()
            dr = OleDbCmd.ExecuteReader()
            bc = New Data.SqlClient.SqlBulkCopy(con)
            con.Open()
            bc.DestinationTableName = TableName
            bc.WriteToServer(dr)
        Catch ex As SqlClient.SqlException
            Throw ex
        Finally
            con.Close()
            dr.Close()
            bc.Close()
            cnExcel.Close()
        End Try
    End Sub

使用方式

    DP.ExceltoSQLServer("select OrderDate,eMail,IP from [Sheet1$] ", Me.FileUpload1.PostedFile.FileName, "tb00_EDM_Mail")