运用SQL输出EXCEL报表 - EXCEL VBA（26）

首发2023-07-09 08:17·银河统计工作室

SQL是Structured Query Language的缩写，中文译为“结构化查询语言”。SQL 是一种计算机语言，用来检索、存储和修改关系型数据库中存储的数据。SQL虽然是一种被ANSI标准化的语言，但是它有很多不同的实现版本。

SQL是关系型数据库的标准语言，所有的关系型数据库管理系统（RDBMS），比如 MySQL、Oracle、SQL Server、MS Access、Sybase、Informix、Postgres 等，都将SQL作为其标准处理语言。

将EXCEL做为数据库，运用强大的SQL语言来操作EXCEL表格，无疑会极大提高数据处理的效率和灵活性。SQL的内容非常丰富、用途广泛，是各种数据库程序员的必备技能。对于习惯于用EXCEL处理数据的用户来说，只需要了解SQL的SELECT语句即可。本文只介绍运用SQL检索EXCEL记录，并生成报表。至于日常工作中创建数据记录、删除数据、修改数据等数据清理工作均可在EXCEL表格中完成。

1、EXCEL数据表

学生数据EXCEL样例文档下载地址： https://pan.baidu.com/s/1dgMPt16OaahyVj9vJBfmAQ?pwd=ff53。

I、sheet1数据表

  • 学生注册数据表：

  • 字段属性：

【学号】ID唯一识别码，11位文本字符；

【姓名】文本字段；

【性别】逻辑字段，表中为男、女，也可以为0、1或M、F；

【省份】文本字段；

【学院】文本字段；

【专业】文本字段；

【生日】日期字段。

II、sheet2数据表

  • 学生成绩数据表：

  • 字段属性：

【学号】关联字段，和sheet1学号ID码关联.EXCEL关联报表需要适当的关联指标字段；

【数学】、【英语】和【政治】数值型字段，成绩在0-100之间。

III、数据预处理

为了SQL检索的准确性，需要运用EXCEL函数对原始数据进行数据预处理，包括，

  • 【姓名】文本字段需要去除所有空格，
  • 【学号】、【性别】、【省份】、【学院】和【专业】文本字段需要用根据码表录入（参见EXCEL数据码表和ID设计）；
  • 【生日】为了和SQL检索匹配，可以用TEXT函数格式化生日字段为“月/日/年”格式，即【=TEXT(字段值, "mm/dd/yyyy")】；
  • 【数学】、【英语】和【政治】数值型成绩字段，应检测字段值是否为整数数值、值域是否在0-100之间。

2、使用ADODB.Connection对象连接本地Excel文档

在模块1编写connTest代码如下：

Sub connTest()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim filePath As String
    ' 设置本地Excel文档的文件路径
    filePath = "D:\excelTest\strdentSQL.xlsx"
    ' 创建ADO连接对象
    Set conn = CreateObject("ADODB.Connection")
    ' 设置连接字符串
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=YES"""
    ' 打开连接
    conn.Open
    ' 读取数据
    strSQL = "SELECT * FROM [Sheet1$]"
    Set rs = conn.Execute(strSQL)
    '显示EXCEL表格字段数量
    MsgBox rs.Fields.Count
    ' 关闭连接
    conn.Close
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing
End Sub
  • 地址代码：第7行代码【filePath = "D:\excelTest\strdentSQL.xlsx"】可根据自己电脑设置连接地址，EXCEL文档扩展名为“.xlsx”或“.xls”；
  • 连接代码：第11行代码（核心代码），【conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=YES"""】。EXCEL版本不同，连接代码略有不同。本文使用EXCEL2010版本，所有SQL SELECT语句运行通过。连接代码中“HDR=YES”表示输出数据集不包含表头；
  • SQL SELECT语句：第15行代码，【strSQL = "SELECT * FROM [Sheet1$]"】检索Sheet1所有数据，是最基本的SQL SELECT语句；
  • 显示字段（EXCEL数据列）数量：第18行代码，【MsgBox rs.Fields.Count】显示出EXCEL数据量数量，说明ADODB.Connection对象连接本地Excel成功。

3、常用SQL SELECT检索语句

不同版本SQL语言用于不同数据库时，语法有所区别。SQL SELECT检索语句在操作EXCEL表格时，有些功能无法使用。

下面列出在EXCEL2010中运行通过的常用SQL SELECT检索语句：

