If you want to add color to Excel cells and make data more visually appealing, you need to know the way to manipulate Excel files using Python code.
There are several Python libraries, where you can achieve this, but we are going to focus on two of them: Openpyxl and XlsxWriter.
Installing Required Libraries
Now, it’s time to install the required libraries. You can install both using the PIP installer.
|
1 2 |
pip install openpyxl pip install xlsxwriter |
Color Formatting with XlsxWriter
XlsxWriter is another Python library used for creating Excel files. It also provides functionality for formatting cells, including adding color.
XlsxWriter also creates a file, so there is no need to create it manually.
Here are the steps to add color to Excel cells using XlsxWriter:
- Import the xlsxwriter module:
|
1 |
import xlsxwriter |
- Create a workbook:
|
1 |
wb = xlsxwriter.Workbook('example.xlsx') |
- Create a worksheet:
|
1 |
ws = wb.add_worksheet() |
- Create a cell format:
|
1 |
greenFormat = wb.add_format({'bg_color': '#00FF00'}) |
- Select the cell to add the format:
|
1 |
cell = ws.write('A1', 'XlsxWriter', greenFormat) |
- Save the workbook:
|
1 |
wb.close() |
Full code:
|
1 2 3 4 5 6 7 |
import xlsxwriter wb = xlsxwriter.Workbook('example.xlsx') ws = wb.add_worksheet() greenFormat = wb.add_format({'bg_color': '#00FF00'}) cell = ws.write('A1', 'XlsxWriter', greenFormat) wb.close() |
Color Formatting with Openpyxl
Openpyxl is a Python library that allows users to read and modify Excel files. It also provides functionality for formatting cells, including adding color.
Openpyxl doesn’t create a file, so you have to create it manually or run the previous XlsxWriter code.
Here are the steps to add color to Excel cells using Openpyxl:
- Import the openpyxl module and the necessary styles module:
|
1 2 |
import openpyxl from openpyxl.styles import PatternFill |
- Load the Excel workbook:
|
1 |
wb = openpyxl.load_workbook('example.xlsx') |
- Select the worksheet:
|
1 |
ws = wb.active |
- Create a pattern fill:
|
1 |
greenFill = PatternFill(start_color='FF00FF', end_color='FF00FF', fill_type='solid') |
- Select the cell to add the fill:
|
1 |
cell = ws['B1'] |
- Add text to a cell:
|
1 |
cell.value = 'Openpyxl' |
- Apply the fill to the cell:
|
1 |
cell.fill = greenFill |
- Save the workbook:
|
1 |
wb.save('example.xlsx') |
This is the full code you can copy:
|
1 2 3 4 5 6 7 8 9 10 |
import openpyxl from openpyxl.styles import PatternFill wb = openpyxl.load_workbook('example.xlsx') ws = wb.active greenFill = PatternFill(start_color='FF00FF', end_color='FF00FF', fill_type='solid') cell = ws['B1'] cell.value = 'Openpyxl' cell.fill = greenFill wb.save('example.xlsx') |
Result
If you open the Excel file, you can see that both cells are filled by different Python libraries:
Conclusion
As you can see, you can easily manipulate Excel using Python. I showed you both ways to do it, but XlsxWriter is the better way as the code is shorter and it can create Excel files, and not only read them.