VB.NET MDB数据库导出到Excel表生成分析报表

Imports System.Data.OleDb

''' <summary>
''' 
''' 数据库连接类
''' 
''' Author:OuHuanHua
''' Date:2023/01/29
''' 
''' </summary>
Public Class Class_AccessDb
    ''' <summary>
    ''' 数据库连接对象
    ''' </summary>
    Private ReadOnly DbConnection As OleDbConnection

    ''' <summary>
    ''' 日志事件
    ''' </summary>
    ''' <param name="msg"></param>
    Public Event CmdLog(msg As String)

    ''' <summary>
    ''' 初始化类
    ''' </summary>
    ''' <param name="DbPath"></param>
    Public Sub New(DbPath As String)
        Dim database As String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & DbPath
        DbConnection = New OleDbConnection(database)
        Try
            ''检测数据库状态
            If DbConnection.State.Equals(ConnectionState.Closed) Then
                DbConnection.Open()
            End If
        Catch ex As Exception
            RaiseEvent CmdLog("数据库初始化异常:" & ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 查询数据并返回DataTable对象
    ''' </summary>
    ''' <param name="SqlStr">sql语句</param>
    ''' <returns></returns>
    Public Function ExecuteDataTable(SqlStr As String) As DataTable
        Try
            ''-------再次判断数据库连接状态
            If DbConnection.State.Equals(ConnectionState.Closed) Then
                DbConnection.Open()
            End If
            ''--------查询数据
            Using CmdObject As New OleDbCommand With {
                                .CommandText = SqlStr,
                                .CommandType = CommandType.Text,
                                .Connection = DbConnection,
                                .CommandTimeout = 0
                              }
                Using AdpObject As New OleDbDataAdapter With {.SelectCommand = CmdObject}
                    Using Dt As New DataTable()
                        AdpObject.Fill(Dt)
                        CmdObject.Parameters.Clear()
                        Return Dt
                    End Using
                End Using
            End Using
        Catch ex As OleDbException
            RaiseEvent CmdLog("查询数据异常:" & ex.Message)
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 执行SQL指令语句
    ''' </summary>
    ''' <param name="SqlStr">sql语句</param>
    ''' <returns></returns>
    Public Function ExecuteNonQuery(SqlStr As String) As Integer
        Try
            ''-------再次判断数据库连接状态
            If DbConnection.State.Equals(ConnectionState.Closed) Then
                DbConnection.Open()
            End If
            ''--------执行指令
            Using CmdObject As New OleDbCommand With {
                          .CommandText = SqlStr,
                          .CommandType = CommandType.Text,
                          .Connection = DbConnection,
                          .CommandTimeout = 0
                        }
                Dim rows As Integer = CmdObject.ExecuteNonQuery()
                CmdObject.Parameters.Clear()
                Return rows
            End Using
        Catch ex As OleDbException
            RaiseEvent CmdLog("执行指令异常:" & ex.Message)
            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 获取当前连接的mdb中的所有表名
    ''' </summary>
    ''' <returns></returns>
    Public Function GetTableNames() As List(Of String)
        Try
            ''-------再次判断数据库连接状态
            If DbConnection.State.Equals(ConnectionState.Closed) Then
                DbConnection.Open()
            End If
            ''-------获取表名
            Dim dt As DataTable = DbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            Dim tableNameList As New List(Of String)
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim tableName As String = dt.Rows(i)("TABLE_NAME").ToString()
                tableNameList.Add(tableName)
            Next
            Return tableNameList
        Catch ex As Exception
            RaiseEvent CmdLog("查询所有表名异常:" & ex.Message)
            Return Nothing
        End Try
    End Function

End Class