Automation is one of the highest-value skills a Python learner can offer a business — turning hours of repetitive manual work (formatting spreadsheets, renaming files, sending reports) into a script that runs in seconds.
The openpyxl library lets Python read, write, and format Excel files directly — no manual clicking required.
pip install openpyxl
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Student Report"
ws.append(["Name", "Course", "Fee"])
ws.append(["Priya", "SEO", 12000])
ws.append(["Rahul", "Google Ads", 15000])
wb.save("student_report.xlsx")
print("Excel report created successfully!")from openpyxl import load_workbook
wb = load_workbook("student_report.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2, values_only=True):
print(row)
ws["D1"] = "Fee with GST"
wb.save("student_report.xlsx")Python can automatically rename, move, copy, and organize files in bulk using the built-in os and shutil modules.
import os
folder = "student_certificates"
for i, filename in enumerate(os.listdir(folder), start=1):
old_path = os.path.join(folder, filename)
new_name = f"certificate_{i:03d}.pdf"
new_path = os.path.join(folder, new_name)
os.rename(old_path, new_path)
print("All files renamed successfully!")import shutil
shutil.copy("report.xlsx", "backup/report.xlsx") # copy
shutil.move("report.xlsx", "archive/report.xlsx") # moveAutomatically organizing files into folders based on type is a classic, highly practical automation task.
import os, shutil
source = "downloads"
for filename in os.listdir(source):
ext = filename.split(".")[-1].lower()
target_folder = os.path.join(source, ext.upper())
os.makedirs(target_folder, exist_ok=True)
src_path = os.path.join(source, filename)
if os.path.isfile(src_path):
shutil.move(src_path, os.path.join(target_folder, filename))
print("Downloads folder organized by file type!")The built-in smtplib module lets Python send emails automatically — perfect for sending automated reports, reminders, or fee due notices.
import smtplib
from email.message import EmailMessage
msg = EmailMessage()
msg["Subject"] = "Weekly Enrollment Report - Samantus Institute"
msg["From"] = "reports@samantus.com"
msg["To"] = "admin@samantus.com"
msg.set_content("Please find this week's enrollment report attached.")
with open("student_report.xlsx", "rb") as f:
msg.add_attachment(f.read(), maintype="application",
subtype="xlsx", filename="student_report.xlsx")
with smtplib.SMTP_SSL("smtp.gmail.com", 465) as server:
server.login("your_email@gmail.com", "your_app_password")
server.send_message(msg)
print("Report emailed successfully!")Business Scenario: Every Monday, the institute needs a fresh enrollment report emailed to management automatically.
import pandas as pd
from openpyxl import Workbook
import smtplib
from email.message import EmailMessage
# 1. Load and summarize data
df = pd.read_csv("enrollments.csv")
summary = df.groupby("course")["fee"].sum()
# 2. Save as Excel
summary.to_excel("weekly_report.xlsx")
# 3. Email the report
msg = EmailMessage()
msg["Subject"] = "Weekly Enrollment Report"
msg["From"] = "reports@samantus.com"
msg["To"] = "owner@samantus.com"
msg.set_content("Attached is this week's automated enrollment summary.")
with open("weekly_report.xlsx", "rb") as f:
msg.add_attachment(f.read(), maintype="application", subtype="xlsx",
filename="weekly_report.xlsx")
# with smtplib.SMTP_SSL(...) as server: ... server.send_message(msg)
print("Weekly report generated and ready to send!")Basic (5 Questions)
1. Create a new Excel file using openpyxl with a header row and 3 data rows.
2. Read back the Excel file you created and print each row.
3. Write a script to rename 5 sample files in a folder with a consistent naming pattern.
4. Create a script that copies one file into a backup folder.
5. Create a folder automatically using os.makedirs() only if it doesn't already exist.
Intermediate (5 Questions)
1. Write a script that organizes a folder's files into subfolders by file extension.
2. Update an existing Excel file by adding a new column with calculated values.
3. Write an email-drafting script (without actually sending) that attaches an Excel file.
4. Combine Pandas and openpyxl to export a groupby() summary directly to a formatted Excel file.
5. Write a script that finds and lists all .csv files in a folder and its subfolders.
Advanced (5 Questions)
1. Build a complete 'Weekly Report Automation' that reads a CSV, summarizes it, saves it as Excel, and drafts an email with it attached.
2. Build a 'Downloads Organizer' that runs automatically and sorts files into folders by type every time it's executed.
3. Build a script that checks a folder daily for new files and renames them with a timestamp.
4. Create an automation script that merges multiple Excel files from a folder into a single combined report.
5. Research how to schedule a Python script to run automatically every day (Task Scheduler / cron) and write a short summary of the steps.
Q1. Which library is commonly used for Excel automation?
Q2. Which module helps copy and move files?
Q3. Which built-in module can send emails from Python?
Q4. What should you check before moving items in a folder loop?
Q5. Why avoid hardcoding email passwords in scripts?
Assignment: Fully Automated Weekly Report System
Expected Output: A complete automation script that takes raw enrollment data all the way to a ready-to-send Excel report with an email draft attached.
Use this space to write down key points, doubts, and your own examples from today's session.