'输出全部数据
1、"SELECT * FROM [Sheet1$]"
-------------------------------
'输出全部数据并按学号单列排序(升序)，关键字ORDER BY
"SELECT * FROM [Sheet1$] ORDER BY 学号"
-------------------------------
'输出全部数据并按学号单列排序(降序)，关键字DESC
2、"SELECT * FROM [Sheet1$] ORDER BY 学号 DESC"
-------------------------------
'统计不重复学号总数，关键字DISTINCT
3、"SELECT DISTINCT 学号 FROM [Sheet1$]"
-------------------------------
'输出全部数据并按学院和省份多列列排序(升序)
4、"SELECT * FROM [Sheet1$] ORDER BY 学院, 省份"
-------------------------------
'输出部分字段全部数据
5、"SELECT 学号, 姓名, 性别  FROM [Sheet1$]"
-------------------------------
'Like通配符"_"表示一个字符、"%"表示多个字符。检索名字为两个汉字、姓王的男生
6、"SELECT * FROM [Sheet1$] WHERE 姓名 Like '王_' AND 性别='男'"
-------------------------------
'检索名字为姓王或姓李的男生，关键字Like '[王李]%'
7、"SELECT * FROM [Sheet1$] WHERE 姓名 Like '[王李]%' AND 性别='男'"
-------------------------------
'检索名字为姓王或姓李的前10名男生，关键字TOP 10
8、"SELECT TOP 10 * FROM [Sheet1$] WHERE 姓名 Like '[王李]%' AND 性别='男'"
-------------------------------
'检索黑龙江、吉林、辽宁三省学生，关键字IN('黑龙江','吉林','辽宁')"
9、"SELECT * FROM [Sheet1$] WHERE 省份 IN('黑龙江','吉林','辽宁') AND 性别='男'"
-------------------------------
'检索1990出生的学生。生日字段数据处理使用YEAR、Month和DAY函数
10、SELECT * FROM [Sheet1$] WHERE YEAR(生日)=1990"
-------------------------------
'检索生日为11月13日的学生
11、"SELECT * FROM [Sheet1$] WHERE Month(生日)=11 AND DAY(生日)=13"
-------------------------------
'检索数学成绩在90-100之间的学生（FROM [Sheet1$]）
12、"SELECT * FROM [Sheet2$] WHERE 数学 BETWEEN 90 AND 100"
-------------------------------
'统计数据总数
13、"SELECT COUNT(*) AS 学生总数 FROM [Sheet1$]"
-------------------------------
'统计数学平均成绩（FROM [Sheet1$]）。SQL的AVG、SUM、MAX和MIN用于数值型字段
14、"SELECT AVG(数学) AS 数学平均成绩 FROM [Sheet2$]"
-------------------------------
 'sheet1和sheet2间表格交叉检索
  15、"SELECT [sheet1$].姓名,[sheet2$].数学 FROM [sheet1$],[sheet2$] WHERE [sheet1$].学号=[sheet2$].学号"
  16、"SELECT [sheet1$].姓名,[sheet2$].数学 FROM [sheet1$],[sheet2$] WHERE [sheet1$].学号=[sheet2$].学号 AND [sheet2$].数学>90"

4、SQL操作学生数据EXCEL文档案例

I、窗体设计

  • ListBox1：列表框，打开窗体时设置常用SQL SELECT语句；
  • TextBox1：文本框，鼠标在列表框中选择SQL SELECT语句时，自动填写该语句；
  • CommandButton1：【条件检索】命令按钮，鼠标点击后根据TextBox1文本框中SQL SELECT语句检索数据、并写入sheet1；
  • CommandButton2：【嵌套检索】命令按钮，鼠标点击后按字段关联检索sheet1和sheet2。

II、窗体初始化代码

Private Sub UserForm_Initialize()
'---ListBox1列表框初始化---
ListBox1.AddItem "SELECT * FROM [Sheet1$]"
ListBox1.AddItem "SELECT * FROM [Sheet1$] ORDER BY 学号"
ListBox1.AddItem "SELECT * FROM [Sheet1$] ORDER BY 学号 DESC"
ListBox1.AddItem "SELECT DISTINCT 学号 FROM [Sheet1$]"
ListBox1.AddItem "SELECT * FROM [Sheet1$] ORDER BY 学院, 省份"
ListBox1.AddItem "SELECT 学号, 姓名, 性别  FROM [Sheet1$]"
ListBox1.AddItem "SELECT * FROM [Sheet1$] WHERE 姓名 Like '王_' AND 性别='男'"
ListBox1.AddItem "SELECT * FROM [Sheet1$] WHERE 姓名 Like '[王李]%' AND 性别='男'"
ListBox1.AddItem "SELECT TOP 10 * FROM [Sheet1$] WHERE 姓名 Like '[王李]%' AND 性别='男'"
ListBox1.AddItem "SELECT * FROM [Sheet1$] WHERE 省份 IN('黑龙江','吉林','辽宁') AND 性别='男'"
ListBox1.AddItem "SELECT * FROM [Sheet1$] WHERE YEAR(生日)=1990"
ListBox1.AddItem "SELECT * FROM [Sheet1$] WHERE Month(生日)=11 AND DAY(生日)=13"
ListBox1.AddItem "SELECT * FROM [Sheet2$] WHERE 数学 BETWEEN 90 AND 100"
ListBox1.AddItem "SELECT COUNT(*) AS 学生总数 FROM [Sheet1$]"
ListBox1.AddItem "SELECT AVG(数学) AS 数学平均成绩 FROM [Sheet2$]"
ListBox1.AddItem "SELECT [sheet1$].姓名,[sheet2$].数学 FROM [sheet1$],[sheet2$] WHERE [sheet1$].学号=[sheet2$].学号"
ListBox1.AddItem "SELECT [sheet1$].姓名,[sheet2$].数学 FROM [sheet1$],[sheet2$] WHERE [sheet1$].学号=[sheet2$].学号 AND [sheet2$].数学>90"
ListBox1.ListIndex = 0
TextBox1.Text = ListBox1.Text
End Sub

