Apply borders to all cells in a range with openpyxl

Question

I have a script that takes a pandas dataframe and chops it up into several hundred chunks and saves each chunk as a separate excel file. Each chunk will have the same number of columns but the number of rows varies. I've figured out how to apply all the other necessary formatting to these files with openpyxl, but I haven't yet determined the fastest way to apply borders. Also, I think I'm just not applying borders correctly, because the code below (which I suspect shouldn't need to loop over each cell individually) doesn't apply any borders.

from openpyxl.style import Border

wb = load_workbook(filename = _fname)
ws = wb.worksheets[0]  
for _row in ws.range('A1:L'+str(ws.get_highest_row() ) ):
    for _cell in _row:
            _cell.style.borders.left.border_style = Border.BORDER_THIN
            _cell.style.borders.right.border_style = Border.BORDER_THIN
            _cell.style.borders.top.border_style = Border.BORDER_THIN
            _cell.style.borders.bottom.border_style = Border.BORDER_THIN
wb.save(_fname)

So this code works, but it doesn't apply the border I expect (the default border in excel) and it takes a lot more steps than I'd prefer. My expectation is that I should be able to do something like this:

from openpyxl.style import Border

wb = load_workbook(filename = _fname)
ws = wb.worksheets[0]

_range = ws.some_range_func('A1:L'+str(ws.get_highest_row() ) ):
    _range.style.borders.all_borders = Borders.BORDER_THIN

Does this functionality exist? If not, can someone please be so kind as to at least explain how to apply the default border style and not this slightly thicker border? None of Border.BORDER_THICK, Border.BORDER_MEDIUM, Border.BORDER_THIN, or Border.BORDER_HAIR seem correct.

Thanks!

score 19 · Answer 1 · answered Nov 25 '20 at 01:40

19

In more pythonic way for openpyxl==3.0.5:

from openpyxl.styles import Border, Side

def set_border(ws, cell_range):
    thin = Side(border_style="thin", color="000000")
    for row in ws[cell_range]:
        for cell in row:
            cell.border = Border(top=thin, left=thin, right=thin, bottom=thin)

set_border(worksheet, 'A5:C10')

answered Nov 25 '20 at 01:40

Shivam Miglani

542
3
9

This worked as the best solution for me and is also less verbose than other suggestions (+1). – Thirst for Knowledge Dec 30 '21 at 15:27

score 8 · Answer 2 · edited Jun 13 '21 at 09:01

8

May be this is handy:

from openpyxl.reader.excel import load_workbook
from openpyxl.style import Border

def set_border(ws, cell_range):
    rows = was[cell_range]
    for row in rows:
        row[0].style.borders.left.border_style = Border.BORDER_THIN
        row[-1].style.borders.right.border_style = Border.BORDER_THIN
    for c in rows[0]:
        c.style.borders.top.border_style = Border.BORDER_THIN
    for c in rows[-1]:
        c.style.borders.bottom.border_style = Border.BORDER_THIN

#usage example:
ws = load_workbook('example.xlsx').get_active_sheet()
set_broder(ws, "C3:H10")

It performs reasonably fast.

edited Jun 13 '21 at 09:01

Abbas

3,872
6
36
63

answered Oct 08 '13 at 09:23

Rabih Kodeih

9,361
11
47
55

3

`def set_border(ws, cell_range): rows = ws[cell_range] for row in rows: row[0].border = Border(left=Side(style='thin')) row[-1].border = Border(right=Side(style='thin')) for c in rows[0]: c.border = Border(top=Side(style='thin')) for c in rows[-1]: c.border = Border(bottom=Side(style='thin'))` – Ryan Davies Jun 07 '19 at 11:09
2

Received the error: `AttributeError: 'Worksheet' object has no attribute 'range'` Specifically, in line: `rows = ws.range(cell_range)` – Anthony R Jul 17 '19 at 14:12
Replace Range by: rows = ws[cell_range] – Laurent T Aug 06 '20 at 12:27
I think this code only borders the first and last columns's all rows, more apt answer would be the @Tarun one(just below) – Arnav Das Sep 08 '21 at 16:21

