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.
    """
    # Rich openpyxl worksheets materialize every formatted cell, including
    # empty ones. Large exports can have millions of them and only ten formulas.
    # Keep the viewer's optional context bounded before allocating that tree.
    with zipfile.ZipFile(io.BytesIO(data)) as package:
        context_bytes = sum(
            part.file_size
            for part in package.infolist()
            if part.filename.startswith("xl/worksheets/")
            and part.filename.endswith(".xml")
        )
    bounded = context_bytes > MAX_CONTEXT_XML_BYTES
    workbook = load_workbook(
        io.BytesIO(data),
        read_only=bounded,
        data_only=False,
        keep_vba=filename.lower().endswith((".xlsm", ".xltm")),
    )
    warnings: list[str] = []
    if bounded:
        warnings.append(
            "Large workbook: sheet context uses bounded previews; comments, "
            "tables, merged ranges, frozen panes and hidden columns were not scanned"
        )
    sheets: list[dict[str, Any]] = []
    total_comments = 0
    try:
        for worksheet in workbook.worksheets:
            max_row = max(worksheet.max_row or preview_rows, 1)
            max_column = max(worksheet.max_column or preview_columns, 1)
            row_limit = min(max_row, preview_rows)
            column_limit = min(max_column, preview_columns)
            preview = [
                {
                    "row": row_number,
                    "values": [
                        _safe_value(cell.value, getattr(cell, "number_format", None))
                        for cell in row
                    ],
                }
                for row_number, row in enumerate(
                    worksheet.iter_rows(
                        min_row=1,
                        max_row=row_limit,
                        min_col=1,
                        max_col=column_limit,
                    ),
                    start=1,
                )
            ]
            comments, comments_truncated = (
                ([], False)
                if bounded
                else _extract_comments(worksheet, max_row, max_column)
            )
            total_comments += len(comments)
            sheet_warnings: list[str] = []
            if comments_truncated:
                warning_msg = (
                    f"Comments on '{worksheet.title}' were truncated for inspection"
                )
                warnings.append(warning_msg)
                sheet_warnings.append(warning_msg)
            sheets.append(
                {
                    "name": worksheet.title,
                    "visibility": worksheet.sheet_state,
                    "dimensions": {
                        "rows": worksheet.max_row,
                        "columns": worksheet.max_column,
                    },
                    "preview_range": f"A1:{num_to_col(column_limit)}{row_limit}",
                    "preview": preview,
                    "freeze_panes": str(worksheet.freeze_panes)
                    if not bounded and worksheet.freeze_panes
                    else None,
                    "merged_ranges": [
                        str(cell_range)
                        for cell_range in (
                            [] if bounded else list(worksheet.merged_cells.ranges)
                        )[:MAX_MERGED_RANGES]
                    ],
                    "hidden_columns": []
                    if bounded
                    else _hidden_columns(worksheet, column_limit),
                    "comments": comments,
                    "tables": [] if bounded else detect_tables(worksheet),
                    "warnings": sheet_warnings,
                }
            )
    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

static_tables_from_rows

static_tables_from_rows(
    rows: list[list[Any]],
    covered: list[tuple[int, int, int, int]],
) -> list[dict[str, Any]]

The heuristic itself, over a top-left window of cell values.

Split out of :func:_detect_static_tables so the analyzer can run it on a window read from the engine: openpyxl only reaches these values by parsing the entire workbook, which costs seconds per million cells for a window of at most STATIC_TABLE_SCAN_ROWS × STATIC_TABLE_SCAN_COLS.

Values are expected as openpyxl's data_only=False hands them over: a formula cell as its =… text, a blank cell as None.

Source code in src/linexcel/insights.py

def static_tables_from_rows(
    rows: list[list[Any]], covered: list[tuple[int, int, int, int]]
) -> list[dict[str, Any]]:
    """The heuristic itself, over a top-left window of cell values.

    Split out of :func:`_detect_static_tables` so the analyzer can run it on a
    window read from the engine: openpyxl only reaches these values by parsing
    the entire workbook, which costs seconds per million cells for a window of
    at most ``STATIC_TABLE_SCAN_ROWS`` × ``STATIC_TABLE_SCAN_COLS``.

    Values are expected as openpyxl's ``data_only=False`` hands them over: a
    formula cell as its ``=…`` text, a blank cell as ``None``.
    """
    found: list[dict[str, Any]] = []
    if len(rows) < 3 or max((len(r) for r in rows), default=0) < STATIC_TABLE_MIN_COLS:
        return found

    used_starts: set[int] = set()
    for r_idx in range(len(rows) - 1):
        row = rows[r_idx]
        r = r_idx + 1  # 1-indexed
        if r in used_starts:
            continue
        # Find the longest run of contiguous non-None string cells in this row.
        best_start, best_len = -1, 0
        run_start, run_len = -1, 0
        for c_idx, val in enumerate(row):
            if isinstance(val, str) and val.strip():
                if run_start < 0:
                    run_start = c_idx
                run_len += 1
            else:
                if run_start >= 0 and run_len > best_len:
                    best_start, best_len = run_start, run_len
                run_start, run_len = -1, 0
        if run_start >= 0 and run_len > best_len:
            best_start, best_len = run_start, run_len
        if best_len < STATIC_TABLE_MIN_COLS:
            continue
        c1 = best_start + 1  # 1-indexed
        c2 = best_start + best_len
        # The row below must have at least one non-None value in those columns.
        next_row = rows[r_idx + 1]
        if not any(next_row[c] is not None for c in range(best_start, c2)):
            continue
        # Extend data rows downward until a fully-blank row in those columns.
        last_data = r_idx + 1
        for dr in range(r_idx + 1, len(rows)):
            if any(rows[dr][c] is not None for c in range(best_start, c2)):
                last_data = dr
            else:
                break
        data_rows = last_data - r_idx
        if data_rows < STATIC_TABLE_MIN_DATA_ROWS:
            continue
        # Skip if this overlaps an existing dynamic table.
        r1, r2 = r, last_data + 1
        if any(
            not (r2 < cr1 or r1 > cr2 or c2 < cc1 or c1 > cc2)
            for cr1, cc1, cr2, cc2 in covered
        ):
            continue
        # ponytail: header cells may hold formulas (=...) or be blank — openpyxl
        # returns the formula text as a str, which would leak into table_column.
        # Fall back to the column letter so the lineage graph stays readable.
        headers: list[Any] = []
        for i, c in enumerate(range(best_start, c2)):
            val = row[c]
            if isinstance(val, str) and val.strip() and not val.startswith("="):
                headers.append(_safe_value(val))
            else:
                headers.append(f"Column {num_to_col(c1 + i)}")
        found.append(
            {
                "name": f"Table{r}C{c1}",
                "kind": "static",
                "ref": f"{num_to_col(c1)}{r}:{num_to_col(c2)}{r2}",
                "header_row": r,
                "first_row": r + 1,
                "last_row": r2,
                "first_col": c1,
                "last_col": c2,
                "headers": headers,
                "data_rows": data_rows,
            }
        )
        used_starts.add(r)
        covered.append((r, c1, r2, c2))
        if len(found) >= MAX_STATIC_TABLES_PER_SHEET:
            break
    return found

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)

empty_sheet_render_exemptions

empty_sheet_render_exemptions(
    data: bytes,
) -> dict[str, str]

Name only sheets proven empty enough to omit a rendered screenshot.

This is a conservative validation aid, not an estimate from dimensions or cached values. Chartsheets, styled cells, comments, drawings, relationships, unknown XML and oversized parts remain expected. Unsupported or malformed packages return no exemptions. No workbook cells are materialized.

Source code in src/linexcel/insights.py

def empty_sheet_render_exemptions(data: bytes) -> dict[str, str]:
    """Name only sheets proven empty enough to omit a rendered screenshot.

    This is a conservative validation aid, not an estimate from dimensions or
    cached values. Chartsheets, styled cells, comments, drawings, relationships,
    unknown XML and oversized parts remain expected. Unsupported or malformed
    packages return no exemptions. No workbook cells are materialized.
    """
    namespaces = {
        "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
        "http://purl.oclc.org/ooxml/spreadsheetml/main",
    }
    structural = {
        "worksheet",
        "sheetPr",
        "outlinePr",
        "pageSetUpPr",
        "dimension",
        "sheetViews",
        "sheetView",
        "selection",
        "pane",
        "sheetFormatPr",
        "sheetData",
        "pageMargins",
        "printOptions",
        "pageSetup",
        "headerFooter",
        "oddHeader",
        "oddFooter",
        "evenHeader",
        "evenFooter",
        "firstHeader",
        "firstFooter",
    }

    scanned_bytes = 0

    def xml(package: zipfile.ZipFile, name: str) -> ElementTree.Element:
        nonlocal scanned_bytes
        scanned_bytes += package.getinfo(name).file_size
        if scanned_bytes > MAX_CONTEXT_XML_BYTES:
            raise ValueError("Part too large to prove empty")
        raw = package.read(name)
        if b"<!DOCTYPE" in raw or b"<!ENTITY" in raw:
            raise ValueError("Unexpected XML declaration")
        return ElementTree.fromstring(raw)

    exemptions: dict[str, str] = {}
    try:
        with zipfile.ZipFile(io.BytesIO(data)) as package:
            # Duplicate members make even a valid relationship ambiguous.
            members = package.namelist()
            if len(members) != len(set(members)):
                return {}
            workbook = xml(package, "xl/workbook.xml")
            rels = xml(package, "xl/_rels/workbook.xml.rels")
            relationships = {rel.get("Id"): rel for rel in rels}
            if len(relationships) != len(rels):
                return {}
            for sheet in workbook.findall("{*}sheets/{*}sheet"):
                rid = next(
                    (v for k, v in sheet.attrib.items() if k.endswith("}id")), None
                )
                rel = relationships.get(rid)
                if rel is None or rel.get("TargetMode") == "External":
                    continue
                if not (rel.get("Type") or "").endswith("/worksheet"):
                    continue
                target = rel.get("Target", "")
                if not target or "\\" in target or ":" in target:
                    continue
                part = posixpath.normpath(posixpath.join("xl", target))
                if target.startswith("/"):
                    part = posixpath.normpath(target.lstrip("/"))
                if not part.startswith("xl/"):
                    continue
                try:
                    root = xml(package, part)
                    relation_part = posixpath.join(
                        posixpath.dirname(part),
                        "_rels",
                        posixpath.basename(part) + ".rels",
                    )
                    if relation_part in members:
                        sheet_rels = xml(package, relation_part)
                        if not sheet_rels.tag.endswith("}Relationships") or len(
                            sheet_rels
                        ):
                            continue
                    if any(
                        not node.tag.startswith("{")
                        or node.tag[1:].split("}", 1)[0] not in namespaces
                        or node.tag.rsplit("}", 1)[-1] not in structural
                        or (node.text or "").strip()
                        or (node.tail or "").strip()
                        for node in root.iter()
                    ):
                        continue
                    if root.tag.rsplit("}", 1)[-1] != "worksheet":
                        continue
                    print_options = root.find("{*}printOptions")
                    if print_options is not None and any(
                        print_options.get(option, "false") not in {"false", "0"}
                        for option in ("headings", "gridLines")
                    ):
                        continue
                except (KeyError, ValueError, ElementTree.ParseError):
                    continue
                name = sheet.get("name")
                if name:
                    exemptions[name] = (
                        "Worksheet XML contains only empty structural metadata; "
                        "no cells, formatting rows/columns or related content"
                    )
    except (
        KeyError,
        ValueError,
        ElementTree.ParseError,
        zipfile.BadZipFile,
        OSError,
        RuntimeError,
        NotImplementedError,
    ):
        return {}
    return exemptions