openpyxl对excel报表进行基本操作（二）

基本说明

1.设置字体样式用Font方法
2.单元格填充使用PatternFill方法
3.设置边框样式使用 Border+Side

设置excel单一单元格样式

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

wb = Workbook()
ws = wb.active

rows = [
    ['字体'],
    ['字体大小'],
    ['是否加粗'],
    ['是否斜体'],
    ['垂直对齐'],
    ['下划线'],
    ['删除线'],
    ['字体颜色'],
]
for row in rows:
    ws.append(row)

a1 = ws['A1']
a1.fill = PatternFill(fill_type='solid', start_color='FFFFFF00')  # solid表示纯色填充,start表示前景色填充，end表示背景色，我们一般使用start填充
a1.font = Font(name='arial')

a2 = ws['A2']
a2.font = Font(size=16)  # 字体大小

a3 = ws['A3']
a3.font = Font(bold=True)  # 加粗

a4 = ws['A4']
a4.font = Font(italic=True)  # 斜体

a5 = ws['A5']
a5.font = Font(vertAlign='superscript')  # 垂直对齐方式：superscript:向上对齐   baseline：居中对齐    subscript:向下对齐

a6 = ws['A6']
a6.font = Font(
    underline='doubleAccounting')  # 下划线类型： double：双下划线  single:单下划线  doubleAccounting：覆盖双下划线  singleAccounting：覆盖单下划线

a7 = ws['A7']
a7.font = Font(strike=True)  # 删除线

a8 = ws['A8']
a8.font = Font(color='FFEE0000')  # 设置对应颜色的ARGB值
a8.border = Border(left=Side(border_style='hair', color='FFEE0000'),
                   right=Side(border_style='thick', color='FF00A8FF'),
                   top=Side(border_style='dashed', color='FF11FF00'))  # 设置边框线，dashed：短线  thick：粗线  hair：虚线  thin:细线

wb.save('w4.xlsx')

保存结果如下：

批量设置单元格样式

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

font = Font(bold=True, size=12, name='微软雅黑', vertAlign='baseline')
border = Border(left=Side(border_style='thin', color='00000000'),
                right=Side(border_style='thin', color='00000000'),
                top=Side(border_style='thin', color='00000000'),
                bottom=Side(border_style='thin', color='00000000')
                )
tc = PatternFill(fill_type='solid', start_color='FFFFFF00')
wb = Workbook()
ws = wb.active

thead = ['服务', '微服务', '姓名', '工号']
thead1= ['服务', '微服务', '姓名', '工号']
thead2 = ['服务', '微服务', '姓名', '工号']
ws.append(thead)
ws.append(thead1)
ws.append(thead2)
for row in ws.iter_rows(min_col=1, max_col=3):  # 最大列为3，最小列为1
    for cell in row:
        if cell.row % 2 == 1:  # 如果行为奇数
            cell.fill = tc  # 填充奇数行
            cell.border = border  # 设置边框
            cell.font = font  # 设置字体以及对齐方式
wb.save('test.xlsx')

保存结果如下：