Automating Excel Reports with Openpyxl
This guide demonstrates how to generate professional Excel reports programmatically. Unlike pandas which is great for data analysis, openpyxl gives you granular control over Excel features like formulas, cell styling, and charts.
Modules Used:
- openpyxl: To create and modify Excel files.
random: To generate dummy data for demonstration.
Installation
pip install openpyxlThe Code
Save this as report_gen.py.
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference
import random
def create_report(filename):
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"
# 1. Add Headers
headers = ["Product", "Region", "Q1", "Q2", "Q3", "Q4", "Total", "Average"]
ws.append(headers)
# 2. Add Dummy Data
products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headset"]
regions = ["North", "South", "East", "West"]
# Generate 20 rows of data
for _ in range(20):
product = random.choice(products)
region = random.choice(regions)
q1 = random.randint(1000, 5000)
q2 = random.randint(1000, 5000)
q3 = random.randint(1000, 5000)
q4 = random.randint(1000, 5000)
# Note: We don't calculate Total/Avg here, we let Excel do it via formulas
ws.append([product, region, q1, q2, q3, q4])
# 3. Add Formulas and Formatting
# Iterate starting from row 2 (since row 1 is headers)
for row in range(2, ws.max_row + 1):
# Add Excel Formulas
ws[f'G{row}'] = f"=SUM(C{row}:F{row})"
ws[f'H{row}'] = f"=AVERAGE(C{row}:F{row})"
# Apply Currency Format to numerical columns (C to H)
for col in range(3, 9):
cell = ws.cell(row=row, column=col)
cell.number_format = '$#,##0.00'
# 4. Style the Headers
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
for cell in ws[1]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal="center")
# 5. Add a Bar Chart
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Quarterly Sales Overview"
chart.y_axis.title = 'Revenue'
chart.x_axis.title = 'Data Points'
# Define data range for the chart (Q1 to Q4 columns)
data = Reference(ws, min_col=3, min_row=1, max_col=6, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
# Place the chart at cell J2
ws.add_chart(chart, "J2")
wb.save(filename)
print(f"Report saved successfully to {filename}")
if __name__ == "__main__":
create_report("quarterly_sales.xlsx")