python对数据提取保存并拆分

思路：先定义数组，然后用xlrd读取excel表格并提取所需要的数据用openpyxl进行保存，而后用pandas进行数据拆分处理。

import os
import xlrd
import openpyxl
import pandas as pd
# pip install -i https://pypi.tuna.tsinghua.edu.cn/simple/ xlrd==1.2.0
file_path = r'' #excel表格所在路径
file_save = r'' + '\\' #excel拆分保存所在路径
files= []
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)
for file in files:
    wb2 = openpyxl.Workbook()
    ws2 = wb2.active
    print(file)
    ID = []
    BARCODE = []
    EPC = []
    ACCESSCODE = []
    ID.append('ID')
    BARCODE.append('BARCODE')
    EPC.append('EPC')
    ACCESSCODE.append('ACCESSCODE')
    s1 = file.split('\\')
    s2 = s1[s1.__len__()-1] #表名+xlsx
    s3 = s2[:s2.index(".xls")] #表名
    os.mkdir(file_save+s3)
    wb1 = xlrd.open_workbook(file)
    table = wb1.sheet_by_name('生成数据')
    row_max = table.nrows
    for i in range(1, row_max):
        if table.cell(i, 19).value == '':
            break
        ID.append(i)
        BARCODE.append(table.cell(i, 17).value)
        EPC.append(table.cell(i, 19).value)
        ACCESSCODE.append('22403403')
    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 = ACCESSCODE[j - 1]
    ID.clear()
    BARCODE.clear()
    EPC.clear()
    ACCESSCODE.clear()
    f1 = file_save+s3 + '\\'+s2
    wb2.save(f1)
    wb2.close()
    #新建拆分数据文件夹
    dipa = file_save+s3+'\\'+'拆分数据'
    os.mkdir(dipa)
    orgName = pd.read_excel(f1, sheet_name='Sheet')
    org_list = list(orgName['BARCODE'].drop_duplicates())
    print('总数量:' + str(org_list.__len__()))
    for i in org_list:
        writer = pd.ExcelWriter(dipa +'\\'+ str(i) + '.xlsx')
        tempdata = orgName[orgName['BARCODE'] == i]
        tempdata.to_excel(writer, index=False)
        writer.save()
        writer.close()