利用 Python 批量合并 Excel 文件

一、导入库

这里会使用 os ，xlrd，pandas，pathlib 四个库。

xlrd 是用来读取 xls 文件的，pandas 是用来合并数据和写文件的。

pathlib 是用来兼容windows和unix平台（linux or macos）的文件路径的

import os
import xlrd
import pandas as pd
from pathlib import Path

二、遍历要合并的文件

给一个目标文件夹，将这里面全部的文件都生成文件路径。

def file_name(file_dir):
    list=[]
    for file in os.listdir(file_dir):
        if os.path.splitext(file)[1] == '.xls' or os.path.splitext(file)[1] == '.xlsx':
            list.append(file)
    return list

三、合并输出文件

def merge_xlsx(path,filenames,sheet_num,output_filename):
    data = []   #定义一个空list
    title = []
    path_folder = Path(path)
    for i in range(len(filenames)):
        read_xlsx = xlrd.open_workbook(path_folder / filenames[i])
        sheet_num_data = read_xlsx.sheets()[sheet_num] #查看指定sheet_num的数据
        title = sheet_num_data.row_values(0)   #表头, 0也就是第一行
        for j in range(1,sheet_num_data.nrows): #逐行打印,1也就是第二行的内容
            data.append(sheet_num_data.row_values(j))
    content= pd.DataFrame(data)
    #修改表头
    content.columns = title
    #写入excel文件
    output_path = path_folder / 'output'
    output_filename_xlsx = output_filename + '.xlsx'
    if not os.path.exists(output_path):
        print("output folder not exist, create it")
        os.mkdir(output_path)
    content.to_excel((output_path / output_filename_xlsx), header=True, index=False)
    print("merge success")

四、写入变量并运行

完整的代码：

import os
import xlrd
import pandas as pd
from pathlib import Path

def get_filename(file_dir):
    list=[]
    for file in os.listdir(file_dir):
        if os.path.splitext(file)[1] == '.xls' or os.path.splitext(file)[1] == '.xlsx':
            list.append(file)
    return list

def merge_xlsx(path,filenames,sheet_num,output_filename):
    data = []   #定义一个空list
    title = []
    path_folder = Path(path)
    for i in range(len(filenames)):
        read_xlsx = xlrd.open_workbook(path_folder / filenames[i])
        sheet_num_data = read_xlsx.sheets()[sheet_num] #查看指定sheet_num的数据
        title = sheet_num_data.row_values(0)   #表头
        for j in range(1,sheet_num_data.nrows): #逐行打印
            data.append(sheet_num_data.row_values(j))
    content= pd.DataFrame(data)
    #修改表头
    content.columns = title
    #写入excel文件
    output_path = path_folder / 'output'
    output_filename_xlsx = output_filename + '.xlsx'
    if not os.path.exists(output_path):
        print("output folder not exist, create it")
        os.mkdir(output_path)
    content.to_excel((output_path / output_filename_xlsx), header=True, index=False)
    print("merge success")

if __name__ == "__main__":
    path = r'/Your/Path' #这里无论什么平台都按照unix风格来输入文件路径
    filenames = get_filename(path)
    merge_xlsx(path,filenames,0,"sheet1") #合并文件中第一个表的数据，输出到 output/sheet1.xlsx中
    merge_xlsx(path,filenames,1,"sheet1") #合并文件中第二个表的数据，输出到 output/sheet2.xlsx中

多个excel的多个sheet进行合并

import os
import xlrd
import pandas as pd


def get_filename(file_dir):
    list = []
    for file in os.listdir(file_dir):
        if os.path.splitext(file)[1] == '.xls' or os.path.splitext(file)[1] == '.xlsx':
            list.append(file)
    return list

# 打开一个excel文件
def open_xls(file):
    fh = xlrd.open_workbook(file)
    return fh

# 获取excel中所有的sheet表
def getsheet(fh):
    return fh.sheets()

# 获取sheet表的行数
def getnrows(fh, sheet):
    table = fh.sheets()[sheet]
    return table.nrows

# 读取文件内容并返回行内容
def getFilect(file, shnum,datavalue):
    fh = open_xls(file)
    table = fh.sheets()[shnum]
    num = table.nrows
    title = table.row_values(0)
    for row in range(1,num):
        rdata = table.row_values(row)
        datavalue.append(rdata)
    return title

# 获取sheet表的个数
def getshnum(fh):
    x = 0
    sh = getsheet(fh)
    for sheet in sh:
        x += 1
    return x

def merge_xlsx(path,filenames,save_path):
    # 存储所有读取的结果
    datavalue = []
    title = ""
    for fll in filenames:
        fl = os.path.join(path,fll)
        fh = open_xls(fl)
        x = getshnum(fh)
        for shnum in range(x):
            print("正在读取文件：" + str(fl) + "的第" + str(shnum+1) + "个sheet表的内容...")
            title = getFilect(fl, shnum,datavalue)

    # 定义最终合并后生成的新文件
    content = pd.DataFrame(datavalue)
    content.columns = title
    content.to_excel(save_path, header=True, index=False)
    print("文件合并完成")




if __name__ == '__main__':
    # 定义要合并的excel文件列表
    path = r'C:\Users\Hu\Desktop\新建文件夹'
    filenames = get_filename(path)
    save_path = 'sss12.xlsx'
    merge_xlsx(path,filenames,save_path)

参考

• 批量合并 Excel 文件