动态导入 Excel 文件到 SQL Server

引言

本文将展示如何动态地将 Excel 文件导入 SQL Server，无需打开 SQL Server 的导入向导。 如果您有很多 Excel 文件需要导入，这将非常有帮助。

使用代码

首先，创建包含导入代码的 Datamanage 类。“excelCommand” 将在 SQL Server 数据库中创建一个新表，并考虑 Excel 文件字段的名称和数据类型。

Public Class Datamanage
    ''' <summary />
    ''' Imports the selected Excel file to SQL Server 
    ''' </summary />
   Public Sub importToServer(ByVal ExcelPath As String, _
              ByVal ServerName As String, _
              ByVal DBName As String, ByVal UserName As String, _
              ByVal Password As String, ByVal InsertedTableName As String)
        Try
            Dim ExceCon As String = _
                &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot; & _
                ExcelPath & &quot;; Extended Properties=Excel 8.0&quot;
            Dim excelConnection As System.Data.OleDb.OleDbConnection = _
                New System.Data.OleDb.OleDbConnection(ExceCon)
            excelConnection.Open()
            'Dim OleStr As String = &quot;SELECT * INTO [ODBC; Driver={SQL Server};&quot; & _
                 &quot;Server=Hoss;Database=Excel_Test;Uid=sa;Pwd=sa2008; ].[myTable]   
            FROM [Sheet1$];&quot;
            Dim OleStr As String = &quot;SELECT * INTO [ODBC; Driver={SQL Server};Server=&quot; _
                & ServerName & &quot;;Database=&quot; & DBName & &quot;;Uid=&quot; & _
                UserName & &quot;;Pwd=&quot; & Password & &quot;; ].[&quot; & _
                InsertedTableName & &quot;]   FROM [Sheet1$];&quot;
            Dim excelCommand As New System.Data.OleDb.OleDbCommand(OleStr, _
                                                      excelConnection)
            excelCommand.ExecuteNonQuery()
            excelConnection.Close()
        Catch ex As Exception
            Throw New Exception(&quot;Error: &quot; & ex.Message)
        End Try
    End Sub
End Class

现在，主窗体

Imports System.IO
Public Class ImportExcel
    Dim dm As New Datamanage
    Private Sub Button2_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles Button2.Click
        OpenFileDialog1.ShowDialog()
        txtexcelPath.Text = OpenFileDialog1.FileName
                     
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles Button1.Click
        Try
                Dim ExcelPath As String = txtexcelPath.Text.ToLower()
                Dim Ext As String = _
                    ExcelPath.Substring(ExcelPath.LastIndexOf(".") + 1)
                If File.Exists(ExcelPath) AndAlso (Ext.Equals("xls") _
                        Or Ext.Equals("xlsx")) Then
                    dm.importToServer(OpenFileDialog1.FileName, txtServer.Text,_
                        txtDbName.Text, txtusername.Text, txtpassword.Text,_
                        txtInsertedTableName.Text)
                    ProgressBar1.Visible = False
                    MessageBox.Show("File imported successfully")
                 End If
        Catch ex As Exception
            ProgressBar1.Visible = False
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

希望这有帮助！