相同表名合表筛选，合并或删除不需要的数据保存为新表（xlrd读取，openpyxl写入）

记录：xlrd能读取xls与xlsx的excel表格，但是读取xls表格需要用1.2.0版本用最高版本不能读取。思路是先循环遍历旧表并筛选数据存入到数组中，等旧表读取完毕，用openpyxl新建新表并依次写入数据。

import openpyxl
import os
import xlrd
# pip install -i https://pypi.tuna.tsinghua.edu.cn/simple/ xlrd==1.2.0
file_path = r''  # excel所在文件夹
file_save_path = r'' + '\\'  # 保存文件夹
files = []
ID = []
BarCode = []
EPC = []
USER_M = []
Password = []
TID = []
XM = []
DM = []
JS = []
RSSI = []
Time = []
DH = []
for dirpath, dirnames, filenames in os.walk(file_path):
    for file in filenames:
        if file.endswith('.xlsx') or file.endswith('.xls'):
            files.append(dirpath + '\\' + file)
file_all = []
def strinte(str):
    f_arr = str.split('\\')
    s = f_arr[f_arr.__len__() - 1]
    s1 = s.split('-')
    return s1[0]
for file in files:
    if file_all.__len__() != 0:
        bl = False
        for arr in file_all:
            for ar1 in arr:
                if strinte(file) == strinte(ar1):
                    arr.append(file)
                    bl = True
                    break
            if bl == True:
                break
        if bl == False:
            file_group = []
            file_group.append(file)
            file_all.append(file_group)
    if file_all.__len__() == 0:
        file_group = []
        file_group.append(file)
        file_all.append(file_group)
print(file_all.__len__())
print(file_all)
for files in file_all:
    wb2 = openpyxl.Workbook()
    ws2 = wb2.active
    ID.append('ID')
    BarCode.append('条码')
    EPC.append('EPC')
    USER_M.append('USER_M')
    Password.append('Password')
    TID.append('TID')
    XM.append('写码成功')
    DM.append('读码成功')
    JS.append('EPC加锁')
    RSSI.append('RSSI')
    Time.append('时间')
    DH.append('单号')
    s_b = 0
    for file in files:
        wb1 = xlrd.open_workbook(file)
        table = wb1.sheets()[0]
        row_max = table.nrows
        s_b = strinte(file)
        for i in range(1, row_max):  # 表格循环遍历筛选出需要的数据
            if table.cell(i, 5).value != '':
                ID.append(table.cell(i, 0).value)
                BarCode.append(table.cell(i, 1).value)
                EPC.append(table.cell(i, 2).value)
                USER_M.append(table.cell(i, 3).value)
                Password.append(table.cell(i, 4).value)
                TID.append(table.cell(i, 5).value)
                XM.append(table.cell(i, 6).value)
                DM.append(table.cell(i, 7).value)
                JS.append(table.cell(i, 8).value)
                RSSI.append(table.cell(i, 9).value)
                Time.append(table.cell(i, 10).value)
                DH.append(table.cell(i, 11).value)
    lent = ID.__len__() + 1
    for j in range(1, lent):
        ws2.cell(j, 1).value = ID[j - 1]
        ws2.cell(j, 2).value = BarCode[j - 1]
        ws2.cell(j, 3).value = EPC[j - 1]
        ws2.cell(j, 4).value = USER_M[j - 1]
        ws2.cell(j, 5).value = Password[j - 1]
        ws2.cell(j, 6).value = TID[j - 1]
        ws2.cell(j, 7).value = XM[j - 1]
        ws2.cell(j, 8).value = DM[j - 1]
        ws2.cell(j, 9).value = JS[j - 1]
        ws2.cell(j, 10).value = RSSI[j - 1]
        ws2.cell(j, 11).value = Time[j - 1]
        ws2.cell(j, 12).value = DH[j - 1]
    ID.clear()
    BarCode.clear()
    EPC.clear()
    USER_M.clear()
    Password.clear()
    TID.clear()
    XM.clear()
    DM.clear()
    JS.clear()
    RSSI.clear()
    Time.clear()
    DH.clear()
    f1 = s_b + '-'+str(lent-2) +'.xlsx'
    wb2.save(file_save_path + f1)
    wb2.close()