image_to_excel_and_pdf_report_4.py

"""
image_to_excel_and_pdf_report_4.py

Creates an Excel report from images and optionally exports it to PDF.

Features:
- Select image folder
- One sheet or separate sheets
- Optional image labels
- mindstorm.gr header
- Developed by Tryfon Papadopoulos
- PDF page numbering
- Optional maximum image dimensions
- Keeps smaller images original size
- If image + details are too tall for one PDF page, image is reduced automatically
"""

from pathlib import Path
from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage
from openpyxl.styles import Font
import tkinter as tk
from tkinter import filedialog, messagebox, simpledialog

IMAGE_EXTENSIONS = {".png", ".jpg", ".jpeg", ".webp", ".bmp"}

#MAX_WIDTH = 800
#MAX_HEIGHT = 600
MAX_WIDTH = 600
MAX_HEIGHT = 450
DEFAULT_ROW_SPACING = 3

APPROX_ROW_HEIGHT_PIXELS = 20
# MAX_IMAGE_BLOCK_ROWS = 42  # image + label must fit inside one PDF page
# MAX_IMAGE_BLOCK_ROWS = 34  # image + label must fit inside one PDF page
MAX_IMAGE_BLOCK_ROWS = 32  # image + label must fit inside one PDF page


def resize_image(img, max_width=MAX_WIDTH, max_height=MAX_HEIGHT):
    if img.width <= max_width and img.height <= max_height:
        return img

    ratio = min(max_width / img.width, max_height / img.height)
    img.width = int(img.width * ratio)
    img.height = int(img.height * ratio)
    return img


def shrink_to_fit_page_old(img, add_labels):
    label_rows = 3 if add_labels else 0
    max_image_rows = MAX_IMAGE_BLOCK_ROWS - label_rows
    max_image_height = max_image_rows * APPROX_ROW_HEIGHT_PIXELS

    if img.height > max_image_height:
        ratio = max_image_height / img.height
        img.width = int(img.width * ratio)
        img.height = int(img.height * ratio)

    return img


def shrink_to_fit_page(img, add_labels):
    label_rows = 5 if add_labels else 1
    safety_rows = 4

    max_image_rows = MAX_IMAGE_BLOCK_ROWS - label_rows - safety_rows
    max_image_height = max_image_rows * APPROX_ROW_HEIGHT_PIXELS

    if img.height > max_image_height:
        ratio = max_image_height / img.height
        img.width = int(img.width * ratio)
        img.height = int(img.height * ratio)

    return img


def select_folder():
    root = tk.Tk()
    root.withdraw()
    return filedialog.askdirectory(title="Select folder with images")


def ask_yes_no(title, message):
    root = tk.Tk()
    root.withdraw()
    return messagebox.askyesno(title, message)


def ask_spacing():
    root = tk.Tk()
    root.withdraw()
    value = simpledialog.askinteger(
        "Spacing",
        "How many empty rows do you want between images?",
        initialvalue=DEFAULT_ROW_SPACING,
        minvalue=0,
        maxvalue=50
    )
    return DEFAULT_ROW_SPACING if value is None else value


def ask_image_dimensions():
    change_dimensions = ask_yes_no(
        "Image Dimensions",
        "Do you want to set maximum image dimensions?\n\n"
        "Yes = choose maximum width and height\n"
        #"No = use default 800 x 600"
        "No = use default 600 x 450"
    )

    if not change_dimensions:
        return MAX_WIDTH, MAX_HEIGHT

    max_width = simpledialog.askinteger(
        "Maximum Width",
        "Enter maximum image width in pixels:",
        initialvalue=MAX_WIDTH,
        minvalue=50,
        maxvalue=5000
    )

    max_height = simpledialog.askinteger(
        "Maximum Height",
        "Enter maximum image height in pixels:",
        initialvalue=MAX_HEIGHT,
        minvalue=50,
        maxvalue=5000
    )

    return max_width or MAX_WIDTH, max_height or MAX_HEIGHT


def clean_sheet_name(name, index):
    invalid_chars = ["\\", "/", "*", "[", "]", ":", "?"]
    for char in invalid_chars:
        name = name.replace(char, "_")

    name = name.strip()[:25]
    return name or f"Image_{index}"


def add_report_header(ws):
    ws["A1"] = "mindstorm.gr"
    ws["A1"].font = Font(bold=True, size=16)

    ws["A2"] = "Developed by Tryfon Papadopoulos"
    ws["A2"].font = Font(italic=True, size=11, color="666666")


def add_label_text(ws, row, image_path, image_number):
    title_cell = ws[f"A{row}"]
    path_cell = ws[f"A{row + 1}"]

    title_cell.value = f"Image {image_number}: {image_path.name}"
    title_cell.font = Font(bold=True, size=10)

    path_cell.value = str(image_path)
    path_cell.hyperlink = image_path.as_uri()
    path_cell.font = Font(size=9, italic=True, color="666666", underline="single")


