Python Pandas 将原始超链接转换为EXCEL文件中的可点击显示

在您的函数中使用HYPERLINK函数，然后设置可点击的URL:

data = {'product_id': [11, 22, 31, 45],
        'product_URL': ['https://www.google.com','https://www.google.com',
                        'https://www.google.com','https://www.google.com']}
data = pd.DataFrame(data)

def create_hyperlink(row):
    return '=HYPERLINK("{1}", "{0}")'.format(row['product_id'], row['product_URL'])

data['product_hyperlinks'] = data.apply(create_hyperlink, axis=1)

#https://stackoverflow.com/a/73489111/2901002
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
data.to_excel(writer, index=False)
    
# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Get the default URL format.
url_format = workbook.get_default_url_format()

# Close the Pandas Excel writer and output the Excel file.
writer.close()

对于HMTL，使用您的解决方案(交换{0}和{1})，并将escape=False添加到DataFrame.to_html:

def create_hyperlink(row):
    return '<a href="{1}">{0}</a>'.format(row['product_id'], row['product_URL'])


data['product_hyperlinks'] = data.apply(create_hyperlink, axis=1)


data.to_html('file.html', index=False, escape=False)

编辑:循环中写入指向Excel的链接的替代解决方案:

data = {'product_id': [11, 22, 31, 45],
        'product_URL': ['https://www.google.com','https://www.google.com',
                        'https://www.google.com','https://www.google.com']}
data = pd.DataFrame(data)


writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
data.to_excel(writer, index=False)


# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

data['product_hyperlinks'] = ""

pos = data.columns.get_loc('product_hyperlinks')

for i, (name, link) in enumerate(zip(data['product_id'], data['product_URL'])):
    worksheet.write_url(i+1, pos, link, None, str(name))

# Close the Pandas Excel writer and output the Excel file.
writer.close()