使用Python在Excel中插入、修改、提取和删除超链接<

pip install Spire.XLS

from spire.xls import *
from spire.xls.common import *
 
# 创建Workbook对象
workbook = Workbook()
 
# 获取第一个工作表
sheet = workbook.Worksheets[0]
 
# 添加指向外部网页的文本超链接
cell1 = sheet.Range["B2"]
webLink = sheet.HyperLinks.Add(cell1)
webLink.Type = HyperLinkType.Url
webLink.TextToDisplay = "Baidu.com"
webLink.Address = "https://baidu.com/"
 
# 添加指向电子邮件地址的文本超链接
cell2 = sheet.Range["B4"]
mailLink = sheet.HyperLinks.Add(cell2)
mailLink.Type = HyperLinkType.Url
mailLink.TextToDisplay = "联系我们"
mailLink.Address = "mailto:support@mycompany.com"
 
# 添加指向外部文件的文本超链接
cell3 = sheet.Range["B6"]
fileLink = sheet.HyperLinks.Add(cell3)
fileLink.Type = HyperLinkType.File
fileLink.TextToDisplay = "打开 报表.xlsx"
fileLink.Address = "C:\\Users\\Administrator\\Desktop\\报表.xlsx"
 
# 添加指向同一工作簿中其他单元格的文本超链接
cell4 = sheet.Range["B8"]
linkToSheet = sheet.HyperLinks.Add(cell4)
linkToSheet.Type = HyperLinkType.Workbook
linkToSheet.TextToDisplay = "跳转到Sheet2!A1"
linkToSheet.Address = "Sheet2!A1"
 
# 在工作表中插入图片
image = sheet.Pictures.Add(10, 2, "baidu.png")
image.LeftColumnOffset = 25
image.TopRowOffset = 25
# 为图片添加超链接
image.SetHyperLink("https://baidu.com/", True)
 
# 设置列宽和行高
sheet.SetColumnWidth(2, 17)
sheet.SetRowHeight(10, image.Height)
 
# 保存结果文件
workbook.SaveToFile("添加超链接.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *
 
# 创建Workbook对象
workbook = Workbook()
# 加载Excel文档
workbook.LoadFromFile("添加超链接.xlsx")
 
# 获取第一个工作表
sheet = workbook.Worksheets[0]
 
# 获取工作表中的第一个超链接
link = sheet.HyperLinks[0]
 
# 修改超链接的显示文本
link.TextToDisplay = "CSDN"
# 修改超链接的目标地址
link.Address = "https://www.csdn.net/"
 
# 保存结果文档
workbook.SaveToFile("修改超链接.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *
 
# 创建Workbook对象
workbook = Workbook()
# 加载Excel文档
workbook.LoadFromFile("添加超链接.xlsx")
 
# 获取第一个工作表
sheet = workbook.Worksheets[0]
 
# 获取工作表中所有文本超链接的集合
links = sheet.HyperLinks
 
# 创建列表来存储提取的超链接信息
list = []
 
# 遍历提取的文本超链接
for link in links:
    # 获取每个超链接的地址
    address = link.Address
    # 将地址添加到列表
    list.append(address)
 
# 遍历工作表中的所有图片
imageCollection = sheet.Pictures
for image in imageCollection:
    # 获取图片超链接的地址
    link = image.GetHyperLink()
    address = link.Address
    list.append(address)
 
# 将提取的超链接地址写入文本文件
with open("超链接.txt", "w", encoding = "utf-8") as file:
    for item in list:
        file.write(item + "\n")
 
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *
 
# 创建Workbook对象
workbook = Workbook()
# 加载Excel文档
workbook.LoadFromFile("添加超链接.xlsx")
 
# 获取第一个工作表
sheet = workbook.Worksheets[0]
 
# # 删除特定单元格中的文本超链接
# sheet.Range["B2"].HyperLinks.RemoveAt(0)
 
# 获取文本超链接的集合
links = sheet.HyperLinks
 
# 从后往前遍历集合，依次删除集合中的文本超链接
for i in range(links.Count - 1, -1, -1):
    links.RemoveAt(i)
 
# 保存结果文档
workbook.SaveToFile("删除超链接.xlsx", ExcelVersion.Version2016)
workbook.Dispose()