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()
文章转自：《敢达争锋对决》第六届王牌机师挑战赛落下帷幕！

作者：9335游戏网，转载请注明原文链接：https://www.clw9335.com/