在AWS Lambda中替换S3里Excel指定工作表并保留其他内容

错误原因 #

xlsxwriter引擎仅支持创建新Excel文件，不支持追加或修改现有文件，因此使用mode='a'会触发ValueError。

最优方案：使用openpyxl引擎修改现有文件 #

openpyxl是支持读写现有Excel文件的引擎，完美适配你的需求——保留透视表工作表，替换数据源工作表。

步骤1：安装依赖

pip install openpyxl

步骤2：修改后的代码

import pandas as pd
import io
import boto3

# 读取外部数据源为DataFrame
df1 = pd.read_csv(data, sep=",")

bucket = 'bucket-name'
filepath = 'my_file.xlsx'

# 从S3读取原Excel文件到内存（避免本地存储）
s3 = boto3.resource('s3')
obj = s3.Bucket(bucket).Object(filepath)

with io.BytesIO(obj.get()['Body'].read()) as excel_buffer:
    # 打开文件并替换指定工作表
    with pd.ExcelWriter(
        excel_buffer,
        engine='openpyxl',
        mode='a',
        if_sheet_exists='replace'
    ) as writer:
        # 将DataFrame写入'Data'工作表，index=False根据需求决定是否保留索引
        df1.to_excel(writer, sheet_name='Data', index=False)
    
    # 将指针移到缓冲区开头，准备上传回S3
    excel_buffer.seek(0)
    # 存回S3
    s3.Bucket(bucket).put_object(Key=filepath, Body=excel_buffer.getvalue())

关键说明 #

• 先将S3上的Excel文件读取到内存的BytesIO对象中，全程无需落地到本地磁盘，适合云环境（如Lambda）
• if_sheet_exists='replace'会直接覆盖名为Data的工作表，保留其他工作表（包括透视表）
• 替换后，Excel透视表的数据源仍指向Data工作表，打开文件时会自动刷新（需Excel启用自动刷新设置）

桌面环境补充：自动刷新透视表 #

如果你的运行环境是桌面且安装了Excel，可以用xlwings实现代码内刷新透视表：

import xlwings as xw
import boto3

# 先下载文件到本地
s3 = boto3.resource('s3')
s3.Bucket(bucket).download_file(filepath, 'local_file.xlsx')

# 打开文件替换数据并刷新透视表
wb = xw.Book('local_file.xlsx')
# 替换数据源
wb.sheets['Data'].range('A1').value = df1
# 刷新透视表（假设透视表在名为'PivotTable'的工作表）
wb.sheets['PivotTable'].api.PivotTables(1).RefreshTable()
wb.save()

# 上传回S3
s3.Bucket(bucket).upload_file('local_file.xlsx', filepath)

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