How to Add Border in Excel (Cell, Range, Rows) by openpyxl

Adding borders in Excel sheet by openpyxl

By using openpyxl library, you may add borders to the Excel cells.

Not only it enables you to add a border of the same style but you may specify different border styles for each side of the cell.

Following border styles are available:

  • dashed
  • dotted
  • double
  • thick
  • thin
  • dashDot
  • dashDotDot
  • medium
  • mediumDashDot
  • mediumDashDotDot
  • mediumDashed
  • hair

See examples below of setting single and different borders for individual cells. Later, you can also see examples of setting single and multiple rows borders.

An example of setting cells border of the same type

In this example, we will set the border of a cell of the same type. We will use the following sample sheet for all our examples below:

Excel border sample

You can see, the Status column cells are without any border. We will set the double border for two cells with the value “In Stock” (green color) and two cells of thick type (in red) with the value “Out of Stock”:

Python program:

# Setting the borders example
from openpyxl import load_workbook
from openpyxl.styles import Border, Side

#Loading the Workbook
wb_style = load_workbook('test_border.xlsx')

#Accessing Product Informaiton Sheet
sheet = wb_style.active

#Specifying border style and color = green
double = Side(border_style="double", color="00FF00")

#Specifying border style and color = red
thick = Side(border_style="thick", color="FF0000")

#Set the border style at individual cell level
cell_border_1 = sheet["D2"]
cell_border_1.border = Border(top=double, left=double, right=double, bottom=double)

cell_border_2 = sheet["D4"]
cell_border_2.border = Border(top=double, left=double, right=double, bottom=double)

cell_border_3 = sheet["D7"]
cell_border_3.border = Border(top=thick, left=thick, right=thick, bottom=thick)

cell_border_4 = sheet["D10"]
cell_border_4.border = Border(top=thick, left=thick, right=thick, bottom=thick)

#Saving the Workbook
wb_style.save("test_border.xlsx")

Resultant sheet:

Excel Border openpyxl

Setting border style and color of each side of Excel range example

In this example, we set the border and colors of each side (left, right, top, and bottom) and assign them to the range of cells in the Excel sheet.

For that, we used the following border types:

  • top=dashDotDot
  • left=mediumDashDot
  • right=slantDashDot
  • bottom=thin

We assigned this from C4 to D8 range of cells as shown in the program below:

The code:

# Setting the borders example
from openpyxl import load_workbook
from openpyxl.styles import Border, Side

#Loading the Workbook
wb_style = load_workbook('test_border.xlsx')

#Accessing Product Informaiton Sheet
sheet = wb_style.active

#Specifying border style and color for each side
dashDotDot = Side(border_style="dashDotDot", color="FFFF80")
mediumDashDot = Side(border_style="mediumDashDot", color="FF8040")
slantDashDot = Side(border_style="slantDashDot", color="800000")
thin = Side(border_style="thin", color="0000A0")

#Assign the set borders to a range of cells
range=sheet['C4':'D8']
for cell in range:
    for x in cell:
        x.border=Border(top=dashDotDot, left=mediumDashDot, right=slantDashDot, bottom=thin)

#Saving the Workbook
wb_style.save("test_border.xlsx")

Result:

Border openpyxl range

Applying border to rows example

In this example, we will apply border style to an unknown number of rows. We only know where to start applying border and we will get the last row number and apply various border styles to each side of the cell.

Have a look at the example and result sheet:

# Setting the borders example
from openpyxl import load_workbook
from openpyxl.styles import Border, Side

#Loading the Workbook
wb_style = load_workbook('test_border.xlsx')

#Accessing Product Informaiton Sheet
sheet = wb_style.active

#Specifying border style and color
thick = Side(border_style="thick", color="008000")
double = Side(border_style="double", color="800000")

#Get the last row number in the sheet
last_row = sheet.max_row

#Applying border from 5th to last row
for rows in sheet.iter_rows(min_row=5, max_row=last_row, min_col=None):
   for cell in rows:
     cell.border = Border(top=thick, left=double, right=double, bottom=thick)


#Saving the Workbook
wb_style.save("test_border.xlsx")

Result:

Border openpyxl rows

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!