Office Automation

Automate repetitive tasks — Excel, PowerPoint, and email delivery — using Python.

---

Installation

pip install openpyxl python-pptx

---

openpyxl — Excel Automation

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference
import datetime


# Create an Excel file
def create_sales_report(data: list[dict], filename: str = "sales_report.xlsx"):
    wb = Workbook()
    ws = wb.active
    ws.title = "Sales Summary"

    # Header style
    header_font = Font(name="Calibri", bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(fill_type="solid", fgColor="2F5496")
    center_align = Alignment(horizontal="center", vertical="center")

    # Write headers
    headers = ["Date", "Product", "Category", "Qty", "Unit Price", "Revenue"]
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_align

    # Write data
    for row, item in enumerate(data, 2):
        ws.cell(row=row, column=1, value=item["date"])
        ws.cell(row=row, column=2, value=item["product"])
        ws.cell(row=row, column=3, value=item["category"])
        ws.cell(row=row, column=4, value=item["quantity"])
        ws.cell(row=row, column=5, value=item["price"]).number_format = "#,##0"
        # Revenue formula
        ws.cell(row=row, column=6, value=f"=D{row}*E{row}").number_format = "#,##0"

    # Totals row
    last_row = len(data) + 2
    ws.cell(row=last_row, column=3, value="Total").font = Font(bold=True)
    ws.cell(row=last_row, column=4, value=f"=SUM(D2:D{last_row-1})")
    ws.cell(row=last_row, column=6, value=f"=SUM(F2:F{last_row-1})").number_format = "#,##0"

    # Auto-adjust column widths
    col_widths = [12, 20, 12, 8, 12, 14]
    for col, width in enumerate(col_widths, 1):
        ws.column_dimensions[get_column_letter(col)].width = width

    # Row height
    ws.row_dimensions[1].height = 22

    # Add chart
    chart = BarChart()
    chart.title = "Revenue by Category"
    chart.style = 10
    data_ref = Reference(ws, min_col=6, min_row=1, max_row=len(data)+1)
    cats_ref = Reference(ws, min_col=3, min_row=2, max_row=len(data)+1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats_ref)
    chart.width = 15
    chart.height = 10
    ws.add_chart(chart, "H2")

    wb.save(filename)
    print(f"✅ Saved: {filename}")


# Modify an existing file
def update_excel(filename: str):
    wb = load_workbook(filename)
    ws = wb.active

    # Read data
    for row in ws.iter_rows(min_row=2, values_only=True):
        print(row)

    # Edit cells
    ws["A1"] = "Updated Title"
    ws.append(["2024-01-15", "New Product", "Electronics", 5, 50000])

    wb.save(filename)

---

python-pptx — PowerPoint Automation

from pptx import Presentation
from pptx.util import Inches, Pt, Emu
from pptx.dml.color import RGBColor
from pptx.enum.text import PP_ALIGN


def create_presentation(title: str, data: list[dict]) -> Presentation:
    prs = Presentation()

    # Slide size (16:9)
    prs.slide_width = Inches(13.33)
    prs.slide_height = Inches(7.5)

    # 1. Title slide
    slide_layout = prs.slide_layouts[0]  # Title layout
    slide = prs.slides.add_slide(slide_layout)
    slide.shapes.title.text = title
    slide.placeholders[1].text = f"Date: {datetime.date.today()}"

    # 2. Data slide (text + table)
    slide = prs.slides.add_slide(prs.slide_layouts[5])  # Blank slide

    # Add title
    txBox = slide.shapes.add_textbox(Inches(0.5), Inches(0.3), Inches(12), Inches(0.8))
    tf = txBox.text_frame
    tf.text = "Monthly Performance Summary"
    tf.paragraphs[0].runs[0].font.size = Pt(28)
    tf.paragraphs[0].runs[0].font.bold = True
    tf.paragraphs[0].runs[0].font.color.rgb = RGBColor(0x2F, 0x54, 0x96)

    # Add table
    rows, cols = len(data) + 1, 4
    table = slide.shapes.add_table(rows, cols,
        Inches(0.5), Inches(1.2), Inches(12), Inches(0.5 * rows)
    ).table

    # Headers
    headers = ["Month", "Revenue", "Target", "Achievement"]
    for i, header in enumerate(headers):
        cell = table.cell(0, i)
        cell.text = header
        cell.text_frame.paragraphs[0].runs[0].font.bold = True
        cell.text_frame.paragraphs[0].alignment = PP_ALIGN.CENTER

    # Data
    for row_idx, item in enumerate(data, 1):
        table.cell(row_idx, 0).text = item["month"]
        table.cell(row_idx, 1).text = f"${item['sales']:,}"
        table.cell(row_idx, 2).text = f"${item['target']:,}"
        rate = item['sales'] / item['target'] * 100
        table.cell(row_idx, 3).text = f"{rate:.1f}%"

    return prs


# Save
data = [
    {"month": "Jan", "sales": 85000000, "target": 80000000},
    {"month": "Feb", "sales": 92000000, "target": 90000000},
    {"month": "Mar", "sales": 78000000, "target": 95000000},
]
prs = create_presentation("2024 H1 Performance Report", data)
prs.save("report.pptx")

---

smtplib — Automated Email Sending

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from pathlib import Path
import os


class EmailSender:
    def __init__(self, smtp_host: str, smtp_port: int, email: str, password: str):
        self.smtp_host = smtp_host
        self.smtp_port = smtp_port
        self.email = email
        self.password = password

    def send(
        self,
        to: str | list[str],
        subject: str,
        body: str,
        html: str = None,
        attachments: list[str] = None,
    ) -> bool:
        msg = MIMEMultipart("alternative" if html else "mixed")
        msg["From"] = self.email
        msg["To"] = to if isinstance(to, str) else ", ".join(to)
        msg["Subject"] = subject

        # Body (text + HTML)
        msg.attach(MIMEText(body, "plain", "utf-8"))
        if html:
            msg.attach(MIMEText(html, "html", "utf-8"))

        # Attachments
        for filepath in (attachments or []):
            path = Path(filepath)
            with open(path, "rb") as f:
                part = MIMEApplication(f.read(), Name=path.name)
                part["Content-Disposition"] = f'attachment; filename="{path.name}"'
                msg.attach(part)

        try:
            with smtplib.SMTP_SSL(self.smtp_host, self.smtp_port) as server:
                server.login(self.email, self.password)
                recipients = [to] if isinstance(to, str) else to
                server.sendmail(self.email, recipients, msg.as_string())
            print(f"✅ Email sent: {to}")
            return True
        except Exception as e:
            print(f"❌ Email failed: {e}")
            return False


# Gmail example (App Password required)
sender = EmailSender(
    smtp_host="smtp.gmail.com",
    smtp_port=465,
    email=os.environ["GMAIL_USER"],
    password=os.environ["GMAIL_APP_PASSWORD"],
)

html_body = """
<html><body>
<h2>Monthly Report</h2>
<p>Hello, please find the <strong>monthly performance</strong> attached.</p>
<table border="1">
  <tr><th>Item</th><th>Value</th></tr>
  <tr><td>Revenue</td><td>$9.2M</td></tr>
</table>
</body></html>
"""

sender.send(
    to=["manager@example.com", "ceo@example.com"],
    subject="[Auto] March 2024 Monthly Report",
    body="Please find the monthly report in the attached files.",
    html=html_body,
    attachments=["sales_report.xlsx", "report.pptx"],
)

---

Summary

Library	Target	Key Features
openpyxl	Excel (.xlsx)	Cell styles, formulas, charts
python-pptx	PowerPoint (.pptx)	Slides, text, tables
smtplib	Email	Text/HTML/attachment delivery
pandas	CSV/Excel	Data analysis and export

This combination enables full automation: collect data → analyze → generate reports → auto-send.