VB.NET 代码 使用SQL导出ACCESS到EXCEL

以下是使用 VB.NET 代码将 SQL 数据导出到 Excel 中的示例：

Imports System.Data.OleDb

Public Class Form1

    Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\test.accdb;Persist Security Info=False;"
        Dim selectCommand As String = "SELECT * FROM Customers"
        Dim excelFilePath As String = "C:\data\customers.xlsx"

        Using connection As New OleDbConnection(connectionString)
            Using command As New OleDbCommand(selectCommand, connection)
                connection.Open()
                Using adapter As New OleDbDataAdapter(command)
                    Dim table As New DataTable()
                    adapter.Fill(table)

                    Using excelConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelFilePath & ";Extended Properties='Excel 12.0 XML;HDR=YES;'")
                        excelConnection.Open()
                        Dim createCommand As String = "CREATE TABLE Customers ("
                        For Each column As DataColumn In table.Columns
                            createCommand += "[" & column.ColumnName & "] text,"
                        Next
                        createCommand = createCommand.TrimEnd(","c) & ")"
                        Using createTableCommand As New OleDbCommand(createCommand, excelConnection)
                            createTableCommand.ExecuteNonQuery()
                        End Using

                        For Each row As DataRow In table.Rows
                            Dim insertCommand As String = "INSERT INTO Customers ("
                            For Each column As DataColumn In table.Columns
                                insertCommand += "[" & column.ColumnName & "],"
                            Next
                            insertCommand = insertCommand.TrimEnd(","c) & ") VALUES ("
                            For Each column As DataColumn In table.Columns
                                insertCommand += "@" & column.ColumnName & ","
                            Next
                            insertCommand = insertCommand.TrimEnd(","c) & ")"
                            Using insertCommandObject As New OleDbCommand(insertCommand, excelConnection)
                                For Each column As DataColumn In table.Columns
                                    insertCommandObject.Parameters.AddWithValue("@" & column.ColumnName, row(column))
                                Next
                                insertCommandObject.ExecuteNonQuery()
                            End Using
                        Next
                        MessageBox.Show("Data Exported Successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    End Using
                End Using
            End Using
        End Using
    End Sub

End Class

在示例中，我们使用 OleDb 连接器来连接 SQL 数据库和 Excel 文件。首先，我们从 SQL 数据库中选择数据，使用 OleDbDataAdapter 将数据填充到 DataTable 中。然后，我们打开 Excel 文件并创建一个工作表，使用 INSERT INTO 语句将数据插入到工作表中。

请注意，我们使用 ACE OLEDB 驱动程序来连接 Access 和 Excel，这需要在您的计算机上安装 Microsoft Access Database Engine。