In this tutorial, we will learn how to set the background color of cells in an Excel worksheet using Python. We will achieve this by leveraging the power of the openpyxl library. The openpyxl module allows Python to read and modify Excel files without any additional software or dependencies.
First, ensure you have the following prerequisites installed:
– Python (version 3.x is preferred)
– openpyxl library
If you do not have the openpyxl library, you can install it using the following pip command:
|
1 |
pip install openpyxl |
Now let’s start with the steps required to set the background color of cells in Excel.
Step 1: Import the openpyxl library
|
1 |
import openpyxl |
Step 2: Create or open an existing Excel file
To create a new workbook or open an existing Excel file, use the following code snippet:
|
1 2 3 4 5 6 7 8 9 10 |
from openpyxl import Workbook # Create a new Excel workbook wb = Workbook() # Create a new Excel sheet ws = wb.active # Save the workbook wb.save('my_excel_file.xlsx') |
To open an existing workbook, use the following code snippet:
|
1 2 3 4 5 6 7 |
from openpyxl import load_workbook # Load the existing workbook wb = load_workbook('my_excel_file.xlsx') # Access the active sheet ws = wb.active |
Step 3: Set the background color in a cell
We now have our workbook and sheet handles stored in the variables “wb” and “ws,” respectively. Use the following code snippet to set the background color of a cell:
|
1 2 3 4 5 6 7 8 9 10 |
from openpyxl.styles import PatternFill # Create a fill object with the desired background color cell_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") # Apply the fill object to the cell A1 ws['A1'].fill = cell_fill # Save the modified workbook wb.save('my_excel_file.xlsx') |
In the above code snippet, we have imported the PatternFill class from the openpyxl.styles module. Using this class, we can create a fill object with the desired background color. The start_color and end_color parameters accept color codes in hexadecimal format. Finally, we apply the fill object to the desired cell using the cell.fill property of the cell.
Full code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
from openpyxl import Workbook, load_workbook from openpyxl.styles import PatternFill # Create a new workbook wb = Workbook() # Access the active sheet ws = wb.active # Set background color of cell A1 cell_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid") ws['A1'].fill = cell_fill # Save the workbook wb.save('my_excel_file.xlsx') |
After executing the above code, you will see an Excel file named “my_excel_file.xlsx” in your current working directory. Opening the file, you will notice that the background color of cell A1 has been set to yellow.
Output
Conclusion
In this tutorial, we learned how to set the background color of cells in Excel using the openpyxl library with Python.
This method can be useful for highlighting important data, creating custom templates, or applying conditional formatting to your worksheets. Remember to explore the openpyxl official documentation to discover more features and possibilities.