Power Query 作为桌面端数据清理和转换的工具,能极大解放生产力,将繁琐的数据处理工作从重复的劳动中解放出来。那么,Power Query 能否对外提供计算服务呢?或者说 Power Query 有没有对外提供的编程接口? 根据我的探索,似乎没有,但在网络上找到下面的两种 walkaround 方式,都比较小众。所以如果真的需要数据处理、数据分析服务的话,不如选择其他的方案,比如 pandas 等等,拥有更大的自由度。
本篇主要介绍第一种方式,第二种方式给出一些参考链接。
貌似 PQ 没有对外的编程接口,但是 Power Pivot 有通过 Excel 工作簿的编程接口,所以我们可以将数据加载到 Power Pivot, 然后在外部调用。因为本文主要是讲 Power Query ,所以对 Power Pivot 不做展开。Power Pivot 是微软推出的 Excel COM 加载项 (COM Add-in),可以在微软官方免费下载并安装,然后启用加载项即可。安装之后,启用方法如下:通过【文件】>【选项】打开如下界面,选择加载项类型的【COM加载项】,点击转到按钮。
然后在出现的对话框中,勾上"Microsoft Power Pivot for Excel"。如果想不启用该插件,也是通过相同的路径进入该界面,去掉这个勾。
接下来,以之前文章讲解的 PQ 实现的进出存查询为例,进一步讲解如何通过 VBA + ADO 调用 PQ 的查询结果。打开上一篇示例数据的 Excel 文件,选择 stock_balance
查询,点击右键菜单的【加载到】菜单:
选择“将此数据添加到数据模型”:
然后在【数据】选项卡,点击【管理数据模型】功能项:
这样就进入了 Power Pivot 的界面。暂时不对 Power Pivot 的细节展开。
打开一个新的 Excel 工作簿,按下 Alt + F11 进入 VBE (Visual Basic Editor) 环境。在 VBE 环境中,通过【工具】>【引用】添加 Microsoft ActiveX Data Model 的引用。这个是 COM 库,运行 ADO 需要。
新建一个模块 (Module),在模块中新建一个函数 ExportExcelDataModel
。该函数实现将 Excel Data Model 导出到工作表:
Public Function ExportExcelDataModel(
excelFilePath As String,
modelName As String,
targetSheet As Worksheet)
'''Reference: Microsoft ActiveX Data Objects
Dim wbTarget As Workbook 'target workbook
Dim ws As Worksheet
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQueryString As String
'Suppress alerts and screen updates
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Err.Clear
On Error GoTo ErrHandler
Set wbTarget = Application.Workbooks.Open(excelFilePath)
'Make sure the model is loaded
wbTarget.Model.Initialize
wbTarget.Model.Refresh
Set conn = wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
sQueryString = "EVALUATE '" & modelName & "'"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sQueryString, conn
targetSheet.Cells.ClearContents
' Write header
Dim colIndex As Integer
For colIndex = 0 To rs.Fields.Count - 1
targetSheet.Range("A1").Offset(0, colIndex).Value = rs.Fields(colIndex).Name
Next
' Write Lines
targetSheet.Range("A1").Offset(1, 0).CopyFromRecordset rs
rs.Close
Set rs = Nothing
' Close workbook
wbTarget.Close
ExitPoint:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set rs = Nothing
Exit Function
ErrHandler:
MsgBox "An error occured - " & Err.Number & "," & Err.Description, vbOKOnly
Resume ExitPoint
End Function
因为本文的主题是 PQ,所以不对代码的细节进行讲解,只稍微提一下 ADO 读取 Excel Data Model 的要点:
someWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection
获得数据连接CopyFromRecordSet
方法要求 RecordSet 的 Cursor Location 为 adUseClient
,否则结果错误,并没有抛出 Exception 或 Error,而是数据出现错误。如果手工代码循环的方式获取,则没有问题。然后再添加一个子例程,调用函数 ExportExcelDataModel
,下面的调用过程既是调用代码,也能体现函数的调用方法。
Public Sub DoExport()
Dim filePath As String
filePath = ThisWorkbook.Path & "\pqservice.xlsx"
Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Extended Properties=""Excel 12.0;HDR=No"";" & _
"data source=" & filePath
' Update cell in another excel workbook
Dim sql As String
sql = "UPDATE [Criteria$A1:B3] SET F2=5 WHERE F1='month'"
conn.Open
conn.Execute sql
conn.Close
' Retrieve data
Dim sht As Worksheet
Set sht = Sheet1
Call ExportExcelDataModel(filePath, "stock_balance", sht)
sht.Activate
End Sub
因为需要将筛选条件:月份,从当前工作簿传递到目标工作簿,我采用了 ADO 直接读写 Excel 工作表的方法。但我平时很少用到 ADO 读写 Excel 工作表的方式,因为数据尽可能存放在数据库中,而不是 Excel。在目标工作簿 -- 即提供 Power Query 服务的 Excel 工作表中,筛选条件界面如下:
所以用
sql = "UPDATE [Criteria$A1:B3] SET F2=5 WHERE F1='month'"
表示更新的 SQL 语句。连接字符串中 HDR = No,表示不启用 Header Row,所以此语句 F1 表示 A 列,F2 表示 B 列。Criteria 是工作表名称 (worksheet name)。
本示例代码提供的功能:
从理论上来说,这种方法适用于所有能操作 COM 对象 (ADO ActiveX) 的编程语言。
微软提供了 Power Query SDK,从而赋予了在 .net 平台中可以直接使用 M 语言的功能。尽管官方的说法,Power Query SDK 已经在 Visual Studio 2019 中可以使用,但我在安装的时候没有成功,也不打算在 Visual Studio 2017 或更老的版本中折腾,这里只能给出相关的连接,有兴趣的读者请自行探索。
Power Query SDK 是一扩展名为 vsix 的文件,在 Visual Studio 中安装的方法请参考:vs2015如何安装vsix扩展工具
网上为数不多的参考文章:
github - Consuming Power Query Service
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。