How to set background color in Excel Sheets by openpyxl
The openpyxl library has a class that can be used to set the background colors of Excel cells, rows, or columns.
The class name is PatternFill which has many options to set the background color by using its arguments. These include:
- fill_type
- start_color
- end_color
The value for the start and end color must be hexadecimal to represent RGB (red, green, blue). For example, the green color is represented by 008040 value.
The fill_type argument has many values. The list is provided below along with its output to give you an idea. Let us start with a basic example of using PatternFill class.
An example of Setting Excel cell background color
For the first example, we will use five different cells and set their background colors. Following is the sample sheet that we will work on:
We will set different colors for each cell as shown in the example below with the code. The cells are B2, B3, B4, B5 and B6.
Python Program:
# Setting the background color examples
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')
#Accessing Product Informaiton Sheet
sheet = wb_style.active
#Set background color of cell
cell_bg1 = sheet["B2"]
cell_bg1.fill = PatternFill(start_color="800000", end_color="800000",fill_type = "solid")
#Set background color of cell
cell_bg1 = sheet["B3"]
cell_bg1.fill = PatternFill(start_color="FF8040", end_color="FF8040",fill_type = "solid")
#Set background color of cell
cell_bg1 = sheet["B4"]
cell_bg1.fill = PatternFill(start_color="FF0000", end_color="FF0000",fill_type = "solid")
#Set background color of cell
cell_bg1 = sheet["B5"]
cell_bg1.fill = PatternFill(start_color="408080", end_color="408080",fill_type = "solid")
#Set background color of cell
cell_bg1 = sheet["B6"]
cell_bg1.fill = PatternFill(start_color="800080", end_color="800080",fill_type = "solid")
wb_style.save("test_Excel.xlsx")
Result:
An example of setting background of rows
In this example, we will set the background color of four rows. For that, we will use the iter_rows function and provide its arguments so that it changes only the 2nd to 5th row colors. Have a look at the code and output below:
The code:
# Setting the background color examples
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')
#Accessing Product Informaiton Sheet
sheet = wb_style.active
#Setting background of 2nf to 5th row
for rows in sheet.iter_rows(min_row=2, max_row=6, min_col=None):
for cell in rows:
cell.fill = PatternFill(start_color="80FF00", end_color="80FF00",fill_type = "solid")
wb_style.save("test_Excel.xlsx")
Result sheet:
Changing background of every other (even) row example
The following example changes the background color of even rows in the Excel sheet:
Python Code:
# Setting the background color examples
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')
#Accessing Product Informaiton Sheet
sheet = wb_style.active
#Change background color of even rows
for rows in sheet.iter_rows(min_row=2, max_row=12, min_col=None):
for cell in rows:
if not cell.row % 2:
cell.fill = PatternFill(start_color="C0C0C0", end_color="C0C0C0",fill_type = "solid")
wb_style.save("test_Excel.xlsx")
Output:
You can see the color of even rows is set to grey.
An example of changing column background color
Similarly, you may change the background color of the column by using iter_cols function. See an example below where we changed the color of the second column in our sample sheet:
The code:
# Setting the background color examples
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
#Loading the Workbook
wb_style = load_workbook('test_Excel.xlsx')
#Accessing Product Informaiton Sheet
sheet = wb_style.active
#Change background color of a column
for rows in sheet.iter_cols(min_col=3, max_col=3, min_row=2, max_row=None):
for cell in rows:
cell.fill = PatternFill(start_color="8080FF", end_color="8080FF",fill_type = "solid")
wb_style.save("test_Excel.xlsx")
Result:
You saw the background color of the 3rd column is changed after the first row.
Using all fill type values example
As mentioned earlier, the fill_type parameter has many possible values. In all our above examples, we used solid value for fill_type.
Following is the list of possible values:
- ‘none’
- ‘solid’
- ‘darkDown’
- ‘darkGray’
- ‘darkTrellis’
- ‘darkGrid’
- ‘darkHorizontal’
- ‘darkUp’
- ‘darkVertical’
- ‘lightGrid’
- ‘gray0625’
- ‘gray125’
- ‘lightDown’
- ‘lightGray’
- ‘lightHorizontal’
- ‘lightTrellis’
- ‘lightUp’
- ‘lightVertical’
- ‘mediumGray’
In the following example, we used all possible values and the Excel cell contains the name of each value along with its application.
See the name and corresponding output in the sheet below:
The code:
# Setting the background color examples
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
#Loading the Workbook
wb_style = load_workbook('test_fillType.xlsx')
#Accessing Product Informaiton Sheet
sheet = wb_style.active
#Set fill type
cell_ft2 = sheet["A2"]
cell_ft2.fill = PatternFill(fill_type = "solid")
cell_ft3 = sheet["A3"]
cell_ft3.fill = PatternFill(fill_type = "darkVertical")
cell_ft4 = sheet["A4"]
cell_ft4.fill = PatternFill(fill_type = "darkGray")
cell_ft5 = sheet["A5"]
cell_ft5.fill = PatternFill(fill_type = "darkDown")
cell_ft6 = sheet["A6"]
cell_ft6.fill = PatternFill(fill_type = "darkHorizontal")
cell_ft7 = sheet["A7"]
cell_ft7.fill = PatternFill(fill_type = "darkTrellis")
cell_ft8 = sheet["A8"]
cell_ft8.fill = PatternFill(fill_type = "darkUp")
cell_ft9 = sheet["A9"]
cell_ft9.fill = PatternFill(fill_type = "lightUp")
cell_ft10 = sheet["A10"]
cell_ft10.fill = PatternFill(fill_type = "gray0625")
cell_ft11 = sheet["A11"]
cell_ft11.fill = PatternFill(fill_type = "darkGrid")
cell_ft12 = sheet["A12"]
cell_ft12.fill = PatternFill(fill_type = "lightDown")
cell_ft13 = sheet["A13"]
cell_ft13.fill = PatternFill(fill_type = "lightGray")
cell_ft14 = sheet["A14"]
cell_ft14.fill = PatternFill(fill_type = "lightGrid")
cell_ft15 = sheet["A15"]
cell_ft15.fill = PatternFill(fill_type = "lightHorizontal")
cell_ft16 = sheet["A16"]
cell_ft16.fill = PatternFill(fill_type = "lightTrellis")
cell_ft17 = sheet["A17"]
cell_ft17.fill = PatternFill(fill_type = "gray125")
cell_ft18 = sheet["A18"]
cell_ft18.fill = PatternFill(fill_type = "lightVertical")
cell_ft19 = sheet["A19"]
cell_ft19.fill = PatternFill(fill_type = "mediumGray")
wb_style.save("test_fillType.xlsx")
Result:
