tests/test_excel_utils.py

from __future__ import annotations

from pathlib import Path

import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from PIL import Image as PILImage

from kneiff.utils.excel import (
    freeze_sheet_panes,
    insert_image,
    pixels_to_column_width,
    pixels_to_row_height,
    read_excel_dataframes,
    set_column_width,
    write_excel_dataframes,
)


def _touch(path: Path, *, size: tuple[int, int] = (24, 12)) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    PILImage.new("RGB", size, color="white").save(path, format="PNG")


def test_freeze_sheet_panes_sets_expected_coordinates() -> None:
    workbook = Workbook()
    worksheet = workbook.active

    freeze_sheet_panes(worksheet, frozen_rows=1, frozen_columns=0)
    assert worksheet.freeze_panes == "A2"

    freeze_sheet_panes(worksheet, frozen_rows=1, frozen_columns=1)
    assert worksheet.freeze_panes == "B2"

    freeze_sheet_panes(worksheet, frozen_rows=0, frozen_columns=0)
    assert worksheet.freeze_panes is None


def test_pixel_conversions_match_excel_layout_helpers() -> None:
    assert pixels_to_row_height(164) == 123
    assert pixels_to_column_width(168) == 23.29
    assert pixels_to_column_width(328) == 46.14


def test_write_excel_dataframes_runs_style_callback(tmp_path: Path) -> None:
    workbook_path = tmp_path / "styled.xlsx"
    seen_sheets: list[str] = []

    def style_sheet(
        sheet_name: str,
        worksheet: Worksheet,
        dataframe: pd.DataFrame,
    ) -> None:
        seen_sheets.append(sheet_name)
        assert list(dataframe.columns) == ["name"]
        freeze_sheet_panes(worksheet, frozen_rows=1, frozen_columns=1)
        set_column_width(worksheet, column_index=1, width=12)

    write_excel_dataframes(
        workbook_path,
        {"images": pd.DataFrame([{"name": "cover.jpg"}])},
        style_sheet=style_sheet,
    )

    assert seen_sheets == ["images"]
    workbook = load_workbook(workbook_path)
    try:
        worksheet = workbook["images"]
        assert worksheet.freeze_panes == "B2"
        assert worksheet.column_dimensions["A"].width == 12
    finally:
        workbook.close()

    dataframes = read_excel_dataframes(workbook_path)
    assert list(dataframes) == ["images"]
    assert dataframes["images"].to_dict("records") == [{"name": "cover.jpg"}]


def test_insert_image_returns_dimensions(tmp_path: Path) -> None:
    image_path = tmp_path / "image.png"
    _touch(image_path, size=(24, 12))
    workbook = Workbook()
    worksheet = workbook.active

    dimensions = insert_image(worksheet, image_path=image_path, cell="A2")

    assert dimensions.width == 24
    assert dimensions.height == 12
    assert len(worksheet._images) == 1