業務自動化の事例集

from openpyxl import load_workbook
from collections import defaultdict

def aggregate_monthly(path):
    wb = load_workbook(path, data_only=True)
    ws = wb.active
    monthly = defaultdict(lambda: {"amount": 0, "count": 0})
    for date, _, _, qty, price in ws.iter_rows(min_row=2, values_only=True):
        if not date: continue
        key = date.strftime("%Y-%m")
        monthly[key]["amount"] += qty * price
        monthly[key]["count"] += 1
    return dict(sorted(monthly.items()))

import win32com.client
from openpyxl import load_workbook

def build_body(name, person, amount, month):
    return (
        f"{name} {person} 様\n\n"
        "いつも大変お世話になっております。\n"
        f"{month}分のご請求書をお送りいたします。\n\n"
        f"ご請求金額: {amount:,} 円(税込)\n\n"
        "ご確認のほど、よろしくお願いいたします。"
    )

outlook = win32com.client.Dispatch("Outlook.Application")
wb = load_workbook("clients.xlsx")
for row in wb.active.iter_rows(min_row=2, values_only=True):
    name, person, email, amount = row[:4]
    mail = outlook.CreateItem(0)
    mail.To = email
    mail.Subject = f"【ご請求のご案内】{name} 様"
    mail.Body = build_body(name, person, amount, "2026年6月")
    mail.Save()      # 送信せず下書きフォルダに保存

import pandas as pd
from pathlib import Path

folder = Path("./branches")
all_dfs = []

for file in folder.glob("*.xlsx"):
    df = pd.read_excel(file)
    df["支店"] = file.stem          # ファイル名から支店名を付与
    all_dfs.append(df)
    print(f"読込: {file.name} ({len(df)}行)")

merged = pd.concat(all_dfs, ignore_index=True)
merged.to_excel("all_branches.xlsx", index=False)
print(f"合計 {len(merged)} 行を統合しました。")

import pdfplumber, re
from pathlib import Path
from openpyxl import Workbook

def extract_invoice(pdf_path):
    """PDFから 取引先名 / 金額 / 支払期日 を抽出する。"""
    with pdfplumber.open(pdf_path) as pdf:
        text = "\n".join(p.extract_text() for p in pdf.pages)
    m_amount   = re.search(r"ご請求金額[::]?\s*([\d,]+)", text)
    m_client   = re.search(r"^(株式会社[^\n]+|[^\n]+(?:商事|工業|物産))", text, re.M)
    m_due      = re.search(r"支払期日[::]?\s*(\d{4}[/-]\d{1,2}[/-]\d{1,2})", text)
    return {
        "client": m_client.group(1) if m_client else None,
        "amount": int(m_amount.group(1).replace(",", "")) if m_amount else None,
        "due":    m_due.group(1) if m_due else None,
    }

wb = Workbook(); ws = wb.active
ws.append(["ファイル", "取引先", "金額", "支払期日"])
for pdf in Path("./invoices").glob("*.pdf"):
    info = extract_invoice(pdf)
    ws.append([pdf.name, info["client"], info["amount"], info["due"]])
wb.save("invoice_summary.xlsx")

import re

def normalize_phone(s):
    """電話番号を 03-1234-5678 形式に統一する。"""
    if not s: return ""
    s = s.translate(str.maketrans("0123456789-", "0123456789-"))   # 全角→半角
    s = re.sub(r"[^0-9]", "", s)              # 数字以外を除去
    if len(s) == 11:    return f"{s[:3]}-{s[3:7]}-{s[7:]}"
    if len(s) == 10:    return f"{s[:2]}-{s[2:6]}-{s[6:]}"
    return s

def normalize_zip(s):
    """郵便番号を 100-0001 形式に統一する。"""
    if not s: return ""
    s = re.sub(r"[^0-9]", "", str(s))
    if len(s) == 7: return f"{s[:3]}-{s[3:]}"
    return s

# 動作確認
print(normalize_phone("090-1234-5678"))     # → 090-1234-5678
print(normalize_phone("09012345678"))         # → 090-1234-5678
print(normalize_zip("〒100-0001"))             # → 100-0001
print(normalize_zip("1000001"))                 # → 100-0001

import pandas as pd

def build_pivot(src="orders.xlsx", dst="pivot.xlsx"):
    df = pd.read_excel(src)
    df["金額"] = df["数量"] * df["単価"]
    df["月"] = pd.to_datetime(df["注文日"]).dt.to_period("M").astype(str)
    pivot = pd.pivot_table(
        df, index="商品コード", columns="月",
        values="金額", aggfunc="sum", fill_value="-"
    )
    pivot.to_excel(dst)
    print(f"{dst} を出力しました。")

if __name__ == "__main__":
    build_pivot()