How to use Worksheet.Shapes in the xlwings API way

In Excel, the Shapes collection of a Worksheet object contains all the shape objects on the sheet, such as rectangles, lines, text boxes, pictures, charts, and other drawing objects. Through xlwings, you can programmatically access and manipulate these shapes, enabling automation of tasks like adding annotations, inserting logos, or modifying chart elements. The Shapes member provides methods to add new shapes, iterate through existing ones, and control their properties, making it a powerful tool for enhancing the visual appeal and functionality of Excel reports.

The xlwings API for accessing the Shapes collection is straightforward. You can reference it via a Worksheet object. The syntax is:

worksheet.shapes

This returns a Shapes collection object. Key methods and properties include:

• add_shape(shape_type, left, top, width, height): Adds a new shape. Parameters: shape_type (integer, e.g., 1 for rectangle, 9 for oval), left and top (distance from left/top edges in points), width and height (in points).
• count: Property that returns the number of shapes on the worksheet.
• item(index): Returns a specific shape by index (1-based) or name.
• Iteration: You can loop through shapes using a for loop.

Common shape types (MsoAutoShapeType enumeration from Microsoft documentation) include:

For example, to add a red rectangle and a text box to a worksheet, and then list all shapes:

import xlwings as xw

# Connect to an existing workbook and worksheet
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Add a rectangle shape
rect = ws.shapes.add_shape(1, left=50, top=50, width=100, height=50)
rect.api.Fill.ForeColor.RGB = 0xFF0000 # Set fill color to red via the underlying API

# Add a text box
text_box = ws.shapes.add_shape(17, left=200, top=50, width=150, height=30)
text_box.text = "Sample Text" # Set text content

# Count and iterate through shapes
print(f"Total shapes: {ws.shapes.count}")
for shape in ws.shapes:
    print(f"Shape name: {shape.name}, type ID: {shape.api.AutoShapeType}")

# Modify an existing shape by index
if ws.shapes.count > 0:
    first_shape = ws.shapes[0] # Access by index (0-based in xlwings)
    first_shape.left = 10 # Move it horizontally

# Save changes
wb.save()