使用Python openpyxl保存带宏xlsm文件时出现Bad file descriptor错误

尝试用openpyxl打开带宏的xlsm文件，修改单元格后保存时出现OSError: [Errno 9] Bad file descriptor错误，代码如下：

from openpyxl import load_workbook

# 加载Excel文件
workbook = load_workbook(r'FFM102-L-10-EBCL9.xlsm', read_only=False, keep_vba=True)

# 打开指定工作表
sheet = workbook['Track']

# 修改目标单元格
sheet["B3"] = 5000

# 保存文件
workbook.save(r"FFM102-L-10-EBCL9.xlsm")

报错信息：

Traceback (most recent call last):   File "write_existing.py", line
56, in 
workbook.save(r"FFM102-L-10-EBCL9.xlsm")   File "C:\Users\z004eu7h\AppData\Roaming\Python\Python310\site-packages\openpyxl\workbook\workbook.py",
line 386, in save
save_workbook(self, filename)   File "C:\Users\z004eu7h\AppData\Roaming\Python\Python310\site-packages\openpyxl\writer\excel.py",
line 294, in save_workbook
writer.save()   File "C:\Users\z004eu7h\AppData\Roaming\Python\Python310\site-packages\openpyxl\writer\excel.py",
line 275, in save
self.write_data()   File "C:\Users\z004eu7h\AppData\Roaming\Python\Python310\site-packages\openpyxl\writer\excel.py",
line 60, in write_data
archive.writestr(ARC_APP, tostring(props.to_tree()))   File "C:\Users\z004eu7h\AppData\Local\Programs\Thonny\lib\zipfile.py", line
1814, in writestr
with self.open(zinfo, mode='w') as dest:   File "C:\Users\z004eu7h\AppData\Local\Programs\Thonny\lib\zipfile.py", line
1178, in close
self._fileobj.seek(self._zinfo.header_offset) OSError: [Errno 9] Bad file descriptor

解决方法 #

1. 避免直接覆盖原文件：openpyxl加载文件后会持有原文件的句柄，直接覆盖会触发文件描述符错误。先保存到临时文件，关闭工作簿后再替换原文件。
2. 确保文件未被其他程序占用：确认原xlsm文件没有被Excel或其他程序打开，否则会锁定文件导致无法写入。

修改后的代码：

from openpyxl import load_workbook
import os
import shutil

original_path = r'FFM102-L-10-EBCL9.xlsm'
temp_path = r'FFM102-L-10-EBCL9_temp.xlsm'

# 加载工作簿，保留宏
wb = load_workbook(original_path, read_only=False, keep_vba=True)
sheet = wb['Track']
sheet["B3"] = 5000

# 保存到临时文件
wb.save(temp_path)
# 释放工作簿资源
del wb

# 替换原文件
os.remove(original_path)
shutil.move(temp_path, original_path)

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