使用openpyxl输入数据后行列样式未生效，如何提取并应用样式？

问题原因 #

Excel中整列设置的样式属于列级默认格式，并非直接存储在每个空单元格中。openpyxl仅能读取单元格自身已保存的样式，未编辑过的空单元格不会携带列的默认样式，因此直接写入值时不会自动继承整列颜色，提取样式也只能拿到默认值。

解决方案 #

1. 写入单元格时手动应用列的默认填充样式 #

先获取目标列的填充样式，再将其应用到新写入的单元格：

from openpyxl import load_workbook

file_path = 'test.xlsx'
workbook = load_workbook(file_path)
sheet = workbook.active

# 获取目标列（示例为C列）的列对象，提取默认填充样式
column_c = sheet.column_dimensions['C']
target_fill = column_c.fill

# 写入数据并应用样式
cell = sheet['C1']
cell.value = 'Your Data Here'
cell.fill = target_fill

workbook.save(file_path)
workbook.close()

2. 提取列的默认样式（而非单个单元格样式） #

若要获取整列的设置样式，需读取列维度对象的属性，而非遍历单元格：

import openpyxl

workbook = openpyxl.load_workbook('test.xlsx')
worksheet = workbook.active

# 直接读取列维度的样式属性
column_letter = 'B'
column_dim = worksheet.column_dimensions[column_letter]

print(f"列{column_letter}的默认样式：")
print(f"  填充样式: {column_dim.fill}")
print(f"  字体: {column_dim.font}")
print(f"  对齐方式: {column_dim.alignment}")
print(f"  数字格式: {column_dim.number_format}")

workbook.close()

3. 批量给列中现有/新单元格应用整列样式 #

若需给列中多个单元格批量应用整列样式，可遍历列并逐个设置：

from openpyxl import load_workbook

file_path = 'test.xlsx'
workbook = load_workbook(file_path)
sheet = workbook.active

target_col = 'C'
column_dim = sheet.column_dimensions[target_col]
# 获取列的所有样式属性
col_fill = column_dim.fill
col_font = column_dim.font
col_alignment = column_dim.alignment
col_num_format = column_dim.number_format

# 遍历列中需要处理的单元格（示例为前10行）
for row in range(1, 11):
    cell = sheet[f"{target_col}{row}"]
    # 空单元格可先设默认值再应用样式
    if not cell.value:
        cell.value = ""
    cell.fill = col_fill
    cell.font = col_font
    cell.alignment = col_alignment
    cell.number_format = col_num_format

workbook.save(file_path)
workbook.close()

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