使用openpyxl的.save方法时出现‘1不是有效列名’ValueError报错求助

我有一段用openpyxl处理Excel的代码，负责打开另一脚本生成的Excel文件，做基础格式化后保存关闭。之前运行正常，但最近调用.save()时随机出现KeyError '1'和ValueError: 1 is not a valid column name报错。修改过生成output.xlsx的代码但文件格式没变，换保存文件名没用，去掉.save()能运行但无法保存结果。首次报错后再运行会出现File is not a zip file，第三次则是There is no item named '[Content_Types].xml' in the archive。

原代码 #

import openpyxl as opxl
from openpyxl.styles import Font, PatternFill

chime_low = 60
chime_high = 66
adj_low = -3
adj_high = 3
red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
green_fill = PatternFill(start_color='0000FF00', end_color='0000FF00', fill_type='solid')

output_path = 'output.xlsx'
output = opxl.load_workbook(filename= output_path)
output_sheet = output.active
max_row = output_sheet.max_row
max_column = output_sheet.max_column

a1 = output_sheet['A1']
a1.font = Font(bold=True)
a1.value = 'Chime ID'

v_centerAlignment = opxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row = max_row, max_col = max_column):
    for cell in col:
        cell_str = str(cell)
        col_let = cell_str[-3]
        cell.alignment = v_centerAlignment
        output_sheet.column_dimensions[f'{col_let}'].width = 20
      
        
for col in output_sheet.iter_cols(min_row=2, min_col=3, max_row = max_row, max_col = 3):
    for cell in col :
        if chime_low <= cell.value <= chime_high:
            cell.fill = green_fill
        else:
            cell.fill = red_fill
            
for col in output_sheet.iter_cols(min_row=2, min_col=4, max_row = max_row, max_col = 4):
    for cell in col :
        if adj_low <= cell.value <= adj_high:
            cell.fill = green_fill
        else:
            cell.fill = red_fill            
            
output.save(output_path)

output.close

报错信息 #

Formatting.py', wdir='C:/Users/LZMYKK/Documents/Python Scripts/Chime Automation')
Traceback (most recent call last):

  File openpyxl\utils\cell.py:121 in openpyxl.utils.cell.column_index_from_string

KeyError: '1'


During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\spyder_kernels\py3compat.py:356 in compat_exec
    exec(code, globals, locals)

  File c:\users\lzmykk\documents\python scripts\chime automation\chime post processing excel formatting.py:49
    output.save(output_path)

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\workbook\workbook.py:407 in save
    save_workbook(self, filename)

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:293 in save_workbook
    writer.save()

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:275 in save
    self.write_data()

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:75 in write_data
    self._write_worksheets()

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:215 in _write_worksheets
    self.write_worksheet(ws)

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:200 in write_worksheet
    writer.write()

  File openpyxl\worksheet\_writer.py:358 in openpyxl.worksheet._writer.WorksheetWriter.write

  File openpyxl\worksheet\_writer.py:103 in openpyxl.worksheet._writer.WorksheetWriter.write_top

  File openpyxl\worksheet\_writer.py:87 in openpyxl.worksheet._writer.WorksheetWriter.write_cols

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\worksheet\dimensions.py:233 in to_tree
    for col in sorted(self.values(), key=sorter):

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\worksheet\dimensions.py:227 in sorter
    value.reindex()

  File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\worksheet\dimensions.py:176 in reindex
    self.min = self.max = column_index_from_string(self.index)

  File openpyxl\utils\cell.py:123 in openpyxl.utils.cell.column_index_from_string

ValueError: 1 is not a valid column name

解决方案 #

1. 修复列名获取的错误逻辑 #

原代码中通过str(cell)[-3]提取列字母的方式完全不可靠：openpyxl的Cell对象转字符串是<Cell '工作表名'.单元格地址>格式，当行号是个位数（比如A1），或者列超过Z（比如AA10）时，取倒数第三个字符会得到行号或其他错误内容，导致column_dimensions接收数字作为列名，触发报错。

替换为openpyxl官方提供的可靠方式：

• 直接使用Cell对象的column_letter属性
• 或者用openpyxl.utils.get_column_letter工具函数通过列号转换

修改后的格式化循环代码：

import openpyxl as opxl
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter  # 新增导入

# ... 其他代码不变 ...

v_centerAlignment = opxl.styles.Alignment(
        horizontal="center", vertical="center", wrapText=True
    )

# 优化后的列格式化逻辑：避免重复设置列宽
for col_idx in range(1, max_column + 1):
    col_let = get_column_letter(col_idx)
    # 设置整列单元格对齐
    for row_idx in range(1, max_row + 1):
        cell = output_sheet.cell(row=row_idx, column=col_idx)
        cell.alignment = v_centerAlignment
    # 设置列宽（只需要执行一次，不需要遍历每个单元格）
    output_sheet.column_dimensions[col_let].width = 20

# ... 后续条件格式代码不变 ...

2. 修复文件关闭的错误 #

原代码中output.close只是引用方法，没有执行关闭操作，导致文件被占用，后续运行时读取到损坏的文件，出现File is not a zip file等报错。需要改为调用方法：

output.save(output_path)
output.close()  # 加上括号执行关闭

3. 确保生成Excel的代码正确关闭文件 #

检查生成output.xlsx的代码，确保它在写入完成后正确关闭文件，避免文件处于未完全写入的损坏状态，导致openpyxl读取失败。

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