描述:筛选excel表格数据存储至另一表格,用openpyxl读取excel,定义数组临时存储数据,而后再保存表格。
需要提取数据表格样板:
提取存储至目标表格样板:
代码:
import openpyxl
import os
file_path = r''#表格所在位置
save_path = file_path +'\\' + '提取数据'
if not os.path.exists(save_path):
os.mkdir(save_path)
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)
break
for file in files:
all_data_arr = []
s1 = file.split('\\')
s2 = s1[s1.__len__()-1]
s3 = s2[:s2.index(".xls")]
wb2 = openpyxl.Workbook()
ws2 = wb2.active
wb1 = openpyxl.load_workbook(file)
table = wb1["0"]
row_max = table.max_row#表格最大数量
for i in range(1, row_max+1): # 提取每一行的数据
data_arr = []
str1 = table.cell(i,1).value
if str1 !=None:
str2 = str1.split('-')
else:
str2 = ['1']
if str2[0] == 'MS':
data_arr.append(str2[1])
data_arr.append(str2[1]+str2[2])
cm_arr = []
for j in range(19,42):
cm_arr1 = []
if table.cell(i,j-16).value !=None:
cm_arr1.append(j)
cm_arr1.append(int(table.cell(i,j-16).value))
cm_arr.append(cm_arr1)
data_arr.append(cm_arr)
all_data_arr.append(data_arr)
target = 1
for data in all_data_arr:
for arr in data[2]:
ws2.cell(target,1).value = data[0]
ws2.cell(target, 2).value = data[1]
ws2.cell(target, 3).value = ""
ws2.cell(target, 4).value = arr[0]
ws2.cell(target, 5).value = arr[1]
target +=1
f1 = s3 + '.xlsx'
wb2.save(save_path + '\\'+f1)
wb2.close()
注意:原来想要用xlrd读取表格数据,但是python高版本已经不支持xlrd了。