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, 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 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) def center_image_in_cell(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)) # 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): """ 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 = "/app/report/checkbox_checked.jpg" # Path to the checked checkbox image unchecked_image_path = "/app/report/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 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 # Desired height (e.g., 40), calculate the new width to maintain aspect ratio desired_height = 40 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 # Insert the resized image into the Excel sheet img = Image(image_path) img.width, img.height = new_width, desired_height # Set the dimensions # sheet.add_image(img, cell.coordinate) center_image_in_cell(sheet, img, cell.coordinate, ) cell.value = None # Clear placeholder elif value is True: img = Image(checked_image_path) img.width = img.height = 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(sheet,img, cell.coordinate, ) cell.value = None # Remove the placeholder text elif value is False: img = Image(unchecked_image_path) img.width = img.height = 10 # sheet.add_image(img, cell.coordinate) # set_image_with_offset(sheet, img, cell.coordinate, offset_x=100) center_image_in_cell(sheet, img, cell.coordinate, ) 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_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}")