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数据库
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

优点：sql语句输出的各列可以灵活根据需要调整顺序，也可以新增sql语句中不存在的列，甚至可以把两段sql的结果根据需要拼接到一个结果数组中，功能灵活

缺点：数组必须预先分配固定的大小，不能动态分配大小