Openpyxl：从columns对象提取列字母以实现列级条件格式

我想给openpyxl的columns对象里的所有列逐列应用条件格式，需要遍历每个列元组并提取对应的列字母。现在我在下面的函数里尝试用get_column_letter(col)从col元组获取列字母，但没成功，求帮忙：

def create_formatted_table(wb, worksheets: list, file_name):
    existing_tables = [
        wb[sheetname].tables.items()[0][0]
        for sheetname in wb.sheetnames
        if len(wb[sheetname].tables.items()) > 0
    ]
    worksheets = [
        worksheet for worksheet in worksheets if worksheet not in existing_tables
    ]
    if worksheets:
        for ws in worksheets:
            worksheet = wb[ws]
            for col in worksheet.columns:
                column_letter = get_column_letter(col)
                min_row = col[0].row
                max_row = col[-1].row
                rule = ColorScaleRule(start_type='min', start_color=Color(rgb="FFB499"), 
                                    end_type='max', end_color=Color(rgb="99FFC3"))  
                worksheet.conditional_formatting.add(f"{column_letter}{min_row}:{column_letter}{max_row}", rule),    
            wb.save(file_name)
    else:
        print("Nothing")

核心问题是get_column_letter()的参数错误：你传入的col是整列的单元格元组，但这个函数需要的是列的数字索引（比如A列对应1，B列对应2）。

修正方法：

• 从列元组的第一个单元格col[0]中提取列索引数字（col[0].column）
• 将该数字传入get_column_letter()，就能得到对应的列字母

另外还需要去掉add()方法末尾多余的逗号，避免生成无用元组。

修正后的完整代码：

from openpyxl.styles import Color
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.utils import get_column_letter

def create_formatted_table(wb, worksheets: list, file_name):
    existing_tables = [
        wb[sheetname].tables.items()[0][0]
        for sheetname in wb.sheetnames
        if len(wb[sheetname].tables.items()) > 0
    ]
    worksheets = [
        worksheet for worksheet in worksheets if worksheet not in existing_tables
    ]
    if worksheets:
        for ws in worksheets:
            worksheet = wb[ws]
            for col in worksheet.columns:
                # 获取列数字索引，转换为列字母
                column_num = col[0].column
                column_letter = get_column_letter(column_num)
                min_row = col[0].row
                max_row = col[-1].row
                rule = ColorScaleRule(start_type='min', start_color=Color(rgb="FFB499"), 
                                    end_type='max', end_color=Color(rgb="99FFC3"))  
                worksheet.conditional_formatting.add(f"{column_letter}{min_row}:{column_letter}{max_row}", rule)
            wb.save(file_name)
    else:
        print("Nothing")

• col[0].column返回当前列的数字索引，这是get_column_letter()的合法参数
• 确保代码顶部已经导入get_column_letter工具函数

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