Automate Excel Reports with Python openpyxl

Manual Excel reporting wastes valuable time. Python openpyxl automates this process. It creates professional reports quickly.

This guide shows how to use openpyxl for report automation. You will learn practical techniques. Save hours of manual work.

What is Python openpyxl?

Openpyxl is a Python library. It reads and writes Excel files. It works with .xlsx file format.

The library handles Excel operations programmatically. You can create sheets, add data, and apply formatting. All through Python code.

Openpyxl doesn't require Excel installed. It works independently. Perfect for server environments.

Install openpyxl

Install openpyxl using pip. Run this command in your terminal.

pip install openpyxl

Basic Report Creation

Start by importing openpyxl. Create a workbook object. Add a worksheet for your data.

from openpyxl import Workbook

# Create new workbook
wb = Workbook()

# Get active worksheet
ws = wb.active
ws.title = "Sales Report"

# Add headers
ws['A1'] = "Product"
ws['B1'] = "Quantity"
ws['C1'] = "Revenue"

# Add sample data
data = [
    ["Laptop", 15, 15000],
    ["Mouse", 45, 2250],
    ["Keyboard", 30, 3600]
]

for row, item in enumerate(data, start=2):
    ws[f'A{row}'] = item[0]
    ws[f'B{row}'] = item[1]
    ws[f'C{row}'] = item[2]

# Save the workbook
wb.save("sales_report.xlsx")

This code creates a basic sales report. It includes headers and sample data. The file saves as sales_report.xlsx.

Working with Data Efficiently

Use append() method for adding multiple rows. It's faster than individual cell assignments.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Add headers as a list
headers = ["Region", "Salesperson", "Total Sales"]
ws.append(headers)

# Add multiple data rows
sales_data = [
    ["North", "John Smith", 45000],
    ["South", "Maria Garcia", 52000],
    ["East", "David Chen", 38000],
    ["West", "Sarah Johnson", 61000]
]

for row in sales_data:
    ws.append(row)

wb.save("regional_sales.xlsx")

The append() method adds entire rows efficiently. Perfect for datasets and database exports.

Formatting Reports Professionally

Formatting makes reports readable and professional. Openpyxl offers extensive styling options.

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill

wb = Workbook()
ws = wb.active

# Add headers with formatting
headers = ["Department", "Budget", "Actual", "Variance"]
ws.append(headers)

# Format header row
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Add sample data
data = [
    ["Marketing", 50000, 52000, 2000],
    ["Engineering", 120000, 115000, -5000],
    ["Sales", 80000, 95000, 15000]
]

for row in data:
    ws.append(row)

# Auto-adjust column widths
for column in ws.columns:
    max_length = 0
    column_letter = column[0].column_letter
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column_letter].width = adjusted_width

wb.save("formatted_budget_report.xlsx")

This creates a professionally formatted budget report. Headers have bold white text on blue background. Columns auto-adjust to content.

For advanced formatting techniques, see our guide on Excel Borders Fills Alignment Python openpyxl Guide.

Adding Formulas and Calculations

Excel formulas automate calculations within your reports. Openpyxl supports most Excel functions.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Add financial data
headers = ["Month", "Revenue", "Expenses", "Profit"]
ws.append(headers)

monthly_data = [
    ["January", 85000, 45000, "=B2-C2"],
    ["February", 92000, 48000, "=B3-C3"],
    ["March", 78000, 42000, "=B4-C4"],
    ["April", 105000, 52000, "=B5-C5"]
]

for row in monthly_data:
    ws.append(row)

# Add summary formulas
ws['A7'] = "Total"
ws['B7'] = "=SUM(B2:B5)"
ws['C7'] = "=SUM(C2:C5)"
ws['D7'] = "=SUM(D2:D5)"

# Add average formulas
ws['A8'] = "Average"
ws['B8'] = "=AVERAGE(B2:B5)"
ws['C8'] = "=AVERAGE(C2:C5)"
ws['D8'] = "=AVERAGE(D2:D5)"

wb.save("financial_report_with_formulas.xlsx")

Formulas calculate automatically when opened in Excel. Profit column shows revenue minus expenses. Summary rows provide totals and averages.

Creating Charts from Data

