使用openpyxl设置单元格区域对齐遇TypeError问题求助

错误原因 #

你的cell_ranges列表中包含了单个单元格（如AC1），当调用ws['AC1']时，返回的是一个Cell对象，而非多行/多列的迭代器。内层循环for row in ws[cell_range]尝试迭代Cell对象，因此触发TypeError: 'Cell' object is not iterable。

修复方案 #

在处理单元格范围时，先判断返回的是单个Cell还是区域对象，分别进行处理：

修改后的完整代码 #

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment

def File_formatting():
    wb = load_workbook(filename=output_file_path)
    ws = wb.worksheets[0]
    ws['B3'] = 'ERSC/VFD/OL configured'
    ws.merge_cells('B2:B3')
    ws['C3'] = 'Motor/VFD/Disc Faults'
    ws.merge_cells('C3:D3')
    ws['E3'] = 'Horns'
    ws.merge_cells('E3:F3')
    ws['G3'] = 'Runs'
    ws.merge_cells('G3:H3')
    ws['I3'] = 'ESTOPS'
    ws.merge_cells('I3:J3')
    ws['K3'] = 'Full(s)'
    ws.merge_cells('K3:L3')
    ws['M3'] = 'Implied Case Jam(s)'
    ws.merge_cells('M3:N3')
    ws['O3'] = 'Jam(s)'
    ws.merge_cells('O3:P3')
    ws['Q3']= 'Beacon Lights'
    ws.merge_cells('Q3:R3')
    ws['S3'] = 'Solenoids'
    ws.merge_cells('S3:T3')
    ws['U3']= 'misc Items'
    ws.merge_cells('U3:V3')
    ws['W3'] = 'Cascade'
    ws.merge_cells('W3:X3')
    ws['Y3']= 'Up/Down Conv ILKs'
    ws.merge_cells('Y3:Z3')
    ws['AA2'] = 'Final Sign Off'
    ws.merge_cells('AA2:AB2')
    ws['AA3'] = 'Test Date'
    ws['AB3'] = 'Engineer Initials'
    ws['AC1'] = '="Functional Check % Completion: " & TEXT( COUNTIF(AA4:AA149, ">1/1/1900")/COUNTA(A4:A149),"0.0%")'
    ws['A1'] = 'Put Checksheet name here'
    ws.merge_cells('A1:AB1')
    ws['C2'] = 'Operational Checkout'
    ws.merge_cells('C2:Z2')
    cell_ranges = ['B2:B3', 'C3:D3', 'E3:F3', 'G3:H3', 'I3:J3', 'K3:L3', 'M3:N3', 'O3:P3', 'Q3:R3',
                   'S3:T3', 'U3:V3', 'W3:X3', 'Y3:Z3', 'AA2:AB2', 'AA3:AB3', 'AC1']

    # Set alignment for specified cell ranges
    blue_fill = PatternFill(start_color='0000FF', end_color='0000FF', fill_type='solid')
    center_alignment = Alignment(horizontal='center', vertical='bottom')

    for cell_range in cell_ranges:
        cells = ws[cell_range]
        # 处理单个单元格的情况
        if isinstance(cells, Cell):
            cells.alignment = center_alignment
            cells.fill = blue_fill
        else:
            # 处理多行多列的区域
            for row in cells:
                for cell in row:
                    cell.alignment = center_alignment
                    cell.fill = blue_fill
    wb.save(filename=output_file_path)

关键修改点 #

1. 提前定义center_alignment对象，避免重复创建，提升效率
2. 对每个cell_range，先获取cells对象，通过isinstance(cells, Cell)判断是否为单个单元格
3. 单个单元格直接设置格式，区域则按行→单元格的层级遍历设置

这样修改后，代码可以同时兼容单个单元格和单元格区域的格式设置，不会再触发迭代错误。

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