如何用openpyxl设置Excel图片‘随单元格移动和调整大小’属性？

I see you're trying to make images in your Excel sheet move and resize alongside cells (so they get filtered correctly). Let's adjust your insert_image function to add this behavior—here's how:

Step 1: Complete & Modify Your insert_image Function #

First, let's finish the function and add the critical line to set the "Move and size with cells" property. The key is setting the editAs attribute of the image's anchor to "twoCell" (this corresponds to the Excel setting you want).

import urllib.request
from PIL import Image as PILImage
from openpyxl.drawing.image import Image

def insert_image(ws, image_url, cell, width=101, height=129):
    image_path = "image.jpg"
    try:
        # Download the image from the URL
        urllib.request.urlretrieve(image_url, image_path)
        
        # Open the image with PIL to handle processing
        with PILImage.open(image_path) as img:
            # Create an openpyxl Image object for Excel
            excel_img = Image(image_path)
            
            # Apply your custom width and height
            excel_img.width = width
            excel_img.height = height
            
            # Add the image to the worksheet at the specified cell
            ws.add_image(excel_img, cell)
            
            # *THIS IS THE CRITICAL LINE*: Enable "Move and size with cells"
            excel_img.anchor.editAs = "twoCell"
            
    except Exception as e:
        print(f"Error inserting image: {e}")

Why This Works #

• By default, openpyxl sets images to editAs = "absolute", which means they stay fixed in place even if cells are filtered, resized, or moved.
• Setting editAs = "twoCell" tells Excel to link the image's position and dimensions directly to the cells it's anchored to. When you filter rows, the images will hide or show exactly like the cells they belong to.

How to Confirm It's Working #

After generating your Excel file:

• Right-click on an inserted image
• Select Size and Properties
• Navigate to the Properties tab
• You'll see Move and size with cells is selected automatically

This ensures your images stay in sync with cell actions like filtering, sorting, and resizing.

内容的提问来源于stack exchange，提问作者AlejandraPM