Simple Python Code for Office Automation: Copy and Use

The following are commonly used code scenarios and core library recommendations for Python in office automation, organized based on multiple practical cases:

1. Excel Data Processing

Data Cleaning and Merging

import pandas as pd
def merge_excel(folder_path):
    all_data = []
    for file in Path(folder_path).glob('*.xlsx'):
        df = pd.read_excel(file, skiprows=2)
        df['Source'] = file.name
        all_data.append(df)
    return pd.concat(all_data).dropna().to_excel("merged.xlsx")

Features: Automatically skips header rows, merges multiple files, intelligently cleans empty values

Data Visualization Reports

import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('data.xlsx')
ws = wb.active
chart = BarChart()
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=10)
categories = Reference(ws, min_col=1, min_row=2, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "G2")
bb.save("report.xlsx")

Advantages: Natively supports Excel chart generation, suitable for financial reports and similar scenarios

2. Email Automation

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
def send_report(subject, body, to_email):
    msg = MIMEMultipart()
    msg.attach(MIMEText(body, 'html'))
    msg['Subject'] = subject
    msg['From'] = os.getenv('EMAIL')
    msg['To'] = to_email
    with smtplib.SMTP_SSL('smtp.office365.com', 587) as server:
        server.login(os.getenv('EMAIL'), os.getenv('EMAIL_PWD'))
        server.send_message(msg)

Security Tip: It is recommended to store sensitive information through environment variables

3. File Management

Intelligent Classification and Archiving

import os, shutil
def auto_organize(folder):
    file_types = {
        'Images': ['.jpg', '.png'],
        'Documents': ['.pdf', '.docx'],
        'Archives': ['.zip', '.rar']
    }
    for file in os.listdir(folder):
        ext = os.path.splitext(file)[1].lower()
        for category, exts in file_types.items():
            if ext in exts:
                os.makedirs(os.path.join(folder, category), exist_ok=True)
                shutil.move(file, os.path.join(folder, category))

Application Scenario: Automatically organize the downloads folder

Incremental Backup System

import shutil, datetime
def backup(src, dst):
    today = datetime.datetime.now().strftime("%Y%m%d")
    backup_dir = f"{dst}/backup_{today}"
    if not os.path.exists(backup_dir):
        shutil.copytree(src, backup_dir)
    else:
        print("Backup already done today")

Extension Suggestion: Can be combined with rsync for differential backup

4. PDF Processing

from PyPDF2 import PdfReader, PdfWriter
def split_pdf(input_pdf, pages):
    reader = PdfReader(input_pdf)
    writer = PdfWriter()
    for page in pages:
        writer.add_page(reader.pages[page-1])
    with open("output.pdf", "wb") as f:
        writer.write(f)

Advanced Solution: Use pdfplumber to extract table data

5. Web Data Scraping

import requests
from bs4 import BeautifulSoup
def get_stock_price(url):
    res = requests.get(url)
    soup = BeautifulSoup(res.text, 'html.parser')
    price = soup.select_one('.price').text
    return f"Current stock price: {price}"

Note: Must comply with the target website’s robots.txt protocol

6. Scheduled Task Management

import schedule
import time
def daily_report():
    # Logic for generating daily report
schedule.every().day.at("09:00").do(daily_report)
while True:
    schedule.run_pending()
    time.sleep(60)

Extension Solution: Use APScheduler for complex scheduling

Recommended Tool Combinations

Basic Office: pandas (data processing) + python-docx (webpage generation)

Complex Reports: openpyxl (advanced Excel operations) + ReportLab (PDF generation)

Full Process Automation: Airflow (task orchestration) + Selenium (web operations)

Learning Suggestion: First master the three basic libraries: os/shutil/pandas, then gradually expand to other specialized libraries. It is recommended to use Jupyter Notebook for code testing and gradually build your own automation tool library.