coi/test_xlsx2.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
from openpyxl.utils import get_column_letter

def center_image_in_cell(ws, cell_address, img_path):
    """
    Center an image in a specific cell.

    Args:
        ws: The worksheet object.
        cell_address (str): The cell address (e.g., "B2") where the image will be centered.
        img_path (str): Path to the image file.

    Returns:
        None
    """
    # Load the image
    img = Image(img_path)
    img.width = img.height = 20

    # Get the cell
    cell = ws[cell_address]

    # Approximate pixel dimensions of the cell
    col_letter = get_column_letter(cell.column)
    col_width = ws.column_dimensions[col_letter].width or 10  # Default width
    row_height = ws.row_dimensions[cell.row].height or 15  # Default height

    # Convert dimensions to pixels
    col_width_pixels = col_width * 7  # Approximation: 1 Excel unit = ~7 pixels
    row_height_pixels = row_height * 0.75  # Approximation: 1 Excel unit = ~0.75 pixels

    # Calculate the center position
    cell_left = pixels_to_EMU(cell.column - 1)  # Column start position in EMU
    cell_top = pixels_to_EMU((cell.row - 1) * row_height_pixels)  # Row start position in EMU
    x_center = cell_left + pixels_to_EMU((col_width_pixels - img.width) / 2)
    y_center = cell_top + pixels_to_EMU((row_height_pixels - img.height) / 2)

    # Set the image position and size
    position = XDRPoint2D(x_center, y_center)
    size = XDRPositiveSize2D(pixels_to_EMU(img.width), pixels_to_EMU(img.height))
    img.anchor = AbsoluteAnchor(pos=position, ext=size)

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


def gen_xlsx4(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 = "./checkbox_checked.jpg"  # Path to the checked checkbox image
    unchecked_image_path = "./checkbox_unchecked.jpg"  # Path to the unchecked checkbox image

    # Load the template workbook
    workbook = load_workbook(template_file)

    # 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}"
                    if sheet_specific_key in data:
                        value = data[sheet_specific_key]
                    elif placeholder in data:
                        value = data[placeholder]

                    if value is not None:
                        if value is True:
                            img = Image(checked_image_path)
                            img.width = img.height = 20
                            print(f"{cell.coordinate}")
                            sheet.add_image(img, cell.coordinate)
                            cell.value = None  # Remove the placeholder text
                        elif value is False:
                            img = Image(unchecked_image_path)
                            img.width = img.height = 20
                            sheet.add_image(img, cell.coordinate)
                            # center_image_in_cell(sheet, cell.coordinate, unchecked_image_path)
                            cell.value = None  # Remove the placeholder text
                        else:
                            # Insert the text value directly
                            cell.value = value

        for key, value in data.items():
            if isinstance(value, str) and re.match(r"^\d+\[\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
                    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",
        "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": False,  # Hide rows 24 to 28 if the prefix is "0"
    }




    output_file = gen_xlsx4(
        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}")