Tutorial on inserting pictures into specific cells in Excel using openpyxl

This article details how to insert pictures into specific cells of an Excel worksheet using Python's `openpyxl` library. The tutorial covers the key steps of loading an image, anchoring it to a specific cell, and automatically adjusting the row height and column width of the cell based on the image size. Through these methods, you can achieve the effect of the picture being perfectly visually adapted and "embedded" in the specified cell, instead of just being a floating object.

In data visualization and report generation, it is sometimes necessary to display pictures directly in Excel cells to enhance the readability and intuitiveness of the data. However, many users encounter a common problem when using the openpyxl library: images are often added to the worksheet as floating objects, rather than "embedded" into specific cells. This tutorial will guide you in detail on how to use the functions of openpyxl to accurately place the image into the specified cell and adjust the cell size to fit the image.

Core steps: Insert pictures into specified cells

The openpyxl library allows you to add images to a worksheet and control their position by specifying anchor points. The key is to use the worksheet.add_image(image_object, cell_reference) method, where the cell_reference parameter is the cell you want the upper left corner of the image to be aligned to.

First, you need to import the necessary modules, create a workbook and worksheet, and then load your images.

 from openpyxl import Workbook
from openpyxl.drawing.image import Image

# Create a new workbook and active worksheet wb = Workbook()
ws = wb.active

# Specify the path to the image file # Please replace "path/to/your/image.jpg" with your actual image path img_path = "path/to/your/image.jpg" 

#Load image img = Image(img_path)

# Add the image to the worksheet and anchor it to the 'A1' cell # This means that the upper left corner of the image will be aligned with the upper left corner of the 'A1' cell ws.add_image(img, 'A1')

Key optimization: adjust cells according to image size

Simply anchoring the image to the cell is not enough, the image itself may be larger or smaller than the cell, resulting in poor display. To make the image appear to be "embedded" in the cell, we need to adjust the row height and column width of the target cell (and adjacent cells) according to the actual size of the image.

The image size in openpyxl is in EMUs (English Metric Units), while the column width and row height in Excel are in character width and points (points) respectively. Therefore, an approximate conversion factor is needed to map image dimensions to cell dimensions. It has been verified that dividing the picture width by 6 can roughly get the appropriate column width, and dividing the picture height by 18 can roughly get the appropriate row height.

 # ... (continue from above code)

# Get the width and height of the image object (in EMUs)
# The img.width and img.height properties provide the original dimensions of the image # Note: These values ​​are internal representations and need to be converted to be used with Excel's column width and row height img_width_emu = img.width
img_height_emu = img.height

# Adjust the width of column 'A' to fit the width of the picture # Experience value: Divide the width of the picture EMUs by 6, you can approximate the Excel column width ws.column_dimensions['A'].width = img_width_emu / 6 

# Adjust the height of row 1 to adapt to the height of the picture # Experience value: Divide the height of the picture EMUs by 18, you can approximately get the Excel row height ws.row_dimensions[1].height = img_height_emu / 18 

# Save the workbook wb.save("output_with_image_in_cell.xlsx")

Complete sample code

Here is the complete sample code to insert a picture into a specified cell and adjust the cell size:

 from openpyxl import Workbook
from openpyxl.drawing.image import Image
import os # Used to check whether the file exists, optional def insert_image_into_excel_cell(image_path, cell_reference, output_filename="output_with_image.xlsx"):
    """
    Insert the picture into the specified cell of the Excel worksheet, and adjust the cell size to fit the picture.

    Args:
        image_path (str): The full path of the image file.
        cell_reference (str): Reference of the target cell, such as 'A1'.
        output_filename (str): The name of the generated Excel file.
    """
    if not os.path.exists(image_path):
        print(f"Error: Image file does not exist at '{image_path}'")
        return

    wb = Workbook()
    ws = wb.active

    #Load image img = Image(image_path)

    #Add the image to the worksheet and anchor it to the specified cell ws.add_image(img, cell_reference)

    # Parse the cell reference to get the column letter and row number # For example, for 'A1', column_letter='A', row_number=1
    from openpyxl.utils import get_column_letter, column_index_from_string
    column_letter = ''.join(filter(str.isalpha, cell_reference))
    row_number = int(''.join(filter(str.isdigit, cell_reference)))

    # Adjust the column width and row height to fit the picture # The picture size of openpyxl is EMUs, and the column width and row height of Excel have different units # The divisor here is an empirical value, which may need to be fine-tuned according to the actual situation ws.column_dimensions[column_letter].width = img.width / 6 
    ws.row_dimensions[row_number].height = img.height / 18 

    # Save the workbook wb.save(output_filename)
    print(f"The picture has been successfully inserted into the '{cell_reference}' cell of '{output_filename}'.")

# --- Usage example ---
# Make sure to replace with your actual image path my_image_path = "example_image.jpg" # Assume you have an image named example_image.jpg in the same directory # Create an example image file if it does not exist
# This is a simple PIL image creation for testing try:
    from PIL import Image as PILImage, ImageDraw
    if not os.path.exists(my_image_path):
        img_pil = PILImage.new('RGB', (200, 100), color = 'red')
        d = ImageDraw.Draw(img_pil)
        d.text((10,10), "Test Image", fill=(255,255,0))
        img_pil.save(my_image_path)
        print(f"Sample image created: {my_image_path}")
except ImportError:
    print("The PIL (Pillow) library is not installed and cannot create the example image. Please make sure you have the 'example_image.jpg' file.")
    print("You can use 'pip install Pillow' to install the Pillow library.")


# Call the function to insert the image into the 'B2' cell insert_image_into_excel_cell(my_image_path, 'B2', "report_with_logo.xlsx")

Things to note and best practices

1. Pictures are not really "embedded" : Please note that even with the above adjustments, pictures inserted by openpyxl are still technically floating objects, but their position and size are precisely controlled to make them appear "embedded" in the cell. This means that if you drag or resize a cell, the picture may not automatically move or scale with it.
2. Path issues : Make sure the image_path is correct and that the Python script has permission to access the path. It is recommended to use an absolute path or ensure that the image file and script are in the same directory.
3. Scaling factors : width / 6 and height / 18 are empirical values. Different image resolutions, Excel versions or display settings may result in a slightly imperfect fit. If you find that the image doesn't completely fill the cell or overflows, you may need to fine-tune these divisors.
4. Performance considerations : Inserting large numbers of images may significantly increase Excel file size and processing time. If you need to process a large number of images, consider optimizing the image size or using a more advanced image processing strategy.
5. Multiple images : If you need to insert multiple images in different cells, just repeat the img = Image(img_path) and ws.add_image(img, cell_reference) steps and adjust the size of its target cell for each image.
6. Error handling : In actual applications, you should add error handling mechanisms such as file non-existence and image corruption to improve the robustness of the code.

Summarize

Through the openpyxl library, we can effectively insert pictures into specified cells of an Excel worksheet, and adjust the row height and column width of the cells to perfectly fit the pictures and cells. Although the picture is still technically a floating object, this method provides the powerful ability to visually "embed" the picture into the cell, greatly improving the flexibility and professionalism of using Python to generate Excel reports. Understanding and using the ws.add_image() method and cell resizing techniques will be an important tool in automating Excel tasks.

The above is the detailed content of Tutorial on inserting pictures into specific cells in Excel using openpyxl. For more information, please follow other related articles on the PHP Chinese website!