Generating an Excel Report with Python

 Objective

In this lesson, we will learn how to:

• Create tabular data using pandas

• Calculate totals

• Save data into an Excel file

• Apply basic formatting (like bold headers, colors, and money format)

 Requirements

Before running the code, install these libraries:

pip install pandas xlsxwriter

Code Explanation

import pandas as pd

def generate_excel_report():
    """Generate formatted Excel report with pandas"""
    # 1. Create sample data
    data = {
        "Product": ["Laptop", "Phone", "Tablet"],
        "Q1_Sales": [120, 200, 80],
        "Q2_Sales": [150, 210, 95]
    }
    
    df = pd.DataFrame(data)   # Make a table (DataFrame)

    # 2. Add totals
    df['Total'] = df['Q1_Sales'] + df['Q2_Sales']  
    df.loc['Grand Total'] = df.sum(numeric_only=True)

    # 3. Save to Excel
    with pd.ExcelWriter("sales_report.xlsx", engine='xlsxwriter') as writer:
        df.to_excel(writer, sheet_name='Sales', index=False)
        
        workbook = writer.book
        worksheet = writer.sheets['Sales']
        
        # 4. Apply formatting
        header_format = workbook.add_format({
            'bold': True,
            'bg_color': '#4472C4',  # Blue background
            'font_color': 'white'   # White text
        })
        
        money_format = workbook.add_format({'num_format': '$#,##0'})
        
        worksheet.set_column('A:A', 15)           # Column width for Product
        worksheet.set_column('B:D', 12, money_format)  # Sales columns in $ format
        worksheet.set_row(0, None, header_format) # Style header row
    
    print("Sales report generated: sales_report.xlsx")

generate_excel_report()

Step-by-Step Breakdown

1. Import pandas → to handle tabular data.

2. Create Data → a dictionary of products and sales values.

3. Convert to DataFrame → makes it look like a spreadsheet in Python.

4. Add Totals → add Q1 + Q2 sales and a “Grand Total” row.

5. Save to Excel → use pd.ExcelWriter() with XlsxWriter engine.

6. Formatting:

   • Headers are bold, white text, blue background.

   • Sales numbers are shown in currency ($) format.

   • Column widths are adjusted for better readability.

Sample Output (Excel File)

Key Takeaways

• pandas helps you work with data tables.

• xlsxwriter allows you to make professional-looking Excel reports.

• Formatting makes reports easy to read and presentable.