如何自动刷新通过ODBC连接AS400的.xlsm文件（Python/CMD实现）

针对你需要定期自动刷新连接AS400的XLSM文件需求，以下是两种可行的无人工干预方案，同时提供绕过凭证输入的具体方法：

方案一：用pywin32操控Office 365 Excel完成自动刷新 #

通过Python的pywin32库直接控制Excel应用，修改连接字符串嵌入凭证，避免手动输入弹窗，然后执行刷新操作。

代码示例 #

import win32com.client as win32
import time

# 初始化Excel后台进程
excel = win32.Dispatch("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False

# 打开目标XLSM文件
wb = excel.Workbooks.Open(r"C:\实际路径\你的文件.xlsm")

# 遍历所有连接，定位AS400相关连接并注入凭证
for conn in wb.Connections:
    # 根据你的连接名称或字符串特征判断是否为AS400连接
    if "AS400" in conn.Name or "iSeries" in conn.OLEDBConnection.Connection:
        original_conn = conn.OLEDBConnection.Connection
        # 在原连接字符串后追加凭证参数（如果已有则替换）
        new_conn_str = original_conn + ";UID=你的用户名;PWD=你的密码;"
        conn.OLEDBConnection.Connection = new_conn_str
        conn.Refresh()

# 执行全表刷新（可选，确保所有数据连接都更新）
wb.RefreshAll()
# 根据数据量设置等待时间，确保刷新完成
time.sleep(30)

# 保存并退出
wb.Save()
wb.Close()
excel.Quit()

注意事项 #

• 确保本地安装了Office 365，且pywin32版本与Office位数（32/64位）匹配；
• 凭证硬编码存在安全风险，建议用环境变量、加密配置文件或keyring库存储和读取；
• 如果是ODBC连接而非OLEDB，需修改为conn.ODBCConnection.Connection来调整连接字符串。

方案二：直接用Python ODBC连接AS400，写入XLSM数据 #

跳过Excel的内置刷新机制，直接通过Python连接AS400获取数据，再写入到XLSM文件的对应工作表，完全自主控制数据更新流程。

代码示例 #

import pyodbc
import openpyxl

# 构建AS400 ODBC连接字符串
as400_conn_str = (
    "DRIVER={IBM i Access ODBC Driver};"
    "SYSTEM=你的AS400服务器IP/主机名;"
    "UID=你的用户名;"
    "PWD=你的密码;"
    "DBQ=你的目标库名;"
)

# 连接AS400并查询数据
db_conn = pyodbc.connect(as400_conn_str)
cursor = db_conn.cursor()
cursor.execute("SELECT * FROM 你的目标表")  # 替换为实际查询语句
data = cursor.fetchall()

# 打开XLSM文件（保留VBA宏）
wb = openpyxl.load_workbook(r"C:\实际路径\你的文件.xlsm", keep_vba=True)
ws = wb["目标工作表名"]  # 替换为实际工作表名称

# 清空原有数据（假设第1行是表头，从第2行开始清空）
for row in ws.iter_rows(min_row=2, max_col=ws.max_column, max_row=ws.max_row):
    for cell in row:
        cell.value = None

# 写入新数据
for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# 保存文件并关闭连接
wb.save(r"C:\实际路径\你的文件.xlsm")
cursor.close()
db_conn.close()

注意事项 #

• 需提前安装IBM i Access ODBC Driver；
• openpyxl仅负责读写单元格数据，不会执行XLSM中的宏，如果宏依赖刷新后的格式调整，需结合pywin32执行宏；
• 确保查询结果的列顺序与XLSM工作表的列顺序一致，避免数据错位。

绕过凭证输入的额外方法 #

如果不想通过代码处理，可直接在Windows系统中配置ODBC数据源：

1. 打开「ODBC数据源管理器」（匹配Office位数）；
2. 添加「IBM i Access ODBC Driver」类型的系统DSN，配置AS400服务器信息并勾选「保存密码」；
3. 修改XLSM文件中的数据连接，指向这个预配置的DSN，后续Excel刷新时会自动使用保存的凭证，无需手动输入。

内容的提问来源于stack exchange，提问作者fil.