Apply Border To Range Of Cells Using Openpyxl

问题:

I am using python 2.7.10 and openpyxl 2.3.2 and I am a Python newbie.

I am attempting to apply a border to a specified range of cells in an Excel worksheet (e.g. C3:H10). My attempt below is failing with the the following message:

AttributeError: 'Cell' object has no attribute 'styles'.

How do I attach a border to a cell? Any insights would be gratefully received.

My current code:

import openpyxl
from openpyxl.styles import Border, Side

def set_border(ws, cell_range):
    rows = ws.iter_rows(cell_range)
    for row in rows:
        row[0].styles.borders = Border(left=Side(border_style='thin', color="FF000000"))
        row[-1].styles.borders = Border(right=Side(border_style='thin', color="FF000000"))
    for c in rows[0]:
        c.styles.borders = Border(top=Side(border_style='thin', color="FF000000"))
    for c in rows[-1]:
        c.styles.borders = Border(bottom=Side(border_style='thin', color="FF000000"))


# Example call to set_border
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.get_sheet_by_name('Sheet1')

set_border(ws, "B3:H10")

回答1:

First of all properties are called style (not styles) and border (not borders). Also to change border you should set cell.border directly.

Besides that you have some problems with borders logic, it's more complex to get it working correctly, because of iterators and corners. Here is a rough version (it is as simple as I could get it, but not memory efficient):

def set_border(ws, cell_range):
    rows = ws[cell_range]
    side = Side(border_style='thin', color="FF000000")

    rows = list(rows)  # we convert iterator to list for simplicity, but it's not memory efficient solution
    max_y = len(rows) - 1  # index of the last row
    for pos_y, cells in enumerate(rows):
        max_x = len(cells) - 1  # index of the last cell
        for pos_x, cell in enumerate(cells):
            border = Border(
                left=cell.border.left,
                right=cell.border.right,
                top=cell.border.top,
                bottom=cell.border.bottom
            )
            if pos_x == 0:
                border.left = side
            if pos_x == max_x:
                border.right = side
            if pos_y == 0:
                border.top = side
            if pos_y == max_y:
                border.bottom = side

            # set new border only if it's one of the edge cells
            if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
                cell.border = border

回答2:

border = Border(
            left=cell.border.left,
            right=cell.border.right,
            top=cell.border.top,
            bottom=cell.border.bottom)

can be replaced by:

border = cell.border.copy()

PS: your answer helped me...