手把手教你4种方法用Python批量实现多Excel多Sheet合并

import pandas as pdimport xlrdimport os

 #要合并文件路径    path="D:/b/"    #获取文件夹下所有EXCEL名    xlsx_names = [x for x in os.listdir(path) if x.endswith(".xlsx")]

   # 获取第一个EXCEL名    xlsx_names1 = xlsx_names[0]
    aa = path + xlsx_names1    #打开第一个EXCEL    first_file_fh=xlrd.open_workbook(aa)    # 获取SHEET名    first_file_sheet=first_file_fh.sheets()

   #按SHEET名循环    for sheet_name in sheet_names:        df = None        # 按EXCEL名循环        for xlsx_name in xlsx_names:            sheet_na = pd.ExcelFile(path + xlsx_name).sheet_names            if sheet_name in sheet_na:                #print(sheet_name)                _df = pd.read_excel(path + xlsx_name, sheet_name=sheet_name,header=None)                if df is None:                    df = _df                else:                    df = pd.concat([df, _df], ignore_index=True)            else:continue

      df.to_excel(excel_writer=writer, sheet_name=sheet_name, encoding="utf-8", index=False)        print(sheet_name + "  保存成功！共%d个，第%d个。" % (len(sheet_names),num))        num += 1    writer.save()    writer.close()

# -*- coding: utf-8 -*-# @Author: hebe# @Date:   2020-04-18 18:31:03# @Last Modified by:   hebe# @Last Modified time: 2020-04-18 19:40:48import os import globimport openpyxl
def merge_xlsx_files(xlsx_files):    wb = openpyxl.load_workbook(xlsx_files[0])    ws = wb.active    ws.title = "merged result"
    for  filename in xlsx_files[1:]:        workbook = openpyxl.load_workbook(filename)        sheet = workbook.active        for row in sheet.iter_rows(min_row=1):            values = [cell.value for cell in row]            ws.append(values)    return wb
#path is very important here , must true.def get_all_xlsx_files(path):    xlsx_files = glob.glob(os.path.join(r'C:\\Users\\pdcfi\\Desktop\\', '*.xlsx'))    sorted(xlsx_files, key=str.lower)    return xlsx_files
def main():    xlsx_files = get_all_xlsx_files(os.path.expanduser('~lmx'))    wb = merge_xlsx_files(xlsx_files)    wb.save('merged_form.xlsx')
if __name__ == '__main__':    main()    print("all excel append OK!")

# -*- coding: utf-8 -*-from openpyxl import load_workbook, Workbookimport glob
path = "C:\\Users\\pdcfi\\Desktop\\excel\\"new_workbook = Workbook()new_sheet = new_workbook.active
# 用flag变量明确新表是否已经添加了表头，只要添加过一次就无须重复再添加flag = 0
for file in glob.glob(path + '/*.xlsx'):    workbook = load_workbook(file)    sheet = workbook.active
    coloum_A = sheet['A']    row_lst = []    for cell in coloum_A:        if cell:            print(cell.row)            row_lst.append(cell.row)
    if not flag:        header = sheet[1]        header_lst = []        for cell in header:            header_lst.append(cell.value)        new_sheet.append(header_lst)        flag = 1
    for row in row_lst:        data_lst = []        for cell in sheet[row]:            data_lst.append(cell.value)        new_sheet.append(data_lst)
new_workbook.save(path + '/' + '符合筛选条件的新表.xlsx')

import tkinter as tkfrom tkinter import filedialogimport osimport pandas as pdimport glob
root = tk.Tk()root.withdraw()
# 选择文件夹位置filelocation = os.path.normpath(filedialog.askdirectory(initialdir=os.getcwd()))lst = []
# 读取文件夹下所有文件（xls和xlsx都读取）for i in glob.glob(filelocation + "\\\\" + "*.*"):    if os.path.splitext(i)[1] in [".xls", ".xlsx"]:        lst.append(pd.read_excel(i))
# 保存合并后的excel文件writer = pd.ExcelWriter(filedialog.asksaveasfilename(title="保存", initialdir=filelocation, defaultextension="xlsx",                                                     filetypes=[("Excel 工作簿", "*.xlsx"),                                                                ("Excel 97-2003 工作簿", "*.xls")]))pd.concat(lst).to_excel(writer, 'all', index=False)writer.save()
print('\n%d个文件已经合并成功！' % len(lst))