含Power Query的Excel文件经openpyxl操作后损坏问题求助

问题场景 #

需要从文件名提取周数，在Excel文件的「Input」工作表M5单元格写入对应日期。现有代码在无Power Query的.xlsm文件中运行正常，但处理包含Power Query的.xlsm文件时，会导致文件损坏，修复后Power Query全部丢失。

原代码如下：

import os
from datetime import datetime, timedelta
import calendar
import openpyxl

folder_path = "c:/icons/ugelistchangedate/"
week_numbers = [11, 12, 13, 15]
sheet_name = "Input"
cell_location = "M5"

for filename in os.listdir(folder_path):
    if filename.endswith(".xlsm"):
        for week_number in week_numbers:
            if f"{week_number}" in filename.lower():
                file_path = os.path.join(folder_path, filename)

                # Calculate last date of week number
                year = datetime.now().year
                week_start = datetime.strptime(f"{year}-W{week_number}-1", "%G-W%V-%u")
                week_end = datetime.strptime(f"{year}-W{week_number}-7", "%G-W%V-%u")

                if week_start.month == week_end.month:
                    # Last date of the given week number
                    last_date_of_week = week_end.date()
                else:
                    # Last date of the month
                    last_date_of_week = datetime(year=year, month=week_start.month, day=calendar.monthrange(year, week_start.month)[1]).date()

                # Open the workbook and write the date in the specified sheet and cell
                workbook = openpyxl.load_workbook(file_path, read_only=False, keep_vba=True, keep_links=True, data_only=True)
                sheet = workbook[sheet_name]
                sheet[cell_location].value = last_date_of_week.strftime("%Y-%m-%d")
                workbook.save(file_path)

问题原因 #

openpyxl仅能解析和修改Excel的基础XML结构，虽然支持保留VBA，但无法识别和完整保留Power Query（Get & Transform）的配置信息。Power Query的M代码、连接规则等存储在文件的隐藏结构中，openpyxl保存时会破坏这些结构，最终导致文件损坏，修复后Power Query内容丢失。

解决方案 #

改用win32com.client调用本地Excel应用程序操作文件。这种方式依托Excel自身的API完成读写，能完整保留文件所有原有结构，包括Power Query、VBA宏等。

修改后的代码：

import os
import win32com.client as win32
from datetime import datetime
import calendar

folder_path = "c:/icons/ugelistchangedate/"
week_numbers = [11, 12, 13, 15]
sheet_name = "Input"
cell_location = "M5"

# 初始化Excel应用实例
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False  # 后台静默运行，不显示Excel窗口
excel.DisplayAlerts = False  # 关闭保存、覆盖等系统提示框

try:
    for filename in os.listdir(folder_path):
        if filename.endswith(".xlsm"):
            for week_number in week_numbers:
                if f"{week_number}" in filename.lower():
                    file_path = os.path.join(folder_path, filename)
                    
                    # 计算目标日期逻辑保持不变
                    year = datetime.now().year
                    week_start = datetime.strptime(f"{year}-W{week_number}-1", "%G-W%V-%u")
                    week_end = datetime.strptime(f"{year}-W{week_number}-7", "%G-W%V-%u")

                    if week_start.month == week_end.month:
                        last_date_of_week = week_end.date()
                    else:
                        last_date_of_week = datetime(year=year, month=week_start.month, day=calendar.monthrange(year, week_start.month)[1]).date()
                    
                    # 通过Excel API打开工作簿
                    wb = excel.Workbooks.Open(file_path)
                    try:
                        # 定位工作表并写入日期
                        ws = wb.Sheets(sheet_name)
                        ws.Range(cell_location).Value = last_date_of_week.strftime("%Y-%m-%d")
                        # 保存修改
                        wb.Save()
                    finally:
                        # 确保工作簿关闭
                        wb.Close(False)
finally:
    # 退出Excel应用，释放资源
    excel.Quit()

注意事项 #

• 需确保本地安装了Excel软件，win32com.client依赖Excel的COM组件
• 运行代码时不要手动打开目标文件，避免文件被锁定导致操作失败
• 调试时可将Visible改为True，查看Excel窗口内的操作过程

内容的提问来源于stack exchange，提问作者Michael Rygaard