Openpyxl执行3条及以上命令时保存Excel工作簿失败问题

问题现象 #

使用Openpyxl创建带格式与数据的Excel文档时，仅执行列宽、行高设置能正常保存；但添加单元格赋值操作（如sheet['A1'] = 'Hello'）后，工作簿保存失败，抛出类型错误及后续权限错误。

可运行代码 #

# 导入依赖（原代码遗漏，需补充）
from openpyxl import Workbook

# 创建新工作簿并获取活动工作表
workbook = Workbook()
sheet = workbook.active

# 设置第一列列宽和第一行行高
sheet.column_dimensions['A'].width = 2
sheet.row_dimensions['1'].height = 10

# 保存工作簿
workbook.save('Documentation.xlsx')

print("Excel文档创建成功。")

不可运行代码 #

# 导入依赖
from openpyxl import Workbook

# 创建新工作簿并获取活动工作表
workbook = Workbook()
sheet = workbook.active

# 设置第一列列宽和第一行行高
sheet.column_dimensions['A'].width = 2
sheet.row_dimensions['1'].height = 10
sheet['A1'] = 'Hello'  # 新增赋值命令引发错误

# 保存工作簿
workbook.save('Documentation.xlsx')

print("Excel文档创建成功。")

报错信息 #

Traceback (most recent call last):
  File "C:\Users\pcristea\Documents\Learning.py", line 59, in <module>
    main()
  File "C:\Users\pcristea\Documents\Learning.py", line 54, in main
    workbook.save('Documentation.xlsx')
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\workbook\workbook.py", line 386, in save
    save_workbook(self, filename)
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\writer\excel.py", line 294, in save_workbook
    writer.save()
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\writer\excel.py", line 77, in write_data
    self._write_worksheets()
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\writer\excel.py", line 215, in _write_worksheets
    self.write_worksheet(ws)
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\writer\excel.py", line 200, in write_worksheet
    writer.write()
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\worksheet\_writer.py", line 359, in write
    self.write_rows()
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\worksheet\_writer.py", line 124, in write_rows
                            ^^^^^^^^^^^
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\worksheet\_writer.py", line 117, in rows
    return sorted(rows.items())
           ^^^^^^^^^^^^^^^^^^^^
TypeError: '<' not supported between instances of 'str' and 'int'
Exception ignored in atexit callback: <function _openpyxl_shutdown at 0x0000026428AECA40>
Traceback (most recent call last):
  File "C:\Users\pcristea\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\worksheet\_writer.py", line 32, in _openpyxl_shutdown
    os.remove(path)
PermissionError: [WinError 32] 无法访问文件，因为它正被另一个进程使用: 'C:\Users\pcristea\AppData\Local\Temp\openpyxl.tky3u7x7'

解决方案 #

根本原因 #

Python 3.12调整了字典排序的底层逻辑，而旧版本openpyxl（低于3.1.2）在处理工作表行数据时，会尝试对混合了字符串和整数类型的键进行排序，触发类型错误；后续的权限错误是类型错误导致临时文件未正常清理引发的连锁问题。

修复步骤 #

1. 升级openpyxl到兼容Python 3.12的版本：
   打开终端执行以下命令：

   pip install --upgrade openpyxl
   

   需确保升级后的版本≥3.1.2，该版本已修复Python 3.12的兼容性问题。

2. 排查文件占用情况：
   保存前确认Documentation.xlsx未被Excel或其他程序打开，避免权限冲突。

验证：升级后重新运行代码，即可正常保存包含格式和单元格数据的工作簿。

内容的提问来源于stack exchange，提问作者Paul Adrian Cristea