1.0 单独设置某个单元格的边框
复制即可使用,仅需要修改传入的Excel名(路径)、Sheet名、要修改的单元格索引
# Author: CDamogu
# Date: 2022/05/12
# Des: Set Border to Excel Singel Cells, openpyxl
import openpyxl
from openpyxl.styles import *
outputXlName = 'excelTest.xlsx'
inputShtName = 'Sheet1'
wb = openpyxl.load_workbook(outputXlName)
ws = wb[inputShtName]
#设置边框{'medium' 中粗 'thin' 细 'thick' 粗 'dashed' 虚线 'dotted' 点线}
def format_border_cell(ws,row_index,col_index):
ws.cell(row_index,col_index).border = Border(top = Side(border_style='thick', color='FF000000'),
right = Side(border_style='thick', color='FF000000'),
bottom = Side(border_style='thick', color='FF000000'),
left = Side(border_style='thick', color='FF000000'))
# 调用函数,给('A1')单元格设置边框
format_border_cell(ws,1,1)
wb.save(outputXlName)
2.0 给某个区域设置边框样式(表格边框内外边框不同处理)
复制即可使用,仅需要修改传入的Excel名(路径)、Sheet名、要修改的行列索引
# Author: CDamogu
# Date: 2022/05/12
# Des: Set Border to Excel Singel Cells, openpyxl
import openpyxl
from openpyxl.styles import *
outputXlName = 'excelTest.xlsx'
inputShtName = 'Sheet1'
wb = openpyxl.load_workbook(outputXlName)
ws = wb[inputShtName]
# ボーダー設定 边界设置 Border settings
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
# Param: 表格Sheet名,起始行,终止行,起始列,终止列
format_border(ws,1,81,3,14)
wb.save(outputXlName)