Applying borders to a cell in OpenPyxl

Question

I am trying to use Openpyxl to apply a border to a cell, but I have failed on the most basic "apply any kind of border to any cell anywhere" task. I tried copying from the Openpyxl documentation (http://pythonhosted.org/openpyxl/styles.html#introduction) default style and modifying, but that gives me

TypeError:__init__() got an unexpected keyword argument 'superscript'

I tried copying straight out of another example here (Apply borders to all cells in a range with openpyxl), but that gives me

AttributeError: type object 'Border' has no attribute 'BORDER_THIN'

(even after I fix the typos and insufficient imports errors).

Does anyone know how to apply borders using Python 3.3 and OpenPyxl 2.0.4? All I'm looking for is a snippet of code that, if I copy-paste it into a blank script, will put a border around any cell in a workbook.

You should avoid asking for code to copy-paste, as it is likely to garner down votes on your question and make people unhappy. You've done a good job describing your problem and the steps you've taken to try to fix it, though you may get better results if you embed the code you're currently working with and the full traceback, rather than just code links sections of the traceback. The call stack provided by Python's exception handling can help diagnose your issue. — skrrgwasme, Jul 23 '14 at 17:46
The exception suggests it may be a bug, though one that relates to fonts rather than borders. Please paste the code you're currently using so that we can investigate it. — Charlie Clark, Jul 24 '14 at 10:16
Sorry if I seem picky, especially since the two previous two comments almost contradict each other, but the link to pythonhosted.org is currently broken. — Michael Scheper, Apr 27 '16 at 17:08
Another possible source to your issue. If you write a value to the cell after you applied formatting it may blank out your previously successful formatting. One more thing to look out for. — M T Head, Sep 19 '16 at 16:35

score 69 · Answer 1 · edited Nov 20 '19 at 10:26

69

With openpyxl version 2.2.5, this snippet works for me:

from openpyxl.styles.borders import Border, Side
from openpyxl import Workbook

thin_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))

wb = Workbook()
ws = wb.get_active_sheet()
# property cell.border should be used instead of cell.style.border
ws.cell(row=3, column=2).border = thin_border
wb.save('border_test.xlsx')

The documentation mentions other values for the style attribute :

Value must be one of {‘double’, ‘dashed’, ‘thin’, ‘medium’, ‘mediumDashDot’, ‘dashDot’, ‘thick’, ‘mediumDashed’, ‘hair’, ‘dotted’, ‘slantDashDot’, ‘mediumDashDotDot’, ‘dashDotDot’}

edited Nov 20 '19 at 10:26

SdaliM

105
4

answered Aug 21 '15 at 05:31

takasu

789
5
5

1

From other answers, it seems borders can also be `'medium'` and therefore, presumably, `'thick'` too. Can somebody post a reference to other valid values? – Michael Scheper Apr 27 '16 at 17:06
With version 2.4.4, other values seem to be: dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin. For other versions, [visit the docs](http://openpyxl.readthedocs.io/en/default/_modules/openpyxl/styles/borders.html#Side) – SdaliM Mar 02 '17 at 15:01
@SdaliM, the docs are dead, any other docs link with updated details? Please share. Thanks! – Dr. Essen Nov 19 '19 at 05:53
1

@Ac3_DeXt3R I edited the answer to include my link. It is being peer-reviewed. Anyway, [here is an updated link to the docs](https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.borders.html#openpyxl.styles.borders.Side.style). The values don't appear to have changed. – SdaliM Nov 20 '19 at 10:02

score 12 · Answer 2 · answered Jul 24 '18 at 08:45

This answer works with version 2.4.8 The difference with the previous two answers is that the property for Side is border_style, not style

from openpyxl.styles.borders import Border, Side, BORDER_THIN
thin_border = Border(
    left=Side(border_style=BORDER_THIN, color='00000000'),
    right=Side(border_style=BORDER_THIN, color='00000000'),
    top=Side(border_style=BORDER_THIN, color='00000000'),
    bottom=Side(border_style=BORDER_THIN, color='00000000')
)
ws.cell(row=3, column=2).border = thin_border

Working with styles: https://openpyxl.readthedocs.io/en/2.5/styles.html

score 11 · Answer 3 · answered Aug 27 '14 at 06:26

With openpyxl version 2.0.4, this snippet works for me:

from openpyxl.styles.borders import Border, Side
from openpyxl.styles import Style
from openpyxl import Workbook

thin_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))
my_style = Style(border=thin_border)

wb = Workbook()
ws = wb.get_active_sheet()
ws.cell(row=3, column=2).style = my_style
wb.save('border_test.xlsx')

score 2 · Answer 4 · answered Sep 13 '21 at 10:08

Set all cells border to white except the cells with value

from openpyxl import Workbook
from openpyxl.styles import Side, Border


def set_border(ws, side=None, blank=True):
    wb = ws._parent
    side = side if side else Side(border_style='thin', color='000000')
    for cell in ws._cells.values():
        cell.border = Border(top=side, bottom=side, left=side, right=side)
    if blank:
        white = Side(border_style='thin', color='FFFFFF')
        wb._borders.append(Border(top=white, bottom=white, left=white, right=white))
        wb._cell_styles[0].borderId = len(wb._borders) - 1


if __name__ == '__main__':
    from openpyxl.styles import Alignment

    wb = Workbook()
    ws = wb.active
    ws.merge_cells('A1:J2')
    ws['A1'] = 'Merge'
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    ws._current_row = 2
    for i in range(1, 26):
        ws.append([i] * 10)

    side = Side(border_style='thin', color='FF0000')
    set_border(ws, side)

    wb.save('test.xlsx')
    wb.close()

score 1 · Answer 5 · answered Jun 07 '21 at 02:59

If I wanna put only the bottom line in a cell, Then mention only for the bottom side of a cell like the code below,

from openpyxl.styles.borders import Border, Side

wb = Workbook()
ws = wb.active

thin_border = Border(bottom=Side(style='thin'))
ws.cell(row=3, column=2).border = thin_border

wb.save("test.xlsx")

score 1 · Answer 6 · answered Aug 16 '21 at 23:11

Define Style and apply all your style and then you can apply that style to any cell.

You can define Border, Font, Alignment, Fill etc.

I have taken the example of Border and Alignment:

bd = Side(border_style='thin')
border = Border(left=bd, top=bd, right=bd, bottom=bd)
center_alignment = Alignment(horizontal="center", vertical="center")


common_style_center = NamedStyle(name="commonStyleCenter")
common_style_center.border = border
common_style_center.alignment = center_alignment

# apply style to the cell. 
summary_sheet.cell(row=scenario_start_row, column=1, value=serial_number).style = 'commonStyleCenter'

Applying borders to a cell in OpenPyxl

6 Answers6

Linked