Excel VBA ——从MySQL数据库中导出一个报表-笔记

本文主要涉及：

VBA中数据库连接参数改成从配置文件获取
VBA连接MySQL数据库
VBA读MySQL数据库
演示两种写入工作簿的代码实现

系统环境：

Windows 10 64bit
Excel 365 64bit
WAMP（3.2.2.2 64bit）集成的MariaDB版本为10.4.10（MariaDB的操作方法和MySQL相同，这里不做区分）

1. VBA连接MySQL前的环境配置

如果还没有用VBA连接过mysql数据库，请参照前篇 VBA连接MySQL数据库
2. VBA报表的优势

其实VBA报表的最大优势就是——它是在Excel表格中实现的！

有些办公场景中，不光需要你出报表，并且呈报的时候还要改excel表格格式，这时vba的优势就体现出来了。你可以首先在office中改好需要的样式，然后使用vba填充数据即可，单纯使用vba填入数据是不会破坏版面的各种样式的，这比其他语言的排版更有优势，代码量更少，且所见即所得，减少版面调整的时间！
3. VBA代码示例
3.1　　直接复制数据集的写法

Sub 直接复制()
    '设置数据库连接对象
    Set conn = CreateObject("Adodb.Connection")
    '设置数据库记录集对象
    Set rs = CreateObject("Adodb.Recordset")
    serverIP = Sheet1.Range("J2").Value
    serverDB = Sheet1.Range("J6").Value
    serverUID = Sheet1.Range("J3").Value
    serverPWD = Sheet1.Range("J4").Value
    '配置连接串
    conn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & serverIP & ";DB=" & serverDB & ";UID=" & serverUID & ";PWD=;OPTION=3;"
    conn.Open
    '拼接sql语句，写成这样子方便检查sql语句，也可以全部写到一行里
    sqlStr = " SELECT "
    sqlStr = sqlStr & " tp_auth.id, "
    sqlStr = sqlStr & " tp_auth. NAME, "
    sqlStr = sqlStr & " tp_hobby.content "
    sqlStr = sqlStr & " FROM "
    sqlStr = sqlStr & " tp_auth, "
    sqlStr = sqlStr & " tp_hobby "
    sqlStr = sqlStr & " WHERE "
    sqlStr = sqlStr & " tp_auth.id = tp_hobby.user_id "
    '从test数据库的YGXM表中取出所有数据
    rs.Open sqlStr, conn
    '设置表头
    Range("A1:C1").Value = Array("ID", "Name", "content")
    '将数据输出到工作表
    Range("A2").CopyFromRecordset rs
    '关闭连接
    rs.Close: Set rs = Nothing
    conn.Close: Set conn = Nothing
End Sub

优点：操作简单

缺点：所有的结果顺序都必须在sql语句中调试好，在写入单元格前如果要修改列的次序，相当于要在结果数组中直接操作数组，相对繁琐
3.2 利用数组转储的写法

Sub 数组转储()
    '设置数据库连接对象
    Set conn = CreateObject("Adodb.Connection")
    '设置数据库记录集对象
    Set rs = CreateObject("Adodb.Recordset")
    serverIP = Sheet1.Range("J2").Value
    serverDB = Sheet1.Range("J6").Value
    serverUID = Sheet1.Range("J3").Value
    serverPWD = Sheet1.Range("J4").Value
    '配置连接串
    conn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & serverIP & ";DB=" & serverDB & ";UID=" & serverUID & ";PWD=;OPTION=3;"
    conn.Open
    '拼接sql语句，写成这样子方便检查sql语句，也可以全部写到一行里
    sqlStr = " SELECT "
    sqlStr = sqlStr & " tp_auth.id, "
    sqlStr = sqlStr & " tp_auth. NAME, "
    sqlStr = sqlStr & " tp_hobby.content "
    sqlStr = sqlStr & " FROM "
    sqlStr = sqlStr & " tp_auth, "
    sqlStr = sqlStr & " tp_hobby "
    sqlStr = sqlStr & " WHERE "
    sqlStr = sqlStr & " tp_auth.id = tp_hobby.user_id "
    '从test数据库的YGXM表中取出所有数据
    rs.Open sqlStr, conn
    '设置表头
    Range("A1:C1").Value = Array("ID", "Name", "content")
    '使用数组存储记录集—————————————————————————————————————————————————————————————————————————————————————————————
    Dim arrRecord(1 To 1000000, 1 To 3)  '数组第一个参数代表数据集的行数，第二个代表列数
    i = 1
    Do While Not rs.EOF '当数据指针未移到记录集末尾时，循环下列操作
        '把数据集指定的字段，依次写入数组指定的列，顺序不必和sql结果相同
        arrRecord(i, 1) = rs("id")
        arrRecord(i, 2) = rs("NAME")
        arrRecord(i, 3) = rs("content")
        
        rs.MoveNext '把指针移向下一条记录
        i = i + 1 'i加1，准备把下一记录相关字段的值保存到工作表的下一行
        Loop '循环
        
        '将数组复制到单元格——————————————————————————————————————————————————————————————————————————————————————————————
        Sheet1.Range("$A$2:$C$1000000") = arrRecord
        
        rs.Close: Set rs = Nothing
        conn.Close: Set conn = Nothing
        
        End Sub