Tarun Pathak · Answer 3 · 2020-07-30T07:02:38.423

There is a slight modification to answer from @Karimov
Below is how your code should be

from openpyxl.styles import Border, Side, Font, Alignment

def __format_ws__(self, ws, cell_range):
     border = Border(left=Side(border_style='thin', color='000000'),
                    right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'),
                    bottom=Side(border_style='thin', color='000000'))

      rows = ws[cell_range]
      for row in rows:
          for cell in row:
              cell.border = border

A much faster way that uses list comprehension is below:

 def __format_ws__(self, ws, cell_range):

        #applying border and alignment
        font = Font(size=9)
        align=Alignment(horizontal='left', vertical='center')
        border = Border(left=Side(border_style='thin', color='000000'),
                        right=Side(border_style='thin', color='000000'),
                        top=Side(border_style='thin', color='000000'),
                        bottom=Side(border_style='thin', color='000000'))

        rows = [rows for rows in ws[cell_range]]
        flattened = [item for sublist in rows for item in sublist]
        [(setattr(cell,'border',border), setattr(cell,'font',font), setattr(cell,'alignment',align)) for cell in flattened]

The way you use it is:

self.__format_ws__(ws=writer.book.worksheets[0], cell_range='A1:G10')

score 5 · Answer 4 · edited Aug 27 '18 at 16:25

5

Decision that works on openpyxl 2.3.5

from openpyxl.styles import Border, Side

def set_border(ws, cell_range):
    border = Border(left=Side(border_style='thin', color='000000'),
                right=Side(border_style='thin', color='000000'),
                top=Side(border_style='thin', color='000000'),
                bottom=Side(border_style='thin', color='000000'))

    rows = ws.iter_rows(cell_range)
    for row in rows:
        for cell in row:
            cell.border = border

set_border(worksheet, 'A5:C10')

edited Aug 27 '18 at 16:25

user6039980

3,108
8
31
57

answered Mar 30 '17 at 06:08

Karimov Dmitriy

140
2
6

4

Error I received: `'TypeError: 'str' object cannot be interpreted as an integer'` – Anthony R Jul 17 '19 at 14:14

score 2 · Answer 5 · answered Aug 13 '14 at 15:58

@user698585 your approach seems nice but it doesn't work anymore as the present version of the openpyxl change the implementation. So this should be updated into e.g.

    ws.cell(row=1, column=1).style.border.top.border_style = borders.BORDER_MEDIUM

but it results with an error that changing the style is not allowed. As a workaround I just defined a dedicated styles, but they are just a duplication of the present styles plus border definition - not so good solution as works only if you know what style has the cell under the change.

    border_style = Style(font=Font(name='Console', size=10, bold=False,
                         color=Color(openpyxl.styles.colors.BLACK)),
                         fill=PatternFill(patternType='solid', fgColor=Color(rgb='00C5D9F1')),
                         border=Border(bottom=Side(border_style='medium', color=Color(rgb='FF000000'))))

Styles are immutable as they may be shared by different cells. The best way to change the style for a cell is to `.copy()` and pass in the parts you want to change. — Charlie Clark, Aug 16 '14 at 12:09

score 2 · Answer 6 · edited Aug 30 '23 at 00:52

from copy import copy 

def set_border(ws, cell_range, style='thin'):
    rows = ws[cell_range]
    for row in rows:
        temp_row = copy(row[0].border)
        row[0].border = Border(left=Side(style=style), right=temp_row.right, top=temp_row.top, bottom=temp_row.bottom)

        temp_row = copy(row[-1].border)
        row[-1].border = Border(right=Side(style=style), left=temp_row.left, top=temp_row.top, bottom=temp_row.bottom)
    for c in rows[0]:
        temp_row = copy(c.border)
        c.border = Border(top=Side(style=style), left=temp_row.left, bottom=temp_row.bottom, right=temp_row.right)
    for c in rows[-1]:
        temp_row = copy(c.border)
        c.border = Border(bottom=Side(style=style), left=temp_row.left, top=temp_row.top, right=temp_row.right)

This keeps the existing borders of the side and you can also style your border

