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

基本说明

1.合并与解除单元格
2.设置单元格样式
3.批量设置单元格样式

合并与解除单元格

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

wb = Workbook()
ws = wb.active

ws['A2'] = '我是合并单元格'
ws.merge_cells('A2:D2')  # 合并单元格

ws['A6'] = '我是合并单元格'
ws.merge_cells('A6:D6')  # 合并单元格
ws.unmerge_cells('A6:D6')  # 解除合并单元格
wb.save('test.xlsx')

保存结果如下：

设置单元格样式

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

wb = Workbook()
ws = wb.active

# 设置合并单元格样式
ws.merge_cells('B2:F4')  # 合并单元格
top_left_cell = ws['B2']  # 合并的单元格成为新的B2单元格
top_left_cell.value = 'My Cell'
top_left_cell.fill = PatternFill('solid', fgColor='DDDDDD')
top_left_cell.font = Font(bold=True, color='FF0000')
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")  # 垂直水平居中
wb.save('test.xlsx')

保存结果如下：

批量设置单元格样式

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

wb = Workbook()
ws = wb.active

rows = [
    ['计算机', '', ''],
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9],
    [0, '.', '%'],
]
for row in rows:
    ws.append(row)
    
for col in ws["A":"C"]:
    for r in col:
        r.font = Font(name='微软雅黑', size=13, color='FFFFFFFF')
        r.fill = PatternFill(fill_type='solid', start_color="FFFF6100")
        r.border = Border(
            left=Side(border_style='thin', color='FF0F0F0F'),
            right=Side(border_style='thin', color='FF0F0F0F'),
            top=Side(border_style='thin', color='FF0F0F0F'),
            bottom=Side(border_style='thin', color='FF0F0F0F'),
        )
        r.alignment = Alignment(horizontal='center', vertical='center')

ws.merge_cells('A1:C1')  # 合并A1到C1
top_left_cell = ws['A1']  # 合并后的单元格成为新的A1单元格
top_left_cell.font = Font(name='微软雅黑', size=14, color='FFFFFFFF', bold=True)  # 需要重新设置样式
wb.save('test.xlsx')

保存结果如下：