Extract hyperlink URL from excel sheet with python

Question

How can I extract the URL of a Hyperlink in a cell in an excel sheet using python? I have tried using Pandas and openly, but they didn't work...

below is the code I have written so far in openpyxl:

    from openpyxl import load_workbook as load
    from openpyxl import Workbook

    file = 'tableCopy.xlsx'
    zipCode = 2110
    codeCol = 5
    linkCol = 6
    
    result = Workbook()
    rws = result.active
    
    wb1 = load(file)
    sheets = [wb1.sheetnames[x] for x in range(len(wb1.sheetnames) - 1)]
    
    for sheet in sheets:
        ws = wb1[sheet]

        for rowNumber in range(1, ws.max_row + 1):
        if ws.cell(row=rowNumber, column=codeCol).value == zipCode:
            rws.append((cell.value for cell in ws[rowNumber]))

            url = ws.cell(column=linkCol, row=rowNumber) # this is what i am using to get the url
            text = ws.cell(column=linkCol + 1, row=rowNumber)
            rws.cell(row=rowNumber, column=linkCol).value = '=HYPERLINK("' + str(url.value) + '","' + str(text.value) + '")'

    result.save(f'code{zipCode}.xlsx')

file is the file name

zipCode is the value i want to filter it with

codeCol is the column which has the zipCode

linkCol is the column with the Hyperlink

rws is a new worksheet(part of result workbook) and it will store the filtered data

i got the code for the url and text from the second answer here: Pandas read_excel with Hyperlink

i ditched the text for now cuz its just showing the text of the next column

this is an example of what i got when i printed out the url variable: =IF(COUNTBLANK(B107:D107)>0,"",HYPERLINK(CONCATENATE(Setup!$B$5,SUBSTITUTE(SUBSTITUTE(CONCATENATE(B107,"+",C107,"+",D107,"+",E107)," ","+"),"/","%2F")),"Google Map")) not the URL that i was hoping for which is a link to google maps.

Answer 1

Here use openpyxl module. First load the workbook (as an example first sheet):

import openpyxl

wb = openpyxl.load_workbook('FILEPATH')
ws = wb['Sheet1']

And then you can use the .hyperlink.target of a cell. Put it in a try..except block, because if cell does not have a hyperlink then it will fail.