Charts visualize data effectively. Openpyxl creates various chart types from worksheet data.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# Add sales data
data = [
    ["Product", "Q1 Sales", "Q2 Sales"],
    ["Laptops", 450, 520],
    ["Tablets", 320, 410],
    ["Phones", 680, 720],
    ["Accessories", 210, 190]
]

for row in data:
    ws.append(row)

# Create bar chart
chart = BarChart()
chart.title = "Product Sales by Quarter"
chart.x_axis.title = "Products"
chart.y_axis.title = "Sales Units"

# Define data ranges
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)

# Add data to chart
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# Position chart on worksheet
ws.add_chart(chart, "A7")

wb.save("sales_report_with_chart.xlsx")

The chart appears starting at cell A7. It shows quarterly sales comparison. Visual representation makes trends obvious.

Working with Multiple Sheets

Complex reports often need multiple worksheets. Openpyxl handles multiple sheets easily.

from openpyxl import Workbook

wb = Workbook()

# Create summary sheet
summary_ws = wb.active
summary_ws.title = "Summary"
summary_ws.append(["Region", "Total Sales", "Growth"])

# Create detailed sheets
regions = ["North", "South", "East", "West"]

for region in regions:
    region_ws = wb.create_sheet(title=region)
    region_ws.append(["Salesperson", "Q1 Sales", "Q2 Sales"])
    # Add sample regional data
    region_data = [
        [f"Rep {i}", i*1000+5000, i*1000+6000] for i in range(1, 4)
    ]
    for row in region_data:
        region_ws.append(row)

# Remove default sheet if unused
if "Sheet" in wb.sheetnames:
    del wb["Sheet"]

wb.save("multi_sheet_regional_report.xlsx")

This creates a summary sheet plus regional detail sheets. Each region has its own worksheet with sales data.

Learn advanced sheet management in our Copy Move Delete Sheets Python openpyxl Guide.

Advanced Number Formatting

Professional reports need proper number formatting. Currency, percentages, and dates require specific formats.

from openpyxl import Workbook
from openpyxl.styles import numbers

wb = Workbook()
ws = wb.active

# Add formatted financial data
headers = ["Item", "Amount", "Percentage", "Date"]
ws.append(headers)

financial_data = [
    ["Revenue", 125000.75, 0.15, "2024-01-15"],
    ["Expenses", -75000.50, -0.10, "2024-01-15"],
    ["Net Income", 50000.25, 0.05, "2024-01-15"]
]

for row in financial_data:
    ws.append(row)

# Apply number formatting
ws['B2'].number_format = '"$"#,##0.00'
ws['B3'].number_format = '"$"#,##0.00'
ws['B4'].number_format = '"$"#,##0.00'

ws['C2'].number_format = "0.00%"
ws['C3'].number_format = "0.00%"
ws['C4'].number_format = "0.00%"

ws['D2'].number_format = "yyyy-mm-dd"
ws['D3'].number_format = "yyyy-mm-dd"
ws['D4'].number_format = "yyyy-mm-dd"

wb.save("formatted_financial_report.xlsx")

Numbers display as currency, percentages, and dates. Formatting makes data interpretation immediate and accurate.

For comprehensive formatting options, check our Advanced Number Formatting Python openpyxl Guide.

Best Practices for Report Automation

Follow these practices for reliable automation. They ensure maintainable and efficient code.

Use template files for complex formatting. Apply data to pre-designed templates. Saves coding time.

Implement error handling for file operations. Catch permission errors and disk space issues. Prevents crashes.

Separate data and presentation logic. Keep data processing separate from formatting code. Easier maintenance.

Add logging to track report generation. Monitor success rates and identify issues quickly.

Schedule automated runs using cron jobs or task scheduler. Generate reports without manual intervention.

Conclusion

Python openpyxl transforms Excel report generation. It automates repetitive tasks completely. This saves significant time.

You learned basic to advanced techniques. From simple data insertion to formatted multi-sheet reports. Charts and formulas add professionalism.

Start automating your Excel reports today. Begin with simple tasks. Gradually add complexity as needed.

The time investment pays off quickly. Focus on analysis rather than manual data entry. Openpyxl handles the heavy lifting.