Python如何批量修改替换Excel Power Query里面的SQL脚本

梦里梦外; 2022-08-30 11:54 176阅读 0赞

# 背景 #

我们现行的BI技术栈主要是`Python`,`MySQL`,`Excel&PowerBI`,一般是ETL做好数据源和清洗之后,将其通过`Power Query`加载到Excel里面,这样可以用最简单的Excel切片器交互,也方便我们自动刷新和分发报表.  
我们以现行的一个模型为例,这个模型名为转介绍数控一.xlsx.它使用了我们常规的技术链

> Python做ETL,SQL写脚本,Excel装载展示

# 问题 #

使用`Power Query`做好的模型一般是写好的固定SQL脚本,不会轻易变动.  
在这个模型中,一共有**14**个`Power Query`查询,分别是:

*  更新日期提示x2
 *  月维度x5个OD层级
 *  日维度x5个OD层级

目前月维度固定的日期都是本月1号至当天,日维度都是当天,那么在SQL中也就是固定到了

# 当月1日
    date_sub(curdate(),interval day(curdate())-1 day)

# 当日
    curdate()

然而需求方的想法是**非常不固定**的,例如突然要昨天的,例如每月5号要一份上月的…  
![在这里插入图片描述][02f6967aa41cbf9b24840f9cf7e54af5.png]  
没关系,办法总比困难多

# 方案 #

我们有`Python`,`Python`有`pywin32`.这个模块没有说明文档,但是这个模块是直接套用的VBA.  
我们只需要写好py脚本,将日期设置为参数,每次运行修改参数就好  
假设现在往前扩大一个月,从本月1号扩大到上月1号

from win32com.client import Dispatch	# 导入pywin32
    import os	# 用来处理路径
    
    #1 参数
    FILENAMES = [
    	'【共和国】【月】转介绍表一.xlsx',
        '【共和国】【月】转介绍表二.xlsx',
        '【共和国】【月】转介绍表三.xlsx',
        '【共和国】【月】转介绍趋势.xlsx'
    ]
    FOLDER = r'E:\Onedrive\doc\for_share\Python_eco\excel'
    
    #2 处理
    app = Dispatch('excel.application')	# 打开Excel程序
    app.Visible = -1	# 显示程序
    for file in FILENAMES:	# 循环表
        filepath = os.path.join(FOLDER,file)	# 路径合并成完整路径
        wkb = app.Workbooks.Open(filepath)	# 打开Excel文件
        for p in wkb.Queries:	# 遍历Power Queries
            replacePair = [
                ('date_sub(curdate(),interval day(curdate())-1 day)',
                'date_sub(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month)')
            ]	# 替换文本 替换为文本
            fml = p.formula
            for a in replacePair:
                fml = fml.replace(a[0],a[1])
            p.formula = fml
            print('**REPLACED')
        wkb.Save()
        wkb.Close(1)
        print('**DONE')

# 总结 #

1.  问题主要在于`pywin32`没有Excel操作相关的说明文档
2.  问题的其次在于网络上也很少有VBA或者Python操作`Power Query`的现成代码
3.  关键代码在于`wkb.Queries`和`p.formula`这两个

## 打包 ##

现在给这一块打包成函数,按照`Python`国际惯例

def replacePowerQuery(filelist,replaceTuple):
    	'''将Excel里面的Power Query SQL脚本替换部分词'''
    	from win32com.client import Dispatch
    	app = Dispatch('excel.application')
    	app.Visible = -1
    	for file in filelist:
    	    wkb = app.Workbooks.Open(file)
    	    for p in wkb.Queries:
    	        fml = p.formula
    	        for a in replaceTuple:
    	            fml = fml.replace(a[0],a[1])
    	        p.formula = fml
    	        print('**REPLACED')
        wkb.Save()
        wkb.Close(1)
        app.Quit()
        print('**DONE')

*  filelist参数为列表或元组,元素为完整的文件路径
 *  replaceTuple为列表嵌套元组或者元组嵌套元组

# 使用示例
    filelist = [
        r'E:\Onedrive\doc\for_share\Python_eco\excel\【共和国】【月】转介绍表一.xlsx',
        r'E:\Onedrive\doc\for_share\Python_eco\excel\【共和国】【月】转介绍表二.xlsx',
        r'E:\Onedrive\doc\for_share\Python_eco\excel\【共和国】【月】转介绍表三.xlsx',
        r'E:\Onedrive\doc\for_share\Python_eco\excel\【共和国】【月】转介绍趋势.xlsx'
    ]
    replaceTuple = [
    	('date_sub(curdate(),interval 1 day)','date_sub(curdate(),interval day(curdate()) day)'),
        ('date_sub(max(everyday),interval 1 day)','date_sub(max(everyday),interval day(max(everyday)) day)')
    ]
    
    replacePowerQuery(filelist,replaceTuple)

[02f6967aa41cbf9b24840f9cf7e54af5.png]: /images/20220829/83f723310adf441fb4a9254a49d87b3e.png