Appending Hyperlinks to Excel Files with pandas and xlsxwriter in Python

To add hyperlinks (web URLs, file paths, or images) in an Excel file generated by Python, you can use the xlsxwriter library as the Excel writer engine for pandas.

Below is a complete example that creates an empty DataFrame, appends rows containing hyperlink formulas, writes the DataFrame to an Excel workbook, obtains the workbook and worksheet objects, defines a blue‑underlined hyperlink format, and then applies that format to each hyperlink cell before saving the file.

import pandas as pd</code><code># 创建一个空的 DataFrame</code><code>df = pd.DataFrame(columns=['Name', 'Link'])</code><code># 添加数据和超链接</code><code>df = df.append({'Name': 'Google', 'Link': '=HYPERLINK("https://www.google.com", "Google")'}, ignore_index=True)</code><code>df = df.append({'Name': 'OpenAI', 'Link': '=HYPERLINK("https://www.openai.com", "OpenAI")'}, ignore_index=True)</code><code>df = df.append({'Name': 'Image', 'Link': '=HYPERLINK("path/to/image.jpg", "Image")'}, ignore_index=True)</code><code># 将 DataFrame 写入 Excel 文件</code><code>writer = pd.ExcelWriter('hyperlinks.xlsx', engine='xlsxwriter')</code><code>df.to_excel(writer, index=False, sheet_name='Sheet1')</code><code># 获取工作簿和工作表对象</code><code>workbook = writer.book</code><code>worksheet = writer.sheets['Sheet1']</code><code># 设置超链接格式</code><code>hyperlink_format = workbook.add_format({'color': 'blue', 'underline': 1})</code><code># 遍历超链接列，设置超链接格式</code><code>for row_num in range(1, len(df) + 1):</code><code>    link_cell = 'B{}'.format(row_num + 1)</code><code>    worksheet.write_url(link_cell, df['Link'][row_num - 1], hyperlink_format)</code><code># 保存 Excel 文件</code><code>writer.save()

The code first builds the DataFrame, uses the =HYPERLINK(...) formula for each link, writes the DataFrame to an Excel file with xlsxwriter, retrieves the workbook/worksheet, defines a hyperlink format, and then writes each hyperlink URL to the appropriate cell.

Make sure the required libraries are installed: pip install pandas xlsxwriter Additional examples show how to append only web links, file links, or image links using the same pattern—create a DataFrame, append rows with the appropriate =HYPERLINK formula, and call df.to_excel to generate the workbook.

Remember to replace the placeholder paths with the actual URLs or file locations you intend to use.