Is copy() from 'import copy'? I'm getting a "'Module' object is not callable" error. — Diego, Aug 29 '23 at 19:03
Found my error. Either change copy() to copy.copy() or import like this: from copy import copy. — Diego, Aug 29 '23 at 19:14

jassinm · Answer 7 · 2012-11-30T19:41:49.407

if you need styling (borders...) for pandas excel dataframe my fork just got merged into master https://github.com/pydata/pandas/pull/2370#issuecomment-10898427

as for you borders problems. setting all borders at once does not seam to work in openpyxl.

In [34]: c.style.borders.all_borders.border_style = openpyxl.style.Border.BORDER_THIN

In [36]: c.style
'Calibri':11:False:False:False:False:'none':False:'FF000000':'none':0:'FFFFFFFF':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':0:'thin':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'general':'bottom':0:False:False:0:'General':0:'inherit':'inherit'

setting individually works ('thin':'FF000000')

In [37]: c.style.borders.top.border_style = openpyxl.style.Border.BORDER_THIN

In [38]: c.style
Out[38]: 'Calibri':11:False:False:False:False:'none':False:'FF000000':'none':0:'FFFFFFFF':'FF000000':'none':'FF000000':'none':'FF000000':'thin':'FF000000':'none':'FF000000':'none':'FF000000':0:'thin':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'general':'bottom':0:False:False:0:'General':0:'inherit':'inherit'

maybe a bug in openpyxl. but no big deal just wrap setting bottom , top, left, right in function

Wait, so with your change I should be able to apply XLSX styling in my call to df.to_excel()? Can you maybe show me a demo of how this would work and also explain what I need to do to update my version of pandas to include your feature? — David Marx, Nov 30 '12 at 18:48
just clone master and install from source. df.to_excel will add borders to header, bold colnames, merge multiindex... here's a pic http://cl.ly/image/2r102L0E1l23. It will autostyle. You can have a look at the source to fix your problem above — jassinm, Nov 30 '12 at 19:11
I want to apply my own style, so this isn't what I need. Sounds like the best way is what I'm already doing. Thanks! — David Marx, Dec 03 '12 at 02:10

Ryan Davies · Answer 8 · 2019-06-10T10:23:00.597

Had the same issue but couldn't find anything that fixes this problem for 2019 because of depreciation. I have something that works below.. could be better but works for all intends and purposes.

def set_border(ws, cell_range):
    rows = ws[cell_range]
    for row in rows:
        if row == rows[0][0] or row == rows[0][-1] or row == rows[-1][0] or row == rows[-1][-1]:
            pass
        else:
            row[0].border = Border(left=Side(style='thin'))
            row[-1].border = Border(right=Side(style='thin'))
        for c in rows[0]:
            c.border = Border(top=Side(style='thin'))
        for c in rows[-1]:
            c.border = Border(bottom=Side(style='thin'))
    rows[0][0].border = Border(left=Side(style='thin'), top=Side(style='thin'))
    rows[0][-1].border = Border(right=Side(style='thin'), top=Side(style='thin'))
    rows[-1][0].border = Border(left=Side(style='thin'), bottom=Side(style='thin'))
    rows[-1][-1].border = Border(right=Side(style='thin'), bottom=Side(style='thin'))

I suppose this applies an outer border to the range – Bruno Vermeulen Jan 02 '21 at 08:05 — Bruno Vermeulen, Jan 02 '21 at 08:05

score -1 · Answer 9 · answered Sep 26 '17 at 03:10

seems there is no built-in for this task, and we have to make some steps ourselves, like:

#need make conversion from alphabet to number due to range function
def A2N(s,e):
    return range(ord(s), ord(e)+1)
#B1 is the border you defined
#Assume you trying border A1-Q1 ... A3-Q3
X = A2N('A','Q')
#print X    
your_desired_sheet_range_rows = range(1,4)
#need two loop to go through cells
for row in your_desired_sheet_rows:
    for col in X:
        ca = chr(col)
        sheet[ca+str(row)].border=B1

Apply borders to all cells in a range with openpyxl

9 Answers9

Linked