[Python] Extract hyperlink from image in Excel

I use OpenPyXl for handling excel in python, but it doesn't support handlling hyperlinks of images and shapes. In that case, we need to use COM (Component Object Model) to handle excel program directly and use other functions available in excel itself.

 

 

Sample : 

test1.xlsx file with two images.

Code 1

import os
import win32com.client

excel = win32com.client.Dispatch("Excel.Application")
# excel.Visible = True # Uncomment if you want

wb = excel.Workbooks.Open(r"D:\workspace\blog-post\test1.xlsx")
ws = wb.Sheets('Sheet1')

links = [x.Hyperlink.Address for x in ws.Shapes]
print(links)

wb.Close() 
excel.Quit

Result 1

['https://google.com/', 'https://naver.com/']

 

I use Shape Object in Excel. There are bunch of properties and methods for Shape Obejct.

Here I will show example of three properties we need

 - Name : Name of the shape.

 - Hyperlink.Address : Address in Hyperlink which connected to the shape.

 - TopLeftCell.Address : Address of Cell that lies under the upper-left corner of the shape. 

 

 

Reference : Shape object in Excel

https://docs.microsoft.com/en-us/office/vba/api/excel.shape

Shape object (Excel)

 

 

 

Code 2

 Find name, cell address and hyperlink of the shapes

import os
import win32com.client

excel = win32com.client.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(r"D:\workspace\blog-post\test1.xlsx")
ws = wb.Sheets('Sheet1')

for shape in ws.Shapes:
    name = shape.Name
    address = shape.TopLeftCell.Address
    link = shape.Hyperlink.Address
    
    print(f"{name}\n{address}\n{link}")

wb.Close() 
excel.Quit

Result 2

Rectangle 1
$C$2
https://google.com/
Dog 1
$E$3
https://naver.com/

 

* Name property shows set name on excel file as below.

Please be noted that there can be some duplicates in shape names depending on the files. 

 

 

Comment

You may find out this is not efficient because it needs to iterate all shapes in the sheet. However, you can handle the list of shape using many properties that shape object have and it's quite convenient.