如何将Selenium下载的多个同结构xlsx文件合并为单个文件

我用Selenium编写了批量下载数据的脚本，所有下载得到的xlsx文件字段结构完全一致，现在想要将这些文件合并为一个文件，请问该如何实现？

现有代码 #

注意：现有代码缺少import os和import datetime依赖导入，运行前需要先补上。

from selenium import webdriver
from selenium.webdriver.support.select import Select
from selenium.webdriver.support.ui import WebDriverWait     
from selenium.webdriver.common.by import By     
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.keys import Keys
import time
import shutil
import os
import datetime

PATH = "driver\chromedriver.exe"
driver = webdriver.Chrome(executable_path=PATH)
url = 'https://www.mergermarket.com/homepage'
driver.get(url)

deals = WebDriverWait(driver, 40).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="header"]/div/div[2]/nav/ul/li[4]/a')))
urldeals = deals.get_attribute("href")
driver.get(urldeals)

start_date = datetime.date(2021, 2, 1)
start_date = datetime.datetime.strftime(start_date, '%d/%m/%Y')

middle_date = datetime.date(2021, 2,15)
middle_date = datetime.datetime.strftime(middle_date, '%d/%m/%Y')

delta = datetime.timedelta(days=15)

n = 1
m = 3

while n <= m:
      # Source path
      source = f"sourcepath/{n}new.xlsx"
 
      # Destination path
      destination = f"destinationpath/{n}new.xlsx"

      driver.get(urldeals)

      date = WebDriverWait(driver, 40).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="date-picker_from"]')))
      date.click()
      date.send_keys(start_date) 
      time.sleep(1)

      date2 = WebDriverWait(driver, 40).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="date-picker_to"]')))
      date2.click()
      date2.send_keys(Keys.CONTROL + "a")
      date2.send_keys(Keys.DELETE)
      date2.send_keys(middle_date)

      download = WebDriverWait(driver, 40).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="__next"]/div/div/div[1]/div/div[2]/div[1]/div/div/div[2]/button[1]')))
      download.click()
      time.sleep(3)

      start_date = datetime.datetime.strptime(start_date, '%d/%m/%Y')
      middle_date = datetime.datetime.strptime(middle_date, '%d/%m/%Y')

      start_date = start_date + delta
      middle_date = middle_date + delta

      start_date = datetime.datetime.strftime(start_date, '%d/%m/%Y')
      middle_date = datetime.datetime.strftime(middle_date, '%d/%m/%Y')

      Initial_path = 'path/Downloads'
      filename = max([os.path.join(Initial_path, f) for f in os.listdir(Initial_path)], key=os.path.getctime)
      shutil.move(filename,os.path.join(Initial_path,f"{n}new.xlsx"))

      shutil.copy(source, destination)
      print("File copied successfully.")

      n = n+1
      time.sleep(1)

输出文件列表 #

使用pandas+openpyxl即可快速完成同结构xlsx文件的合并，操作步骤如下：

• 首先安装依赖库：

pip install pandas openpyxl

• 将以下合并逻辑添加到原有脚本的末尾即可，不需要修改原有下载逻辑：

import pandas as pd

# 配置路径参数
xlsx_storage_dir = "destinationpath" # 替换为你存放所有下载xlsx的目录
merged_output_path = "merged_all_data.xlsx" # 合并后文件的保存路径
df_collection = []

# 遍历读取所有符合命名规则的xlsx文件
for file_name in os.listdir(xlsx_storage_dir):
    if file_name.endswith("new.xlsx"):
        full_file_path = os.path.join(xlsx_storage_dir, file_name)
        # 默认读取第一个sheet，如有多sheet需求可调整sheet_name参数
        single_df = pd.read_excel(full_file_path, engine="openpyxl")
        df_collection.append(single_df)

# 合并所有数据，忽略原文件的索引，生成连续的新索引
merged_df = pd.concat(df_collection, ignore_index=True)
# 输出合并后的文件，不保留pandas自动生成的索引列
merged_df.to_excel(merged_output_path, index=False, engine="openpyxl")
print(f"合并完成，共处理{len(df_collection)}个文件，结果已保存至{merged_output_path}")

内容的提问来源于stack exchange，提问作者LaurieFalcon