coi/app/report/gen_report.py

from openpyxl import load_workbook, Workbook
from datetime import datetime
from openpyxl.drawing.image import Image
import re

from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU, pixels_to_points
from openpyxl.utils import get_column_letter, column_index_from_string
from django.db import models
import os
from django.db.models.fields.files import ImageFieldFile
from pprint import pprint
from PIL import Image as PILImage
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor,  OneCellAnchor
from django.conf import settings

def set_image_with_offset_old(sheet, img, cell_coordinate, offset_x=0, offset_y=0):
    """
    Add an image to the sheet with an offset relative to the top-left corner of a cell.

    :param sheet: The worksheet
    :param img: The openpyxl Image object
    :param cell_coordinate: Cell to place the image (e.g., "B2")
    :param offset_x: Horizontal offset in pixels
    :param offset_y: Vertical offset in pixels
    """
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))  # Extract column letter
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))  # Extract row number

    # Get column width and row height in pixels
    col_width = sheet.column_dimensions[get_column_letter(column_index_from_string(col_letter))].width or 10
    row_height = sheet.row_dimensions[row_number].height or 15

    # Approximate conversion of Excel units to pixels
    col_pixels = col_width * 7.5  # Excel's ~7.5 pixels per width unit
    row_pixels = row_height * 0.75  # Approximation for row height in pixels

    # Calculate absolute positions based on offsets
    anchor_x = col_pixels + offset_x
    anchor_y = row_pixels + offset_y

    # Set the anchor for the image
    img.anchor = cell_coordinate
    img.anchor.dx = int(anchor_x * 9525)  # Convert to EMUs (Excel Measurement Units)
    img.anchor.dy = int(anchor_y * 9525)  # Convert to EMUs (Excel Measurement Units)

    sheet.add_image(img)


def set_image_with_offset(sheet, img, cell_coordinate, offset_x=0, offset_y=0):
    """
    Add an image to the sheet with an offset relative to the top-left corner of a cell.

    :param sheet: The worksheet
    :param img: The openpyxl Image object
    :param cell_coordinate: Cell to place the image (e.g., "B2")
    :param offset_x: Horizontal offset in pixels
    :param offset_y: Vertical offset in pixels
    """
    # Extract the column and row from the cell coordinate
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))  # Extract column letter
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))  # Extract row number

    # Get the zero-based indices for the cell
    col_idx = column_index_from_string(col_letter) - 1
    row_idx = row_number - 1

    # Approximate column width and row height to pixels
    col_width = sheet.column_dimensions[col_letter].width or 10  # Default column width
    row_height = sheet.row_dimensions[row_number].height or 15  # Default row height

    # Convert column width and row height to pixels
    col_pixels = col_width * 7.5  # Approximation: ~7.5 pixels per width unit
    row_pixels = row_height * 0.75  # Approximation: ~0.75 pixels per height unit

    # Calculate the position in pixels for the top-left corner of the cell
    cell_x = col_idx * col_pixels
    cell_y = row_idx * row_pixels

    # Apply the offsets
    final_x = cell_x + offset_x
    final_y = cell_y + offset_y

    # Convert to EMUs
    pos = XDRPoint2D(pixels_to_EMU(final_x), pixels_to_EMU(final_y))
    size = XDRPositiveSize2D(pixels_to_EMU(img.width), pixels_to_EMU(img.height))

    # Set the image's anchor with the position and size
    img.anchor = AbsoluteAnchor(pos=pos, ext=size)

    # Add the image to the worksheet
    sheet.add_image(img)

from openpyxl.drawing.xdr import XDRPositiveSize2D

def center_image_in_cell(sheet, img, cell_coordinate):
    """
    Center an image inside a specified cell using OneCellAnchor.
    """

    # Extract column and row (e.g. "C3")
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))
    col_idx = column_index_from_string(col_letter) - 1  # Zero-based index

    # Get cell size (Excel default values if not set)
    col_width = sheet.column_dimensions[col_letter].width or 8.43
    row_height = sheet.row_dimensions[row_number].height or 15

    # Convert Excel column/row units → pixels
    col_pixels = col_width * 7.5
    row_pixels = row_height * 0.75

    # Image size (already in pixels)
    img_width, img_height = img.width, img.height

    # Calculate centering offset inside the cell
    offset_x_px = max((col_pixels - img_width) / 2, 0)
    offset_y_px = max((row_pixels - img_height) / 2, 0)

    offset_x_px += 40
    offset_y_px -= 50

    # Convert pixel offset to EMU
    offset_x = pixels_to_EMU(offset_x_px)
    offset_y = pixels_to_EMU(offset_y_px)

    # Create the `_from` anchor marker (top-left corner)
    _from = AnchorMarker(
        col=col_idx,
        row=row_number - 1,
        colOff=offset_x,
        rowOff=offset_y
    )

    # Create XDRPositiveSize2D for ext (width & height in EMU)
    ext = XDRPositiveSize2D(
        cx=pixels_to_EMU(img_width),
        cy=pixels_to_EMU(img_height)
    )

    # Final anchor using OneCellAnchor
    img.anchor = OneCellAnchor(
        _from=_from,
        ext=ext
    )

    sheet.add_image(img)

