Python openpyxl 操作Excel 为指定Cells、区域设置边框线、颜色

最新推荐文章于 2025-02-28 10:29:31 发布

CDamogu

最新推荐文章于 2025-02-28 10:29:31 发布

阅读量1w

点赞数 10

文章标签： python 开发语言

本文链接：https://blog.csdn.net/qq_33704787/article/details/124730506

版权

openpyxl 同时被 2 个专栏收录

1 篇文章

订阅专栏

border

1 篇文章

订阅专栏

设置边框线

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)