Python openpyxl connector with anchor

Question

I really would like to draw a curved arrow connector with anchor to cell with openpyxl library, like the image below;

After I went through the official documentation, I can not find the way to do this, (though I found some relevant keyword in class openpyxl.drawing.shapes.PresetGeometry2D)

How can I draw this kind of anchored connector with Python? I am open to other packages if the OpenPyxl is uncapable of this feature.

Answer 1

Openpyxl doesn't generally play well with Shapes using an alternative module like Xlwings or Win32com would usually be a better option;

The following is a basic example of doing this in Xlwings;

1. Creates a new workbook and Sheet
2. Adds two rectangles
3. Add a Curved connector
4. Joins the start of the curved connector to Rectangle 1 at position 1
5. Joins the end of the curved connector to Rectangle 2 at position 1
6. Re-routes the connection to the shortest path between the two shapes

Code sample
Numeric values used other than for positioning, are the Excel enumeration value for the setting. To obtain these values reference the required Excel Enumerations method.
(Note the commands for win32com will be similar to this)

import xlwings as xw
from xlwings.utils import rgb_to_int


with xw.App(visible=True) as app:
    wb = xw.Book()
    ws = wb.sheets.active

    # Add two rectangle shapes
    ### Excel AddShape command, Values are Type, Left, Top, Width & Height

    shape1 = ws.api.Shapes.AddShape(1, 50, 44, 95, 27)  # 1 is a rectangle
    print(f"Shape1 name '{shape1.Name}'")
    shape1.Fill.ForeColor.RGB = rgb_to_int((255, 233, 00))  # Set Fill colour

    shape2 = ws.api.Shapes.AddShape(1, 290, 115, 94, 28)
    print(f"Shape2 name '{shape2.Name}'")
    shape2.Fill.ForeColor.RGB = '&HFF0000'  # Alternate method to set fill colour

    ### AddConnector (line) has same params; Type, Left, Top, Width & Height
    connector1 = ws.api.Shapes.AddConnector(3, 1, 1, 1, 1)  # 3 is a msoConnectorCurve (Curved connector)
    # connector1.Line.ForeColor.RGB = '&H000000'  # Set Line colour method 1
    # connector1.Line.ForeColor.RGB = rgb_to_int((0, 0, 0))  # Set Line colour method 2
    connector1.Line.ForeColor.ObjectThemeColor = 13  # Set line colour method 3
    connector1.Line.EndArrowheadStyle = 2  # Set Arrow head style as desired
    connector1.Line.EndArrowheadLength = 3  # Set Arrow head length as desired
    connector1.Line.EndArrowheadWidth = 3  # Set Arrow head width as desired

    ### Connecting the curved connector between the two rectangles
    connector1.ConnectorFormat.BeginConnect(ConnectedShape=shape1, ConnectionSite=1)
    connector1.ConnectorFormat.EndConnect(ConnectedShape=shape2, ConnectionSite=1)
    ### Re-route the connectors to the shortest route between the two shapes
    connector1.RerouteConnections()

    wb.save('Shapes.xlsx')

Resultant Sheet

If you move the shapes you'll see the connector is linked to each.

Additional Details
For the AddShape command first parameter, 'Type' this the shape type to add. These are the Excel enumeration types so can be either the name or number,
I.e. msoShapeRectangle (Rectangle) enumeration is 1 so either 'msoShapeRectangle' or 1 can be used as the type.
The rest of the parameters are the position and size of the shape as noted in the code.
I have used arbitrary values to place the rectangles.

The connector can also be sized and positioned. However since we are connecting it between two shapes we can use arbitrary values for these and leave it to Excel join the start and end points, which is why I have just set them all to 1 in this example.
When connecting the curved connector between the two shapes you need to specify the start shape and the point (connection site) on the start shape to connect to and the end shape and the connection site on the end shape to connect to.
The connection site, represented by a number, will vary depending on the shape. If you know what the required connection site value is, it can be specified.
Example in the example I've used Rectangle 1 connection site is 4 and Rectangle 2 connection site is 2 so the command could have been;

connector1.ConnectorFormat.BeginConnect(ConnectedShape=shape1, ConnectionSite=4)
connector1.ConnectorFormat.EndConnect(ConnectedShape=shape2, ConnectionSite=2)

However if the connector site numbers are not known you can connect to two arbitrary points as I have done here and then have Excel re-route the connector to the shortest route using RerouteConnections()

Follow On
It is possible to do away with the [rectangle] shapes and just draw the curved connector so it reaches between two cells. The connector would then move with the cells.
As noted the cells don't have connector points however we can get the Left, Top, Width & Height details of a cell and make calculations from that. It's suggested that the cell details be obtained from a Sheet which is set to Zoom 100% to ensure accuracy. I have included the setting in the example code below.
The example below inputs the start and end cell co-ordinates, gets the Left, Top, Width & Height details and uses these to calculate the Connector start and end details. In the code the connector params are;
Type, BeginX, BeginY, EndX, EndY

BeginX being the start of connector position across the row
BeginY being the start of connector position down the column
EndX, EndY being the same for the end of connector position.

In the calculation of the X positions I have intruded into the cell by 1/4 of the cell width to be clear where the connector sits.

Example code
Add a connector from cell 'B3' to 'E6'

import xlwings as xw
from xlwings.utils import rgb_to_int


def add_connector(begin_cell, end_cell):
    with xw.App(visible=True) as app:
        wb = xw.Book()
        ws = wb.sheets.active
        app.api.ActiveWindow.Zoom = 100  # Ensure the zoom on the sheet is 100%

        ### Start cell postion values
        ws[begin_cell].color = (255, 192, 0)
        cell_start = ws[begin_cell]
        sleft = cell_start.left
        stop = cell_start.top
        sheight = cell_start.height
        swidth = cell_start.width
        print(f"Cell A3 details: Left:{sleft}, Top:{stop}, Height{sheight}, Width{swidth}")

        ### End cell postion values
        ws[end_cell].color = (47, 117, 181)
        cell_dest = ws[end_cell]
        dleft = cell_dest.left
        dtop = cell_dest.top
        dheight = cell_dest.height
        dwidth = cell_dest.width
        print(f"Cell F10 details: Left:{dleft}, Top:{dtop}, Height{dheight}, Width{dwidth}")

        ### Obtain the connector start and end points from the cells postion
        ### Add 1/4 of the cell width intrusion in to the cell
        ### Start cell
        begin_x = (sleft + swidth) - swidth/4
        begin_y = stop + sheight/2
        ### End cell
        end_x = dleft + swidth/4
        end_y = dtop + dheight/2

        ### AddConnector (line) has params; Type, BeginX, BeginY, EndX, EndY
        connector1 = ws.api.Shapes.AddConnector(3, begin_x, begin_y, end_x, end_y)
        # connector1.Line.ForeColor.RGB = '&H000000'  # Set Line colour method 1
        # connector1.Line.ForeColor.RGB = rgb_to_int((0, 0, 0))  # Set Line colour method 2
        connector1.Line.ForeColor.ObjectThemeColor = 13  # Set line colour method 3
        connector1.Line.EndArrowheadStyle = 2  # Set Arrow head style as desired
        connector1.Line.EndArrowheadLength = 3  # Set Arrow head length as desired
        connector1.Line.EndArrowheadWidth = 3  # Set Arrow head width as desired

        wb.save('connector.xlsx')

add_connector('B3', 'E6')

Example Sheet