Overview
In this tutorial, we’ll create an automated system for generating Excel reports using Python’s OpenPyXL library. This is a valuable skill for professionals who deal with repetitive Excel tasks, such as data analysts, accountants, and business managers. By the end of this tutorial, you’ll learn how to:
- Read data from an Excel file.
- Perform calculations and manipulate the data.
- Format Excel cells dynamically.
- Automate the creation of summary reports.
Why Automate Excel Reports?
Excel is a powerful tool, but manual tasks like calculating totals, applying conditional formatting, and creating charts can be time-consuming. Python provides an efficient way to automate these tasks, saving hours of effort while ensuring consistency and accuracy.
Step 1: Setting Up Your Environment
Install OpenPyXL
First, ensure you have Python installed. Then, install the OpenPyXL library using pip:
pip install openpyxl
Step 2: Preparing the Data
Create a sample Excel file named sales_data.xlsx with the following structure:
| Product | Sales (Jan) | Sales (Feb) | Sales (Mar) |
|---|---|---|---|
| Product A | 1000 | 1200 | 1100 |
| Product B | 1500 | 1600 | 1700 |
| Product C | 900 | 850 | 950 |
Step 3: Reading Data from Excel
Here’s how to load and read data from an Excel file:
from openpyxl import load_workbook
# Load the workbook
workbook = load_workbook('sales_data.xlsx')
# Select the active sheet
sheet = workbook.active
# Iterate through rows and print data
for row in sheet.iter_rows(values_only=True):
print(row)
This will output the rows of data as tuples. The first row will contain the headers.
Step 4: Performing Calculations
Let’s calculate the total sales for each product and add it as a new column:
# Add a new header for the total sales
sheet.cell(row=1, column=5, value="Total Sales")
# Calculate total sales for each product
for row in range(2, sheet.max_row + 1):
jan = sheet.cell(row=row, column=2).value
feb = sheet.cell(row=row, column=3).value
mar = sheet.cell(row=row, column=4).value
total = jan + feb + mar
sheet.cell(row=row, column=5, value=total)
# Save the updated workbook
workbook.save('sales_data_with_totals.xlsx')
Step 5: Formatting the Excel File
To make the report visually appealing, we’ll apply some formatting:
- Set column widths for better readability.
- Apply bold formatting to headers.
- Add number formatting to the sales data.
from openpyxl.styles import Font, Alignment
# Bold headers
for col in range(1, 6):
cell = sheet.cell(row=1, column=col)
cell.font = Font(bold=True)
# Set column widths
sheet.column_dimensions['A'].width = 15
for col in ['B', 'C', 'D', 'E']:
sheet.column_dimensions[col].width = 12
# Center-align headers
for col in range(1, 6):
cell = sheet.cell(row=1, column=col)
cell.alignment = Alignment(horizontal="center")
# Save the formatted workbook
workbook.save('sales_report_formatted.xlsx')
Step 6: Adding Conditional Formatting
We’ll highlight products with total sales greater than 4000 in green.
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill
# Define the fill color
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
# Add conditional formatting
sheet.conditional_formatting.add(
'E2:E100',
CellIsRule(operator='greaterThan', formula=['4000'], fill=green_fill)
)
# Save the workbook
workbook.save('sales_report_highlighted.xlsx')
Step 7: Generating a Summary Report
Create a summary sheet to show the total sales for all products and the highest-selling product.
# Add a new sheet for the summary
summary = workbook.create_sheet('Summary')
# Calculate total sales for all products
total_sales = sum(sheet.cell(row=row, column=5).value for row in range(2, sheet.max_row + 1))
# Find the highest-selling product
highest_sales = max(sheet.cell(row=row, column=5).value for row in range(2, sheet.max_row + 1))
highest_product = None
for row in range(2, sheet.max_row + 1):
if sheet.cell(row=row, column=5).value == highest_sales:
highest_product = sheet.cell(row=row, column=1).value
break
# Write summary data
summary.cell(row=1, column=1, value="Total Sales")
summary.cell(row=1, column=2, value=total_sales)
summary.cell(row=2, column=1, value="Highest-Selling Product")
summary.cell(row=2, column=2, value=highest_product)
# Save the updated workbook
workbook.save('sales_report_with_summary.xlsx')
Step 8: Final Output
After completing the above steps, you will have a fully automated Excel report with:
- Formatted data for better readability.
- Conditional highlighting for key metrics.
- A summary sheet with actionable insights.
What’s Next?
- Dynamic Data Sources: Integrate with APIs to pull live sales data.
- Email Automation: Use the
smtpliblibrary to email reports. - Dashboard Integration: Connect the report to a visualization tool like Power BI.
Conclusion
This tutorial introduced practical skills in automating Excel tasks with Python. Automating reports is a valuable skill that can save hours of work and provide error-free results. If you enjoyed this tutorial, explore integrating these skills into larger automation workflows, such as automating financial dashboards or sales forecasting systems.