Build nested folder trees from an Excel hierarchy

import sys
import json
import os
import re
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

with open(sys.argv[1], encoding="utf-8") as f:
    inputs = json.load(f)

mode = inputs["mode"]
folder = inputs["folder"]
excel_path = inputs["excel_file"]

INVALID_CHARS = r'\/:*?"<>|'
INVALID_PATTERN = re.compile(r'[\\/:*?"<>|]')

def validate_name(name):
    found = set(INVALID_PATTERN.findall(name))
    return sorted(found)

try:
    # ── Generate template ───────────────────────────
    if mode == "Generate template":
        wb = Workbook()

        ws_data = wb.active
        ws_data.title = "Data"

        # Header row (4 columns by default; add more columns to support deeper trees)
        DEFAULT_LEVELS = 4
        header_fill = PatternFill(fill_type="solid", fgColor="4472C4")
        header_font = Font(bold=True, color="FFFFFF")
        for col_i in range(1, DEFAULT_LEVELS + 1):
            cell = ws_data.cell(row=1, column=col_i, value=f"Depth {col_i}")
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center")
            ws_data.column_dimensions[get_column_letter(col_i)].width = 20

        # Legend sheet
        ws_legend = wb.create_sheet(title="Legend")

        section_font = Font(bold=True, color="FFFFFF")
        section_fill = PatternFill(fill_type="solid", fgColor="4472C4")
        sample_fill = PatternFill(fill_type="solid", fgColor="E9F0FB")
        indent_fill = PatternFill(fill_type="solid", fgColor="F5F5F5")

        ws_legend["A1"] = "📋 Folder builder — how to fill this in"
        ws_legend["A1"].font = Font(bold=True, size=13)
        ws_legend.merge_cells("A1:D1")

        ws_legend["A3"] = "Rules"
        ws_legend["A3"].font = section_font
        ws_legend["A3"].fill = section_fill
        ws_legend.merge_cells("A3:D3")

        rules = [
            "- Column A (Depth 1) is the top folder; Column B (Depth 2) sits inside it; Column C and beyond continue the pattern",
            "- The leftmost filled column on a row determines that folder's depth",
            "- Add more columns to support deeper trees (Depth 5, Depth 6, and so on)",
            "- Blank rows are skipped",
            f"- Forbidden characters in folder names: {INVALID_CHARS}",
        ]
        for i, rule in enumerate(rules, start=4):
            ws_legend[f"A{i}"] = rule
            ws_legend.merge_cells(f"A{i}:D{i}")

        ws_legend["A10"] = "Sample"
        ws_legend["A10"].font = section_font
        ws_legend["A10"].fill = section_fill
        ws_legend.merge_cells("A10:D10")

        for col_i, h in enumerate(["Depth 1", "Depth 2", "Depth 3", "Depth 4"], start=1):
            cell = ws_legend.cell(row=11, column=col_i, value=h)
            cell.font = Font(bold=True)
            cell.fill = PatternFill(fill_type="solid", fgColor="BDD7EE")
            cell.alignment = Alignment(horizontal="center")

        sample_rows = [
            ["Project A", "", "", ""],
            ["", "Materials", "", ""],
            ["", "", "FY2024", ""],
            ["", "", "FY2025", ""],
            ["", "Minutes", "", ""],
            ["", "", "Weekly meeting", ""],
            ["Project B", "", "", ""],
            ["", "Materials", "", ""],
            ["", "Reports", "", ""],
        ]
        for row_i, row_data in enumerate(sample_rows, start=12):
            for col_i, val in enumerate(row_data, start=1):
                cell = ws_legend.cell(row=row_i, column=col_i, value=val)
                cell.fill = sample_fill if val else indent_fill

        ws_legend["A22"] = "↑ This example creates: Project A/Materials/FY2024, Project A/Materials/FY2025, Project A/Minutes/Weekly meeting, Project B/Materials, Project B/Reports"
        ws_legend["A22"].font = Font(color="808080", italic=True)
        ws_legend.merge_cells("A22:D22")

        for col in ["A", "B", "C", "D"]:
            ws_legend.column_dimensions[col].width = 22

        save_path = os.path.join(folder, "folder_template.xlsx")
        wb.save(save_path)
        print(f"Template written: {save_path}")

    # ── Create folders ──────────────────────────────
    elif mode == "Create folders":
        if not excel_path:
            print("Error: please specify the Excel file", file=sys.stderr)
            sys.exit(1)

        wb = load_workbook(excel_path, data_only=True)
        ws = wb["Data"]

        max_col = ws.max_column
        print(f"Detected columns: {max_col} (up to {max_col} levels deep)\n")

        # Read from row 2 (row 1 is the header)
        all_rows = []
        for row in ws.iter_rows(min_row=2, values_only=True):
            all_rows.append([str(c).strip() if c is not None else "" for c in row])

        # Validate
        errors = []
        for row_i, row in enumerate(all_rows, start=2):
            for col_i, val in enumerate(row):
                if val:
                    bad = validate_name(val)
                    if bad:
                        errors.append(f"Row {row_i}, Col {col_i+1} '{val}' contains forbidden characters: {' '.join(bad)}")

        if errors:
            print("⚠️ Issues found. Fix the Excel and rerun.\n", file=sys.stderr)
            for e in errors:
                print(f"  - {e}", file=sys.stderr)
            sys.exit(1)

        print("✅ Validation OK\n")

        # Walk depths and build paths
        current_path = {}
        paths_to_create = []

        for row in all_rows:
            level = None
            value = None
            for col_i, val in enumerate(row):
                if val:
                    level = col_i
                    value = val
                    break

            if level is None:
                continue

            current_path[level] = value
            for deeper in list(current_path.keys()):
                if deeper > level:
                    del current_path[deeper]

            parts = [current_path[l] for l in sorted(current_path.keys())]
            full_path = os.path.join(folder, *parts)
            paths_to_create.append((full_path, "/".join(parts)))

        print(f"Detected {len(paths_to_create)} folders\n")

        created = 0
        skipped = 0

        for full_path, display in paths_to_create:
            if not os.path.exists(full_path):
                os.makedirs(full_path)
                print(f"Created: {display}/")
                created += 1
            else:
                print(f"Skipped (existing): {display}/")
                skipped += 1

        print(f"\nDone — created: {created} / skipped: {skipped}")
        print(f"Saved under: {folder}")

except Exception as e:
    print(f"Error: {e}", file=sys.stderr)