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.

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’}

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')

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

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"

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")

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()

enter image description here