python 设置 Excel 单元格边框线的各种风格

0、import

import openpyxl
form openpyxl.styles import Border, borders, colors, Side

 

1、查看边框线的内置 styles

print(dir(borders))

border_styles = ['BORDER_DASHDOT', 'BORDER_DASHDOTDOT', 'BORDER_DASHED', 'BORDER_DOTTED', 
          'BORDER_DOUBLE', 'BORDER_HAIR', 'BORDER_MEDIUM', 'BORDER_MEDIUMDASHDOT', 
          'BORDER_MEDIUMDASHDOTDOT', 'BORDER_MEDIUMDASHED', 'BORDER_NONE', 
          'BORDER_SLANTDASHDOT', 'BORDER_THICK', 'BORDER_THIN']

border_styles = [eval(f'openpyxl.styles.borders.{bs}') for bs in border_styles] 
border_styles

 

 

 2、在 excel 中设置各种边框线

wb = openpyxl.Workbook()
ws = wb.active

di = 1   # 每块合并 2 行
dj = 2    # 每块合并 3 列
top_cells = []    # 合并块中最上面的单元格
bottom_cells = []    # 合并块中最下面的单元格
for i in range(2, 23, 6):
    for j in range(2, 15, 4):
        # 合并单元格
        ws.merge_cells(start_row=i,
                       start_column=j,
                       end_row=i+di,
                       end_column=j+dj)
        # 添加单元格的列表
        top_cells.append([ws.cell(row=i, column=j+k) for k in range(dj+1)])
        bottom_cells.append([ws.cell(row=i+di, column=j+k) for k in range(dj+1)])

for i, bs in enumerate(border_styles):
    side = Side(border_style=bs,
                color=colors.RED)
    for cell_a, cell_b in zip(top_cells[i], bottom_cells[i]):
        cell_a.border = Border(top=side)    # 设置合并块中最上面的单元格的上边框线
        cell_b.border = Border(bottom=side)    # 设置合并块中最下面的单元格的下边框线
    if not bs:
        bs = 'None'
    top_cells[i][0].value = bs    # 在合并块中左上角单元格中写入值

wb.save('test.xlsx')

excel 效果：