Automate Excel with Python

A collection of simple, copy-paste-friendly Python scripts to automate common Excel tasks: merging files, cleaning data, generating reports, emailing results, creating charts, and more.

📘 About

This repository contains sample code from the book "Automate Excel with Python". It is intended for educational purposes and provides example scripts that accompany the book.

For full explanations, detailed instructions, and more examples, please refer to the book itself.

📖 Get the book here: Amazon Kindle Link

Quick Start (Windows PowerShell)

# 1) Create and activate a virtual environment
python -m venv .venv
.\.venv\Scripts\Activate.ps1

# 2) Install dependencies
pip install -r requirements.txt

# 3) Run a sample script (place input files in the data/ folder first)
python .\scripts\merge.py

• Input files: put .xlsx files under data/
• Outputs: scripts write result files to the repository root (e.g., merged.xlsx)

What's Inside

• scripts/merge.py — Merge multiple Excel workbooks into one
• scripts/clean.py — Clean duplicates, fill empty cells, normalize headers
• scripts/weekly_report.py — Create a dated weekly report with a detail and summary sheet
• scripts/email_report.py — Send a report via email (Gmail example)
• scripts/create_chart.py — Generate a chart image from Excel data
• scripts/monitor_folder.py — Watch a folder for new Excel files and process them
• scripts/combine_sheets.py — Combine all sheets from a single workbook
• scripts/formatting_example.py — Add basic styling to an Excel file
• scripts/daily_dashboard.py — End-to-end daily dashboard: merge, summarize, save, and email
• scripts/test.py — Verify your Python environment

Supporting folders:

• data/ — Put your input workbooks here (sample file names are referenced in scripts)
• incoming/ — Used by the folder monitoring example
• daily/ — Place daily source files for the dashboard example

Minimal Requirements

• Python 3.9+
• Packages: pandas, openpyxl, matplotlib

Install them with:

pip install -r requirements.txt

Notes on Emailing (Gmail)

• Enable 2-Step Verification on your Google account
• Create an App Password and use it in place of your normal password
• Update placeholders in scripts/email_report.py and scripts/daily_dashboard.py

License

MIT — see LICENSE.