6

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")
Yaroslav Admin
  • 13,880
  • 6
  • 63
  • 83
Pete B
  • 139
  • 2
  • 2
  • 6

2 Answers2

18

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
Adam Stewart
  • 2,001
  • 1
  • 16
  • 16
Yaroslav Admin
  • 13,880
  • 6
  • 63
  • 83
  • Firstly thank you for the response. Once my reputation exceeds 15 my feedback will show your response was useful. Second, ignoring the possible logical problems for the moment, changing the properties name to border (from borders) and setting the cell.border directly (by removing .styles) yields TypeError: 'generator' object has no attribute '__getitem__' at line 9 (for c in rows[0]:). Could you offer any help on what this means? – Pete B Dec 30 '15 at 02:19
  • @PeteB `ws.iter_rows(cell_range)` returns [generator](http://stackoverflow.com/q/1756096/1377864). You can't use `row[N]` syntax to get `N`th item from generator (that's the reason why I convert it to list in my example). – Yaroslav Admin Dec 30 '15 at 02:24
  • The answer overwrites any existing borders such that if you have inner thinner borders this creates an outer that deleted the inner. Is this intentional? – ctrl-alt-delete Feb 22 '16 at 15:46
  • @toasteez I'm not sure what do you mean by inner and outer borders. I've added a fix to leave all borders except the one being added unmodified. Check if it solves your issue. – Yaroslav Admin Feb 23 '16 at 11:17
0
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...

Denis Walrave
  • 1
  • 2