【業務自動化】Chapter1-6｜簡単なExcel自動化を体験しよう

import pathlib
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.styles.colors import Color


def create_main_sheet(sheet):
    sheet.title = "Sheet1"

    # 行の高さ・列幅
    sheet.sheet_format.defaultRowHeight = 18.75
    sheet.sheet_format.defaultColWidth = 9
    sheet.column_dimensions["B"].width = 11.625
    sheet.column_dimensions["D"].width = 11.375

    # データ（Chapter1-5で使う元データ）
    data_list = [
        ["名前", "性別", "年齢", "年齢区分"],
        ["吉田 彩花", "女", 45, "成人"],
        ["小林 翔太", "男", 21, "成人"],
        ["長谷川 清", "男", 80, "シニア"],
        ["山田 大輔", "男", 40, "成人"],
        ["佐藤 陽翔", "男", 1, "未成年"],
        ["渡辺 亮", "男", 50, "成人"],
        ["伊藤 陽菜", "女", 12, "未成年"],
        ["木村 智子", "女", 75, "シニア"],
        ["鈴木 結衣", "女", 3, "未成年"],
        ["松本 健太", "男", 30, "成人"],
        ["石井 誠", "男", 70, "シニア"],
        ["中村 莉子", "女", 18, "未成年"],
        ["高橋 悠真", "男", 6, "未成年"],
        ["田中 美月", "女", 9, "未成年"],
        ["岡田 隆", "男", 60, "成人"],
        ["藤田 美穂", "女", 55, "成人"],
        ["山本 大和", "男", 15, "未成年"],
        ["村上 幸子", "女", 65, "シニア"],
        ["佐々木 真由", "女", 35, "成人"],
        ["加藤 愛美", "女", 25, "成人"],
    ]

    # スタイル設定
    font = Font(name="游ゴシック", size=11)

    fill = PatternFill(
        patternType="solid",
        fgColor=Color(theme=5, tint=0.4)
    )

    thin_side = Side(style="thin", color=Color(auto=1))

    header_border = Border(
        left=thin_side, right=thin_side, top=thin_side, bottom=thin_side
    )
    row3_b_d_border = Border(
        left=thin_side, right=thin_side, bottom=thin_side
    )
    row3_c_border = Border(
        left=thin_side, bottom=thin_side
    )
    body_b_d_border = Border(
        left=thin_side, right=thin_side, top=thin_side, bottom=thin_side
    )
    body_c_border = Border(
        left=thin_side, top=thin_side, bottom=thin_side
    )
    body_e_border = Border(
        left=thin_side, right=thin_side, top=thin_side, bottom=thin_side
    )

    header_alignment = Alignment(vertical="center")
    body_alignment = Alignment(vertical="center", wrap_text=True)

    # セルに書き込み
    start_row = 2
    start_col = 2  # B列から開始

    for r, row_data in enumerate(data_list, start=start_row):
        for c, value in enumerate(row_data, start=start_col):
            cell = sheet.cell(row=r, column=c, value=value)
            cell.font = font

            if r == 2:
                cell.fill = fill
                cell.border = header_border
                cell.alignment = header_alignment
            else:
                if c in [2, 4]:  # B列、D列
                    if r == 3:
                        cell.border = row3_b_d_border
                    else:
                        cell.border = body_b_d_border
                    cell.alignment = body_alignment

                elif c == 3:  # C列
                    if r == 3:
                        cell.border = row3_c_border
                    else:
                        cell.border = body_c_border
                    cell.alignment = body_alignment

                elif c == 5:  # E列
                    cell.border = body_e_border
                    cell.alignment = Alignment(vertical="center")


def split_to_new_sheets(book, source_sheet, key_index, keep_main_sheet_format=False):
    HEADER_ROW_NO = 2
    READ_START_ROW_NO = 3

    # 見出し行をリスト化（A列の空欄も含める）
    header_row_values = []
    start_column = max(1, source_sheet.min_column - 1)
    for i in range(start_column, source_sheet.max_column + 1):
        header_row_values.append(source_sheet.cell(HEADER_ROW_NO, i).value)

    # データ行を振り分け
    for row in source_sheet.iter_rows(min_row=READ_START_ROW_NO):
        row_values = [cell.value for cell in row]
        key_value = str(row_values[key_index])

        if key_value in book.sheetnames:
            to_sheet = book[key_value]
        else:
            to_sheet = book.create_sheet(title=key_value)

            # アップロードされた最終形に合わせて、性別シートのみ既定の行高・列幅をそろえる
            if keep_main_sheet_format:
                to_sheet.sheet_format.defaultRowHeight = source_sheet.sheet_format.defaultRowHeight
                to_sheet.sheet_format.defaultColWidth = source_sheet.sheet_format.defaultColWidth

            to_sheet.append(header_row_values)

        to_sheet.append(row_values)


def make_workbook():
    folder_path = pathlib.Path(__file__).parent / "workbooks"
    folder_path.mkdir(exist_ok=True)
    save_path = folder_path / "Chapter1-5.xlsx"

    workbook = Workbook()
    sheet = workbook.active

    # 元の一覧シートを作成（Chapter1-4の完成状態）
    create_main_sheet(sheet)

    # Chapter1-5本文の結果（性別ごとのシート）
    split_to_new_sheets(
        book=workbook,
        source_sheet=sheet,
        key_index=2,   # 性別（C列）
        keep_main_sheet_format=True
    )

    # Chapter1-5練習問題の結果（年齢区分ごとのシート）
    split_to_new_sheets(
        book=workbook,
        source_sheet=sheet,
        key_index=4,   # 年齢区分（E列）
        keep_main_sheet_format=False
    )

    workbook.save(save_path)
    print(f"{save_path} を作成しました。")


if __name__ == "__main__":
    make_workbook()

業務自動化学習/			  # 学習フォルダ
 ├── Chap1/				 # チャプター１の保存フォルダ
 │   ├── Chap1-6/		# チャプター1-6の保存フォルダ
 │   │   ├── Chapter1-6.py
 │   │   ├── make_workbook1-5.py # ☆ここに保存
 │   │   └── workbooks/
 │   │　      ├── Chapter1-5.xlsx 
 │   │　      └── outputs/