Excel to word

Step & What It Does:

Read Excel data — Uses pandas.read_excel() with dtype=str and fillna("") to load all cells as strings

Resolve column roles — Automatically assigns group_column, section_title_column, and content_column from the first available columns if not provided

Create Word document — Instantiates a python-docx Document, sets the Normal style font, and adds a title heading

Group and iterate — Uses DataFrame.groupby() on the group column, then iterates each group's rows

Write sections — For each row, writes a SECTION heading, optional CONTENT paragraph, and METADATA bullet list

Save document — Creates parent directories if needed and saves the .docx file

"""Excel to Word document converter optimized for AI parsing.

Transforms structured Excel data into a Word document with deterministic
GROUP → SECTION → CONTENT → METADATA markers for AI agent consumption.

All sensitive values (paths, usernames, project names) must be configured
via arguments — never hardcoded.
"""

from __future__ import annotations

import logging
import os
from dataclasses import dataclass
from pathlib import Path
from typing import Union

import pandas as pd
from docx import Document
from docx.shared import Pt

# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------

logger = logging.getLogger(__name__)

_VALID_EXTENSIONS = {".xlsx", ".xls", ".xlsm", ".xlsb"}
_MAX_FILE_SIZE_MB = 100  # Guard against memory exhaustion

# ---------------------------------------------------------------------------
# Custom Exception
# ---------------------------------------------------------------------------

class ExcelToWordError(Exception):
    """Raised when the Excel-to-Word conversion encounters a recoverable error.

    Provides actionable context about what went wrong and how to fix it.
    """

    pass

# ---------------------------------------------------------------------------
# Result Data Class
# ---------------------------------------------------------------------------

@dataclass(frozen=True)
class ConversionResult:
    """Immutable result of a successful conversion."""

    output_path: Path
    groups_written: int
    sections_written: int
    rows_processed: int
    columns_used: list[str]

# ---------------------------------------------------------------------------
# Validation Layer
# ---------------------------------------------------------------------------

def _validate_inputs(
    excel_path: Path,
    word_path: Path,
    sheet_name: Union[int, str],
) -> None:
    """Validate all inputs before any processing begins.

    Args:
        excel_path: Path to the source Excel file.
        word_path: Path where the Word document will be saved.
        sheet_name: Sheet name (str) or index (int) to read.

    Raises:
        ExcelToWordError: If any validation check fails.
    """
    # Check Excel file exists
    if not excel_path.is_file():
        raise ExcelToWordError(
            f"Excel file not found: '{excel_path}'. "
            "Verify the path exists and is accessible."
        )

    # Check extension
    if excel_path.suffix.lower() not in _VALID_EXTENSIONS:
        raise ExcelToWordError(
            f"Unsupported file extension: '{excel_path.suffix}'. "
            f"Expected one of: {sorted(_VALID_EXTENSIONS)}"
        )

    # Check file size
    file_size_mb = excel_path.stat().st_size / (1024 * 1024)
    if file_size_mb > _MAX_FILE_SIZE_MB:
        raise ExcelToWordError(
            f"File size ({file_size_mb:.1f} MB) exceeds the "
            f"{_MAX_FILE_SIZE_MB} MB safety limit."
        )

    # Check output directory is writable
    output_dir = word_path.parent
    if output_dir.exists() and not os.access(output_dir, os.W_OK):
        raise ExcelToWordError(
            f"Output directory is not writable: '{output_dir}'"
        )

    # Check sheet_name type
    if not isinstance(sheet_name, (int, str)):
        raise ExcelToWordError(
            f"sheet_name must be int or str, got: {type(sheet_name).__name__}"
        )

# ---------------------------------------------------------------------------
# Data Loading Layer
# ---------------------------------------------------------------------------

def _load_dataframe(
    excel_path: Path,
    sheet_name: Union[int, str],
) -> pd.DataFrame:
    """Load Excel file into a DataFrame with all values as strings.

    Args:
        excel_path: Validated path to the Excel file.
        sheet_name: Sheet name or index to read.

    Returns:
        DataFrame with string values and empty strings replacing NaN.

    Raises:
        ExcelToWordError: If the file cannot be read or is empty.
    """
    try:
        df = pd.read_excel(excel_path, sheet_name=sheet_name, dtype=str)
    except FileNotFoundError:
        raise ExcelToWordError(f"Excel file not found: '{excel_path}'")
    except ValueError as exc:
        raise ExcelToWordError(
            f"Invalid sheet reference '{sheet_name}': {exc}"
        ) from exc
    except Exception as exc:
        raise ExcelToWordError(
            f"Failed to read Excel file: {type(exc).__name__}: {exc}"
        ) from exc

    df = df.fillna("")

    if df.empty:
        raise ExcelToWordError(
            f"Excel file has no data rows: '{excel_path}' "
            f"(sheet: '{sheet_name}')"
        )

    logger.info(
        "Loaded Excel file",
        extra={
            "rows": len(df),
            "columns": len(df.columns),
            "column_names": list(df.columns),
        },
    )
    return df

# ---------------------------------------------------------------------------
# Column Resolution Layer
# ---------------------------------------------------------------------------

@dataclass
class ResolvedColumns:
    """Holds the resolved column assignments for document generation."""

    group_column: str
    section_title_column: str | None
    content_column: str | None
    metadata_columns: list[str]

def _resolve_columns(
    df: pd.DataFrame,
    group_column: str | None,
    section_title_column: str | None,
    content_column: str | None,
) -> ResolvedColumns:
    """Resolve and validate column role assignments.

    If columns are not explicitly provided, they are inferred from position:
    - group_column: first column
    - section_title_column: second column (first after group)
    - content_column: third column (second after group)

    Args:
        df: The loaded DataFrame.
        group_column: Explicit group column name, or None for auto.
        section_title_column: Explicit section title column, or None for auto.
        content_column: Explicit content column, or None for auto.

    Returns:
        ResolvedColumns with validated assignments.

    Raises:
        ExcelToWordError: If a specified column does not exist in the DataFrame.
    """
    columns = list(df.columns)

    # Resolve group column
    if group_column is None:
        group_column = columns[0]
        logger.info("Auto-assigned group_column='%s' (first column)", group_column)
    elif group_column not in df.columns:
        raise ExcelToWordError(
            f"group_column '{group_column}' not found. "
            f"Available: {columns}"
        )

    # Determine remaining columns
    remaining = [c for c in columns if c != group_column]

    # Resolve section title column
    if section_title_column is None and remaining:
        section_title_column = remaining[0]
        logger.debug(
            "Auto-assigned section_title_column='%s'", section_title_column
        )
    elif section_title_column is not None and section_title_column not in df.columns:
        raise ExcelToWordError(
            f"section_title_column '{section_title_column}' not found. "
            f"Available: {columns}"
        )

    # Resolve content column
    if content_column is None and len(remaining) > 1:
        content_column = remaining[1]
        logger.debug("Auto-assigned content_column='%s'", content_column)
    elif content_column is not None and content_column not in df.columns:
        raise ExcelToWordError(
            f"content_column '{content_column}' not found. "
            f"Available: {columns}"
        )

    # Build metadata columns (exclude group, section_title, and content)
    excluded = {group_column, section_title_column, content_column} - {None}
    metadata_columns = [c for c in columns if c not in excluded]

    logger.info(
        "Column resolution complete",
        extra={
            "group_column": group_column,
            "section_title_column": section_title_column,
            "content_column": content_column,
            "metadata_columns": metadata_columns,
        },
    )

    return ResolvedColumns(
        group_column=group_column,
        section_title_column=section_title_column,
        content_column=content_column,
        metadata_columns=metadata_columns,
    )

# ---------------------------------------------------------------------------
# Document Creation Layer
# ---------------------------------------------------------------------------

def _create_document() -> Document:
    """Create a new Word document with standard styling.

    Returns:
        A configured Document instance ready for content.
    """
    doc = Document()
    normal_style = doc.styles["Normal"]
    normal_style.font.name = "Calibri"
    normal_style.font.size = Pt(10)

    doc.add_heading("Structured Knowledge Export", level=0)
    doc.add_paragraph(
        "Format: Group → Row Section → Metadata. "
        "This structure is optimized for deterministic parsing by AI agents."
    )
    return doc

# ---------------------------------------------------------------------------
# Content Generation Layer
# ---------------------------------------------------------------------------

def _write_row_section(
    doc: Document,
    row_dict: dict[str, str],
    section_number: int,
    cols: ResolvedColumns,
) -> None:
    """Write a single row as a SECTION in the Word document.

    Args:
        doc: The Document to write to.
        row_dict: Dictionary of column_name → value for this row.
        section_number: Global section counter for this row.
        cols: Resolved column configuration.
    """
    # Determine section title
    section_title = ""
    if cols.section_title_column:
        section_title = str(row_dict.get(cols.section_title_column, "")).strip()
    if not section_title:
        section_title = f"Item {section_number}"

    doc.add_heading(f"SECTION {section_number}: {section_title}", level=2)

    # Write content if available
    if cols.content_column:
        content_val = str(row_dict.get(cols.content_column, "")).strip()
        if content_val:
            p = doc.add_paragraph()
            p.add_run("CONTENT: ").bold = True
            p.add_run(content_val)

    # Write metadata
    if cols.metadata_columns:
        doc.add_paragraph("METADATA:", style="List Bullet")
        for col_name in cols.metadata_columns:
            val = str(row_dict.get(col_name, "")).strip()
            doc.add_paragraph(f"{col_name} = {val}", style="List Bullet 2")

    doc.add_paragraph("--- END_SECTION ---")

def _write_groups(
    doc: Document,
    df: pd.DataFrame,
    cols: ResolvedColumns,
) -> tuple[int, int]:
    """Write all groups and their rows to the document.

    Args:
        doc: The Document to write to.
        df: The full DataFrame.
        cols: Resolved column configuration.

    Returns:
        Tuple of (groups_written, sections_written).
    """
    grouped = df.groupby(cols.group_column, sort=False, dropna=False)
    section_counter = 0
    group_count = 0

    for group_value, group_df in grouped:
        group_title = str(group_value).strip() or "UNGROUPED"
        doc.add_heading(f"GROUP: {group_title}", level=1)
        group_count += 1

        for row_dict in group_df.to_dict("records"):
            section_counter += 1
            _write_row_section(doc, row_dict, section_counter, cols)

        doc.add_paragraph("--- END_GROUP ---")

    logger.info(
        "Content generation complete",
        extra={"groups": group_count, "sections": section_counter},
    )
    return group_count, section_counter

# ---------------------------------------------------------------------------
# File Saving Layer
# ---------------------------------------------------------------------------

def _save_document(doc: Document, word_path: Path) -> None:
    """Save the document to disk, creating parent directories if needed.

    Args:
        doc: The completed Document.
        word_path: Target file path.

    Raises:
        ExcelToWordError: If the file cannot be saved.
    """
    try:
        word_path.parent.mkdir(parents=True, exist_ok=True)
        doc.save(str(word_path))
    except PermissionError:
        raise ExcelToWordError(
            f"Permission denied when saving to: '{word_path}'"
        )
    except OSError as exc:
        raise ExcelToWordError(
            f"Failed to save document: {type(exc).__name__}: {exc}"
        ) from exc

    file_size_kb = word_path.stat().st_size / 1024
    logger.info(
        "Document saved successfully",
        extra={"path": str(word_path), "size_kb": round(file_size_kb, 1)},
    )

# ---------------------------------------------------------------------------
# Public API — Orchestrator
# ---------------------------------------------------------------------------

def excel_to_ai_word(
    excel_path: str | Path,
    word_path: str | Path,
    sheet_name: Union[int, str] = 0,
    group_column: str | None = None,
    section_title_column: str | None = None,
    content_column: str | None = None,
) -> ConversionResult:
    """Convert an Excel file to an AI-optimized Word document.

    Reads the specified Excel sheet, groups rows by a configurable column,
    and writes each row as a structured SECTION with CONTENT and METADATA
    markers that AI agents can parse deterministically.

    Args:
        excel_path: Path to the source Excel file (.xlsx, .xls, .xlsm, .xlsb).
        word_path: Path where the output Word document will be saved.
        sheet_name: Sheet name (str) or zero-based index (int). Default: 0.
        group_column: Column to group rows by. Default: first column.
        section_title_column: Column for section headings. Default: second column.
        content_column: Column for main content. Default: third column.

    Returns:
        ConversionResult with metadata about the conversion.

    Raises:
        ExcelToWordError: If validation fails, the file cannot be read,
            a specified column is missing, or the file cannot be saved.

    Example:
        >>> result = excel_to_ai_word(
        ...     excel_path="/path/to/project/data.xlsx",
        ...     word_path="/path/to/project/output.docx",
        ...     group_column="Category",
        ... )
        >>> print(f"Wrote {result.sections_written} sections in {result.groups_written} groups")
    """
    excel_path = Path(excel_path)
    word_path = Path(word_path)

    logger.info("Starting Excel-to-Word conversion", extra={"source": str(excel_path)})

    # 1. Validate inputs
    _validate_inputs(excel_path, word_path, sheet_name)

    # 2. Load data
    df = _load_dataframe(excel_path, sheet_name)

    # 3. Resolve columns
    cols = _resolve_columns(df, group_column, section_title_column, content_column)

    # 4. Create document
    doc = _create_document()

    # 5. Generate content
    groups_written, sections_written = _write_groups(doc, df, cols)

    # 6. Save
    _save_document(doc, word_path)

    result = ConversionResult(
        output_path=word_path,
        groups_written=groups_written,
        sections_written=sections_written,
        rows_processed=len(df),
        columns_used=list(df.columns),
    )

    logger.info("Conversion complete", extra={"result": str(result)})
    return result

# ---------------------------------------------------------------------------
# Entry Point (safe for public sharing)
# ---------------------------------------------------------------------------

if __name__ == "__main__":
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s | %(levelname)-8s | %(name)s | %(message)s",
    )

    # Replace these placeholders with your actual file paths before running.
    # Never commit real paths, usernames, or project names to public repos.
    result = excel_to_ai_word(
        excel_path="/path/to/project/input.xlsx",
        word_path="/path/to/project/output.docx",
        sheet_name=0,
        group_column="Category",
    )
    print(f"Done: {result.sections_written} sections across {result.groups_written} groups")