def insert_images_single_sheet(ws, image_paths, add_labels, row_spacing, max_width, max_height):
    add_report_header(ws)
    current_row = 4

    for image_number, image_path in enumerate(image_paths, start=1):
        img = XLImage(str(image_path))

        resize_image(img, max_width, max_height)
        shrink_to_fit_page(img, add_labels)

        ws.add_image(img, f"A{current_row}")

        rows_used = int(img.height / 20) + 1

        text_rows = 0
        if add_labels:
            label_row = current_row + rows_used + 1
            add_label_text(ws, label_row, image_path, image_number)
            text_rows = 3

        current_row += rows_used + text_rows + row_spacing


def insert_images_multiple_sheets(wb, image_paths, add_labels, max_width, max_height):
    for image_number, image_path in enumerate(image_paths, start=1):
        ws = wb.create_sheet(title=clean_sheet_name(image_path.stem, image_number))

        add_report_header(ws)

        img = XLImage(str(image_path))

        resize_image(img, max_width, max_height)
        shrink_to_fit_page(img, add_labels)

        ws.add_image(img, "A4")

        if add_labels:
            rows_used = int(img.height / 20) + 1
            label_row = 4 + rows_used + 2
            add_label_text(ws, label_row, image_path, image_number)

        ws.sheet_view.showGridLines = False
        ws.column_dimensions["A"].width = 120


def create_excel_from_images(
    input_folder,
    output_file,
    single_sheet,
    add_labels,
    row_spacing,
    max_width,
    max_height
):
    image_paths = sorted(
        [
            p for p in Path(input_folder).iterdir()
            if p.suffix.lower() in IMAGE_EXTENSIONS
        ],
        key=lambda p: p.name.lower()
    )

    if not image_paths:
        messagebox.showinfo("No Images", "No supported image files were found.")
        return False

    wb = Workbook()

    if single_sheet:
        ws = wb.active
        ws.title = "All Images"
        ws.sheet_view.showGridLines = False
        ws.column_dimensions["A"].width = 120

        insert_images_single_sheet(
            ws,
            image_paths,
            add_labels,
            row_spacing,
            max_width,
            max_height
        )
    else:
        wb.remove(wb.active)

        insert_images_multiple_sheets(
            wb,
            image_paths,
            add_labels,
            max_width,
            max_height
        )

    wb.save(output_file)
    return True


def export_excel_to_pdf(excel_file, pdf_file):
    try:
        import win32com.client
    except ImportError:
        messagebox.showerror(
            "Missing Package",
            "PDF export requires pywin32.\n\nInstall it with:\npip install pywin32"
        )
        return False

    try:
        excel = win32com.client.Dispatch("Excel.Application")
        excel.Visible = False

        workbook = excel.Workbooks.Open(str(excel_file))

        for sheet in workbook.Worksheets:
            sheet.PageSetup.CenterFooter = "Page &P of &N"

        workbook.ExportAsFixedFormat(0, str(pdf_file))
        workbook.Close(False)
        excel.Quit()

        return True

    except Exception as e:
        messagebox.showerror(
            "PDF Export Error",
            f"Could not export PDF.\n\nDetails:\n{e}"
        )
        return False


if __name__ == "__main__":
    folder = select_folder()

    if not folder:
        print("No folder selected.")
        raise SystemExit

    single_sheet = ask_yes_no(
        "Layout Choice",
        "Do you want ALL images in ONE sheet, stacked vertically?\n\n"
        "Yes = One sheet\nNo = Separate sheets"
    )

    add_labels = ask_yes_no(
        "Add Labels",
        "Do you want to add image numbering, filename, and clickable full path under each image?"
    )

    max_width, max_height = ask_image_dimensions()

    row_spacing = DEFAULT_ROW_SPACING
    if single_sheet:
        row_spacing = ask_spacing()

    export_pdf = ask_yes_no(
        "Export PDF",
        "Do you also want to export the Excel report as a PDF?"
    )

    output_excel = Path(folder) / "image_report.xlsx"
    output_pdf = Path(folder) / "image_report.pdf"

    created = create_excel_from_images(
        input_folder=folder,
        output_file=output_excel,
        single_sheet=single_sheet,
        add_labels=add_labels,
        row_spacing=row_spacing,
        max_width=max_width,
        max_height=max_height
    )

    if created and export_pdf:
        pdf_created = export_excel_to_pdf(output_excel, output_pdf)

        if pdf_created:
            messagebox.showinfo(
                "Done",
                f"Files created:\n\n{output_excel}\n{output_pdf}"
            )
        else:
            messagebox.showinfo(
                "Done",
                f"Excel file created:\n\n{output_excel}\n\nPDF was not created."
            )

    elif created:
        messagebox.showinfo(
            "Done",
            f"Excel file created:\n\n{output_excel}"
        )