Python openpyxl样式设置详解：打造专业美观Excel报表教程

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# 设置字体
cell.font = Font(name='Arial', size=12, bold=True)

# 设置填充
cell.fill = PatternFill(start_color='FF0000', fill_type='solid')

# 设置边框
cell.border = Border(left=Side(style='thin'))

# 设置对齐
cell.alignment = Alignment(horizontal='center', vertical='center')

# 设置数字格式
cell.number_format = '#,##0.00'

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active

# 字体样式
ws['A1'] = '加粗标题'
ws['A1'].font = Font(name='微软雅黑', size=16, bold=True, color='2196F3')

ws['A2'] = '斜体文字'
ws['A2'].font = Font(size=12, italic=True, color='FF5722')

ws['A3'] = '下划线文字'
ws['A3'].font = Font(size=12, underline='single')

# 填充样式
ws['B1'] = '红色背景'
ws['B1'].fill = PatternFill(start_color='FFCDD2', end_color='FFCDD2', fill_type='solid')

ws['B2'] = '蓝色背景'
ws['B2'].fill = PatternFill(start_color='BBDEFB', end_color='BBDEFB', fill_type='solid')

ws['B3'] = '绿色背景'
ws['B3'].fill = PatternFill(start_color='C8E6C9', end_color='C8E6C9', fill_type='solid')

print("样式设置完成")
wb.save('font_fill_style.xlsx')
print("字体填充样式文件已保存")

样式设置完成
字体填充样式文件已保存

from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill

wb = Workbook()
ws = wb.active

# 定义边框样式
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

thick_bottom = Border(
    bottom=Side(style='medium')
)

# 表头样式
header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='2196F3', end_color='2196F3', fill_type='solid')
header_align = Alignment(horizontal='center', vertical='center')

# 写入表头
headers = ['编号', '姓名', '部门', '工资']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_align
    cell.border = thin_border

# 写入数据
data = [
    [1, '张三', '技术部', 15000],
    [2, '李四', '市场部', 12000],
    [3, '王五', '技术部', 18000],
]
data_align = Alignment(horizontal='center', vertical='center')
for r, row_data in enumerate(data, 2):
    for c, value in enumerate(row_data, 1):
        cell = ws.cell(row=r, column=c, value=value)
        cell.border = thin_border
        cell.alignment = data_align

# 设置列宽
ws.column_dimensions['A'].width = 10
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12

wb.save('border_align_style.xlsx')
print("边框对齐样式文件已保存")

边框对齐样式文件已保存

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, NamedStyle

wb = Workbook()
ws = wb.active

# 数字格式
ws['A1'] = '数字格式示例'
ws['A2'] = 1234567.89
ws['A2'].number_format = '#,##0.00'  # 千分位+2位小数

ws['A3'] = 0.85
ws['A3'].number_format = '0.0%'  # 百分比

ws['A4'] = 15000
ws['A4'].number_format = '\u00a5#,##0'  # 货币格式

# 命名样式（可复用）
highlight_style = NamedStyle(name='highlight')
highlight_style.font = Font(bold=True, color='FF0000')
highlight_style.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# 注册并使用命名样式
wb.add_named_style(highlight_style)
ws['B1'] = '重要数据'
ws['B1'].style = 'highlight'

ws['B2'] = '另一个重要数据'
ws['B2'].style = 'highlight'

print("数字格式与命名样式设置完成")
wb.save('number_named_style.xlsx')
print("数字格式样式文件已保存")

数字格式与命名样式设置完成
数字格式样式文件已保存