excel表合并（笔记）

import os
import pandas as pd
import xlrd
import traceback

rootdir = 'excel表名'
pathname = []
for (dirpath, dirnames, filenames) in os.walk(rootdir):
    for filename in filenames:
        # print(os.path.join(dirpath, filename))
        pathname.append(os.path.join(dirpath, filename))

print(pathname)
print(len(pathname))

n = 0
df = pd.DataFrame()
df_B1_all = pd.DataFrame()
df_B2_all = pd.DataFrame()
df_B3_1all = pd.DataFrame()
df_B3_2all = pd.DataFrame()
df_B4_all = pd.DataFrame()
df_B5_all = pd.DataFrame()
df_B6_all = pd.DataFrame()
df_Filler_Info_all = pd.DataFrame()
for filename in pathname:
    if filename.split('.')[-1] == 'xlsx' or filename.split('.')[-1] == 'xls' in filename:
        print(filename)
        wb = xlrd.open_workbook(filename)
        sheets = wb.sheet_names()
        print(len(sheets))
        print(n)
        try:
            if n == 0:
                df_B1 = pd.read_excel(filename, sheet_name=sheets[0], index=False, encoding='utf8')
                df_B1['表名'] = filename
                # print(df_B1)
                df_B1_all = df_B1_all.append(df_B1, sort=False)
                # print('==========++n=0++=========>', df_B1_all)
                df_B2 = pd.read_excel(filename, sheet_name=sheets[1], index=False, encoding='utf8')
                df_B2['表名'] = filename
                # print(df_B2)
                df_B2_all = df_B2_all.append(df_B2, sort=False)
                df_B3_1 = pd.read_excel(filename, sheet_name=sheets[2], index=False, encoding='utf8')
                df_B3_1['表名'] = filename
                df_B3_1all = df_B3_1all.append(df_B3_1, sort=False)
                df_B3_2 = pd.read_excel(filename, sheet_name=sheets[3], index=False, encoding='utf8')
                df_B3_2['表名'] = filename
                df_B3_2all = df_B3_2all.append(df_B3_2, sort=False)
                df_B4 = pd.read_excel(filename, sheet_name=sheets[4], index=False, encoding='utf8')
                df_B4['表名'] = filename
                df_B4_all = df_B4_all.append(df_B4, sort=False)
                df_B5 = pd.read_excel(filename, sheet_name=sheets[5], index=False, encoding='utf8')
                df_B5['表名'] = filename
                df_B5_all = df_B5_all.append(df_B5, sort=False)
                df_B6 = pd.read_excel(filename, sheet_name=sheets[6], index=False, encoding='utf8')
                df_B6['表名'] = filename
                df_B6_all = df_B6_all.append(df_B6, sort=False)
                df_Filler_Info = pd.read_excel(filename, sheet_name=sheets[7], index=False, encoding='utf8')
                df_Filler_Info['表名'] = filename
                df_Filler_Info_all = df_Filler_Info_all.append(df_Filler_Info, sort=False)
            else:
                df_B1 = pd.read_excel(filename, sheet_name=sheets[0], index=False, encoding='utf8')
                df_B1['表名'] = filename
                # print(df_B1_1)
                df_B1_all = df_B1_all.append(df_B1[2:], sort=False)
                # print('df_B1_all=========>', df_B1_all)
                df_B2 = pd.read_excel(filename, sheet_name=sheets[1], index=False, encoding='utf8')
                df_B2['表名'] = filename
                # print('df_B2[2:]=========>', df_B2[2:])
                df_B2_all = df_B2_all.append(df_B2[2:], sort=False)
                # print('df_B2_all=========>', df_B2_all)
                df_B3_1 = pd.read_excel(filename, sheet_name=sheets[2], index=False, encoding='utf8')
                df_B3_1['表名'] = filename
                df_B3_1all = df_B3_1all.append(df_B3_1[3:], sort=False)
                print('df_B3_1all======>', df_B3_1all)
                df_B3_2 = pd.read_excel(filename, sheet_name=sheets[3], index=False, encoding='utf8')
                df_B3_2['表名'] = filename
                df_B3_2all = df_B3_2all.append(df_B3_2[3:], sort=False)
                df_B4 = pd.read_excel(filename, sheet_name=sheets[4], index=False, encoding='utf8')
                df_B4['表名'] = filename
                df_B4_all = df_B4_all.append(df_B4[3:], sort=False)
                df_B5 = pd.read_excel(filename, sheet_name=sheets[5], index=False, encoding='utf8')
                df_B5['表名'] = filename
                df_B5_all = df_B5_all.append(df_B5[3:], sort=False)
                df_B6 = pd.read_excel(filename, sheet_name=sheets[6], index=False, encoding='utf8')
                df_B6['表名'] = filename
                df_B6_all = df_B6_all.append(df_B6[4:], sort=False)
                # print(df_B6_all)
                df_Filler_Info = pd.read_excel(filename, sheet_name=sheets[7], index=False, encoding='utf8')
                df_Filler_Info['表名'] = filename
                df_Filler_Info_all = df_Filler_Info_all.append(df_Filler_Info[1:], sort=False)
                # print(df_Filler_Info_all)
        except Exception as e:
            error_output = open('tb.txt', 'a+')
            error_output.write('error file============>' + filename + "\r\n")
            traceback.print_exc(file=open('tb.txt', 'a+'))
        n += 1

writer = pd.ExcelWriter('merge_tb.xlsx')
df_B1_all.to_excel(writer, sheet_name="B1")
df_B2_all.to_excel(writer, sheet_name="B2")
df_B3_1all.to_excel(writer, sheet_name="B3_1")
df_B3_2all.to_excel(writer, sheet_name="B3_2")
df_B4_all.to_excel(writer, sheet_name="B4")
df_B5_all.to_excel(writer, sheet_name="B5")
df_B6_all.to_excel(writer, sheet_name="B6")
df_Filler_Info_all.to_excel(writer, sheet_name="Filler_Info")
# df_B2_all.to_excel('test1.xlsx')
# df_B3_1all.to_excel('test2.xlsx')

writer.save()