从Python基础语法到办公自动化实战的学习路线

from pathlib import Path
import shutil
import datetime

def organize_download_folder(target_dir: str):
    base_path = Path(target_dir).resolve()
    if not base_path.exists():
        print(f"[错误] 目标路径不存在: {base_path}")
        return

    # 定义目标类别及对应的文件后缀
    rule_map = {
        "表格文件": [".xlsx", ".xls", ".csv"],
        "文档资料": [".docx", ".doc", ".pdf", ".pptx"],
        "压缩归档": [".zip", ".rar", ".7z", ".tar.gz"],
        "图片资源": [".png", ".jpg", ".jpeg", ".svg"]
    }

    archive_root = base_path / "归档目录"
    archive_root.mkdir(exist_ok=True)
    summary_count = {category: 0 for category in rule_map}
    summary_count["其他杂项"] = 0

    # 遍历目标目录下的所有顶层文件（跳过子文件夹）
    for file_path in base_path.iterdir():
        if file_path.is_dir() or file_path.name.startswith("~"):
            continue

        ext = file_path.suffix.lower()
        matched_category = "其他杂项"
        for category, extensions in rule_map.items():
            if ext in extensions:
                matched_category = category
                break

        dest_dir = archive_root / matched_category
        dest_dir.mkdir(exist_ok=True)

        # 获取文件修改时间并格式化
        mtime = datetime.datetime.fromtimestamp(file_path.stat().st_mtime)
        date_str = mtime.strftime("%Y%m%d")
        new_name = f"{date_str}_{file_path.name}"
        dest_file = dest_dir / new_name

        try:
            shutil.move(str(file_path), str(dest_file))
            summary_count[matched_category] += 1
            print(f"[已整理] {file_path.name} -> {matched_category}/{new_name}")
        except Exception as e:
            print(f"[跳过] 移动文件失败 {file_path.name}: {e}")

    print("
--- 整理结果汇总 ---")
    for cat, count in summary_count.items():
        print(f"{cat}: {count} 个文件")

if __name__ == "__main__":
    # 执行示例：请传入读者本地的实际测试目录
    organize_download_folder("./workspace/downloads")

from pathlib import Path
import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side

def process_sales_reports(input_dir: str, output_file: str):
    data_dir = Path(input_dir).resolve()
    all_data = []

    # 1. 批量遍历各部门报表
    for xlsx_file in data_dir.glob("*.xlsx"):
        if xlsx_file.name.startswith("~") or xlsx_file.name == Path(output_file).name:
            continue
        try:
            df = pd.read_excel(xlsx_file, engine="openpyxl")
            # 校验必需字段
            required_cols = {"部门", "销售员", "目标额", "实际额"}
            if not required_cols.issubset(set(df.columns)):
                print(f"[警告] {xlsx_file.name} 缺少必要列，跳过处理")
                continue
            all_data.append(df)
        except Exception as e:
            print(f"[报错] 读取文件失败 {xlsx_file.name}: {e}")

    if not all_data:
        print("[提示] 未找到有效的销售数据文件")
        return

    # 2. 使用 pandas 完成分组聚合与指标核算
    combined_df = pd.concat(all_data, ignore_index=True)
    summary = combined_df.groupby("部门", as_index=False)[["目标额", "实际额"]].sum()
    summary["达成率(%)"] = (summary["实际额"] / summary["目标额"] * 100).round(2)
    summary["状态"] = summary["达成率(%)"].apply(lambda x: "达成" if x >= 100 else ("预警" if x < 80 else "正常"))

    # 3. 输出至临时文件并使用 openpyxl 进行样式美化
    temp_output = Path(output_file).resolve().with_suffix(".tmp.xlsx")
    summary.to_excel(temp_output, index=False, engine="openpyxl")

    wb = openpyxl.load_workbook(temp_output)
    ws = wb.active
    ws.title = "部门达成汇总"

    # 样式配置：工程蓝表头，浅红预警填充
    header_fill = PatternFill(start_color="345E82", end_color="345E82", fill_type="solid")
    header_font = Font(name="Microsoft YaHei", size=11, bold=True, color="FFFFFF")
    warn_fill = PatternFill(start_color="FCE8E6", end_color="FCE8E6", fill_type="solid")
    warn_font = Font(name="Microsoft YaHei", size=10, color="C5221F", bold=True)
    thin_border = Border(
        left=Side(style='thin', color='CBD5E1'),
        right=Side(style='thin', color='CBD5E1'),
        top=Side(style='thin', color='CBD5E1'),
        bottom=Side(style='thin', color='CBD5E1')
    )

    # 渲染表头
    for col in range(1, ws.max_column + 1):
        cell = ws.cell(row=1, column=col)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center")

    # 渲染数据行与预警
    for row in range(2, ws.max_row + 1):
        rate_cell = ws.cell(row=row, column=4)  # 达成率列
        status_cell = ws.cell(row=row, column=5) # 状态列

        for col in range(1, ws.max_column + 1):
            c = ws.cell(row=row, column=col)
            c.border = thin_border
            c.font = Font(name="Microsoft YaHei", size=10)

        # 达成率低于 80% 则标红预警
        if rate_cell.value and float(rate_cell.value) < 80.0:
            rate_cell.fill = warn_fill
            rate_cell.font = warn_font
            status_cell.fill = warn_fill
            status_cell.font = warn_font

    # 自适应列宽
    for col in ws.columns:
        max_len = max(len(str(cell.value or '')) for cell in col)
        col_letter = openpyxl.utils.get_column_letter(col[0].column)
        ws.column_dimensions[col_letter].width = max(max_len * 2.2, 14)

    wb.save(temp_output)
    # 原子性替换为正式输出文件
    target_path = Path(output_file).resolve()
    temp_output.replace(target_path)
    print(f"[成功] 汇总报表已生成: {target_path}")

if __name__ == "__main__":
    process_sales_reports("./sales_data", "./reports/2026_03_部门月报汇总.xlsx")

import logging
from pathlib import Path

def setup_logger(log_dir: Path) -> logging.Logger:
    log_dir.mkdir(parents=True, exist_ok=True)
    log_file = log_dir / "automation_runtime.log"

    logger = logging.getLogger("OfficeAutomation")
    logger.setLevel(logging.INFO)

    # 避免重复挂载 Handler
    if not logger.handlers:
        file_handler = logging.FileHandler(log_file, encoding="utf-8")
        formatter = logging.Formatter(
            fmt="%(asctime)s [%(levelname)s] (%(filename)s:%(lineno)d) - %(message)s",
            datefmt="%Y-%m-%d %H:%M:%S"
        )
        file_handler.setFormatter(formatter)
        logger.addHandler(file_handler)

    return logger

在编写自动化脚本时，永远不要直接对原始数据文件执行写操作。网络波动、断电、内存溢出或字段格式转换异常都可能导致写入中途崩溃，从而将原文件损坏为 0 字节的空文件。工程化最佳实践是：先读取原文件，处理完毕后先写入带有 .tmp 后缀的临时文件；在验证临时文件大小与格式无误后，再调用 replace() 方法进行原子替换，或将处理结果写入独立的输出目录中并对源文件做备份存档。