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", "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_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}")