Excel Automation with Python

import pandas as pd

# Read specific sheet:
df = pd.read_excel("report.xlsx", sheet_name="Sales")

# Read all sheets:
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)
# all_sheets is a dict: {"Sales": df1, "HR": df2, ...}

for sheet_name, df in all_sheets.items():
    print(sheet_name, df.shape)

sheet_name=None is the fastest way to read all sheets. Iterating the dict lets you process each sheet separately — useful for monthly workbooks where each tab is a different month.

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

wb = Workbook()
ws = wb.active
ws.title = "Sales Report"

# Write header with formatting:
headers = ["Region", "Sales", "Growth %"]
for col, h in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=h)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="FF6B00")

# Write data:
data = [("North", 450000, 12.5), ("South", 380000, 8.3)]
for row_num, row_data in enumerate(data, 2):
    for col_num, value in enumerate(row_data, 1):
        ws.cell(row=row_num, column=col_num, value=value)

wb.save("formatted_report.xlsx")

openpyxl gives full control over Excel formatting — fonts, colours, borders, number formats. Use it when you need to deliver professionally formatted Excel reports automatically.

import pandas as pd
from datetime import datetime

# Read this month's data:
df = pd.read_csv("sales_data.csv")
df["date"] = pd.to_datetime(df["date"])
this_month = df[df["date"].dt.month == datetime.today().month]

# Compute KPIs:
kpis = this_month.groupby("region").agg(
    total_sales=("sales", "sum"),
    avg_order=("sales", "mean"),
    order_count=("order_id", "count")
).reset_index()

# Write to Excel:
filename = f"report_{datetime.today().strftime('%Y_%m')}.xlsx"
kpis.to_excel(filename, index=False)
print(f"Report saved: {filename}")

This pattern is the core of most automation projects: read data → filter to current period → compute KPIs → write to Excel with a date-stamped filename. Scheduled with a cron job or Task Scheduler, this replaces manual monthly reporting.

import glob
import pandas as pd

# Read all Excel files in folder:
files = glob.glob("monthly_reports/*.xlsx")

dfs = []
for f in files:
    df = pd.read_excel(f)
    df["source_file"] = f  # track source
    dfs.append(df)

combined = pd.concat(dfs, ignore_index=True)
combined.to_excel("combined_report.xlsx", index=False)
print(f"Combined {len(files)} files, {len(combined)} rows")

glob finds all files matching a pattern. Adding source_file column lets you trace where each row came from — essential for debugging when combined data has issues. This replaces manual copy-paste across files.

# Windows — Task Scheduler (GUI) or via command:
# schtasks /create /tn "MonthlyReport" /tr "python report.py" /sc monthly /d 1 /st 08:00

# Mac/Linux — cron:
# 0 8 1 * * /usr/bin/python3 /path/to/report.py

# Python schedule library (for within-script scheduling):
import schedule, time

def run_report():
    print("Running report...")
    # your report code here

schedule.every().day.at("08:00").do(run_report)

while True:
    schedule.run_pending()
    time.sleep(60)

Cron (Linux/Mac) and Task Scheduler (Windows) are the standard ways to automate Python scripts. The schedule library is simpler for demos. Always add logging to automated scripts so failures are visible.