Excel Report Generator

Build a utility that automates Excel to generate a formatted sales report. The utility should create a workbook, populate it with sales data, apply formatting, and calculate totals.

Requirements

Data Population

Create a new Excel workbook with a worksheet named "Sales Report". The worksheet should contain:

• A header row (row 1) with columns: "Product", "Quantity", "Price", "Total"
• Data rows starting from row 2 with the following sales records:
  • Product: "Widget A", Quantity: 10, Price: 25.50
  • Product: "Widget B", Quantity: 5, Price: 50.00
  • Product: "Widget C", Quantity: 8, Price: 12.75

Calculations

For each data row, calculate the Total column (Quantity × Price) and set it in the appropriate cell.

After all data rows, add a row with:

• "Grand Total" in the Product column
• The sum of all Total values in the Total column

Formatting

Apply the following formatting:

• Make the header row (row 1) bold
• Set the Price and Total columns to display as currency (e.g., "$25.50")
• Make the Grand Total row bold

Output

Save the workbook to a file named "sales_report.xlsx" in the current directory.

Test Cases

• Creating a workbook and worksheet produces an Excel file with the correct structure @test
• Data is correctly populated in the specified cells with accurate calculations @test
• Formatting is applied correctly to header row, currency columns, and grand total row @test
• The file is saved successfully and can be opened in Excel @test

Implementation

@generates

API

def generate_sales_report(output_path: str) -> None:
    """
    Generate an Excel sales report with formatted data and calculations.

    Args:
        output_path: Path where the Excel file should be saved

    Raises:
        Exception: If COM automation fails or file cannot be saved
    """
    pass

Dependencies { .dependencies }

comtypes { .dependency }

Provides COM automation support for interacting with Excel application.

@satisfied-by