Overview

In this tutorial, we’ll create an automated system for generating Excel reports using Python’s OpenPyXL library. This is a valuable skill for professionals who deal with repetitive Excel tasks, such as data analysts, accountants, and business managers. By the end of this tutorial, you’ll learn how to:

  • Read data from an Excel file.
  • Perform calculations and manipulate the data.
  • Format Excel cells dynamically.
  • Automate the creation of summary reports.

Why Automate Excel Reports?

Excel is a powerful tool, but manual tasks like calculating totals, applying conditional formatting, and creating charts can be time-consuming. Python provides an efficient way to automate these tasks, saving hours of effort while ensuring consistency and accuracy.


Step 1: Setting Up Your Environment

Install OpenPyXL

First, ensure you have Python installed. Then, install the OpenPyXL library using pip:


pip install openpyxl

Step 2: Preparing the Data

Create a sample Excel file named sales_data.xlsx with the following structure:

Product Sales (Jan) Sales (Feb) Sales (Mar)
Product A 1000 1200 1100
Product B 1500 1600 1700
Product C 900 850 950

Step 3: Reading Data from Excel

Here’s how to load and read data from an Excel file:


from openpyxl import load_workbook

# Load the workbook
workbook = load_workbook('sales_data.xlsx')

# Select the active sheet
sheet = workbook.active

# Iterate through rows and print data
for row in sheet.iter_rows(values_only=True):
    print(row)

This will output the rows of data as tuples. The first row will contain the headers.


Step 4: Performing Calculations

Let’s calculate the total sales for each product and add it as a new column:


# Add a new header for the total sales
sheet.cell(row=1, column=5, value="Total Sales")

# Calculate total sales for each product
for row in range(2, sheet.max_row + 1):
    jan = sheet.cell(row=row, column=2).value
    feb = sheet.cell(row=row, column=3).value
    mar = sheet.cell(row=row, column=4).value
    total = jan + feb + mar
    sheet.cell(row=row, column=5, value=total)

# Save the updated workbook
workbook.save('sales_data_with_totals.xlsx')

Step 5: Formatting the Excel File

To make the report visually appealing, we’ll apply some formatting:

  1. Set column widths for better readability.
  2. Apply bold formatting to headers.
  3. Add number formatting to the sales data.

from openpyxl.styles import Font, Alignment

# Bold headers
for col in range(1, 6):
    cell = sheet.cell(row=1, column=col)
    cell.font = Font(bold=True)

# Set column widths
sheet.column_dimensions['A'].width = 15
for col in ['B', 'C', 'D', 'E']:
    sheet.column_dimensions[col].width = 12

# Center-align headers
for col in range(1, 6):
    cell = sheet.cell(row=1, column=col)
    cell.alignment = Alignment(horizontal="center")

# Save the formatted workbook
workbook.save('sales_report_formatted.xlsx')

Step 6: Adding Conditional Formatting

We’ll highlight products with total sales greater than 4000 in green.


from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

# Define the fill color
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')

# Add conditional formatting
sheet.conditional_formatting.add(
    'E2:E100',
    CellIsRule(operator='greaterThan', formula=['4000'], fill=green_fill)
)

# Save the workbook
workbook.save('sales_report_highlighted.xlsx')

Step 7: Generating a Summary Report

Create a summary sheet to show the total sales for all products and the highest-selling product.


# Add a new sheet for the summary
summary = workbook.create_sheet('Summary')

# Calculate total sales for all products
total_sales = sum(sheet.cell(row=row, column=5).value for row in range(2, sheet.max_row + 1))

# Find the highest-selling product
highest_sales = max(sheet.cell(row=row, column=5).value for row in range(2, sheet.max_row + 1))
highest_product = None
for row in range(2, sheet.max_row + 1):
    if sheet.cell(row=row, column=5).value == highest_sales:
        highest_product = sheet.cell(row=row, column=1).value
        break

# Write summary data
summary.cell(row=1, column=1, value="Total Sales")
summary.cell(row=1, column=2, value=total_sales)
summary.cell(row=2, column=1, value="Highest-Selling Product")
summary.cell(row=2, column=2, value=highest_product)

# Save the updated workbook
workbook.save('sales_report_with_summary.xlsx')

Step 8: Final Output

After completing the above steps, you will have a fully automated Excel report with:

  • Formatted data for better readability.
  • Conditional highlighting for key metrics.
  • A summary sheet with actionable insights.

What’s Next?

  1. Dynamic Data Sources: Integrate with APIs to pull live sales data.
  2. Email Automation: Use the smtplib library to email reports.
  3. Dashboard Integration: Connect the report to a visualization tool like Power BI.

Conclusion

This tutorial introduced practical skills in automating Excel tasks with Python. Automating reports is a valuable skill that can save hours of work and provide error-free results. If you enjoyed this tutorial, explore integrating these skills into larger automation workflows, such as automating financial dashboards or sales forecasting systems.