1.5 格式化Excel - 让表格好看一点

from openpyxl.styles import Font# 创建格式bold_font = Font(name='微软雅黑', size=12, bold=True, color='FF0000')normal_font = Font(name='微软雅黑', size=11)# 应用到单元格sheet['A1'].font = bold_fontsheet['A2'].font = normal_font

import openpyxlfrom openpyxl.styles import Font, Alignment, PatternFill, Border, Sidedef style_cell(cell, font=None, alignment=None, fill=None, border=None):    """设置单元格样式"""    if font:        cell.font = font    if alignment:        cell.alignment = alignment    if fill:        cell.fill = fill    if border:        cell.border = border# 创建工作簿wb = openpyxl.Workbook()sheet = wb.active# 定义样式header_font = Font(name='微软雅黑', size=14, bold=True, color='FFFFFF')header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')header_alignment = Alignment(horizontal='center', vertical='center')data_font = Font(name='微软雅黑', size=11)data_alignment = Alignment(horizontal='center', vertical='center')border = Border(    left=Side(style='thin'),    right=Side(style='thin'),    top=Side(style='thin'),    bottom=Side(style='thin'))# 写入表头headers = ['姓名', '语文', '数学', '英语', '总分']for col, header in enumerate(headers, start=1):    cell = sheet.cell(row=1, column=col)    cell.value = header    style_cell(cell, font=header_font, fill=header_fill,                alignment=header_alignment, border=border)# 写入数据students = [    ('张三', 90, 85, 92),    ('李四', 78, 92, 88),    ('王五', 85, 80, 95),    ('赵六', 92, 88, 90),]for row_idx, (name, chinese, math, english) in enumerate(students, start=2):    # 姓名    cell = sheet.cell(row=row_idx, column=1)    cell.value = name    style_cell(cell, font=data_font, alignment=data_alignment, border=border)    # 成绩    for col, score in enumerate([chinese, math, english], start=2):        cell = sheet.cell(row=row_idx, column=col)        cell.value = score        style_cell(cell, font=data_font, alignment=data_alignment, border=border)    # 总分公式    cell = sheet.cell(row=row_idx, column=5)    cell.value = f'=SUM(B{row_idx}:D{row_idx})'    style_cell(cell, font=data_font, alignment=data_alignment, border=border)# 设置行高列宽sheet.row_dimensions[1].height = 30for col in ['A', 'B', 'C', 'D', 'E']:    sheet.column_dimensions[col].width = 12wb.save('美化后的成绩表.xlsx')print("美化完成！")

# ❌ 错误：每次都创建新样式for cell in sheet['A1:A10']:    cell.font = Font(bold=True)  # 这样会每次都创建新对象# ✅ 正确：提前定义样式bold_font = Font(bold=True)for cell in sheet['A1:A10']:    cell.font = bold_font  # 复用同一个对象

# openpyxl使用RGB格式，颜色值以'FF'开头# FF0000 = 红色# 00FF00 = 绿色# 0000FF = 蓝色# FFFFFF = 白色# 000000 = 黑色

# 设置样式后，新的赋值不会清除样式sheet['A1'].value = '新文字'  # 字体、颜色等样式会保留

from openpyxl.styles import Font, Alignment, PatternFill, Border, Side# ==================== 定义样式 ====================# 字体header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')data_font = Font(name='微软雅黑', size=11)# 对齐center_align = Alignment(horizontal='center', vertical='center')# 填充header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')# 边框thin_border = Border(    left=Side(style='thin'),    right=Side(style='thin'),    top=Side(style='thin'),    bottom=Side(style='thin'))# ==================== 应用样式 ====================cell.font = header_fontcell.alignment = center_aligncell.fill = header_fillcell.border = thin_border# ==================== 调整尺寸 ====================sheet.row_dimensions[1].height = 25sheet.column_dimensions['A'].width = 15