如何使用openpyxl为指定单元格范围批量添加边框

背景 #

我正在使用openpyxl处理Excel文件，已掌握为单个单元格添加边框的方法，也实现了带条件的边框设置脚本，但当前实现是分别给单元格设置垂直和水平边框，我认为该方案不符合最佳实践。我希望可以实现直接指定单元格范围（例如调用border_range(B2:F8)）即可为对应范围添加边框的功能，可封装为自定义函数。

原有实现代码 #

import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Border, Side
import pandas as pd
import numpy

# 创建工作簿
wb = Workbook()
ws = wb.create_sheet('pandas', index = 0)

# 工作表属性设置
ws.sheet_view.showGridLines = False
ws.sheet_view.zoomScale = 85
for column in ["A","B","C","D","E","F"]:
    ws.column_dimensions[column].width = 17

# 创建DataFrame
data = {
    "Product Name": ["ID", "Product 0", "Product 1", "Product 2", "Product 3", "Product 4","Product 5"],
    "Sales Month 1": [30, 30, 30, 20, 20, 20, 10],
    "Sales Month 2": [30, 30, 5, 5, 35, 10, 20],
    "Sales Month 3": [30,30, 5, 10, 20, 10, 20],
    "Sales Month 4": [30, 30, 5, 5, 35, 10, 20],
    "Calification": ['0', '0', '1', '2', '3', '4', '5'],
    "Sales Month 6": [ 20 , 30, 5, 5, 35, 10, 20],
}

df = pd.DataFrame(data)
df = df.set_index(df.columns[0])

# 写入数据到Excel
for row in dataframe_to_rows(df, index = False, header=True):
    ws.append(row)

# 定义边框样式
borde_2_lados_derecha_Izquierda = Border(
                    left=Side(style='medium'),  
                    right=Side(style='medium'), 
                    top=Side(style=None), 
                    bottom=Side(style=None)
                    )
borde_1_lado_abajo = Border(
                        left=Side(style='medium'),  
                        right=Side(style='medium'), 
                        top=Side(style=None), 
                        bottom=Side(style='medium')
                        )
# 添加垂直边框
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
    for cell in row:
        cell.border = borde_2_lados_derecha_Izquierda

# 添加水平边框区分产品ID
columna = []
for row in ws.iter_rows(min_row=ws.min_row + 1, 
                        max_row=ws.max_row, 
                        min_col=ws.min_column, 
                        max_col=ws.min_column):
    for cell in row:
        columna.append(cell.value)

conjunto = list(set(columna))[::-1]
numero_filas = [columna.count(i) for i in conjunto]
a = numpy.cumsum(numero_filas)
for i in a:
    celda = i + 1
    for cell in ws[f"{celda}"]:
        cell.border = borde_1_lado_abajo

# 导出工作簿
wb.save("Practice.xlsx")

封装实现方案 #

可封装通用的范围边框设置函数，支持传入工作表、单元格范围字符串、边框样式和颜色参数，自动为指定范围添加外边框，同时保留单元格原有的其他边框设置：

def border_range(ws, range_str, border_style='medium', border_color='000000'):
    """
    为指定单元格范围添加外边框
    :param ws: openpyxl工作表对象
    :param range_str: 单元格范围字符串，例如'B2:F8'
    :param border_style: 边框样式，可选值同openpyxl的Side.style，如'thin','medium','thick'等
    :param border_color: 边框颜色，十六进制RGB字符串，默认黑色
    """
    # 解析单元格范围
    min_col, min_row, max_col, max_row = openpyxl.utils.range_boundaries(range_str)
    side = Side(style=border_style, color=border_color)
    
    # 遍历范围内的每个单元格
    for row in range(min_row, max_row + 1):
        for col in range(min_col, max_col + 1):
            cell = ws.cell(row=row, column=col)
            # 保留原有边框样式，仅修改外边框部分
            current_border = cell.border
            new_border = Border(
                left = side if col == min_col else current_border.left,
                right = side if col == max_col else current_border.right,
                top = side if row == min_row else current_border.top,
                bottom = side if row == max_row else current_border.bottom
            )
            cell.border = new_border

使用示例 #

需要设置边框时直接调用函数即可，例如给B2到F8范围添加默认黑色中等粗细的外边框：

border_range(ws, 'B2:F8')

如果需要自定义边框样式，比如设置为红色粗边框，调整对应参数即可：

border_range(ws, 'B2:F8', border_style='thick', border_color='FF0000')

该方案相比原有分别设置垂直、水平边框的逻辑复用性更强，不需要重复编写循环逻辑，也支持灵活适配不同的范围和边框样式需求。

内容的提问来源于stack exchange，提问作者Joel Barrantes