III、鼠标点击ListBox1列表框选项代码

Private Sub ListBox1_Click()
TextBox1.Text = ListBox1.Text
End Sub

IV、CommandButton1【条件检索】命令按钮

Private Sub CommandButton1_Click()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim filePath As String
    Dim c, i, k As Integer
    filePath = "D:\excelTest\strdentSQL.xlsx"
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=YES"""
    conn.Open
    ' 读取SQL SELECT语句
    strSQL = TextBox1.Text
    Set rs = conn.Execute(strSQL)
    Sheet1.Cells.Clear
' 输出表头行
    c = rs.Fields.Count
    For i = 1 To c
      Sheet1.Cells(1, i) = rs.Fields(i - 1).Name
    Next i
 ' 输出数据
    k = 2
    Do Until rs.EOF
      For i = 1 To c
         Sheet1.Cells(k, i) = rs.Fields(i - 1).Value
      Next i
        k = k + 1
        rs.MoveNext
    Loop
   conn.Close
   Set rs = Nothing
    Set conn = Nothing
End Sub

V、项目应用

  • 打开窗体：

  • 鼠标选择列表SQL SELECT语句：

  • 鼠标点击【条件检索】命令按钮，sheet1数据如下：

分别选择列表中选择不同SQL SELECT语句，观察sheet1数据输出效果。

VI、表格嵌套检索

嵌套检索也称“子查询”或者“嵌套查询”，是指将一个 SELECT查询的结果作为另一个 SQL 语句的数据来源或者判断条件。例如，

SELECT 姓名, 学院, 专业 FROM [Sheet1$] WHERE 学号=(SELECT 学号 FROM [Sheet1$] WHERE 数学>90)

由于EXCEL VBA的SQL SELECT语句不支持SELECT嵌套检索，需要自编EXCEL VBA代码来完成。【嵌套检索】命令按钮代码如下：

Private Sub CommandButton2_Click()
    Dim conn As Object
    Dim rt As Object
    Dim rs As Object
    Dim strSQL, strSQL1 As String
    Dim filePath As String
    Dim c, i, k As Integer
    Dim id As String
    filePath = "D:\excelTest\strdentSQL.xlsx"
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=YES"""
    conn.Open
    '设置 检索sheet1的SQL SELECT语句
    strSQL = "SELECT * FROM [Sheet1$] WHERE 姓名 Like '[王李]%' AND 性别='男'"
    Set rs = conn.Execute(strSQL)
    Sheet1.Cells.Clear
'写表头标题
    Sheet1.Cells(1, 1) = "学号"
    Sheet1.Cells(1, 2) = "姓名"
    Sheet1.Cells(1, 3) = "数学"
'读取数据
    k = 2
'第1个数据集循环
    Do Until rs.EOF
 '获得关联字段（学号）值
        id = rs.Fields(0).Value
 '设置 检索sheet2的SQL SELECT语句
        strSQL1 = "SELECT 数学 FROM [Sheet2$] WHERE 学号='" & id & "'"
        Set rt = conn.Execute(strSQL1)
'第2个数据集循环
        Do Until rt.EOF
'按字段名称写数据到sheet1
            Sheet1.Cells(k, 1) = rs.Fields("学号").Value
            Sheet1.Cells(k, 2) = rs.Fields("姓名").Value
            Sheet1.Cells(k, 3) = rt.Fields("数学").Value
            k = k + 1
            rt.MoveNext
        Loop
         rs.MoveNext
    Loop
    conn.Close
    Set rt = Nothing
    Set rs = Nothing
    Set conn = Nothing
End Sub

代码用【"SELECT * FROM [Sheet1$] WHERE 姓名 Like '[王李]%' AND 性别='男'"】语句从sheet1中检索出姓王或姓李的男生，然后再有对应（关联）学号通过【"SELECT 数学 FROM [Sheet2$] WHERE 学号='" & id & "'"】语句检索出数学成绩。通过这种方法可以实现多表嵌套查询。

在窗体中运行【嵌套检索】命令按钮，sheet1数据显示如下：

使用SQL技术操作EXCEL文档需要注意以下事项：

  • EXCEL文档数据要规范、“干净”；
  • EXCEL文档命名、存储地址要有规律，这样便于批量操作；
  • EXCEL表格中为原始数据，尽量不用函数或对数据进行格式化。如果需要用EXCEL函数将数据格式化或标准化，数据处理完成要按值分组、粘贴后存储；
  • 通过ADO连接访问Excel文档时，Excel应该在后台保持关闭状态，否则可能会出现冲突或无法正常访问的问题。

SQL SELECT检索语句可以灵活、高效地组合成任意查询语句，单表条件查询、多表交叉查询和嵌套查询，每个查询语句就是一个EXCEL数据报表。在实际工作中，面对众多EXCEL表格，只需要建立ADO连接，任何复杂的数据分析和统计报表工作都可以通过SELECT查询语句来处理。

举报
评论 0
    热门：