def center_image_in_cell_old(sheet, img, cell_coordinate):
    """
    Center an image inside a specified cell.

    :param sheet: The worksheet
    :param img: The openpyxl Image object
    :param cell_coordinate: The cell to center the image in (e.g., "C3")
    """
    # Extract column and row from the cell coordinate
    col_letter = ''.join(filter(str.isalpha, cell_coordinate))  # Extract column letter
    row_number = int(''.join(filter(str.isdigit, cell_coordinate)))  # Extract row number
    col_idx = column_index_from_string(col_letter) - 1  # Convert to zero-based column index

    # Get cell dimensions
    col_width = sheet.column_dimensions[col_letter].width or 10  # Default width if not set
    row_height = sheet.row_dimensions[row_number].height or 15  # Default height if not set

    # Convert dimensions to pixels (approximation)
    col_pixels = col_width * 7.5  # 1 Excel column width unit ≈ 7.5 pixels
    row_pixels = row_height * 0.75  # 1 Excel row height unit ≈ 0.75 pixels

    # Get image dimensions
    img_width, img_height = img.width, img.height

    # Calculate offsets to center the image
    offset_x = int((col_pixels - img_width) / 2 * pixels_to_EMU(1)) + 100 # Center horizontally
    offset_y = int((row_pixels - img_height) / 2 * pixels_to_EMU(1))  # Center vertically

    # Define the anchor for the image
    _from = AnchorMarker(col=col_idx, row=row_number - 1, colOff=offset_x, rowOff=offset_y)
    to = AnchorMarker(col=col_idx + 1, row=row_number, colOff=-offset_x, rowOff=-offset_y)

    # Use TwoCellAnchor for positioning
    img.anchor = TwoCellAnchor(editAs="oneCell", _from=_from, to=to)

    # Add the image to the sheet
    sheet.add_image(img)


def gen_xlsx(template_file, selected_sheets, prefix_filename, data):
    pprint(f"5.0 = {template_file}, {selected_sheets}, {prefix_filename}, {data}")
    """
    Generate an Excel file from a template, fill placeholders, and include only selected sheets.

    Args:
        template_file (str): Path to the Excel template file.
        selected_sheets (list): List of sheet names to include in the output file.
        prefix_filename (str): Prefix for the output filename.
        data (dict): Data dictionary with sheet-specific keys and fallback keys.

    Returns:
        str: Path of the generated Excel file.
    """
    checked_image_path = f"{settings.BASE_DIR}/report/checkbox_checked.jpg"  # Path to the checked checkbox image
    unchecked_image_path = f"{settings.BASE_DIR}/report/checkbox_unchecked.jpg"  # Path to the unchecked checkbox image

    # Load the template workbook
    print(f"before load workbook {template_file}")
    workbook = load_workbook(template_file)
    print("after load")
    # Remove sheets not in selected_sheets
    for sheet_name in workbook.sheetnames:
        if sheet_name not in selected_sheets:
            del workbook[sheet_name]

    # Process the selected sheets
    for sheet_name in selected_sheets:
        if sheet_name not in workbook.sheetnames:
            raise ValueError(f"Sheet '{sheet_name}' not found in the template.")

        sheet = workbook[sheet_name]

        # Replace placeholders with actual values
        # Handle hiding rows based on patterns in data
         
        for row in sheet.iter_rows():
            for cell in row:
                if cell.value and isinstance(cell.value, str) and cell.value.startswith("<") and cell.value.endswith(">"):
                    placeholder = cell.value.strip("<>")
                    
                    # Determine value priority: `sheet_name.key` > `key`
                    value = None
                    sheet_specific_key = f"{sheet_name}.{placeholder}"
                    pprint(f"5.01 = {sheet_specific_key}")
                    pprint(sheet_specific_key in data)
                    if sheet_specific_key in data:
                        value = data[sheet_specific_key]
                    elif placeholder in data:
                        value = data[placeholder]
                        
                    if value is not None:
                        if isinstance(value, ImageFieldFile):
                            pprint("ImageField")
                            image_path = value.path
                            if os.path.exists(image_path):
                                # img = Image(image_path)
                                # img.height = 40  # Adjust size as needed
                                pil_img = PILImage.open(image_path)
                                original_width, original_height = pil_img.size
                                pprint(f"1.0 = f{image_path}")
                                pprint(f"1.1 = {original_width}x{original_height}")

                                # Desired height (e.g., 40), calculate the new width to maintain aspect ratio
                                row_height = sheet.row_dimensions[cell.row].height
                                desired_height = int(row_height) 

                                fixed_pixel_height = int(desired_height * 1.33)  # Convert point → pixel
                                fixed_pixel_height = 100  # Convert point → pixel

                                aspect_ratio = original_width / original_height
                                new_width = int(fixed_pixel_height * aspect_ratio)

                                resized_img = pil_img.resize((new_width, fixed_pixel_height), PILImage.Resampling.LANCZOS)

