# 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:**
*   [[programming/python/modules/openpyxl-module|openpyxl]]: To create and modify Excel files.
*   `random`: To generate dummy data for demonstration.

## Installation

```bash
pip install openpyxl
```

## The Code

Save this as `report_gen.py`.

```python
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")
```

[[programming/python/python]]