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").ValueserverDB = Sheet1.Range("J6").ValueserverUID = Sheet1.Range("J3").ValueserverPWD = 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 = Nothingconn.Close: Set conn = Nothing
End Sub

优点：操作简单

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

Sub 数组转储()'设置数据库连接对象Set conn = CreateObject("Adodb.Connection")'设置数据库记录集对象Set rs = CreateObject("Adodb.Recordset")serverIP = Sheet1.Range("J2").ValueserverDB = Sheet1.Range("J6").ValueserverUID = Sheet1.Range("J3").ValueserverPWD = 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 = 1Do 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") = arrRecordrs.Close: Set rs = Nothingconn.Close: Set conn = NothingEnd Sub