python利用excel分析过杀漏失
from openpyxl import load_workbook
from utils import *
from tqdm import tqdm
import xlsxwriter
excel_contrast_path = 'test.xlsx'
job_image_dir = r"F:\新2000pcs缺陷料\2000pcs原图\S3\S3-A1-1-Right\OK\Org"
excel=load_workbook(excel_contrast_path)
table = excel.get_sheet_by_name(excel.sheetnames[0])
rows=table.max_row
cols=table.max_column
real_values_None = []
real_values = []
predict_values_None = []
predict_values = []
for i in range(rows):
real_values_None.append(table.cell(row=i+1,column=1).value)
predict_values_None.append(table.cell(row=i+1,column=2).value)
for val in real_values_None:
if val is not None:
real_values.append(val)
for val in predict_values_None:
if val is not None:
predict_values.append(val)
true_predict_list = []
false_predict_list = []
for item in predict_values:
if (item in real_values):
true_predict_list.append(item)
else:
false_predict_list.append(item)
print(len(true_predict_list),true_predict_list)
loushi = list(set(real_values) - set(true_predict_list))
print("漏失:",len(loushi),loushi)
print("漏失率:",len(loushi)/len(real_values))
print(len(false_predict_list),false_predict_list)
print("过杀:",len(false_predict_list))
print("过杀率:",len(false_predict_list)/len(predict_values))
'''
生成过杀漏失分析表
'''
workbook = xlsxwriter.Workbook('过杀漏失分析表.xlsx')
worksheet = workbook.add_worksheet('漏失')
for i in range(0,200):
worksheet.set_row(i, 150)
worksheet.set_column('A:O',50)
for item_index,item in enumerate(tqdm(loushi)):
try:
worksheet.write(item_index, 0, str(item))
img_dir_path = os.path.join(job_image_dir,item)
img_files = get_files_path(img_dir_path)
for img_index,img in enumerate(img_files):
worksheet.insert_image(item_index,img_index+1,img,{'x_scale': 0.01, 'y_scale': 0.01})
except Exception as exp:
pass
worksheet = workbook.add_worksheet('过杀')
for i in range(0,200):
worksheet.set_row(i, 150)
worksheet.set_column('A:O',50)
for item_index,item in enumerate(tqdm(false_predict_list)):
try :
worksheet.write(item_index, 0, str(item))
img_dir_path = os.path.join(job_image_dir,item)
img_files = get_files_path(img_dir_path)
for img_index,img in enumerate(img_files):
worksheet.insert_image(item_index,img_index+1,img,{'x_scale': 0.01, 'y_scale': 0.01})
except Exception as exp:
pass
workbook.close()