A Guide to Changing Excel Cell Background Colors With Python and OpenPyXL
Table of Contents
Introduction
Hello, fellow data manipulators! Today, utilizing Python and the potent Openpyxl package, we’ll delve into the wonderful world of Excel automation. You’ll discover how to customize cell background colors so that your spreadsheets seem good and are simpler to analyze. So grab a cup of your preferred beverage and let’s explore the vibrant world of Python and Excel!
By Rubaitul Azad on Unsplash
Changing the Background Color of a Single Cell
We’ll start by coloring a single cell. First, we need to install the OpenPyXL library if you haven’t already. Open your terminal or command prompt and type:
pip install openpyxl
Suppose we already have an Excel file named test.xlsx under data folder. We will firstly open it and change the background color of cell A2 to yellow.
import openpyxl
from openpyxl.styles import PatternFill
# Load the workbook
workbook = openpyxl.load_workbook('data/test.xlsx')
# Select the worksheet
worksheet = workbook.active
# Access the cell you want to change the background color of
cell = worksheet['A2']
# Create a PatternFill object with the desired color
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# Apply the background color to the cell
cell.fill = yellow_fill
# Save the workbook
workbook.save('data/test.xlsx')
Before:
After:
Coloring a Range of Cells
We can also set background color for a range of cells by looping the rows and columns.
import openpyxl
from openpyxl.styles import PatternFill
# Open an existing workbook
wb = openpyxl.load_workbook('data/test.xlsx')
sheet = wb.active
# Create a PatternFill object with the desired color
blue_fill = PatternFill(start_color='0000FF', end_color='0000FF', fill_type='solid')
# Loop through the cells in the range and apply the background color
for row in range(2, 5):
for col in range(2, 5):
cell = sheet.cell(row=row, column=col)
cell.fill = blue_fill
# Save the workbook
wb.save('data/test.xlsx')
Before:
After:
