import enum from genericpath import isdir import shutil from tkinter import VERTICAL import imutils import os from multiprocessing import connection from PyQt5.QtWidgets import * from peewee import * import sqlalchemy as db from PyQt5.QtGui import * from PyQt5.QtCore import Qt import time global film_table global ResultSet global engine import numpy as np import cv2 import pandas as pd import openpyxl from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font import xlwt from openpyxl import load_workbook import xlsxwriter global show_ show_export=" film_table.Result='1'" def get_passw(passW): global passw passw=passW def set_excel_pass(excel_path,passw): import openpyxl import os import win32com.client as win32 abs_path=os.path.abspath(excel_path) # set password for workSHEET # workbook = openpyxl.load_workbook(filename="export.xlsx") #open excel # worksheet = workbook.active # worksheet.protection.set_password('123') # workbook.save('export.xlsx') #set password for entire workbook #excel = win32.gencache.EnsureDispatch('Excel.Application') excel = win32.Dispatch('Excel.Application') #Before saving the file set DisplayAlerts to False to suppress the warning dialog: excel.DisplayAlerts = False wb = excel.Workbooks.Open(abs_path) # refer https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb214129(v=office.12)?redirectedfrom=MSDN # FileFormat = 51 is for .xlsx extension wb.SaveAs(abs_path, 51, passw) wb.Close() excel.Application.Quit() def show_user_db(self): engine = db.create_engine('sqlite:///db\\database1.db') connection = engine.connect() metadata = db.MetaData() film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine) self.treeView_2.model =QStandardItemModel(0,3)#4 colums query_user="SELECT film_table.prime,film_table.Company,film_table.Project,film_table.Weld_no FROM film_table WHERE film_table.Result=='1' ORDER BY prime DESC LIMIT 15" ResultProxy_user = connection.execute(query_user) ResultSet_user = ResultProxy_user.fetchall() # for i in film_table.columns.keys(): # if i in ["prime","Company","Project","Weld_no"]: # print("i:",i) for row,item in enumerate (ResultSet_user): parent=self.treeView_2.model.appendRow(QStandardItem(str(item[0]))) for column,k in enumerate(item): self.treeView_2.model.setItem(row,column,QStandardItem(str(k))) # print("item:\t",item) self.treeView_2.setHeaderHidden(True) self.treeView_2.setModel(self.treeView_2.model) def get_user_db(self,prime): engine = db.create_engine('sqlite:///db\\database1.db') connection = engine.connect() metadata = db.MetaData() film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine) query_user='SELECT * FROM film_table WHERE film_table.prime = '+'"'+str(prime)+'"' ResultProxy_user = connection.execute(query_user) ResultSet_user = ResultProxy_user.fetchall() # for i , it in enumerate(ResultSet_user): # user_item.append(it[i]) return(ResultSet_user[0]) def show_db(self,show): global film_table global defect_table global ResultSet global engine global shh global show_export shh=show global connection engine = db.create_engine('sqlite:///db\\database1.db') connection = engine.connect() metadata = db.MetaData() film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine) defect_table= db.Table('defect_table', metadata, autoload=True, autoload_with=engine) #query_replace="UPDATE film_table SET Project = REPLACE(Project, '\"'"+","+"'*'"+")" #query_replace="UPDATE film_table SET Project = REPLACE(Project, '\"'" + "," + "'\''"+"'\''" +")" #replaces " with two '' in column Project #ResultProxy_replace = connection.execute(query_replace) #print(film_table.columns.keys()) #print all columns #print(repr(metadata.tables['film_table'])) #print all metadata of columns query = db.select([film_table]) #shows every image taken if show=='result': show_=' AND '+"film_table.Result='1'" #only shows results show_export=" film_table.Result='1'" if show=='all': show_='' #only shows results show_export="" ResultProxy = connection.execute(query) ResultSet = ResultProxy.fetchall() #print(ResultSet) #self.treeView.model = QFileSystemModel() #self.treeView.model.setRootPath(r'O:\Project 2\Welding Film Scanner\App\GUI V0.11') self.treeView.model =QStandardItemModel(0,len(film_table.columns.keys())) for i,c in enumerate(film_table.columns.keys()): self.treeView.model.setHeaderData(i, Qt.Horizontal, str(c)) query_replace="UPDATE film_table SET "+str(c) +"= REPLACE("+str(c)+", '\"'" + "," + "'\''"+"'\''" +")" #replaces " with two '' in column c ResultProxy_replace = connection.execute(query_replace) # query_replace="UPDATE film_table SET "+str(c) +"= REPLACE("+str(c)+", ')'" + "," + "''"+")" #replaces " with two '' in column c # ResultProxy_replace = connection.execute(query_replace) # query_replace="UPDATE film_table SET "+str(c) +"= REPLACE("+str(c)+", '('" + "," + "''"+")" #replaces " with two '' in column c # ResultProxy_replace = connection.execute(query_replace) #print(str(c)," replaced") ########################### original without sorting #############################3 ''' for row,item in enumerate (ResultSet): parent=self.treeView.model.appendRow(QStandardItem(str(item[0]))) for column,k in enumerate(item): self.treeView.model.setItem(row,column,QStandardItem(str(k))) ''' ########################### original without sorting #############################3 rootNode = self.treeView.model.invisibleRootItem() global rr,rr2,rr3 rr=0 rr2=0 rr3=0 rr4=0 rr5=0 ''' for row,item in enumerate (ResultSet): parent=QStandardItem(str(item[1])) #sorts based on company self.treeView.model.appendRow(parent) rr=rr+1 for column,k in enumerate(item): child=QStandardItem(str(k)) #self.treeView.model.appendRow(parent.appendRow(child)) parent.setChild(row-rr+1,column,child) ''' #query_company="SELECT * FROM film_table GROUP BY Company" #only shows company as parent #query_company="SELECT * FROM (SELECT * FROM film_table GROUP BY Company,Project) film_table GROUP BY Company" query_="SELECT DISTINCT film_table.Company FROM film_table WHERE film_table.Result=='1'" ResultProxy_ = connection.execute(query_) ResultSet_ = ResultProxy_.fetchall() for row,item in enumerate (ResultSet_): column_=str(item[0]) #item[0]=== companies parent=QStandardItem(column_) #sorts based on company self.treeView.model.appendRow(parent) rr=rr+1 print(column_) query_company='SELECT DISTINCT film_table.Project FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+show_ #gets each company name ResultProxy_company = connection.execute(query_company) ResultSet_company = ResultProxy_company.fetchall() for row2,item2 in enumerate (ResultSet_company): #adds all companies with same name as children rr2=rr2+1 column_2=str(item2[0]) child=QStandardItem(column_2) parent.setChild(row2,0,child) query_project='SELECT DISTINCT film_table.Report FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'+show_ #gets each company name #query_project='SELECT * FROM film_table WHERE film_table.Project='+'"'+str(column_2)+'"' ResultProxy_project = connection.execute(query_project) ResultSet_project = ResultProxy_project.fetchall() for row3,item3 in enumerate (ResultSet_project): rr3=rr3+1 column_3=str(item3[0]) #Report child2=QStandardItem(column_3) child.setChild(row3,0,child2) #parent.setChild(row2-rr+2,column,child) #child.setChild(row2-rr2+1,column,QStandardItem("child")) query_report='SELECT DISTINCT film_table.Weld_no FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'+' AND '+'film_table.Report='+'"'+str(column_3)+'"'+show_ ResultProxy_report = connection.execute(query_report) ResultSet_report = ResultProxy_report.fetchall() for row4,item4 in enumerate (ResultSet_report): rr4=rr4+1 column_4=str(item4[0]) #Report child3=QStandardItem(column_4) child2.setChild(row4,0,child3) #query_weld_no='SELECT DISTINCT film_table.Weld_no FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'' AND '+'film_table.Report='+'"'+str(column_3)+'"'+' AND '+'film_table.Weld_no='+'"'+str(column_4)+'"'+show_ #gets each company name query_weld_no='SELECT * FROM film_table WHERE film_table.Company = '+'"'+str(column_)+'"'+' AND '+'film_table.Project='+'"'+str(column_2)+'"'+' AND '+'film_table.Report='+'"'+str(column_3)+'"'+' AND '+'film_table.Weld_no='+'"'+str(column_4)+'"'+show_+' ORDER BY film_table.Sort' #query_project='SELECT * FROM film_table WHERE film_table.Project='+'"'+str(column_2)+'"' ResultProxy_weld_no = connection.execute(query_weld_no) ResultSet_weld_no = ResultProxy_weld_no.fetchall() for row5,k in enumerate(ResultSet_weld_no): child4=QStandardItem(str(k[5])) child3.setChild(row5,0,child4) #all data #self.treeView.setModel(self.treeView.model) #self.treeView.setColumnWidth(0, 800) rr5=rr5+1 for cc,kk in enumerate(k): child5=QStandardItem(str(kk)) child3.setChild(row5,cc,child5) #all data ''' for column,k in enumerate(item): child=QStandardItem(str(k)) child2=QStandardItem(str(k)) ##self.treeView.model.appendRow(parent.appendRow(child)) #parent.setChild(row-rr+1,column,child) #child.setChild(row-rr+1,column,child2) ''' # Test ''' parent=QStandardItem(("Parent")) self.treeView.model.appendRow(parent) child=QStandardItem(str("Child")) self.treeView.model.appendRow(parent.appendRow(child)) parent.setChild(0,1,QStandardItem(str("k"))) ''' #Test #rootNode.appendRow(parent) #self.treeView.expandAll() self.treeView.setModel(self.treeView.model) def additem2db(self,item): global film_table global ResultSet global connection global engine engine = db.create_engine('sqlite:///db\\database1.db') connection = engine.connect() metadata = db.MetaData() film_table = db.Table('film_table', metadata, autoload=True, autoload_with=engine) #query = db.insert(film_table).values(project='naveen', weld_no=60000, picture_num=123,welder='',image_path='C:\\') query = db.insert(film_table).values(item) ResultProxy = connection.execute(query) def additem2_defect_db(self,item): global defect_table global defect_ResultSet global d_connection global d_engine d_engine = db.create_engine('sqlite:///db\\database1.db') d_connection = d_engine.connect() metadata = db.MetaData() defect_table = db.Table('defect_table', metadata, autoload=True, autoload_with=d_engine) query = db.insert(defect_table).values(item) d_ResultProxy = d_connection.execute(query) def export_excel_raw(self,company,project,report,location,weld_no,result): export_excel_engine = db.create_engine('sqlite:///db\\database1.db') export_excel_connection = export_excel_engine.connect() export_excel_metadata = db.MetaData() export_excel_film_table = db.Table('film_table', export_excel_metadata, autoload=True, autoload_with=export_excel_engine) #print(film_table.columns.keys()) #print all columns item=[] q="" r="" filters=["Company","Project","Report","Location","Weld_no","Result"] filter_items=[company,project,report,location,weld_no,result] for i,filter in enumerate(filter_items): if filter!="" : q="film_table."+filters[i+1]+", " r=r+filters[i]+"='"+filter_items[i]+"'"+" AND " q=q.rstrip(", ") r=r.rstrip(" AND ") if r=="" : query_return="SELECT * FROM film_table WHERE Result='"+"1"+"'" else: query_return="SELECT * FROM film_table WHERE Result='"+"1"+"' AND "+r #print(query_return) ResultProxy_return = connection.execute(query_return) ResultSet__return = ResultProxy_return.fetchall() for i , it in enumerate(ResultSet__return): item.append(str(it).rstrip("'),").lstrip("('")) export_excel_ResultProxy = connection.execute(query_return) export_excel_ResultSet = export_excel_ResultProxy.fetchall() workbook = xlsxwriter.Workbook('export_excel_raw.xlsx') worksheet = workbook.add_worksheet() header_format = workbook.add_format({'font_name':'Courier New','bold': True, 'text_wrap': True, 'fg_color': '#FDE9D9', 'border': 1, 'align':'center', 'valign':'vcenter', }) header_format.set_text_wrap(False) for header_no,header in enumerate(export_excel_film_table.columns.keys()): #write headers to excel worksheet.write(0,header_no, header,header_format) for row_no,row in enumerate(export_excel_ResultSet): #writes values to excel #print(row) for idx,item in enumerate(row): #print(item) worksheet.write(row_no+1,idx, item,header_format) #worksheet.write('B1', 'lkjhg') workbook.close() wb = load_workbook('export_excel_raw.xlsx') ws=wb['Sheet1'] ws.column_dimensions['G'].width=12 ws.column_dimensions['H'].width=35 wb.save('export_excel_raw.xlsx') def edititem2db(self,i2e,id_in_table): global ResultSet global shh u = db.update(film_table) u = u.values(i2e) u = u.where(film_table.c.prime == id_in_table) engine.execute(u) show_db(self,shh) def edititem2db_by_col_no(self,i2e,id_in_table): global ResultSet global shh u = db.update(film_table) c=film_table.columns.keys()[i2e[0]] i2eb={str(c):i2e[1]} u = u.values(i2eb) u = u.where(film_table.c.prime == id_in_table) engine.execute(u) # show_db(self,shh) def delete_row(self,id): stmnt=db.delete(film_table).where(film_table.c.prime == id) #to delete record from db queryy='SELECT DISTINCT film_table.image_path FROM film_table WHERE film_table.prime = '+'"'+str(id)+'"' ResultProxyy = connection.execute(queryy) ResultSett = ResultProxyy.fetchall() for i , it in enumerate(ResultSett): #to_del=str(it).rstrip("'),").lstrip("('") to_del=(it[0]) to_del=to_del.rstrip("\\0.jpg") print("to_del:\t",to_del) if os.path.isdir(to_del): #to delete record folder from pc shutil.rmtree(to_del) print("result directory deleted from pc") else: print("result directory does not exist") to_del=to_del.replace("\\result\\","") to_del_path=r'%s' % to_del #to_del_path=str(to_del_path).rstrip("'),").lstrip("('") to_del_path=to_del_path[0] print("to_del_path:\t",to_del_path) if os.path.isdir(to_del_path): #to delete record folder from pc shutil.rmtree(to_del_path) print("directory deleted from pc") else: print("directory does not exist") connection.execute(stmnt) #to delete record from db ''' if os.path.isfile(to_del): #to delete record from pc os.remove(to_del) print("Image deleted from pc") else: print("Image does not exist") connection.execute(stmnt) ''' def query_return_multi(self,company,project,report,weld_no,location,result): item=[] q="" r="" filters=["Company","Project","Report","Location","Weld_no","Result"] filter_items=[company,project,report,location,weld_no,result] for i,filter in enumerate(filter_items): if filter!="" : q="film_table."+filters[i+1]+", " r=r+filters[i]+"='"+filter_items[i]+"'"+" AND " q=q.rstrip(", ") r=r.rstrip(" AND ") query_return="SELECT DISTINCT "+q+" FROM film_table WHERE "+r+" AND "+"Result='1'" #print(query_return) ResultProxy_return = connection.execute(query_return) ResultSet__return = ResultProxy_return.fetchall() for i , it in enumerate(ResultSet__return): #item.append(str(it).rstrip("'),").lstrip("('")) item.append(it[0]) return(item) def query_return_single(self,column): item=[] query_return="SELECT DISTINCT film_table.%s FROM film_table WHERE Result='1'"%column ResultProxy_return = connection.execute(query_return) ResultSet__return = ResultProxy_return.fetchall() for i , it in enumerate(ResultSet__return): #print(it,"\n") #item.append(str(it).rstrip("'),").lstrip("('")) item.append(it[0]) return(item) def export_excel(self,company,project,report,location,weld_no,result,remark,date_of_report,password): global worksheet export_excel_engine = db.create_engine('sqlite:///db\\database1.db') export_excel_connection = export_excel_engine.connect() export_excel_metadata = db.MetaData() export_excel_film_table = db.Table('film_table', export_excel_metadata, autoload=True, autoload_with=export_excel_engine) #print(film_table.columns.keys()) #print all columns item=[] q="" r="" filters=["Company","Project","Report","Location","Weld_no","Result","Remark","Date_of_Report"] filters_dic={"Company":(1,2),"Project":(2,2),"Report":(1,10),"Location":(3,2),"Weld_no":(9,2),"Result":(9,8),"Remark":(9,9),"Date_of_Report":(2,10)} filter_items=[company,project,report,location,weld_no,result,remark,date_of_report] for i,filter in enumerate(filter_items): if filter!="" : q="film_table."+filters[i+1]+", " r=r+filters[i]+"='"+filter_items[i]+"'"+" AND " q=q.rstrip(", ") r=r.rstrip(" AND ") if r=="" : query_return="SELECT film_table.Description,film_table.Weld_no,film_table.Welder,film_table.Diameter,film_table.Film_Size,'',film_table.Defect_type,film_table.accept_reject,film_table.image_path,film_table.Remark,film_table.Date_of_Report FROM film_table ORDER BY film_table.Weld_no" else: query_return="SELECT film_table.Description,film_table.Weld_no,film_table.Welder,film_table.Diameter,film_table.Film_Size,'',film_table.Defect_type,film_table.accept_reject,film_table.image_path,film_table.Remark,film_table.Date_of_Report FROM film_table WHERE"+show_export+' AND '+r+" ORDER BY film_table.Weld_no" #export print(query_return) ResultProxy_return = connection.execute(query_return) ResultSet__return = ResultProxy_return.fetchall() for i , it in enumerate(ResultSet__return): #item.append(str(it).rstrip("'),").lstrip("('")) item.append(it[0]) export_excel_ResultProxy = connection.execute(query_return) export_excel_ResultSet = export_excel_ResultProxy.fetchall() workbook = openpyxl.load_workbook(filename="export.xlsx") #open excel worksheet = workbook.active #open sheet #worksheet = workbook.add_worksheet() #header_format = workbook.add_format({'font_name':'Courier New','bold': True, 'text_wrap': True, 'fg_color': '#FDE9D9', 'border': 1, #'align':'center', #'valign':'vcenter', #}) #header_format.set_text_wrap(False) #worksheet.merge_range(0,0,5,12,"Some long text to wrap in a cell",header_format) for i,item in enumerate(filters): #print titles pos_row=filters_dic[item][0] pos_column=filters_dic[item][1] worksheet.cell(row=pos_row+1, column=pos_column+1).value = filter_items[i] #worksheet.write(pos_row,pos_column, filter_items[i],header_format) print(item," ",pos_row," ",pos_column) prev_item='' prev_row_no=0 for row_no,row in enumerate(export_excel_ResultSet): #writes values to excel worksheet.cell(row=row_no+8, column=1).value = row_no+1 #for row numbers worksheet.cell(row=row_no+8, column=1).font=Font(name='Calibri',size=11) worksheet.cell(row=row_no+8, column=1).alignment=Alignment(horizontal="center", vertical="center") bd = Side(style='thin', color="000000") worksheet.cell(row=row_no+8, column=1).border = Border(left=bd, top=bd, right=bd, bottom=bd) for idx,item in enumerate(row): #print(item) #worksheet.write(row_no+10,idx+1, item,header_format) if idx==1: #if it was weld_no make hyperlink head, sep, tail=row[8].partition("\\"+company+"\\") seperated_img_path2=tail print("seperated_img_path2:\t",seperated_img_path2) worksheet.cell(row=row_no+8, column=idx+2).hyperlink = seperated_img_path2 #worksheet.cell(row=row_no+8, column=idx+2).hyperlink = row[8] worksheet.cell(row=row_no+8, column=idx+2).value = item worksheet.cell(row=row_no+8, column=idx+2).font=Font(name='Calibri',size=14,color="00B7D7") worksheet.cell(row=row_no+8, column=idx+2).border = Border(left=bd, top=bd, right=bd, bottom=bd) worksheet.cell(row=row_no+8, column=idx+2).alignment=Alignment(horizontal="center", vertical="center") else: worksheet.cell(row=row_no+8, column=idx+2).value = item #for other items worksheet.cell(row=row_no+8, column=idx+2).font=Font(name='Calibri',size=14,) worksheet.cell(row=row_no+8, column=idx+2).border = Border(left=bd, top=bd, right=bd, bottom=bd) worksheet.cell(row=row_no+8, column=idx+2).alignment=Alignment(horizontal="center", vertical="center") a=export_excel_ResultSet[0][10] worksheet.cell(row=filters_dic["Date_of_Report"][0]+1, column=filters_dic["Date_of_Report"][1]+1).value = export_excel_ResultSet[0][10] #writes 1st Date_of_Report to excel worksheet.merge_cells (start_row=row_no+8, start_column=9, end_row=row_no+8, end_column=10) #merging lasr column ''' for row_no,row in enumerate(export_excel_ResultSet): #writes values to excel #print(row) for idx,item in enumerate(row): #print(item) worksheet.write(row_no+1,idx, item,header_format) #worksheet.write('B1', 'lkjhg') ''' seperate_img_export(self,company,project,report,location,weld_no,result) #save_to0=str(QFileDialog.getExistingDirectory(self, "Select Excel Directory",directory=f"test1/{company}/{project}/{report}/")) #open in a specific folder save_to0=str(QFileDialog.getExistingDirectory(self, "Select Excel Directory")) print("default dir openfile:\t",f"\\test1\\{company}\\{project}\\{report}") ttime=time.strftime("%a_%d_%b_%Y_%H-%M-%S", time.localtime()) save_to=save_to0+'\\'+'export_'+ttime+'.xlsx' workbook.save(filename=save_to) workbook.close() set_excel_pass(save_to,password) #workbook.close() #wb = load_workbook('export.xlsx') #ws=wb['Sheet1'] #ws.column_dimensions['G'].width=12 #ws.column_dimensions['H'].width=35 #wb.save('export.xlsx') def show_seperate_img(self,id,axis): global film_table #print("\n\n\nID:\t",id,"\n\n\n\n") que="SELECT * from film_table WHERE film_table.prime="+(id) ResultProxy_ = connection.execute(que) ResultSet_ = ResultProxy_.fetchall() #print(ResultSet_[0][5]) #weld_no. que="SELECT film_table.prime,film_table.image_path FROM film_table WHERE Weld_no="+"'"+str(ResultSet_[0][5])+"'"+" AND "+"Company="+"'"+ResultSet_[0][1]+"'"+" AND "+"Project="+"'"+ResultSet_[0][2]+"'"+" AND "+"Result='1'" ResultProxy_ = connection.execute(que) ResultSet_ = ResultProxy_.fetchall() images=[] horizontal_image=[] for row,item in enumerate (ResultSet_): column_=str(item[1]) #image path #print(column_) images.append(column_) head, sep, tail = str(item[1]).partition('result') seperated_img_path=head+r"\\"+sep print("seperated_img_path\t:",seperated_img_path) image=cv2.imread(images[0]) width_ = int(image.shape[1]) height_ = int(image.shape[0]) dim = (width_, height_) resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA) horizontal_image=image for i,image_p in enumerate(images): if i>0 and i<=len(images): image=cv2.imread(image_p) # resize image resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA) horizontal_image=np.concatenate((horizontal_image, resized), axis=axis) #axis=1 horiz #axis=0 verti # if axis==1: # img_name=seperated_img_path+"\\"+"H"+".jpg" # cv2.imwrite(img_name, cv2.cvtColor(horizontal_image,cv2.COLOR_RGBA2BGR )) # elif axis==0: # img_name=seperated_img_path+"\\"+"V"+".jpg" # cv2.imwrite(img_name, cv2.cvtColor(horizontal_image,cv2.COLOR_RGBA2BGR )) if axis==0: cv2.namedWindow('horizontal_image', cv2.WINDOW_NORMAL) resized = imutils.resize(horizontal_image, height=horizontal_image.shape[0]) cv2.imshow('horizontal_image',resized) cv2.resizeWindow('horizontal_image', 500, 1280) elif axis==1: cv2.namedWindow('vertical_image', cv2.WINDOW_NORMAL) resized = imutils.resize(horizontal_image, width=horizontal_image.shape[1]) cv2.imshow('vertical_image',resized) cv2.resizeWindow('vertical_image', 1280, 500) cv2.waitKey(0) return(resized) def defected_rectangles(item): items=[] d_query_return="SELECT defect_table.xpos,defect_table.ypos,defect_table.width ,defect_table.height from defect_table where defect_table.film_table_prime="+"'"+item+"'" d_ResultProxy_return = connection.execute(d_query_return) d_ResultSet__return = d_ResultProxy_return.fetchall() for i , it in enumerate(d_ResultSet__return): #print(it,"\n") #items.append(str(it).rstrip("'),").lstrip("('")) items.append((it)) return(items) def remove_defected_rectangles(item,x,y,w,h): items=[] global defect_table d_query_return="SELECT defect_table.defect_prime, defect_table.xpos,defect_table.ypos,defect_table.width ,defect_table.height from defect_table where defect_table.film_table_prime="+"'"+item+"'" d_ResultProxy_return = connection.execute(d_query_return) d_ResultSet__return = d_ResultProxy_return.fetchall() for i , it in enumerate(d_ResultSet__return): #print(it,"\n") #items.append(str(it).rstrip("'),").lstrip("('")) if x>it[1]-int(it[3]/2) and xit[2]-int(it[4]/2) and y0 and i<=len(img_path_array): image=cv2.imread(image_p) # resize image resized = cv2.resize(image, dim, interpolation = cv2.INTER_AREA) horizontal_image=np.concatenate((horizontal_image, resized), axis=1) #axis=1 horiz #axis=0 verti vertical_image=np.concatenate((vertical_image, resized), axis=0) #axis=1 horiz #axis=0 verti img_name=seperated_img_path+"\\"+"H"+".jpg" cv2.imwrite(img_name, cv2.cvtColor(horizontal_image,cv2.COLOR_RGBA2BGR )) img_name=seperated_img_path+"\\"+"V"+".jpg" cv2.imwrite(img_name, cv2.cvtColor(vertical_image,cv2.COLOR_RGBA2BGR )) # print("written to:\t",img_name) for i , it in enumerate(ResultSet__return): #print(it[1]) head, sep, tail = str(it[8]).partition('result') seperated_img_path=head+r"\\"+sep #print("seperated_img_path:\t",seperated_img_path) head2, sep2, tail2=seperated_img_path.partition("\\"+company+"\\") seperated_img_path2=tail2 #print("seperated_img_path2:\t",seperated_img_path2) bd = Side(style='thin', color="000000") worksheet.cell(row=i+8, column=12).hyperlink = seperated_img_path2+"\\"+"H.jpg" worksheet.cell(row=i+8, column=12).value = "Horizontal" worksheet.cell(row=i+8, column=12).font=Font(name='Calibri',size=14,color="00B7D7") #horizontal hyper link 12th column worksheet.cell(row=i+8, column=12).border = Border(left=bd, top=bd, right=bd, bottom=bd) worksheet.cell(row=i+8, column=12).alignment=Alignment(horizontal="center", vertical="center") worksheet.cell(row=i+8, column=13).hyperlink = seperated_img_path2+"\\"+"V.jpg" worksheet.cell(row=i+8, column=13).value = "Vertical" worksheet.cell(row=i+8, column=13).font=Font(name='Calibri',size=14,color="00B7D7") #horizontal hyper link 13th column worksheet.cell(row=i+8, column=13).border = Border(left=bd, top=bd, right=bd, bottom=bd) worksheet.cell(row=i+8, column=13).alignment=Alignment(horizontal="center", vertical="center") count_weld_no2="SELECT DISTINCT Weld_no FROM film_table where Project="+"'"+project+"'" +" and " +"Report='"+report+"'"+" and "+"Company='"+company+"'"+" and "+"Result='1'" +" ORDER BY film_table.Weld_no" count_weld_no_return2 = connection.execute(count_weld_no2) count_weld_no_Result2 = count_weld_no_return2.fetchall() count_weld_no_array=[] for i , it in enumerate(count_weld_no_Result2): #print(it[0]) count_weld_no="SELECT count(Weld_no) FROM film_table where Project="+"'"+project+"'" +" and " +"Report='"+report+"'"+" and "+"Company='"+company+"'"+" and "+"Result='1'"+" and "+"Weld_no='"+it[0]+"'"+" ORDER BY film_table.Weld_no" count_weld_no_return = connection.execute(count_weld_no) count_weld_no_Result = count_weld_no_return.fetchall() #print("how many:\t",str(count_weld_no_Result[0]).rstrip(",) ").lstrip("("),"\tWeld_no:\t",it[0],"") #count_weld_no_array[i]=(it[0],int(str(count_weld_no_Result[0]).rstrip(",) ").lstrip("("))) count_weld_no_array.append((it[0],int(str(count_weld_no_Result[0]).rstrip(",) ").lstrip("(")))) #print("count_weld_no_array:\t",count_weld_no_array) prev_end_row=8 start_row=8 for j , weld_ in enumerate (count_weld_no_array): end_row=int(count_weld_no_array[j][1])+start_row #print("int(count_weld_no_array[j][1]):\t",int(count_weld_no_array[j][1]),"start_row:\t",start_row,"end_row:\t",end_row) worksheet.merge_cells (start_row=start_row, start_column=12, end_row=end_row-1, end_column=12) worksheet.merge_cells (start_row=start_row, start_column=13, end_row=end_row-1, end_column=13) start_row=end_row