While standard libraries like openpyxl are excellent for handling tabular data, they often lack comprehensive features for graphic manipulation within spreadsheets. To efficiently handle image insertion and removal without relying on the Excel COM interface or installed Excel instances, libraries like Free Spire.XLS for Python offer a robust solution. This library supports both .xls and .xlsx formats, allowing developers to manage visual elements directly through a Python script, which is essential for automated reporting and document generation workflows.

Before executing the scripts, ensure the library is installed in your environment:

pip install spire.xls.free

Inserting Images into Specific Cells

To embed a graphic into a worksheet, you must initialize a Workbook instance, access the target sheet, and use the Pictures.Add() method. The following example demonstrates how to place an image at a specific coordinate and adjust the cell dimensions to fit it properly.

from spire.xls import *
from spire.xls.common import *

def insert_visual(filename, image_path):
    # Initialize the workbook object
    excel_doc = Workbook()
    target_sheet = excel_doc.Worksheets[0]
    
    # Add the image to the specified row and column (1, 3)
    inserted_img = target_sheet.Pictures.Add(1, 3, image_path)
    
    # Auto-fit the cell dimensions for the image
    target_sheet.Columns[2].ColumnWidth = 25
    target_sheet.Rows[0].RowHeight = 135
    
    # Save and release resources
    excel_doc.SaveToFile(filename, ExcelVersion.Version2013)
    excel_doc.Dispose()

Positioning and Resizing Graphics

Precise control over image placement and scale is achieved by manipulating the properties of the ExcelPicture object. You can define the exact pixel offsets and dimensions after insertion.

from spire.xls import *
from spire.xls.common import *

def insert_custom_sized_visual(output_file, source_image):
    excel_doc = Workbook()
    active_sheet = excel_doc.Worksheets[0]
    
    # Add the visual element
    visual_obj = active_sheet.Pictures.Add(1, 3, source_image)
    
    # Define geometry and offsets
    visual_obj.LeftColumnOffset = 90
    visual_obj.TopRowOffset = 20
    visual_obj.Width = 150
    visual_obj.Height = 150
    
    excel_doc.SaveToFile(output_file, ExcelVersion.Version2016)
    excel_doc.Dispose()

Deleting Specific Graphics

Removing visual elements requires loading an existing document and accessing the Pictures collection. You can target a specific image by its index within the collection.

from spire.xls import *
from spire.xls.common import *

def remove_first_image(source_path, dest_path):
    file_handler = Workbook()
    file_handler.LoadFromFile(source_path)
    current_sheet = file_handler.Worksheets[0]
    
    # Verify existence and remove the first image (Index 0)
    if current_sheet.Pictures.Count > 0:
        current_sheet.Pictures[0].Remove()
    
    file_handler.SaveToFile(dest_path, ExcelVersion.Version2013)
    file_handler.Dispose()

Bulk Removal of All Images

To clear a worksheet of all embedded graphics, iterate through the Pictures collection in reverse order. Iterating backwards prevents index shifting issues that occur when modifying a collection during a forward loop.

from spire.xls import *
from spire.xls.common import *

def clear_all_graphics(input_xlsx, output_xlsx):
    doc_mgr = Workbook()
    doc_mgr.LoadFromFile(input_xlsx)
    ws = doc_mgr.Worksheets[0]
    
    # Iterate backwards to safely remove items
    total_images = ws.Pictures.Count
    for index in range(total_images - 1, -1, -1):
        ws.Pictures[index].Remove()
    
    doc_mgr.SaveToFile(output_xlsx, ExcelVersion.Version2013)
    doc_mgr.Dispose()