from openpyxl import load_workbook import pyodbc import time import sys from pprint import pprint import datetime from openpyxl.styles import NamedStyle, Font, colors, Border, Side import openpyxl.drawing.text as drawingText from copy import deepcopy from openpyxl.chart import LineChart, Reference, Series from openpyxl.chart.text import RichText from openpyxl.drawing.text import RichTextProperties,Paragraph,ParagraphProperties, CharacterProperties import openpyxl.utils as utils from openpyxl.chart.text import RichText dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM') codeMap = { "001": "D", "002": "T", "003": "H", "02": "P", "03": "E", "39": "E", "04": "W", "05": "JK", "06": "F", "51": "F", "07": "G", "B2": "G", "08": "U", "09": "M", "10": "A", "11": "N", "12": "J", "13": "X", "14": "Flatness", "15": "K", "16": "Thickness", "17": "Q", "18": "S", "72": "X" } # Specifying the ODBC driver, server name, database, etc. directly #cnxn = None #cursor = None '''cursor.execute('select * from data_wb') rows = cursor.fetchall() for row in rows: print(row)''' #wb = load_workbook(filename = './measurement-temp.xlsx') wb = load_workbook(filename = './press-temp.xlsx') #wb.save(filename = './createfrompy.xlsx'); def findCustomer(code): global cursor global env tempResult = [] views = [] if env == "prod": views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"] else: views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"] ts = [] select = "distinct MI24 as unbStd, PRO1C as customer, cast(PRO8 as varchar) + ' ' + cast(PRO9 as varchar) + cast(PRO10 as varchar) + 'x' + cast(PRO11 as varchar ) + 'x' + cast(PRO12 as varchar) as sizestring, cast(PRO13 as varchar) + ' ' + cast(PRO14 as varchar) + ' ' + cast(PRO15 as varchar) + ' ' + cast(PRO16 as varchar) + ' ' + cast(PRO17 as varchar) + ' ' + cast(PRO18 as varchar) as spec" where = "PRO1 = '{0}'".format(code) for v in views: sqlQuery = "select {0} from {1} where {2}".format(select, v, where) ts.append(sqlQuery) merged = [] for t in ts: merged += cursor.execute(t) return merged def createWS(name, values, codeno, filters): global wb #wb.create_sheet(name) wo = wb['Data Measurement'] ws = wb.copy_worksheet(wo) ws.title = name cust = findCustomer(codeno.replace("-", "")) ws["B2"] = cust[0].customer ws["B3"] = codeno.replace("-", "") ws["B4"] = cust[0].sizestring ws["B5"] = cust[0].spec bcode = ord('B') ccode = 2 for idx, val in enumerate(values): #print(idx) c = chr(bcode + idx) #print(val.lot_no) if ( bcode + idx) > ord('Z'): r = bcode + idx - ord('Z') c = 'A' + chr(ord('A') + (r - 1)) #print("c = "+c) ws.cell(column= ccode, row=45, value = val.lot_no) ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no)) #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters) #print("master view") #print(ms) if ms: ws.cell(column = ccode, row=35, value = ms[0].MI24) #ws[c+'38'] = ms[0].STD_Balance ws.cell(column = ccode , row=37, value = ms[0].PRO6) #ws[c+'44'] = ms[0].PRO5 ws.cell(column = ccode, row = 43, value = ms[0].PRO10) ws.cell(column = ccode, row = 42, value = ms[0].PRO12) #if pressInfo: ws.cell(column = ccode , row=39, value = val.Press_UserName) ws.cell(column = ccode, row = 40, value = val.Press_McName) #print(type(pressInfo[0].Insp_Date)) #ws[c+'46'].number_format = "Short Date" ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date()) #print(type(ws[c+'46'].value)) #ws[c+'46'].style = dateStyle #ws[c+'46'].value = datetime.datetime(2010, 7, 21) #print(ws[c+'46'].number_format) #ws[c+'46']. = datetime.datetime ws.cell(column = ccode , row = 44, value = val.FG_Qty) dws = updateLength(val.lot_no) for j,v in enumerate(dws): ws.cell(column = ccode , row = 48+v.row_no , value = v.weight) dws = updateUb(val.lot_no) for j,v in enumerate(dws): ws.cell(column = ccode , row = 171+v.row_no , value = v.weight) ccode += 1 c1 = LineChart() c1.legend.position = "t"; c1.title = "Graph of Result Cp Unbalance" c1.title.font = Font(underline=Font.UNDERLINE_SINGLE) c1.y_axis.title = "Unb Weight" c1.y_axis.number_format = "#,##0.00" c1.x_axis.title = "Lot No" c1.width = 30 c1.height = 10 #print(len(values)) lt = Reference(ws, min_col=2, max_col=len(values)+1, min_row=45, max_row=45) data = Reference(ws, min_col=1, max_col=len(values)+1, min_row=34, max_row=34) maxUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=29, max_row=29) minUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=30, max_row=30) avgUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=31, max_row=31) vsStd = Reference(ws, min_col=1, max_col=len(values)+1, min_row=36, max_row=36) c1.add_data(data, from_rows=True, titles_from_data=True) c1.add_data(minUB, from_rows=True, titles_from_data=True) c1.add_data(avgUB, from_rows=True, titles_from_data=True) c1.add_data(vsStd, from_rows=True, titles_from_data=True) c1.add_data(maxUB, from_rows=True, titles_from_data=True) s1 = c1.series[0] s1.marker.symbol = "triangle" s1.graphicalProperties.line.noFill = True s1 = c1.series[1] s1.marker.symbol = "circle" s1.graphicalProperties.line.noFill = True s1 = c1.series[2] s1.marker.symbol = "square" s1.graphicalProperties.line.noFill = True s1 = c1.series[4] s1.marker.symbol = "diamond" s1.graphicalProperties.line.noFill = True c1.set_categories(lt) c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000", spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"), p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())]) ws.add_chart(c1, "A7") #newws.title = name #copy data cp ws #insert values in the sheet def findInMasterView(select, where): global cursor global env tempResult = [] views = [] if env == "prod": views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"] else: views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"] ts = [] for v in views: sqlQuery = "select {0} from {1} where {2}".format(select, v, where) ts.append(sqlQuery); qj = ';'.join(ts) #print(qj) merged = [] for t in ts: merged += cursor.execute(t) if len(merged) > 0: return merged return merged def findPressInfo(code, lot, year): global cursor global env fyear = year.replace("created_at", "Insp_Date") q = "select * from tb_fg_pressinfo_lotlist where ProductCode = '{0}' and LotNo = '{1}' and {2}" \ .format(code, lot, fyear) cursor.execute(q) return cursor.fetchall() def findProductDimension(codeno): stdSizes = """ select * from AllProduct_Dimension_ForInsProcess where ProductCode='{0}'""".format(codeno.replace("-", "")) #print(stdSizes) return stdSizes def findProductPress(codeno): stdSizes = """ select * from AllProduct_PressPosition_PressWeight where ProductCode='{0}'""".format(codeno.replace("-", "")) #print(stdSizes) return stdSizes def generateSQLDataMS(codeno, filters): if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] if filters is not None: qMsData = """ select * from data_ms where code='{0}' and (size1 > 0 or size2 > 0) and {1} order by lot_no asc, row_no asc """.format(codeString, filters) else: qMsData = """ select * from data_ms where code='{0}' and (size1 > 0 or size2 > 0 ) order by lot_no asc, row_no asc """.format(codeString) return qMsData def updateLength(lotno): global cursor global wb q = "select * from data_wb where lot_no = '{0}' and judgement='OK' order by row_no asc".format(lotno) #print(q); return cursor.execute(q) def updateUb(lotno): global cursor global wb q = "select * from data_wb where lot_no = '{0}' and judgement='NG' order by row_no asc".format(lotno) #print(q); return cursor.execute(q) def actualChart(ws, title, data, position_data, body_data, cat_pos, position): ''' for row in rows: ws.append(row) ''' if isinstance(position_data, str): rs = ws[position_data] else: rs = tuple(ws.iter_rows(**position_data)) for i in range(len(rs)): for j in range(len(rs[i])): print(rs[i][j]) rs[i][j].value = data[i][j] #ws["A1"].value = rows #print(cells) print(rs) #return c1 = LineChart() c1.width = 10 #c1.height = 10 c1.title = title c1.style = 13 c1.y_axis.title = 'Size' c1.x_axis.title = 'Lot' #print(ws.title+"?"+position_data) if isinstance(body_data, str): rdata = Reference(range_string=ws.title+"!"+body_data) else: rdata = Reference(ws, **body_data) c1.add_data(rdata, titles_from_data=True) if isinstance(cat_pos, str): lots = Reference(range_string=ws.title+"!"+cat_pos) else: lots = Reference(ws, **cat_pos) c1.set_categories(lots) # Style the lines s1 = c1.series[0] s1.marker.symbol = "triangle" s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline s1.graphicalProperties.line.noFill = True s2 = c1.series[1] s2.graphicalProperties.line.solidFill = "00AAAA" s2.graphicalProperties.line.dashStyle = "sysDot" s2.graphicalProperties.line.width = 100050 # width in EMUs s2 = c1.series[2] s2.smooth = True # Make the line smooth if isinstance(position,str): ws.add_chart(c1, position) else: ptemp = get_column_letter(position[1]) print("ptemp = ", ptemp) ws.add_chart(c1, ptemp + str(position[0])) def createXBarChart(codeno, filters): global cursor global wb global dateStyle global codeMap ws = wb.create_sheet("X Bar,R Chart") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductPress(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() #print(list(row2)) temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} pressType1 = pressType2 = pressType3 = "" for k in row2: if k.PressType_1 != "" and k.PressType_1 is not None: sizes["size1"] = [] stdSizes["size1"] = k pressType1 = k.PressType_1 if k.PressType_2 != "" and k.PressType_2 is not None: sizes["size2"] = [] stdSizes["size2"] = k pressType2 = k.PressType_2 if k.Press_T != "" and k.Press_T is not None: sizes["tpresssize"] = [] stdSizes["tpresssize"] = k pressType3 = "Thickness Press" num = 0 for r in row: #print(r) if r.lot_no not in temp: temp[r.lot_no] = [] temp[r.lot_no].append(r) for k,v in sizes.items(): sizes[k].append(getattr(r, k, None)) #print("temp = ", temp) #print("temp keys = ", temp.keys()); for k,v in temp.items(): #print("k = ", k) num = len(v) #break #print("num = ", num) if num > 5: num = 5 #filter only dimensions in master #print("TEMP == \n") #print(sizes) avgSize = {} minSize = {} maxSize = {} data2 = [] data2.append(["Lot No"]) for k,v in temp.items(): data2[0].append(k) #print("data 2 = ", data2) #create maxtrix in form ''' Lot, 1, 2, 3, 4, 5, max, min xxx, v, v, v, v, v, v, v ''' bp = 1 for keySize, keyValue in sizes.items(): l = [] n0 = 0 beginRow = 2 #print("key size = %s\n" %(keySize) ) for k,v in temp.items(): a0 = [] a0.append(k) sv = len(v) if sv > 5: sv = 5 n0 = sv for i in range(sv): a0.append(getattr(v[i], keySize, 0)) avg = sum(a0[1:]) / len(a0[1:]) a0.append(avg) r = max(a0[1:]) - min(a0[1:]) a0.append(r) #print("std size ", stdSizes[keySize]) max_min = 0 if k == "size1": max_min = stdSizes[k].PressWeight_1 if k == "size2": max_min = stdSizes[k].PressWeight_2 if k == "tpresssize": maxMaster = stdSizes[k].PressT + stdSizes[k].Press_T_Tol minMaster = stdSizes[k].PressT - stdSizes[k].Press_T_Tol else: maxMaster = minMaster = float(max_min) a0.append(maxMaster) a0.append(minMaster) l.append(a0) #print("l = ", l) header = ["Lot No"] + [str(i+1) for i in range(n0)] + ["avg", "r", "max", "min"] #print("header =", header) l = [header] + l; #print("l = ", l) ''' tranpose above matrix into form: Lot,xxx, xxx, xxx, 1, v, v, v 2, v, v, v ... ''' m, n = len(l), len(l[0]) #print("mxn = ", m, n) ta = [ [0 for j in range(m)] for i in range(n)] #print("ta = ", ta) for i in range(len(l)): for j in range(len(l[i])): try: ta[j][i] = l[i][j] except Exception as e: return #print("TA ", ta) #print(len(ta), len(ta[0])) #print("===\n") #print(beginRow, beginRow + len(ta) - 1, bp, (bp-1) + len(ta[0])) wsRows = tuple(ws.iter_rows(min_row=beginRow, max_row=beginRow + len(ta) - 1, min_col=bp, max_col=(bp-1)+len(ta[0]))) for i in range(len(wsRows)): for j in range(len(wsRows[i])): #print(i,j) wsRows[i][j].value = ta[i][j] wsRows[i][j].number_format = '0.00' bd = Side(style='medium', color="000000") wsRows[i][j].border = Border(left=bd, top=bd, right=bd, bottom=bd) if keySize == "size1": barTitle = pressType1 if keySize == "size2": barTitle = pressType2 if keySize == "tpresssize": barTitle = pressType3 ws.cell(1, bp, value = "%s (bar)" % (barTitle)) #bp += len(ta[0]) + 2 #continue cpos = utils.get_column_letter(bp) + "15" font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) c1 = LineChart() cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) minValues = Reference(ws, min_row=beginRow+9, min_col=bp+1, max_row=beginRow+9, max_col=(bp-1)+len(ta[0]) ) minSerie = Series(minValues, title="Min") c1.append(minSerie) maxValues = Reference(ws, min_row=beginRow+8, min_col=bp+1, max_row=beginRow+8, max_col=(bp-1)+len(ta[0]) ) maxSerie = Series(maxValues, title="Max") c1.append(maxSerie) avgValues = Reference(ws, min_row=beginRow+6, min_col=bp+1, max_row=beginRow+6, max_col=(bp-1)+len(ta[0]) ) avgSerie = Series(avgValues, title="Avg") avgSerie.marker.symbol = "circle" avgSerie.marker.graphicalProperties.solidFill = "4f81bd" # Marker filling avgSerie.graphicalProperties.line.solidFill = "4f81bd" c1.append(avgSerie) lots = Reference(ws,min_row=beginRow, min_col=bp+1, max_row=beginRow, max_col=(bp-1)+len(ta[0])) c1.set_categories(lots) c1.width = len(ta[0])*3.5 if keySize == "size1": c1.title = "%s (X Bar) " % (pressType1) if keySize == "size2": c1.title = "%s (X Bar) " % (pressType2) if keySize == "tpresssize": c1.title = "%s (X Bar) " % (pressType3) #c1.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) if keySize == "tpresssize": c1.y_axis.title = "Value(mm.)" else: c1.y_axis.title = "Value(mg.)" c1.x_axis.title = "Lot No." c1.x_axis.title.tx.rich.p[0].pPr = pp# Works! if c1.y_axis.title != None: c1.y_axis.title.tx.rich.p[0].pPr = pp# Works! c1.title.tx.rich.p[0].pPr = pp1# Works! c1.legend.position = "b" ws.add_chart(c1, cpos) cpos = utils.get_column_letter(bp) + "30" c2 = LineChart() #c2.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) rValues = Reference(ws, min_row=beginRow+7, min_col=bp+1, max_row=beginRow+7, max_col=(bp-1)+len(ta[0]) ) rSerie = Series(rValues, title="R") c2.append(rSerie) lots = Reference(ws,min_row=beginRow, min_col=bp+1, max_row=beginRow, max_col=(bp-1)+len(ta[0])) c2.set_categories(lots) c2.width = len(ta[0])*3.5 if keySize == "size1": c2.title = "%s (R) " % (pressType1) if keySize == "size2": c2.title = "%s (R) " % (pressType2) if keySize == "tpresssize": c2.title = "%s (R) " % (pressType3) c2.y_axis.title = "Value(mm.)" c2.x_axis.title = "Lot No." c2.x_axis.title.tx.rich.p[0].pPr = pp# Works! c2.y_axis.title.tx.rich.p[0].pPr = pp# Works! c2.title.tx.rich.p[0].pPr = pp1# Works! c2.legend.position = "b" ws.add_chart(c2, cpos) #move column cursor bp += len(ta[0]) + 5 #create each size table and chart types # actual chart def createPressCP(codeno, filters): global cursor global wb global dateStyle global codeMap ws = wb.create_sheet("Press CP") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductPress(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} #select distinct lot no bq = '''select ds.code,\ ds.lot_no, max(ds.size1) maxsize1, min(ds.size1) minsize1, max(ds.size2) maxsize2, min(ds.size2) minsize2 ,\ avg(ds.size1) avgSize1 , avg(ds.size2) avgSize2, stdev(ds.size1) sdsize1, stdev(ds.size2) sdsize2, \ empids = STUFF((SELECT ',' + str(emp_id) FROM data_ms b WHERE b.lot_no = ds.lot_no group by emp_id FOR XML PATH('')), 1, 2, ''), \ min(ds.created_at) minca,\ pp.Mold_D, pp.Center_D, pp.PressWeight_1, pp.PressWeight_2, pp.PressType_1, pp.PressType_2, pp.UWeight, pp.Press_Ton, pp.PO_Qty \ from data_ms ds \ inner join AllProduct_PressPosition_PressWeight pp on pp.ProductCode = Replace('{0}', '-', '') \ and pp.Lot_No = ds.lot_no \ where \ ds.code = '{0}' and ( (ds.size1 > 0) or (ds.size2 > 0) ) \ group by ds.code, ds.lot_no, pp.Press_Ton, pp.PO_Qty, pp.Mold_D, pp.Center_D, pp.PressWeight_1, pp.PressWeight_2, pp.PressType_1, pp.PressType_2, pp.UWeight'''.format(codeno) #print("bq ", bq) cursor.execute(bq) cp_rows = cursor.fetchall() #print(cp_rows) max1Ub = ["Max1 Weight."] min1Ub = ["Min1 Weight."] max2Ub = ["Max2 Weight."] min2Ub = ["Min2 Weight."] avg1Ub = ["Avg1 Weight."] avg2Ub = ["Avg2 Weight."] r1Ub = ["R1"] r2Ub = ["R2"] sd1 = ["sd1"] sd2 = ["sd2"] w1 = ["Weight(1) std"] w2 = ["Weight(2) std"] unitW = ["Unit Weight"] op = ["Operator"] press = ["Press M/C"] holes = ["Hole (mm)"] dias = ["Dia (mm)"] qtys = ["QTY"] lotnos = ["Lot no."] inspecs = ["Inspection Date"] for r in cp_rows: max1Ub.append(r.maxsize1) min1Ub.append(r.minsize1) max2Ub.append(r.maxsize2) min2Ub.append(r.minsize2) avg1Ub.append(r.avgSize1) avg2Ub.append(r.avgSize2) r1Ub.append(r.maxsize1 - r.minsize1) r2Ub.append(r.maxsize2 - r.minsize2) sd1.append(r.sdsize1) sd2.append(r.sdsize2) w1.append(r.PressWeight_1) w2.append(r.PressWeight_2) unitW.append(r.UWeight) op.append(r.empids) press.append(r.Press_Ton) holes.append(r.Mold_D) dias.append(r.Center_D) qtys.append(r.PO_Qty) lotnos.append(r.lot_no) #print("xdfdsf = "+r.minca.strftime("%m/%d/%Y %H:%M:%S")) inspecs.append(r.minca.strftime("%m/%d/%Y %H:%M:%S")) dataArray = [ max1Ub, #0 min1Ub, #1 max2Ub, #2 min2Ub, #3 avg1Ub, #4 avg2Ub,#5 r1Ub, #6 r2Ub, sd1, sd2,#9 w1, w2, unitW, #12 op, press, holes, dias, qtys, lotnos, inspecs ] #print(dataArray) startRow = 20 startCol = 2 for i, row in enumerate(dataArray): for j, o in enumerate(row): cell = ws.cell(startRow + i, startCol +j) cell.value = o applyStyleName(cell, "allborder") lc = LineChart() lc.title = "Result CP Press Chart" lc.y_axis.title = "Press.weight (g)" lc.x_axis.title = "Lot No." lc.width = len(dataArray[0]) * 2.5 #print("width ", lc.width) if lc.width <= 5: lc.width = 10 enables = [0, 1,2,3,4,5, 10,11 ] for r in enables: data = Reference(ws, min_row=startRow+r, max_row=startRow+r, min_col=startCol, max_col=startCol + len(dataArray[0])) lc.add_data(data, from_rows=True, titles_from_data=True) lots = Reference(ws, min_row=startRow + 18, max_row=startRow+18, min_col=startCol+1, max_col=startCol+len(dataArray[0]) ) lc.set_categories(lots) for se in lc.series: se.graphicalProperties.line.noFill = True se.marker.symbol = "auto" lc.series[6].graphicalProperties.line.noFill = False lc.series[7].graphicalProperties.line.noFill = False font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) lc.x_axis.title.tx.rich.p[0].pPr = pp# Works! lc.y_axis.title.tx.rich.p[0].pPr = pp# Works lc.title.tx.rich.p[0].pPr = pp1# Works! lc.legend.position = "t" lc.marker = True ws.add_chart(lc, "B2") #create press thickness #find max, min sizes = {} stdSizes = {} #select distinct lot no bq = '''select ds.code,\ ds.lot_no, max(ds.tpresssize) maxsize1, min(ds.tpresssize) minsize1,\ avg(ds.tpresssize) avgSize1 , stdev(ds.tpresssize) sdsize1, \ empids = STUFF((SELECT ',' + str(emp_id) FROM data_ms b WHERE b.lot_no = ds.lot_no group by emp_id FOR XML PATH('')), 1, 2, ''), \ min(ds.created_at) minca,\ pp.Mold_D, pp.Center_D, pp.Press_Ton, pp.PO_Qty, pp.Press_T \ from data_ms ds \ inner join AllProduct_PressPosition_PressWeight pp on pp.ProductCode = Replace('{0}', '-', '') \ and pp.Lot_No = ds.lot_no \ where \ ds.code = '{0}' and ( (ds.size1 > 0) or (ds.size2 > 0) or (ds.tpresssize > 0)) \ group by ds.code, ds.lot_no, pp.Press_Ton, pp.PO_Qty, pp.Mold_D, pp.Center_D, pp.Press_T'''.format(codeno) #print("bq ", bq) cursor.execute(bq) cp_rows = cursor.fetchall() #print(cp_rows) max1Ub = ["Max1 Weight."] min1Ub = ["Min1 Weight."] avg1Ub = ["Avg1 Weight."] r1Ub = ["R1"] sd1 = ["sd1"] w1 = ["Weight(1) std"] unitW = ["Unit Weight"] press_t = ["T Press"] op = ["Operator"] press = ["Press M/C"] holes = ["Hole (mm)"] dias = ["Dia (mm)"] qtys = ["QTY"] lotnos = ["Lot no."] inspecs = ["Inspection Date"] for r in cp_rows: max1Ub.append(r.maxsize1) min1Ub.append(r.minsize1) avg1Ub.append(r.avgSize1) r1Ub.append(r.maxsize1 - r.minsize1) sd1.append(r.sdsize1) press_t.append(r.Press_T) op.append(r.empids) press.append(r.Press_Ton) holes.append(r.Mold_D) dias.append(r.Center_D) qtys.append(r.PO_Qty) lotnos.append(r.lot_no) #print("xdfdsf = "+r.minca.strftime("%m/%d/%Y %H:%M:%S")) inspecs.append(r.minca.strftime("%m/%d/%Y %H:%M:%S")) dataArray = [ max1Ub, #0 min1Ub, #1 avg1Ub, #2 r1Ub, #3 sd1, #4 press_t, #5 op, press, holes, dias, qtys, lotnos, inspecs ] #print(dataArray) startRow = 20 startCol = 15 for i, row in enumerate(dataArray): for j, o in enumerate(row): cell = ws.cell(startRow + i, startCol +j) cell.value = o applyStyleName(cell, "allborder") lc = LineChart() lc.title = "Result CP Thickness press Chart" lc.y_axis.title = "TPress (mm)" lc.x_axis.title = "Lot No." lc.width = len(dataArray[0]) * 2.5 #print("width ", lc.width) if lc.width <= 5: lc.width = 10 enables = [0, 1,2,3,4,5 ] for r in enables: data = Reference(ws, min_row=startRow+r, max_row=startRow+r, min_col=startCol, max_col=startCol + len(dataArray[0])) lc.add_data(data, from_rows=True, titles_from_data=True) lots = Reference(ws, min_row=startRow + 11, max_row=startRow+11, min_col=startCol+1, max_col=startCol+len(dataArray[0]) ) lc.set_categories(lots) for se in lc.series: se.graphicalProperties.line.noFill = True se.marker.symbol = "auto" lc.series[5].graphicalProperties.line.noFill = False #lc.series[7].graphicalProperties.line.noFill = False font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) lc.x_axis.title.tx.rich.p[0].pPr = pp# Works! lc.y_axis.title.tx.rich.p[0].pPr = pp# Works lc.title.tx.rich.p[0].pPr = pp1# Works! lc.legend.position = "t" lc.marker = True ws.add_chart(lc, "O2") def applyStyleName(cell, styleName, format=None): cell.style = styleName if format is not None: #print(format) cell.number_format = format def createChart(codeno, filters): global cursor global wb global dateStyle global codeMap #ws = wb.create_sheet("chart2") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductPress(codeno) #print(filters) #print(sql) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} pressType1 = pressType2 = "" for k in row2: if k.PressType_1 != "" and k.PressType_1 is not None: sizes["size1"] = [] stdSizes["size1"] = k pressType1 = k.PressType_1 if k.PressType_2 != "" and k.PressType_2 is not None: sizes["size2"] = [] stdSizes["size2"] = k pressType2 = k.PressType_2 if k.Press_T != "" and k.Press_T is not None: sizes["tpresssize"] = [] stdSizes["tpresssize"] = k pressType3 = k.Press_T for r in row: #print(r) if r.lot_no not in temp: temp[r.lot_no] = [] temp[r.lot_no].append(r) for k,v in sizes.items(): sizes[k].append(getattr(r, k, None)) #filter only dimensions in master #print("TEMP == \n") #print(sizes) avgSize = {} minSize = {} maxSize = {} for k, v in sizes.items(): if len(v) > 0 and v[0] is not None: avgSize[k] = sum(v)/len(v) minSize[k] = min(v) maxSize[k] = max(v) #print("avg size \n",avgSize) #print("min size \n",minSize) #print("max size \n",maxSize) for k1,v1 in temp.items(): #print("\nlot %s \n" %( k1)) ws = wb.create_sheet("lot %s actual" %(k1)) i = 0 bp = 1 for k,v in sizes.items(): #print("%s actual" % (k)) data = [ ["Max (%s) size" % (k) ], ['Min'], ['Actual'], ['No.'], ['Lot No.'] ] nrow = len(data) for v2 in v1: #print("std size ", stdSizes[keySize]) max_min = 0 if k == "size1": max_min = stdSizes[k].PressWeight_1 if k == "size2": max_min = stdSizes[k].PressWeight_2 if k == "tpresssize": max_min = stdSizes[k].Press_T if max_min is None: continue if k == "size1" or k == "size2": maxMaster = minMaster = float(max_min) else: maxMaster = stdSizes[k].Press_T + stdSizes[k].Press_T_Tol minMaster = stdSizes[k].Press_T - stdSizes[k].Press_T_Tol data[0].append(maxMaster) data[1].append(minMaster) data[2].append(getattr(v2, k)) data[3].append(v2.row_no) data[4].append(v2.lot_no) #print(data) #gen chart #print("min col ", bp) wsRows = tuple(ws.iter_rows(min_row=1, max_row=len(data), min_col=bp, max_col= (bp - 1)+len(data[0]))) for i in range(len(wsRows)): for j in range(len(wsRows[i])): #print(i,j) #data[i][j]) #print(wsRows[i][j]) wsRows[i][j].value = data[i][j] if i != 3: wsRows[i][j].number_format = '0.00' bd = Side(style='medium', color="000000") wsRows[i][j].border = Border(left=bd, top=bd, right=bd, bottom=bd) cpos = utils.get_column_letter(bp) + "10" font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) c1 = LineChart() maxValues = Reference(ws, min_row=1, min_col=bp+1, max_row=1, max_col=(bp-1)+len(data[0]) ) maxSerie = Series(maxValues, title="Max") maxSerie.graphicalProperties.line.solidFill = "FF0000" c1.append(maxSerie) minValues = Reference(ws, min_row=2, min_col=bp+1, max_row=2, max_col=(bp-1)+len(data[0]) ) minSerie = Series(minValues, title="Min") minSerie.graphicalProperties.line.solidFill = "000000" c1.append(minSerie) acValues = Reference(ws, min_row=3, min_col=bp+1, max_row=3, max_col=(bp-1)+len(data[0]) ) acSerie = Series(acValues, title="Actual") acSerie.marker.graphicalProperties.solidFill = "4f81bd" # Marker filling acSerie.graphicalProperties.line.solidFill = "4f81bd" acSerie.marker.symbol = "circle" c1.append(acSerie) lots = Reference(ws,min_row=4, min_col=bp+1, max_row=4, max_col=(bp-1)+len(data[0])) c1.set_categories(lots) c1.width = len(data[0])*1.8 if k == "size1": c1.title = "%s (X Bar) " % (pressType1) if k == "size2": c1.title = "%s (X Bar) " % (pressType2) if k == "tpresssize": c1.title = "%s (X Bar) " % ("Thickness press") c1.y_axis.title = "Value(mm.)" c1.x_axis.title = "Item No." c1.x_axis.title.tx.rich.p[0].pPr = pp# Works! c1.y_axis.title.tx.rich.p[0].pPr = pp# Works! c1.title.tx.rich.p[0].pPr = pp1# Works! c1.legend.position = "b" ws.add_chart(c1, cpos) bp += len(data[0]) + 2 #print() #print("M"+str(bp)) #bp += coEndCell[1]+1+10 #print(ws.calculate_dimension()) #create each size table and chart types # actual chart def createExcel(codeno, filters): global cursor global wb global dateStyle wb["Styles"].sheet_state = 'hidden' ws = wb["Data Measurement"] codeString = "" if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] # std sizes stdSizes = """ select * from AllProduct_PressPosition_PressWeight where ProductCode='{0}'""".format(codeString.replace("-", "")) #print(stdSizes) cursor.execute(stdSizes) rows = cursor.fetchall() stdSizeMapColumn = { "D": "F", "T": "G", "H": "H", "A": "I", "B": "J", "C": "K", "E": "L", "F": "M", "G": "N", "J": "O", "K": "P", "L": "Q", "M": "R", "N": "S", "O": "T", "P": "U", "U": "V", "Q": "W", "R": "X", "S": "Y", "W": "Z", "Y": "AA", "Z": "AB", "X1": "AC", "X2": "AD", "Thickness": "AE", "Flatness": "AF", "Cen": "AG", "Weight": "AH", "Result": "AI", } ''' for k,v in stdSizeMapColumn.items(): if k not in ["Weight", "Result"]: ws.column_dimensions[v].hidden = True ''' enableCols = [] for r in rows: if r.PressType_1 != "" and r.PressType_1 is not None: ws["F8"] = r.PressWeight_1 ws["F10"] = r.PressType_1 else: ws.column_dimensions["F"].hidden = True if r.PressType_2 != "" and r.PressType_2 is not None: ws["G8"] = r.PressWeight_1 ws["G10"] = r.PressType_1 else: ws.column_dimensions["G"].hidden = True if r.Press_T != "" and r.Press_T is not None: ws["I8"] = r.Press_T else: ws.column_dimensions["I"].hidden = True if filters is not None: qMsData = """ select a.*, b.v1, b.v2, b.ac1, b.ac2, b.ac3, b.ac4, b.ac5, b.ac6, b.c0 from data_ms a left join press_cal b on a.lot_no = b.lot_no where code='{0}' and ((size1 > 0) or (size2 > 0 ) or (tpresssize > 0)) and {1} order by lot_no asc, row_no asc """.format(codeString, filters.replace("created_at", "a.created_at")) else: qMsData = """ select a.*, b.v1, b.v2, b.ac1, b.ac2, b.ac3, b.ac4, b.ac5, b.ac6, b.c0 from data_ms a left join press_cal b on a.lot_no = b.lot_no where code='{0}' and ((size1 > 0) or (size2 > 0 ) or (tpresssize > 0)) order by lot_no asc, row_no asc """.format(codeString) cursor.execute(qMsData) rows = cursor.fetchall() startRow = 11 for r in rows: isNG = False if int(r.row_no) == 1: ws.cell(column = 1, row = startRow, value = r.created_at).number_format = "m/d/yy" applyStyleName(ws.cell(column=1, row=startRow),"allborder", "m/d/yy hh:mm") ws.cell(column = 2, row = startRow, value = r.code) applyStyleName(ws.cell(column=2, row=startRow), "allborder") ws.cell(column = 3, row = startRow, value=r.lot_no) applyStyleName(ws.cell(column=3, row=startRow), "allborder") countLot = """ select count(*) as c from data_ms where lot_no = '{0}' """.format(r.lot_no) cursor.execute(countLot) countResult = cursor.fetchall() #print(countResult) ws.cell(column = 4, row = startRow, value = countResult[0].c).number_format = "0" applyStyleName(ws.cell(column=4, row=startRow), "allborder") c = ws.cell(column=5, row = startRow, value = int(r.row_no)) applyStyleName(ws.cell(column=5, row=startRow), "allborder") c.number_format = "0" colstr = "F" #print("x1", r.x1size) ws[colstr + str(startRow)] = r.size1 applyStyleName(ws[colstr + str(startRow)], "allborder") if r.size1Ok == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = "G" ws[colstr + str(startRow)] = r.size2 applyStyleName(ws[colstr + str(startRow)], "allborder") if r.size2Ok == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = "I" ws[colstr + str(startRow)] = r.tpresssize applyStyleName(ws[colstr + str(startRow)], "allborder") if r.tpresssizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = "J" ws[colstr + str(startRow)] = r.tpresssizeOk applyStyleName(ws[colstr + str(startRow)], "allborder") if r.tpresssizeOk == "NG": ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = "K" r.v1 = r.v1 or 0 r.v2 = r.v2 or 0 ws[colstr + str(startRow)] = r.v1 + r.v2 ws[colstr + str(startRow)].number_format = "#,##0.00" applyStyleName(ws[colstr + str(startRow)], "allborder") colstr = "H" if isNG == True: ws[colstr+ str(startRow)] = "NG" ws[colstr+ str(startRow)].font = Font(color = colors.RED) else: ws[colstr+ str(startRow)] = "OK" ws["N"+ str(startRow)] = r.ac1 or 0 applyStyleName(ws["N" + str(startRow)], "allborder") ws["O"+ str(startRow)] = r.ac2 or 0 applyStyleName(ws["O" + str(startRow)], "allborder") ws["P"+ str(startRow)] = r.ac3 or 0 applyStyleName(ws["P" + str(startRow)], "allborder") ws["Q"+ str(startRow)] = r.ac4 or 0 applyStyleName(ws["Q" + str(startRow)], "allborder") ws["R"+ str(startRow)] = r.ac5 or 0 applyStyleName(ws["R" + str(startRow)], "allborder") ws["S"+ str(startRow)] = r.ac6 or 0 applyStyleName(ws["S" + str(startRow)], "allborder") ws["T"+ str(startRow)] = r.c0 or 0 applyStyleName(ws["T" + str(startRow)], "allborder") ''' colstr = stdSizeMapColumn.get("X") ws[colstr+str(startRow)].value = r.xsize if r.xsizeOk == "NG": ws[colstr+ str(startRow)].font = Font(color = colors.RED) ''' #ws['U'+str(startRow)] = r.ysize or 0 #ysize.font= Font(color = colors.Red) #ws['V'+str(startRow)] = r.zsize startRow += 1 #print(ws._charts) cust = findCustomer(codeno.replace("-", "")) ws["B3"] = codeno.replace("-", "") if cust: ws["B2"] = cust[0].customer ws["B4"] = cust[0].sizestring ws["B5"] = cust[0].spec if __name__ == "__main__": env = sys.argv[1] code = sys.argv[2] #print(len(sys.argv)) df = None if len(sys.argv) == 4: filter = sys.argv[3] #import ast #df = ast.literal_eval(filter) #print(df['date']) df = filter global cnxn global cursor if env == "prod": cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.253;DATABASE=OB2011DB;UID=user1;PWD=1234') cursor = cnxn.cursor() else: cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=OB2011DB;UID=admin;PWD=1234') cursor = cnxn.cursor() #print("code is "+code) createExcel(code, df) createChart(code, df) createXBarChart(code, df) createPressCP(code, df) #createXRangeChart(code, df) ut = int(time.time()) filename = "press{0}_{1}.xlsx".format(code.replace("-", ""), ut) outfile = './public/excel/'+filename wb.save(filename = outfile) print(filename)