from openpyxl import load_workbook, Workbook
# 从旧表中根据行号提取符合条件的行,并遍历单元格获取值,以列表形式写入新表
def write():
for row in row_lst:
data_lst = []
for cell_value in sheet[row]:
data_lst.append(cell_value.value)
ns.append(data_lst)
# 创建和原数据 一样的表头(第一行)
def write_head():
header = sheet[1]
header_lst = []
for cel in header:
header_lst.append(cel.value)
ns.append(header_lst)
# 数据所在的文件夹目录
path = 'E:\\QTworkspace\\B1\\arco_test_tool\\docs\\'
# 打开数据工作簿
workbook = load_workbook(path + '/' + 'SPI_REG_B1_final.xlsx',data_only=True)
# 打开工作表
sheet = workbook.active
First_line = sheet['A']
row_lst = []
new_workbook = Workbook()
for cell in First_line :
if cell.value==None:
continue
# if isinstance(cell.value, str):
row_lst.append(cell.row)
ns = new_workbook.active
write()
new_workbook.save(path + '/' +'final_5.xlsx')
筛选数据
from openpyxl import load_workbook, Workbook
# 从旧表中根据行号提取符合条件的行,并遍历单元格获取值,以列表形式写入新表
def write(ns):
for row in row_lst:
data_lst = []
for cell_value in sheet[row]:
data_lst.append(cell_value.value)
ns.append(data_lst)
# 创建和原数据 一样的表头(第一行)
def write_head(ns):
header = sheet[1]
header_lst = []
for cel in header:
header_lst.append(cel.value)
ns.append(header_lst)
# 数据所在的文件夹目录
path = 'E:\\QTworkspace\\B1\\arco_test_tool\\docs\\'
# 打开数据工作簿
workbook = load_workbook(path + '/' + 'final_5.xlsx',data_only=True)
# 打开工作表
sheet = workbook.active
TYPE = sheet['H']
row_lst = []
count=0
a=0
sheet_index=0
new_workbook = Workbook()
for cell in TYPE:
if(cell.value=='TYPE'):
continue
if (a!=cell.value ):
if count!=0:
write(ns)
row_lst = []
ns = new_workbook.create_sheet(cell.value,sheet_index)
sheet_index=sheet_index+1
write_head(ns)
#if isinstance(cell.value, str):
row_lst.append(cell.row)
a=cell.value
else:
row_lst.append(cell.row)
count=count+1
write(ns)
#count_delete =1
#sheets =new_workbook.sheetnames
# 最后切记保存
new_workbook.save(path + '/' +'fin.xlsx')