# Generate new filename by adding '_resized' before extension
                                base, ext = os.path.splitext(image_path)
                                resized_path = f"{base}_resized{ext}"

                                resized_img.save(resized_path)
                                # aspect_ratio = original_width / original_height
                                # new_width = int(desired_height * aspect_ratio)
                                #
                                # # Resize the image using Pillow (optional, for saving memory during export)
                                # resized_img = pil_img.resize((new_width, desired_height), PILImage.Resampling.LANCZOS)
                                # resized_img.save(image_path)  # Save the resized image back to the same path
                                pprint(f"1.2 = {new_width}, {desired_height}")
                                # Insert the resized image into the Excel sheet
                                img = Image(resized_path)
                                img.width, img.height = new_width, fixed_pixel_height  # Set the dimensions
                                # sheet.add_image(img, cell.coordinate)
                                print(f"1.3 = {row_height}")
                                center_image_in_cell(sheet, img, cell.coordinate, )
                                cell.value = None  # Clear placeholder

                        elif value is True:
                            img = Image(checked_image_path)
                            img.width = pixels_to_points(20)
                            img.height = pixels_to_points(10)

                            # img.width = 15
                            # img.hegiht = 10
                            print(f"{cell.coordinate}")
                            # sheet.add_image(img, cell.coordinate)
                            # set_image_with_offset(sheet, img, cell.coordinate, offset_x=100)
                            center_image_in_cell_old(sheet,img, cell.coordinate, )
                            cell.value = None  # Remove the placeholder text
                        elif value is False:
                            img = Image(unchecked_image_path)
                            # img.width = 15
                            # img.height = 10
                            img.width = pixels_to_points(20)
                            img.height = pixels_to_points(10)
                            # sheet.add_image(img, cell.coordinate)
                            # set_image_with_offset(sheet, img, cell.coordinate, offset_x=100)
                            center_image_in_cell_old(sheet, img, cell.coordinate, )
                            cell.value = None  # Remove the placeholder text
                        else:
                            # Insert the text value directly
                            # cell.value = value
                            if isinstance(value, str) and "[" in value and "]" in value:
                                # Extract the part before "[i:j]"
                                value = value.split("[")[0]
                            # Insert the cleaned value into the cell
                            cell.value = value

        for key, value in data.items():
            pprint(f"7 = {key} => {value}")
            
            # if isinstance(value, str) and re.match(r"^\d+\[\d+:\d+\]$", value):
            if isinstance(value, str) and re.match(r"^[0-9\s]+\[\d+:\d+\]$", value):
                # Parse the prefix and row range
                prefix, row_range = value.split("[")
                row_start, row_end = map(int, row_range[:-1].split(":"))
                
                # Hide rows if the prefix matches the condition
                # if prefix == "0":  # Adjust the condition as needed
                pprint(f"5.1 = {sheet} {value} => {prefix} ({row_start}, {row_end})")
                
                if " " in prefix or prefix.strip() == "0":
                    if key.split('.')[0] == sheet.title:
                        pprint(f"HIDE !!! {key.split('.')[0]} {sheet.title} = {row_start}, {row_end}")
                        sheet.row_dimensions.group(row_start, row_end, hidden=True)
        

    # Generate the output filename with a timestamp
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_path = f"{prefix_filename}_{timestamp}.xlsx"
    workbook.save(output_path)

    return output_path

if __name__ == "__main__":
    # Example usage
    data = {
        "customer": "Tum Coder",
        "inspect_date": "2025-01-15",
        "lot_no": "12345",
        "staff_name":  "Tum 8888",
        "man_name":  "Tum 999",
        "size": "Large",
        "pcs": "10 pcs",
        "spec": "Spec-A",
        "hardness.d1_act": "10",
        "hardness.d2_act": "0[24:28]",  # Hide rows 24 to 28 if the prefix is "0"
        "hardness.acc": True,  # Hide rows 24 to 28 if the prefix is "0"
        "hardness.spe_acc": False,  # Hide rows 24 to 28 if the prefix is "0"
        "dimension_app.d1_act": "33",
        "dimension_app.d2_act": "0[26:32]",  # Hide rows 24 to 28 if the prefix is "0"
        "dimension_app.acc": True,  # Hide rows 24 to 28 if the prefix is "0"
        "dimension_app.spe_acc": True,  # Hide rows 24 to 28 if the prefix is "0"
    }




    output_file = gen_xlsx(
        template_file="./hardness.xlsx",
        selected_sheets=["hardness", "dimension_app"],  # Replace with your actual sheet names
        prefix_filename="./output/output",
        data=data
    )

    print(f"Generated file: {output_file}")