为什么需要设置Excel字体和颜色？

在数据处理和报表生成中，字体样式和颜色是提升Excel文档可读性和专业性的关键因素。通过Python的openpyxl库，您可以自动化完成这些样式设置：

• 突出显示关键数据和重要指标
• 创建专业美观的业务报表
• 自动化数据可视化样式
• 提高文档可读性和信息层级
• 批量处理大量Excel文件的样式

安装openpyxl库

开始之前，请确保已安装openpyxl库：

如果您需要处理旧版.xls文件，可以额外安装兼容库：

基础字体设置

以下示例展示如何设置基本字体属性：

from openpyxl import Workbook
from openpyxl.styles import Font

# 创建工作簿和工作表
wb = Workbook()
ws = wb.active

# 创建字体对象
bold_font = Font(name='Arial', size=14, bold=True, italic=False, 
                underline='single', color='FF0000')

# 应用字体到单元格
ws['A1'] = "标题样式示例"
ws['A1'].font = bold_font

# 保存工作簿
wb.save("font_example.xlsx")

颜色设置详解

openpyxl支持多种颜色设置方式：

# 红色字体
red_font = Font(color="FF0000")

# 绿色字体
green_font = Font(color="00FF00")

# 蓝色字体
blue_font = Font(color="0000FF")

from openpyxl.styles.colors import RED, GREEN, BLUE, YELLOW

# 使用预定义颜色
red_font = Font(color=RED)
green_font = Font(color=GREEN)
blue_font = Font(color=BLUE)
yellow_font = Font(color=YELLOW)

高级应用技巧

from openpyxl.styles import PatternFill

# 设置条件格式：数值大于90用绿色，小于60用红色
for row in ws.iter_rows(min_row=2, max_row=10, min_col=3, max_col=3):
    for cell in row:
        if cell.value > 90:
            cell.font = Font(color="00FF00", bold=True)
            cell.fill = PatternFill(start_color="C6EFCE", 
                                  end_color="C6EFCE", 
                                  fill_type="solid")
        elif cell.value < 60:
            cell.font = Font(color="FF0000", bold=True)
            cell.fill = PatternFill(start_color="FFC7CE", 
                                  end_color="FFC7CE", 
                                  fill_type="solid")

# 创建标题样式
header_font = Font(name='Calibri', size=16, bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="2c3e50", 
                        end_color="2c3e50", 
                        fill_type="solid")

# 应用样式到表头行
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 设置数据行样式
data_font = Font(name='Arial', size=12)
for row in ws.iter_rows(min_row=2, max_row=100):
    for cell in row:
        cell.font = data_font

完整示例：销售报表生成

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.styles.colors import WHITE, BLUE, RED, GREEN

# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售报表"

# 创建样式
title_font = Font(name='Arial', size=18, bold=True, color=WHITE)
header_font = Font(name='Arial', size=12, bold=True, color=WHITE)
data_font = Font(name='Calibri', size=11)
highlight_font = Font(name='Calibri', size=11, bold=True, color=RED)
good_font = Font(name='Calibri', size=11, bold=True, color=GREEN)

title_fill = PatternFill(start_color="2c3e50", fill_type="solid")
header_fill = PatternFill(start_color="3498db", fill_type="solid")
center_aligned = Alignment(horizontal='center')

# 设置标题
ws['A1'] = "2023年销售业绩报告"
ws['A1'].font = title_font
ws['A1'].fill = title_fill
ws.merge_cells('A1:E1')

# 设置表头
headers = ["产品", "一季度", "二季度", "三季度", "四季度"]
ws.append(headers)
for cell in ws[2]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_aligned

# 添加数据
data = [
    ["产品A", 15000, 22000, 18000, 25000],
    ["产品B", 9000, 12000, 15000, 21000],
    ["产品C", 25000, 18000, 22000, 19000]
]

for row in data:
    ws.append(row)

# 应用样式并高亮突出数据
for row in ws.iter_rows(min_row=3, max_row=5, min_col=2, max_col=5):
    for cell in row:
        cell.font = data_font
        cell.alignment = center_aligned
        # 高亮超过20000的销售额
        if cell.value and cell.value > 20000:
            cell.font = good_font
        # 标记低于10000的销售额
        elif cell.value and cell.value < 10000:
            cell.font = highlight_font

# 调整列宽
ws.column_dimensions['A'].width = 15
for col in ['B', 'C', 'D', 'E']:
    ws.column_dimensions[col].width = 12

# 保存文件
wb.save("sales_report.xlsx")