手把手教你4種方法用Python批量實(shí)現(xiàn)多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")]

   # 獲取第一個(gè)EXCEL名    xlsx_names1 = xlsx_names[0]
    aa = path + xlsx_names1    #打開第一個(gè)EXCEL    first_file_fh=xlrd.open_workbook(aa)    # 獲取SHEET名    first_file_sheet=first_file_fh.sheets()

   #按SHEET名循環(huán)    for sheet_name in sheet_names:        df = None        # 按EXCEL名循環(huán)        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個(gè)，第%d個(gè)。" % (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變量明確新表是否已經(jīng)添加了表頭，只要添加過一次就無(wú)須重復(fù)再添加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都讀?。?/span>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個(gè)文件已經(jīng)合并成功！' % len(lst))