How to fill cells with colors using openpyxl in Python
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.
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