Reading an excel sheet containing hyperlinks using pythons pandas.read_excel

Question

I made an excel sheet using pandas dataframe to generate texts with clickable urls using the following code

import pandas as pd
df = pd.DataFrame({'link':['=HYPERLINK("https://ar.wikipedia.org/wiki/","wikipidia")',
                           '=HYPERLINK("https://www.google.com", "google")']})
df.to_excel('links.xlsx')

But currently i need to read the generated excel sheet (links.xlsx) using pandas.read_excel so i tried the following code:

import pandas as pd
excelDf=pd.read_excel('links.xlsx')
print(excelDf)

but this generates a dataframe with all zeroes in the link column. Is there another way I can read the excel file i created, or another way to create an excel sheet containing clickable links on text using pandas dataframe that is readable?

The problem is that you are feeding the information as a hyperlink function. Can you do it any other way? — gtomer, Aug 15 '22 at 12:25

score 0 · Answer 1 · answered Aug 15 '22 at 15:02

you can do the same as a csv which is cleaner (avoids excel issues).

# %% write the date

import pandas as pd
df = pd.DataFrame({'link':['=HYPERLINK("https://ar.wikipedia.org/wiki/","wikipidia")',
                           '=HYPERLINK("https://www.google.com", "google")']})
df.to_csv('F:\\links.xlsx')



# %%  read the data

import pandas as pd
excelDf=pd.read_csv('F:\\links.xlsx')
print(excelDf)

result:

   Unnamed: 0                                               link
0           0  =HYPERLINK("https://ar.wikipedia.org/wiki/","w...
1           1     =HYPERLINK("https://www.google.com", "google")

Reading an excel sheet containing hyperlinks using pythons pandas.read_excel

1 Answers1