python - 如何将多个 .xls 文件与 python 中的超链接合并？

Question

我正在尝试合并多个 .xls 文件，这些文件有很多列，但有 1 列带有超链接。我尝试使用 Python 执行此操作，但一直遇到无法解决的错误。

为简洁起见，超链接隐藏在文本部分下。以下 ctrl-click 超链接是我在 .xls 文件中遇到的示例:ES2866911 (T3) .

为了提高再现性，我在下面添加了 .xls1 和 .xls2 示例。

xls1:

<表类="s-表"><头>标题Publication_Number<正文>P_A ES2866911 (T3) P_B EP3887362 (A1)

.xls2:

<表类="s-表"><头>标题Publication_Number<正文>P_C AR118706 (A2) P_D ES2867600 (T3)

期望的结果:

<表类="s-表"><头>标题Publication_Number<正文>P_A ES2866911 (T3) P_B EP3887362 (A1) P_C AR118706 (A2) P_D ES2867600 (T3)

我无法在不丢失格式或超链接的情况下将 .xls 文件导入 Python。此外，我无法将 .xls 文件转换为 .xlsx。我不可能获得 .xlsx 格式的 .xls 文件。下面我简单总结一下我尝试过的:

1.) 用 Pandas 阅读是我的第一次尝试。很容易做到，但所有超链接在 PD 中都丢失了，而且原始文件的所有格式都丢失了。

2.) 使用 openpyxl.load 读取 .xls 文件

InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.

3.) 将 .xls 文件转换为 .xlsx

from xls2xlsx import XLS2XLSX
x2x = XLS2XLSX(input.file.xls)
wb = x2x.to_xlsx()
x2x.to_xlsx('output_file.xlsx')
TypeError: got invalid input value of type <class 'xml.etree.ElementTree.Element'>, expected string or Element

import pyexcel as p
p.save_book_as(file_name=input_file.xls, dest_file_name=export_file.xlsx)
TypeError: got invalid input value of type <class 'xml.etree.ElementTree.Element'>, expected string or Element
During handling of the above exception, another exception occurred:
StopIteration

4.) 即使我们能够使用 xlrd 读取 .xls 文件(这意味着我们永远无法将文件另存为 .xlsx，我什至看不到超链接:

import xlrd
wb = xlrd.open_workbook(file) # where vis.xls is your test file
ws = wb.sheet_by_name('Sheet1')
ws.cell(5, 1).value   
'AR118706 (A2)' #Which is the name, not hyperlink

5.) 我尝试安装旧版本的 openpyxl==3.0.1 来克服类型错误，但没有成功。我尝试使用带有 xlrd 引擎的 openpyxl 打开 .xls 文件，出现类似的打字错误“xml.entree.elementtree.element”错误。我尝试了很多方法将 .xls 文件批量转换为 .xlsx，但都出现了类似的错误。

显然，我可以只用 excel 打开并另存为 .xlsx，但这违背了整个目的，我不能对 100 个文件这样做。

Answer 1

您需要使用 xlrd 库正确读取超链接，使用 pandas 将所有数据合并在一起，并使用 xlsxwriter 正确写入数据。假设所有输入文件具有相同的格式，您可以使用以下代码。

# imports
import os
import xlrd
import xlsxwriter
import pandas as pd

# required functions
def load_excel_to_df(filepath, hyperlink_col):
    book = xlrd.open_workbook(file_path)
    sheet = book.sheet_by_index(0)
    hyperlink_map = sheet.hyperlink_map
    
    data = pd.read_excel(filepath)
    hyperlink_col_index = list(data.columns).index(hyperlink_col)
    
    required_links = [v.url_or_path for k, v in hyperlink_map.items() if k[1] == hyperlink_col_index]
    data['hyperlinks'] = required_links
    return data

# main code
# set required variables
input_data_dir = 'path/to/input/data/'
hyperlink_col = 'Publication_Number'
output_data_dir = 'path/to/output/data/'
output_filename = 'combined_data.xlsx'

# read and combine data
required_files = os.listdir(input_data_dir)
combined_data = pd.DataFrame()
for file in required_files:
    curr_data = load_excel_to_df(data_dir + os.sep + file, hyperlink_col)
    combined_data = combined_data.append(curr_data, sort=False, ignore_index=True)
cols = list(combined_data.columns)
m, n = combined_data.shape
hyperlink_col_index = cols.index(hyperlink_col)

# writing data
writer = pd.ExcelWriter(output_data_dir + os.sep + output_filename, engine='xlsxwriter')
combined_data[cols[:-1]].to_excel(writer, index=False, startrow=1, header=False) # last column contains hyperlinks
workbook  = writer.book
worksheet = writer.sheets[list(workbook.sheetnames.keys())[0]]
for i, col in enumerate(cols[:-1]):
    worksheet.write(0, i, col)
for i in range(m):
    worksheet.write_url(i+1, hyperlink_col_index, combined_data.loc[i, cols[-1]], string=combined_data.loc[i, hyperlink_col])
writer.save()

引用资料:

1. 阅读超链接 - https://stackoverflow.com/a/7057076/17256762
2. pandas to_excel 标题格式 - Remove default formatting in header when converting pandas DataFrame to excel sheet
3. 使用 xlsxwriter 编写超链接 - https://xlsxwriter.readthedocs.io/example_hyperlink.html