Excel中使用SQL语句的四种方法

总结在 Excel 中使用 SQL 语句的四种方法，各种方法都有各自的适用场景，可以选择自己熟悉的或喜欢方式。本文以在 Excel 中操作 MS SQL 数据库的数据为例进行说明。MS SQL 的数据如下，使用微软 SQLExpress 版本。

方法 1： Excel 现有连接功能

在这里插入图片描述

点击浏览更多按钮，在出现下面的界面后，新建一个 sql server 的连接：

输入要连接的sql server服务器，以及连接方式：

在下一步，选择数据库，选择表：

然后一路下一步，将数据放在希望的工作表：

默认情况下，显示的是一个表，我们可以通过右键菜单进行修改：

进入的时候，是这样的：

我们将命令类型改为 SQL，然后在命令文本中写入合法的 sql 语句：

这样就可以了。这个方法的数据源也可以是 Excel 工作簿，灵活性还是不错的。但如果用 Excel 作为数据源，工作表名称后面需要加上$符号。

下面演示将 Excel 工作簿作为数据源。点击数据选项卡的现有连接，将类型改为所有文件或者Excel文件，直接找到有数据的 Excel 工作簿。

在这里插入图片描述

选择表格：

可以把 Excel 工作簿的数据源当做表格，用 SQL 语句操作：

显示的结果如下：

方法2： ADO

使用 ADO 操作数据，需要编写 VBA 代码，但灵活性更高，甚至可以封装成函数。有两个知识点：

连接字符串的写法
参照 ADO 库

连接字符串可以使用方法1的 OLEDB 连接字符串。也可以使用另外一种方法：新建一个扩展名为 udl 的文件，然后双击，出现下面的界面，填写好相应的字段：

点击测试连接，测试是否可以连接成功。然后用文本编辑器打开 udl 文件，获取连接字符串：

第二个知识点是在 VBE 环境中加载 ADO 库。方法如下，打开 Excel 文件后，按 ALT + F11 进入 VBE 环境，然后通过菜单工具->引用，选中 Microsoft ActiveX Data Object 库的应用，有不同的版本，可以选择高一点的版本：

以下是示例代码，代码比较简单，不做讲解。

Public Sub ImportData()Dim conn As New ADODB.Connectionconn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SimpleStockManager;Data Source=LAPTOP-1VEJV1RM\SQLEXPRESS"Dim rst As New ADODB.Recordsetconn.Openrst.Open "select * from t_m_materials", conn, adOpenKeyset, adLockReadOnly' print headersDim i As IntegerDim leftTopCell As RangeSet leftTopCell = Sheet1.Range("A1")For i = 0 To rst.Fields.Count - 1leftTopCell.Offset(0, i).Value = rst.Fields(i).NameNext' import dataleftTopCell.Offset(1, 0).CopyFromRecordset rst
End Sub

方法3：利用 Power Query

首先获取数据库的连接：

填写服务器名称，数据库可以选填。
在这里插入图片描述

点击确定按钮，在下面的界面中选择 t_m_materials 表，然后点击加载按钮。

此时，加载的是表，按下 ALT + F12 进入 Power Query 编辑器，在高级编辑器中，将自动生成的代码进行改写，改写后 M 代码如下：

letSource = Sql.Database("LAPTOP-1VEJV1RM\SQLEXPRESS", "SimpleStockManager"),dbo_t_m_materials = Value.NativeQuery(Source, "select MaterialNo, MaterialDesc from t_m_materials")
indbo_t_m_materials

通过 M 语言的 Value.NativeQuery 函数，使用 SQL 语句获取数据。这种方法还是比较简单的。

方法4：pandas + xlwings

如果熟悉 Python 语言，那么使用 pandas 库，结合 xlwings 的功能，使用起来就更简单了。在安装了 pandas 库和 xlwings 库之后，先在 CMD 窗口中运行下面的命令，创建一个 xlsm 文件以及同名的 py 文件：

xlwings quickstart UseSql

打开 UseSql.py ，编写如下代码：

import xlwings as xw
from sqlalchemy import create_engine
import pandas as pdengine = create_engine('mssql+pymssql://username:pwd@localhost:10577/SimpleStockManager?charset=utf8')def main():# do nothing@xw.func
def read_sql(sql):df = pd.read_sql(sql, engine)return dfif __name__ == "__main__":xw.Book("UseSql.xlsm").set_mock_caller()main()