Insert an Excel Table covering the contiguous data range in the worksheet

excel_table_inserter.py

#!/usr/bin/env python3
from __future__ import annotations

import argparse
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.worksheet.worksheet import Worksheet

# Centralized defaults to avoid magic values
DEFAULT_TABLE_STYLE = "TableStyleMedium9"
DEFAULT_FREEZE_PANES = "A2"


def build_argument_parser() -> argparse.ArgumentParser:
    """
    Builds and configures an argument parser for processing Excel tables.

    The function creates and returns a pre-configured argument parser object to handle
    command-line input for processing Excel files. The parser includes options to
    specify the source Excel file, process all worksheets or only the active worksheet,
    and overwrite the source file or create a new file.

    Returns:
        argparse.ArgumentParser: The configured argument parser instance.
    """
    parser = argparse.ArgumentParser(
        description="Insert Excel tables into worksheets and optionally clean borders."
    )
    parser.add_argument(
        "excel_path",
        type=Path,
        help="Path to the source .xlsx file."
    )
    parser.add_argument(
        "-a", "--all",
        dest="process_all",
        action="store_true",
        help="Process all worksheets (default: only the active sheet)."
    )
    parser.add_argument(
        "--inplace",
        action="store_true",
        help="Overwrite the source file instead of writing a new file."
    )
    return parser


def name_output_file(src_path: Path | str) -> str:
    """
    Generate a default output file name next to the source path.
    If src is 'book.xlsx', output becomes 'book_tables.xlsx'.
    """
    src = Path(src_path)
    suffix = src.suffix
    base = src.with_suffix("")
    return str(base.with_name(f"{base.name}_tables").with_suffix(suffix))


def remove_borders(ws: Worksheet) -> None:
    """
    Remove borders in the used range of a worksheet.
    Keeps other styling untouched.
    """
    min_row, min_col, max_row, max_col = _detect_data_range(ws)
    if max_row < min_row or max_col < min_col:
        return
    # Reset borders for used cells only
    for r in range(min_row, max_row + 1):
        for c in range(min_col, max_col + 1):
            cell = ws.cell(row=r, column=c)
            cell.border = None  # Removes borders


def _detect_data_range(ws: Worksheet) -> tuple[int, int, int, int]:
    """
    Detect a contiguous data range bounded by non-empty cells.
    Returns (min_row, min_col, max_row, max_col).
    Falls back to (1,1,1,1) if the sheet is empty.
    """
    max_row = 0
    max_col = 0
    min_row = None
    min_col = None

    for row in ws.iter_rows(values_only=True):
        pass  # prime the generator for performance reasoning

    for r in range(1, ws.max_row + 1):
        row_has_data = False
        for c in range(1, ws.max_column + 1):
            val = ws.cell(row=r, column=c).value
            if val is not None and str(val).strip() != "":
                row_has_data = True
                if min_col is None or c < min_col:
                    min_col = c
                if c > max_col:
                    max_col = c
        if row_has_data:
            if min_row is None:
                min_row = r
            max_row = r

    if min_row is None or min_col is None:
        return (1, 1, 0, 0)  # indicates empty range

    return (min_row, min_col, max_row, max_col)


def _table_reference_from_range(min_row: int, min_col: int, max_row: int, max_col: int) -> str:
    """
    Convert numeric bounds to A1-style Excel reference for a table.
    """
    start_col = get_column_letter(min_col)
    end_col = get_column_letter(max_col)
    return f"{start_col}{min_row}:{end_col}{max_row}"


def _build_table_style() -> TableStyleInfo:
    """
    Construct a default TableStyleInfo with banded rows and header row.
    """
    return TableStyleInfo(
        name=DEFAULT_TABLE_STYLE,
        showFirstColumn=False,
        showLastColumn=False,
        showRowStripes=True,
        showColumnStripes=False,
    )


def insert_table(ws: Worksheet) -> None:
    """
    Insert an Excel Table covering the contiguous data range in the worksheet.
    Assumes the first row is a header. Freezes the header row.
    """
    # Find the data range
    min_row, min_col, max_row, max_col = _detect_data_range(ws)
    if max_row < min_row or max_col < min_col:
        return  # nothing to do on an empty sheet

    # Create table range string
    ref = _table_reference_from_range(min_row, min_col, max_row, max_col)

    # Ensure a unique table name per sheet
    safe_title = "".join(ch if ch.isalnum() else "_" for ch in ws.title)
    table_name = f"Table_{safe_title}"

    # Create and add table
    table = Table(displayName=table_name, ref=ref)
    table.tableStyleInfo = _build_table_style()
    ws.add_table(table)

    # Freeze top row
    ws.freeze_panes = DEFAULT_FREEZE_PANES


def process_worksheets(wb: Workbook, process_all: bool = False) -> None:
    """
    Process either the active worksheet or all worksheets.
    For each sheet: remove borders then insert a table.
    """
    sheets: list[Worksheet] = list(wb.worksheets) if process_all else [wb.active]
    for ws in sheets:
        remove_borders(ws)
        insert_table(ws)


def main() -> None:
    parser = build_argument_parser()
    args = parser.parse_args()

    src: Path = args.excel_path
    if not src.exists() or src.suffix.lower() != ".xlsx":
        raise SystemExit(f"Input must be an existing .xlsx file: {src}")

    wb = load_workbook(src)
    process_worksheets(wb, process_all=args.process_all)

    out_path = str(src) if args.inplace else name_output_file(src)
    wb.save(out_path)
    print(f"Saved: {out_path}")


if __name__ == "__main__":
    main()