pandas 将超链接添加到由熊猫数据框 to_excel 方法创建的 Excel 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31820069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
add hyperlink to excel sheet created by pandas dataframe to_excel method
提问by wright17
I have converted a pandasDataFrame to an Excel sheet using df.to_excel.
我已经转换一个大Pandas数据帧到使用Excel片df.to_excel。
Now, I want to add hyperlinks to the values in one column. In other words, when a customer sees my excel sheet, he would be able to click on a cell and bring up a webpage (depending on the value in this cell).
现在，我想为一列中的值添加超链接。换句话说，当客户看到我的 excel 表时，他将能够点击一个单元格并打开一个网页（取决于该单元格中的值）。
回答by maxymoo
You could use the HYPERLINKfunction
您可以使用该HYPERLINK功能
import pandas as pd
df = pd.DataFrame({'link':['=HYPERLINK("http://www.someurl.com", "some website")']})
df.to_excel('test.xlsx')
回答by Dannid
Building on the approach by @guillaume-jacquenot we can use applyto apply this to an entire Series.
基于@guillaume-jacquenot 的方法，我们可以将apply其应用于整个系列。
df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})
For cleanliness, I wrote a helper method.
为了清洁，我写了一个辅助方法。
def make_hyperlink(value):
url = "https://custom.url/{}"
return '=HYPERLINK("%s", "%s")' % (url.format(value), value)
Then, applyit to the Series:
然后，apply它到系列：
df['hyperlink'] = df['Year'].apply(lambda x: make_hyperlink(x))
>
>
Year hyperlink
0 2000 =HYPERLINK("https://custom.url/2000", "2000")
1 2001 =HYPERLINK("https://custom.url/2001", "2001")
2 2002 =HYPERLINK("https://custom.url/2002", "2002")
3 2003 =HYPERLINK("https://custom.url/2003", "2003")
回答by Abhishek Roy
I was generating text files from a excel file and wanted to link the names of the generated .txt files to a particular existing column in the Dataframe.
我正在从 excel 文件生成文本文件，并希望将生成的 .txt 文件的名称链接到 Dataframe 中特定的现有列。
I was trying to push the local drive directory where the generated .txt files are stored, to the corresponding "File Name". So that on clicking the file name, it will open the .txt file.
我试图将存储生成的 .txt 文件的本地驱动器目录推送到相应的“文件名”。这样在单击文件名时，它将打开 .txt 文件。
rancheck_DF = pd.read_excel(excel_file, delim_whitespace = True, encoding = 'utf-8')
for index_df in range(len(rancheck_DF)):
Desc = rancheck_DF.loc[index_df,'Description Text']
MainFile = rancheck_DF.loc[index_df,'File Name']
fileName = r'.\Documents\TestF\TestF_{}.txt'.format(index_df)
with open(fileName, 'w', encoding='utf-8') as txtfile:
txtfile.write(Desc)
rancheck_DF.loc[index_df,'File Name'] = '=HYPERLINK("{}","{}")'.format(fileName,MainFile)
rancheck_DF.to_excel('./damn.xlsx', index=None)
回答by Guillaume Jacquenot
From @maxymoo's answer, here is a full example
来自@maxymoo 的回答，这是一个完整的例子
import pandas as pd
df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})
df['link'] = '-'
df.set_value(0, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2000", 2000)')
df.set_value(1, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2001", 2001)')
df.set_value(2, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2002", 2002)')
df.set_value(3, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2003", 2003)')
df.to_excel('test.xlsx', index = False)
回答by Akshit Gupta
You can use:
您可以使用：
df = pd.DataFrame(list(range(5)), columns=['a'])
df['a'] = df['a'].apply(lambda x: '<a href="http://youtube.com/{0}">link</a>'.format(x))
HTML(df.to_html(escape=False))
