上海公司做网站的价格,广西建设官方网站,天津做网站.都找津坤科技,做网站公司西安总结在 Excel 中使用 SQL 语句的四种方法#xff0c;各种方法都有各自的适用场景#xff0c;可以选择自己熟悉的或喜欢方式。本文以在 Excel 中操作 MS SQL 数据库的数据为例进行说明。MS SQL 的数据如下#xff0c;使用微软 SQLExpress 版本。 方法 1#xff1a; Excel 现…总结在 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 ProviderSQLOLEDB.1;Integrated SecuritySSPI;Persist Security InfoFalse;Initial CatalogSimpleStockManager;Data SourceLAPTOP-1VEJV1RM\SQLEXPRESSDim 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 语句获取数据。这种方法还是比较简单的。
方法4pandas 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(mssqlpymssql://username:pwdlocalhost:10577/SimpleStockManager?charsetutf8)def main():# do nothingxw.func
def read_sql(sql):df pd.read_sql(sql, engine)return dfif __name__ __main__:xw.Book(UseSql.xlsm).set_mock_caller()main()只需要几句代码就编写了一个可以根据 sql 语句获取数据的函数。在 Excel 中使用效果如下
