python 操作excel 样式

1、给表格添加边框
引用函数 format_border(ws, start_row, end_row, start_col, end_col)

需要引入的包
import openpyxl
from openpyxl.styles import Border, Side
from openpyxl.utils.cell import get_column_letter

ws代表：ws=openpyxl.load_workbook(dir_name1)["Sheetname"]

def format_border(ws, start_row, end_row, start_col, end_col):
    #  内部边界  Internal border
    for row in tuple(ws[start_row:end_row]):
        for cell in row[start_col-1:end_col]:
            cell.border = set_border('medium', 'medium', 'medium', 'medium')
    #   左边界  Left border
    for cell in [row[start_col-1] for row in ws[start_row:end_row]]:
        cell.border = set_border(cell.border.top.style, cell.border.bottom.style, 'medium', cell.border.right.style)
    #  右边界  Right border
    for cell in [row[end_col-1] for row in ws[start_row:end_row]]:
        cell.border = set_border(cell.border.top.style, cell.border.bottom.style, cell.border.left.style, 'medium')
    #  上边界  Upper border
    for cell in ws[start_row][start_col-1:end_col]:
        cell.border = set_border('medium', cell.border.bottom.style, cell.border.left.style, cell.border.right.style)
    #  下边界  Lower border
    for cell in ws[end_row][start_col-1:end_col]:
        cell.border = set_border(cell.border.top.style, 'medium', cell.border.left.style, cell.border.right.style)
    return ws

# 定义的边界风格  Defined border style
def set_border(t_border, b_border, l_border, r_border, t_color='000000', b_color='000000', l_color='000000', r_color='000000'):
    border = Border(top=Side(border_style=t_border, color=t_color),
                    bottom=Side(border_style=b_border, color=b_color),
                    left=Side(border_style=l_border, color=l_color),
                    right=Side(border_style=r_border, color=r_color))
    return border

2、合并单元格

ws.merge_cells('B3:D3')

3、设置表格字体与字体大小

font = Font(name="宋体",size=12)  # bold指是否加粗,italic指是否倾斜
#get_column_letter()用来获取列名 如A、B、C、
for k in range(sheet.max_column):
    for l in range(sheet.max_row):
        cell = ws[f"{get_column_letter(k + 1)}{str(l + 1)}"]
        cell.font = font

4、设置行高

#设置第一行的高度为25
ws.row_dimensions[1].height = 25

5、设置列宽

#设置A列的宽度为20  如果需要转化为PDF 总宽度不得超过80
ws.column_dimensions["A"].width=20.0

6、设置所有数据水平居中 垂直居中

 for key in list(ws._cells.keys()):
        ws._cells[key].alignment = Alignment(wrapText=True,horizontal='center',vertical='center')