How to fill cells with colors using openpyxl in Python

By Varsha Neelamma

Over the years, Microsft Excel has become an essential part of all types of work. Did you know that Python has a library that is designed specifically to allow you to work with excel sheets? Yes, you heard that right! Python provides a library called openpyxl for working directly with excel sheets. this allows you to create, read, write and format Excel sheets using python codes.
In this tutorial, you will learn about one important feature of openpyxl, which is how to fill cells with colors.

Installing openpyxl

Firstly, make sure that you install the openpyxl library. You can do that by running the below command on your terminal.

pip install openpyxl

You can refer to How to install openpyxl in Python

Fill cells with colors using openpyxl in python- PatternFill

Whenever you are working with an Excel sheet, you might want to highlight some parts of the sheet. Using colored cells for such parts makes it easy to identify them distinctively.
This can be done by using the openpyxl’s PatternFill class from the styles method.

You can specify the following arguments through PatternFill;
patternType, Color, bgColor, fill_type, start_color, end_color

from openpyxl import Workbook 
from openpyxl.styles import PatternFill 
def fill_colors(path): 
    wb = Workbook() 
    sheet = wb.active 
    peach = "00FFCC99" 
    for rows in sheet.iter_rows(min_row=4, max_row=6, min_col=3, max_col=9): 
        for cell in rows: 
            cell.fill = PatternFill(start_color=peach, end_color=peach,fill_type = "solid") 
    wb.save(path) 
if __name__ == "__main__": 
    fill_colors("CodeSpeedy.xlsx")

In the above code,

• Firstly we import the necessary modules.
• Once this is done, we create an excel sheet to work on in the default path.
• Further, we use the for loop to specify the rows and columns across which we want to fill the cells with color.
• We then loop through these cells and use PatternFill to fill the color as shown.
• Once done, we must not forget to save the sheet using the save() method of the Workbook class.

You can further create your own patterns and fill colors accordingly to make it more pleasant. An example is as follows;

from openpyxl import Workbook 
from openpyxl.styles import PatternFill 
def fill_colors(path): 
    wb = Workbook() 
    sheet = wb.active 
    cyan = "00FFFF" 
    for rows in sheet.iter_rows(min_row=4, max_row=6, min_col=3, max_col=9): 
        for cell in rows: 
            if cell.row % 2 !=0: 
                cell.fill = PatternFill(start_color=cyan, end_color=cyan,fill_type = "solid") 
    wb.save(path) 
if __name__ == "__main__": 
    fill_colors("CodeSpeedy.xlsx")

Also read, Easy example of openpyxl iter_rows in Python