How to Add Border in Excel (Cell, Range, Rows) 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.
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
# 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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
# 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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
# 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:
