linexcel.insights

linexcel.insights

Workbook context extraction and optional screenshot rendering.

WorkbookRenderError

Bases: RuntimeError

Raised when the optional workbook screenshot renderer is unavailable.

Source code in src/linexcel/insights.py

class WorkbookRenderError(RuntimeError):
    """Raised when the optional workbook screenshot renderer is unavailable."""

extract_workbook_context

extract_workbook_context(data: bytes, filename: str = 'workbook.xlsx', *, preview_rows: int = PREVIEW_ROWS, preview_columns: int = PREVIEW_COLUMNS) -> dict[str, Any]

Extract bounded presentation context without launching Excel.

The preview preserves the first cells as they are, without guessing which row is a header. Comments and sheet layout markers complement formula lineage with the cues users usually see when opening a workbook.

Source code in src/linexcel/insights.py

def extract_workbook_context(
    data: bytes,
    filename: str = "workbook.xlsx",
    *,
    preview_rows: int = PREVIEW_ROWS,
    preview_columns: int = PREVIEW_COLUMNS,
) -> dict[str, Any]:
    """Extract bounded presentation context without launching Excel.

    The preview preserves the first cells as they are, without guessing which
    row is a header. Comments and sheet layout markers complement formula
    lineage with the cues users usually see when opening a workbook.
    """
    workbook = load_workbook(
        io.BytesIO(data),
        read_only=False,
        data_only=False,
        keep_vba=filename.lower().endswith((".xlsm", ".xltm")),
    )
    warnings: list[str] = []
    sheets: list[dict[str, Any]] = []
    total_comments = 0
    try:
        for worksheet in workbook.worksheets:
            max_row = max(worksheet.max_row or 1, 1)
            max_column = max(worksheet.max_column or 1, 1)
            row_limit = min(max_row, preview_rows)
            column_limit = min(max_column, preview_columns)
            preview = [
                {
                    "row": row[0].row,
                    "values": [
                        _safe_value(cell.value, getattr(cell, "number_format", None))
                        for cell in row
                    ],
                }
                for row in worksheet.iter_rows(
                    min_row=1,
                    max_row=row_limit,
                    min_col=1,
                    max_col=column_limit,
                )
            ]
            comments, comments_truncated = _extract_comments(
                worksheet, max_row, max_column
            )
            total_comments += len(comments)
            if comments_truncated:
                warnings.append(
                    f"Comments on '{worksheet.title}' were truncated for inspection"
                )
            sheets.append(
                {
                    "name": worksheet.title,
                    "visibility": worksheet.sheet_state,
                    "dimensions": {"rows": max_row, "columns": max_column},
                    "preview_range": f"A1:{num_to_col(column_limit)}{row_limit}",
                    "preview": preview,
                    "freeze_panes": str(worksheet.freeze_panes)
                    if worksheet.freeze_panes
                    else None,
                    "merged_ranges": [
                        str(cell_range)
                        for cell_range in list(worksheet.merged_cells.ranges)[
                            :MAX_MERGED_RANGES
                        ]
                    ],
                    "hidden_columns": _hidden_columns(worksheet, column_limit),
                    "comments": comments,
                    "tables": detect_tables(worksheet),
                }
            )
    finally:
        workbook.close()
    return {
        "filename": filename,
        "sheets": sheets,
        "stats": {"sheets": len(sheets), "comments": total_comments},
        "warnings": warnings,
    }

detect_tables

detect_tables(worksheet) -> list[dict[str, Any]]

Detect Excel tables (TableObjects) and static tables on a worksheet.

Returns one dict per table with name, kind ("dynamic" or "static"), ref, bounds (header_row, first_row, last_row, first_col, last_col), headers and data_rows.

Dynamic tables come from ws.tables (what Excel calls a Table / List Object). Static tables are ranges that look like a table — a header row of text above contiguous data — detected heuristically so a workbook with no formal tables still benefits from header/index enrichment.

Source code in src/linexcel/insights.py

def detect_tables(worksheet) -> list[dict[str, Any]]:
    """Detect Excel tables (TableObjects) and static tables on a worksheet.

    Returns one dict per table with ``name``, ``kind`` ("dynamic" or
    "static"), ``ref``, bounds (``header_row``, ``first_row``, ``last_row``,
    ``first_col``, ``last_col``), ``headers`` and ``data_rows``.

    Dynamic tables come from ``ws.tables`` (what Excel calls a *Table* / List
    Object). Static tables are ranges that *look* like a table — a header row
    of text above contiguous data — detected heuristically so a workbook with
    no formal tables still benefits from header/index enrichment.
    """
    tables: list[dict[str, Any]] = []
    covered: list[tuple[int, int, int, int]] = []  # (r1, c1, r2, c2) already taken

    # --- dynamic tables (TableObject) -------------------------------------
    try:
        table_items = list(worksheet.tables.items())
    except Exception:
        table_items = []
    for _name, _value in table_items[:MAX_TABLES_PER_SHEET]:
        try:
            tbl = worksheet.tables[_name]
        except Exception:
            continue
        ref = getattr(tbl, "ref", None)
        if not ref:
            continue
        try:
            min_col, min_row, max_col, max_row = range_boundaries(ref)
        except (ValueError, TypeError):
            continue
        header_row = min_row
        header_count = getattr(tbl, "headerRowCount", 1) or 1
        first_data_row = min_row + header_count
        headers = [
            _safe_value(worksheet.cell(row=min_row, column=c).value)
            for c in range(min_col, max_col + 1)
        ]
        tables.append(
            {
                "name": getattr(tbl, "displayName", _name) or _name,
                "kind": "dynamic",
                "ref": ref,
                "header_row": header_row,
                "first_row": first_data_row,
                "last_row": max_row,
                "first_col": min_col,
                "last_col": max_col,
                "headers": headers,
                "data_rows": max(0, max_row - first_data_row + 1),
            }
        )
        covered.append((min_row, min_col, max_row, max_col))

    # --- static tables (heuristic) ---------------------------------------
    tables.extend(_detect_static_tables(worksheet, covered))
    return tables

find_libreoffice

find_libreoffice() -> str | None

Locate the LibreOffice launcher, on PATH or in a standard install.

Windows and macOS installers do not put LibreOffice on PATH, so a PATH-only lookup reports the renderer as missing on machines where it is installed. Well-known install directories are therefore searched as well.

Source code in src/linexcel/insights.py

def find_libreoffice() -> str | None:
    """Locate the LibreOffice launcher, on ``PATH`` or in a standard install.

    Windows and macOS installers do not put LibreOffice on ``PATH``, so a
    ``PATH``-only lookup reports the renderer as missing on machines where it is
    installed. Well-known install directories are therefore searched as well.
    """
    for name in _LAUNCHER_NAMES:
        found = shutil.which(name)
        if found:
            return found
    for candidate in _launcher_install_paths():
        if candidate.is_file():
            return str(candidate)
    return None

find_pdftoppm

find_pdftoppm() -> str | None

Locate Poppler's pdftoppm, on PATH or in a standard install.

Source code in src/linexcel/insights.py

def find_pdftoppm() -> str | None:
    """Locate Poppler's ``pdftoppm``, on ``PATH`` or in a standard install."""
    found = shutil.which("pdftoppm")
    if found:
        return found
    for candidate in _pdftoppm_install_paths():
        if candidate.is_file():
            return str(candidate)
    return None

render_workbook_screenshots

render_workbook_screenshots(data: bytes, filename: str, output_dir: str | Path, *, dpi: int = 144, timeout: int = 180, per_sheet: bool = True) -> dict[str, list[Path]] | list[Path]

Render workbook sheets to PNG with LibreOffice and Poppler.

Works on Linux, macOS and Windows. LibreOffice runs headlessly; no desktop Excel process is needed. It exports the workbook to PDF, then pdftoppm creates one PNG per rendered page.

With per_sheet — the default — LibreOffice is asked to put each sheet on a single page, so the result is a {sheet name: [png]} mapping keyed by the workbook's own sheet names, and the report shows each image under the sheet it belongs to. Sheets are not split across pages, so a long one comes out as one tall image rather than as print pages nobody can map back.

Setting per_sheet=False returns the flat list[Path] of print pages instead, as the page setup of the workbook lays them out.

The mapping is only returned when LibreOffice produced exactly one page per sheet. When it did not — an older build ignoring the option, a page setup that overrides it — the flat page list is returned rather than a guessed mapping, because a screenshot filed under the wrong sheet is worse than one filed under none.

Source code in src/linexcel/insights.py

def render_workbook_screenshots(
    data: bytes,
    filename: str,
    output_dir: str | Path,
    *,
    dpi: int = 144,
    timeout: int = 180,
    per_sheet: bool = True,
) -> dict[str, list[Path]] | list[Path]:
    """Render workbook sheets to PNG with LibreOffice and Poppler.

    Works on Linux, macOS and Windows. LibreOffice runs headlessly; no desktop
    Excel process is needed. It exports the workbook to PDF, then ``pdftoppm``
    creates one PNG per rendered page.

    With ``per_sheet`` — the default — LibreOffice is asked to put each sheet on
    a single page, so the result is a ``{sheet name: [png]}`` mapping keyed by
    the workbook's own sheet names, and the report shows each image under the
    sheet it belongs to. Sheets are not split across pages, so a long one comes
    out as one tall image rather than as print pages nobody can map back.

    Setting ``per_sheet=False`` returns the flat ``list[Path]`` of print pages
    instead, as the page setup of the workbook lays them out.

    The mapping is only returned when LibreOffice produced exactly one page per
    sheet. When it did not — an older build ignoring the option, a page setup
    that overrides it — the flat page list is returned rather than a guessed
    mapping, because a screenshot filed under the wrong sheet is worse than one
    filed under none.
    """
    office = find_libreoffice()
    converter = find_pdftoppm()
    if not office or not converter:
        raise WorkbookRenderError(_missing_renderer_message(office, converter))
    if dpi <= 0:
        raise ValueError("dpi must be positive")
    if timeout <= 0:
        raise ValueError("timeout must be positive")

    target = Path(output_dir)
    target.mkdir(parents=True, exist_ok=True)
    suffix = Path(filename).suffix.lower()
    if suffix not in {".xls", ".xlsx", ".xlsm", ".xlsb", ".xltx", ".xltm"}:
        suffix = ".xlsx"
    stem = re.sub(r"[^A-Za-z0-9_.-]+", "-", Path(filename).stem).strip(".-")
    stem = stem or "workbook"
    sheet_names = _sheet_names(data) if per_sheet else []

    with tempfile.TemporaryDirectory(prefix="linexcel-render-") as temp_dir:
        temp = Path(temp_dir)
        input_path = temp / f"workbook{suffix}"
        pdf_dir = temp / "pdf"
        profile_dir = temp / "profile"
        input_path.write_bytes(data)
        pdf_dir.mkdir()
        try:
            subprocess.run(
                [
                    office,
                    # A throwaway profile: a LibreOffice already open on the
                    # desktop otherwise owns the default one, and the headless
                    # process exits 0 without converting anything.
                    f"-env:UserInstallation={profile_dir.as_uri()}",
                    "--headless",
                    "--norestore",
                    "--convert-to",
                    _PDF_FILTER_SINGLE_PAGE if per_sheet else "pdf",
                    "--outdir",
                    str(pdf_dir),
                    str(input_path),
                ],
                check=True,
                capture_output=True,
                text=True,
                timeout=timeout,
            )
        except subprocess.TimeoutExpired as exc:
            raise WorkbookRenderError(
                f"LibreOffice did not finish within {timeout} seconds"
            ) from exc
        except subprocess.CalledProcessError as exc:
            details = (exc.stderr or exc.stdout or "unknown error").strip()
            raise WorkbookRenderError(
                f"LibreOffice could not render the workbook: {details}"
            ) from exc

        pdfs = list(pdf_dir.glob("*.pdf"))
        if not pdfs:
            raise WorkbookRenderError("LibreOffice did not produce a PDF")
        # Rendered aside, then moved in: pdftoppm pads the page number to the
        # width of the page count, so a directory reused across two workbooks
        # holds both "-1.png" and "-01.png" and a glob over it would return the
        # previous run's pages alongside this one's.
        png_dir = temp / "png"
        png_dir.mkdir()
        try:
            subprocess.run(
                [converter, "-png", "-r", str(dpi), str(pdfs[0]), str(png_dir / stem)],
                check=True,
                capture_output=True,
                text=True,
                timeout=timeout,
            )
        except subprocess.TimeoutExpired as exc:
            raise WorkbookRenderError(
                f"PDF conversion did not finish within {timeout} seconds"
            ) from exc
        except subprocess.CalledProcessError as exc:
            details = (exc.stderr or exc.stdout or "unknown error").strip()
            raise WorkbookRenderError(
                f"pdftoppm could not create screenshots: {details}"
            ) from exc
        pages = sorted(png_dir.glob(f"{stem}-*.png"))
        if not pages:
            raise WorkbookRenderError("pdftoppm did not produce PNG screenshots")
        if per_sheet and sheet_names and len(pages) == len(sheet_names):
            return _place_by_sheet(pages, sheet_names, target, stem)
        return _place_pages(pages, target, stem)