如何用Python脚本修改Excel 2016中Power Query的数据源？

方法一：通过COM接口操作Excel（Windows环境，需安装Excel） #

这种方法直接调用Excel的API修改Power Query查询，逻辑直观，适合有Excel环境的场景。

1. 先安装依赖库：

pip install pywin32

2. 编写Python脚本：

import win32com.client as win32
import os

# 定义文件路径
excel_path = r"Test.xlsx"
old_csv_path = r"D:\HUONGBT\file1.csv"
new_csv_path = r"D:\HUONGBT\file2.csv"

# 启动Excel后台进程
excel = win32.DispatchEx("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False

try:
    # 打开目标Excel文件
    workbook = excel.Workbooks.Open(os.path.abspath(excel_path))
    
    # 遍历所有Power Query查询
    for query in workbook.Queries:
        if old_csv_path in query.Formula:
            # 替换数据源路径
            query.Formula = query.Formula.replace(old_csv_path, new_csv_path)
            print(f"已修改查询 {query.Name} 的数据源路径")
    
    # 保存并关闭文件
    workbook.Save()
    workbook.Close()
    print("操作完成，文件已保存")
except Exception as e:
    print(f"发生错误：{str(e)}")
finally:
    # 退出Excel进程
    excel.Quit()
    del excel

方法二：直接修改Excel压缩包内的查询文件（跨平台，无需Excel） #

Excel本质是ZIP压缩包，Power Query的查询代码通常存储在xl/queries/目录下的.pq文件中。我们可以直接解压、修改、重新打包完成操作。

编写Python脚本（使用Python内置zipfile库）：

import zipfile
import tempfile
import shutil
import os

# 定义路径
excel_path = r"Test.xlsx"
old_csv_path = r"D:\HUONGBT\file1.csv"
new_csv_path = r"D:\HUONGBT\file2.csv"

# 创建临时目录用于解压Excel文件
with tempfile.TemporaryDirectory() as temp_dir:
    # 解压Excel到临时目录
    with zipfile.ZipFile(excel_path, 'r') as zip_ref:
        zip_ref.extractall(temp_dir)
    
    # 查找并修改所有查询文件
    queries_dir = os.path.join(temp_dir, "xl", "queries")
    if os.path.exists(queries_dir):
        for filename in os.listdir(queries_dir):
            if filename.endswith(".pq"):
                file_path = os.path.join(queries_dir, filename)
                with open(file_path, 'r', encoding='utf-8') as f:
                    content = f.read()
                if old_csv_path in content:
                    new_content = content.replace(old_csv_path, new_csv_path)
                    with open(file_path, 'w', encoding='utf-8') as f:
                        f.write(new_content)
                    print(f"已修改查询文件 {filename}")
    
    # 重新打包成新的Excel文件
    output_path = r"Test_modified.xlsx"  # 可替换为原路径直接覆盖
    with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zip_ref:
        for root, dirs, files in os.walk(temp_dir):
            for file in files:
                file_full_path = os.path.join(root, file)
                arcname = os.path.relpath(file_full_path, temp_dir)
                zip_ref.write(file_full_path, arcname)
    
    print(f"修改完成，新文件已保存为 {output_path}")

注意事项 #

• 方法一需确保Excel已安装，且脚本运行环境有足够权限操作Excel进程。
• 方法二操作前建议备份原Excel文件，避免因格式问题损坏文件。
• 若Power Query查询是嵌入在工作表而非独立查询中，需修改xl/workbook.xml里的对应节点，查找包含旧路径的XML内容替换即可。

内容的提问来源于stack exchange，提问作者Huong Bui