여러 Excel 시트를 출처 시트 열과 함께 하나의 표로 합치기

from pathlib import Path
from openpyxl import Workbook

SOURCE_DIR = Path("outputs") / "excel_merge_demo"
SOURCE = SOURCE_DIR / "regional_orders.xlsx"
HEADERS = ["order_id", "customer", "amount"]
DATA = {
    "East": [
        ["E001", "Ana", 120],
        ["E002", "Ben", 80],
    ],
    "West": [
        ["W001", "Cara", 150],
        ["W002", "Dan", 70],
    ],
    "Online": [
        ["O001", "Emi", 90],
        [None, None, None],
        ["O002", "Finn", 110],
    ],
}

SOURCE_DIR.parent.mkdir(parents=True, exist_ok=True)
SOURCE_DIR.mkdir()  # Stop if the synthetic source folder already exists.

workbook = Workbook()
for index, (sheet_name, rows) in enumerate(DATA.items()):
    if index == 0:
        sheet = workbook.active
        sheet.title = sheet_name
    else:
        sheet = workbook.create_sheet(sheet_name)
    sheet.append(HEADERS)
    for row in rows:
        sheet.append(row)

workbook.save(SOURCE)

python create_excel_merge_demo.py

from pathlib import Path
from openpyxl import Workbook, load_workbook

SOURCE = Path("outputs") / "excel_merge_demo" / "regional_orders.xlsx"
OUTPUT_DIR = Path("outputs") / "excel_merge_result"
OUTPUT = OUTPUT_DIR / "merged_orders.xlsx"
SHEETS = ("East", "West", "Online")
OUTPUT_SHEET = "Combined"
SOURCE_COLUMN = "source_sheet"


def read_sheet_rows(sheet, expected_header=None):
    first_row = next(
        sheet.iter_rows(min_row=1, max_row=1, values_only=True),
        None,
    )
    if first_row is None:
        raise ValueError(f"Sheet is empty: {sheet.title}")

    header = tuple(first_row)
    if any(not isinstance(name, str) or not name.strip() for name in header):
        raise ValueError(f"Invalid header in sheet: {sheet.title}")
    if len(set(header)) != len(header):
        raise ValueError(f"Duplicate header name in sheet: {sheet.title}")
    if SOURCE_COLUMN in header:
        raise ValueError(f"Reserved column already exists: {SOURCE_COLUMN}")
    if expected_header is not None and header != expected_header:
        raise ValueError(f"Header mismatch in sheet: {sheet.title}")

    rows = []
    for excel_row, values in enumerate(
        sheet.iter_rows(min_row=2, max_col=len(header), values_only=True),
        start=2,
    ):
        if all(value is None for value in values):
            continue
        if any(isinstance(value, str) and value.startswith("=") for value in values):
            raise ValueError(
                f"Formula found in {sheet.title} row {excel_row}; "
                "this example merges literal values only."
            )
        rows.append(tuple(values))

    return header, rows


def main() -> None:
    source_path = SOURCE.resolve(strict=True)
    if not source_path.is_file():
        raise ValueError("SOURCE must be an Excel file.")
    if len(set(SHEETS)) != len(SHEETS):
        raise ValueError("SHEETS contains a duplicate sheet name.")

    source_workbook = load_workbook(
        source_path,
        read_only=True,
        data_only=False,
    )
    try:
        missing = [name for name in SHEETS if name not in source_workbook.sheetnames]
        if missing:
            raise ValueError(f"Missing sheets: {missing}")

        expected_header = None
        combined_rows = []
        for sheet_name in SHEETS:
            sheet = source_workbook[sheet_name]
            header, rows = read_sheet_rows(sheet, expected_header)
            if expected_header is None:
                expected_header = header
            for row in rows:
                combined_rows.append((sheet_name, *row))
    finally:
        source_workbook.close()

    OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
    OUTPUT_DIR.mkdir()  # Refuse to reuse an existing output folder.

    output_workbook = Workbook()
    output_sheet = output_workbook.active
    output_sheet.title = OUTPUT_SHEET
    output_sheet.append([SOURCE_COLUMN, *expected_header])
    for row in combined_rows:
        output_sheet.append(row)
    output_workbook.save(OUTPUT)

    check_workbook = load_workbook(OUTPUT, read_only=True, data_only=False)
    try:
        if check_workbook.sheetnames != [OUTPUT_SHEET]:
            raise RuntimeError("Unexpected output worksheet structure.")
        check_sheet = check_workbook[OUTPUT_SHEET]
        written = list(check_sheet.iter_rows(values_only=True))
    finally:
        check_workbook.close()

    expected = [(SOURCE_COLUMN, *expected_header), *combined_rows]
    if written != expected:
        raise RuntimeError("Saved workbook does not match the expected rows.")

    print(f"Merged {len(SHEETS)} sheets into {len(combined_rows)} data rows.")
    print(f"Verified {len(expected_header) + 1} columns and {len(combined_rows)} data rows.")
    print(f"Output: {OUTPUT.as_posix()}")


if __name__ == "__main__":
    main()

python excel_merge_sheets.py

Merged 3 sheets into 6 data rows.
Verified 4 columns and 6 data rows.
Output: outputs/excel_merge_result/merged_orders.xlsx