# -*- coding: utf-8 -*- 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 from shutil import copyfile from copy import copy dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM') from openpyxl.styles.numbers import FORMAT_DATE_DATETIME 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", "FLATNESS": "AF", "Cen": "AG", "Weight": "AH", "Result": "AI", } 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", "61": "U" } # 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',read_only=False, keep_links=False, data_only=False) #wb0 = load_workbook(filename = './testfile.xlsx', read_only=False, keep_links=False, data_only=False) #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 applyStyleName(ws["B2"], "blackborder") ws["B3"] = codeno.replace("-", "") applyStyleName(ws["B3"], "blackborder") ws["B4"] = cust[0].sizestring applyStyleName(ws["B4"], "blackborder") ws["B5"] = cust[0].spec applyStyleName(ws["B5"], "blackborder") 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) applyStyleName(ws.cell(column= ccode, row=45, value = val.lot_no), "blackborder") 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 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 {1} order by lot_no asc, row_no asc """.format(codeString, filters) else: qMsData = """ select * from data_ms where code='{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", 3) #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductDimension(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} num = 0 for k in row2: if k.Size_Id in codeMap: sn = codeMap[k.Size_Id].lower()+"size" sizes[sn] = [] stdSizes[sn] = k 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]) maxMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUp) minMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUn) 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: pass #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) ws.cell(1, bp, value = "%s-size (bar)" % (keySize.upper().replace("SIZE", ""))) #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) c1 = LineChart() pp = ParagraphProperties(defRPr=cp) 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])*2.7 c1.title = "%s-size (X bar)" % (keySize.upper().replace("SIZE", "")) #c1.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) c1.y_axis.title = "Value(mm.)" c1.x_axis.title = "Lot 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) 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])*2.7 c2.title = "%s-size (R)" % (keySize.upper().replace("SIZE", "")) 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 def createCp(codeno, filters): global cursor global wb global dateStyle global codeMap ws = wb.create_sheet("CP Press") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductDimension(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} for k in row2: if k.Size_Id in codeMap: sn = codeMap[k.Size_Id].lower()+"size" sizes[sn] = [] stdSizes[sn] = k 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]) maxMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUp) minMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUn) 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])): ta[j][i] = l[i][j] #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) ws.cell(1, bp, value = "%s-size (bar)" % (keySize.upper().replace("SIZE", ""))) #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) c1 = LineChart() pp = ParagraphProperties(defRPr=cp) 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 c1.title = "%s-size (X bar)" % (keySize.upper().replace("SIZE", "")) #c1.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) c1.y_axis.title = "Value(mm.)" c1.x_axis.title = "Lot 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) 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 c2.title = "%s-size (R)" % (keySize.upper().replace("SIZE", "")) 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 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) #print(sql) sql2 = findProductDimension(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} for k in row2: if k.Size_Id in codeMap: sn = codeMap[k.Size_Id].lower()+"size" sizes[sn] = [] stdSizes[sn] = k 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(): #print(k, v) 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]) maxMaster = float(stdSizes[k].Std) + float(stdSizes[k].TolUp) minMaster = float(stdSizes[k].Std) + float(stdSizes[k].TolUn) 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 c1.title = "%s-size (actual) " % (k.upper().replace("SIZE", "")) 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 global stdSizeMapColumn 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_Dimension_ForInsProcess where ProductCode='{0}'""".format(codeString.replace("-", "")) #print(stdSizes) cursor.execute(stdSizes) rows = cursor.fetchall() for k,v in stdSizeMapColumn.items(): if k not in ["Weight", "Result"]: ws.column_dimensions[v].hidden = True enableCols = [] for r in rows: minv = float(r.Std) + float(r.TolUn) stdv = float(r.Std) maxv = float(r.Std) + float(r.TolUp) #print(r.Size_Name.replace("size", "").replace("Size", "").strip()) #print(r.Size_Name.replace("size", "").replace("Size", "").strip() in stdSizeMapColumn) if r.Size_Name.replace("size", "").replace("Size", "").strip() in stdSizeMapColumn: colName = stdSizeMapColumn[r.Size_Name.replace("size", "").replace("Size", "").strip()] #print(colName) #print(colName+"12") #print(min) ws.column_dimensions[colName].hidden = False ws.column_dimensions[colName].bestFit = True ws.column_dimensions[colName].width = 8 #print(colName) #print(ws.column_dimensions[colName].hidden) #print(ws.column_dimensions[colName].collapsed) #print(ws.column_dimensions[colName].bestFit) #print(ws.column_dimensions[colName].width) ws[colName+"12"] = minv #min applyStyleName(ws[colName+"12"], "border", "0.00") ws[colName+"11"] = stdv #std applyStyleName(ws[colName+"11"], "border", "0.00") ws[colName+"10"] = maxv #max applyStyleName(ws[colName+"10"], "border", "0.00") enableCols.append(r.Size_Name.replace("size", "").replace("Size", "").strip()) #print(enableCols) maxQuery = [ "max(%ssize) as %s"% ( x.lower(), x.upper() ) for x in enableCols] #print(maxQuery) if filters is not None: if codeno != "undefined": qt = "select {2} from data_ms where code='{0}' and ( (size1 > 0) or (size2 > 0)) and {1}".format(codeString, filters, ",".join(maxQuery)) else: qt = "select * from data_ms where ( (size1 > 0) or (size2 > 0)) and {1}".format(codeString, filters, ",".join(maxQuery)) else: if codeno != "undefined": qt = "select {1} from data_ms where code='{0}' and ( (size1 > 0 ) or (size2 > 0))".format(codeString, ",".join(maxQuery)) else: qt = "select * from data_ms where ( (size1 > 0) or (size2 > 0))".format(codeString, ",".join(maxQuery)) #print("qt = ", qt) cursor.execute(qt) rows = cursor.fetchall() for r in rows: #print(r.cursor_description) for c in r.cursor_description: #print(c) #print(getattr(r, c[0])) colName = stdSizeMapColumn[c[0]] ws[colName+"8"] = getattr(r, c[0]) applyStyleName(ws[colName+"8"], "border") maxQuery = [ "min(%ssize) as %s"% ( x.lower(), x.upper() ) for x in enableCols] #print(maxQuery) if filters is not None: if codeno != "undefined": qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery)) else: qt = "select * from data_ms where {1}".format(codeString, filters, ",".join(maxQuery)) else: if codeno != "undefined": qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery)) else: qt = "select * from data_ms".format(codeString, ",".join(maxQuery)) ''' if filters is not None: qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery)) else: qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery)) ''' #print(qt) cursor.execute(qt) rows = cursor.fetchall() for r in rows: #print(r.cursor_description) for c in r.cursor_description: #print(c) #print(getattr(r, c[0])) colName = stdSizeMapColumn[c[0]] ws[colName+"9"] = getattr(r, c[0]) applyStyleName(ws[colName+"9"], "border") #query data if filters is not None: qMsData = """ select * from data_ms where code='{0}' and {1} order by lot_no asc, row_no asc """.format(codeString, filters) else: qMsData = """ select * from data_ms where code='{0}' order by lot_no asc, row_no asc """.format(codeString) cursor.execute(qMsData) rows = cursor.fetchall() startRow = 15 for r in rows: isNG = False if r.dsize == 0 and r.hsize == 0 and r.tsize == 0: continue 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), "border", "m/d/yy") ws.cell(column = 2, row = startRow, value = r.code) applyStyleName(ws.cell(column = 2, row = startRow), "border") ws.cell(column = 3, row = startRow, value=r.lot_no) applyStyleName(ws.cell(column = 3, row = startRow), "border") 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), "border") c = ws.cell(column=5, row = startRow, value = int(r.row_no)) applyStyleName(ws.cell(column = 5, row = startRow), "border") c.number_format = "0" colstr = stdSizeMapColumn.get("D") ws[colstr + str(startRow)] = r.dsize applyStyleName(ws[colstr + str(startRow)], "border") if r.dsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("T") ws[colstr + str(startRow)] = r.tsize applyStyleName(ws[colstr + str(startRow)], "border") if r.tsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("H") applyStyleName(ws[colstr + str(startRow)], "border") if ( r.hdev is not None) and ( r.hsizeproxy is not None ) and (r.hsizeproxy != 'null') and ( r.hdev.strip() != "") and (r.hdev.strip() != 'null'): ws[colstr + str(startRow)] = "%s/%s" % (r.hsizeproxy.strip(), r.hdev.strip()) else: if ( r.hsizeproxy is not None ) and ( r.hsizeproxy.strip() != 'null'): proxyStrip = r.hsizeproxy.strip() if proxyStrip == "OK" or proxyStrip == "NG": ws[colstr + str(startRow)] = proxyStrip else: try: ws[colstr + str(startRow)] = float(proxyStrip.replace("+", "")) except Exception as e: pass if r.hsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("C") ws[colstr + str(startRow)] = r.csize applyStyleName(ws[colstr + str(startRow)], "border") if r.csizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("E") ws[colstr + str(startRow)] = r.esize applyStyleName(ws[colstr + str(startRow)], "border") if r.esizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("F") ws[colstr + str(startRow)] = r.fsize applyStyleName(ws[colstr + str(startRow)], "border") if r.fsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("G") ws[colstr + str(startRow)] = r.gsize applyStyleName(ws[colstr + str(startRow)], "border") if r.gsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("J") ws[colstr+str(startRow)] = r.jsize applyStyleName(ws[colstr + str(startRow)], "border") if r.jsizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("K") ws[colstr+str(startRow)] = r.ksize applyStyleName(ws[colstr + str(startRow)], "border") if r.ksizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("M") ws[colstr+str(startRow)] = r.msize applyStyleName(ws[colstr + str(startRow)], "border") if r.msizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("N") ws[colstr+str(startRow)] = r.nsize applyStyleName(ws[colstr + str(startRow)], "border") if r.nsizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("P") ws[colstr+str(startRow)] = r.psize applyStyleName(ws[colstr + str(startRow)], "border") if r.psizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("U") ws[colstr+str(startRow)] = r.usize applyStyleName(ws[colstr + str(startRow)], "border") if r.usizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("W") ws[colstr+str(startRow)] = r.wsize applyStyleName(ws[colstr + str(startRow)], "border") if r.wsizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("Weight") ws[colstr+str(startRow)] = r.weight applyStyleName(ws[colstr + str(startRow)], "border") colstr = stdSizeMapColumn.get("Result") applyStyleName(ws[colstr + str(startRow)], "border") if isNG == True: ws[colstr+ str(startRow)] = "NG" ws[colstr+ str(startRow)].font = Font(color = colors.RED) else: ws[colstr+ str(startRow)] = "OK" ''' 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) if codeno != "undefined": cust = findCustomer(codeno.replace("-", "")) ws["B3"] = codeno.replace("-", "") applyStyleName(ws["B3"], "border") if cust: ws["B2"] = cust[0].customer applyStyleName(ws["B2"], "border") ws["B4"] = cust[0].sizestring applyStyleName(ws["B4"], "border") ws["B5"] = cust[0].spec applyStyleName(ws["B5"], "border") def enableColFunc(ws, code, enableCols): global cursor global stdSizeMapColumn # std sizes stdSizes = """ select * from AllProduct_Dimension_ForInsProcess where ProductCode='{0}'""".format(code.replace("-", "")) #print(stdSizes) cursor.execute(stdSizes) rows = cursor.fetchall() for r in rows: if r.Size_Name.replace("size", "").replace("Size", "").strip() in stdSizeMapColumn: colName = stdSizeMapColumn[r.Size_Name.replace("size", "").replace("Size", "").strip()] #print(colName+"12") #print(min) ws.column_dimensions[colName].hidden = False stripSize = r.Size_Name.replace("size", "").replace("Size").strip() if stripSize not in enableCols: enableCols.append(stripSize) def applyStyleName(cell, styleName, format=None): cell.style = styleName if format is not None: #print(format) cell.number_format = format else: cell.number_format = "0.00" def createExcelDaily(codeno, filters, segment): global cursor global wb global dateStyle #wb["Data Measurement"].sheet_state = 'hidden' ws = wb["Daily"] codeString = "" for idx in range(1, 13): ws.row_dimensions[idx].hidden = True for k,v in stdSizeMapColumn.items(): if k not in ["Weight", "Result"]: ws.column_dimensions[v].hidden = True enableCols = [] if codeno != "" and codeno != "undefined": 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_Dimension_ForInsProcess where ProductCode='{0}'""".format(codeString.replace("-", "")) #print(stdSizes) cursor.execute(stdSizes) rows = cursor.fetchall() for r in rows: minv = float(r.Std) + float(r.TolUn) stdv = float(r.Std) maxv = float(r.Std) + float(r.TolUp) if r.Size_Name.replace("size", "").strip() in stdSizeMapColumn: colName = stdSizeMapColumn[r.Size_Name.replace("size", "").strip()] #print(colName+"12") #print(min) ws.column_dimensions[colName].hidden = False #ws[colName+"12"] = minv #min #ws[colName+"11"] = stdv #std #ws[colName+"10"] = maxv #max enableCols.append(r.Size_Name.replace("size", "").strip()) if codeno == "undefined": distinct_qt = "select distinct(code) code from data_ms where {1}".format(codeString, filters) cursor.execute(distinct_qt) rows = cursor.fetchall() #print("dist = ", distinct_qt) for r in rows: enableColFunc(ws, r.code, enableCols) maxQuery = [ "max(%ssize) as %s"% ( x.lower(), x.upper() ) for x in enableCols] #print(maxQuery) if filters is not None: if codeno != "undefined": qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery)) else: qt = "select {2} from data_ms where {1}".format(codeString, filters, ",".join(maxQuery)) else: if codeno != "undefined" and codeno != "": qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery)) else: qt = "select {1} from data_ms".format(codeString, ",".join(maxQuery)) #print("qt = ", qt) cursor.execute(qt) rows = cursor.fetchall() for r in rows: #print(r.cursor_description) #enableColFunc(ws, r.code, enableCols) for c in r.cursor_description: #print(c) #print(getattr(r, c[0])) if c[0] in stdSizeMapColumn: colName = stdSizeMapColumn[c[0]] #ws[colName+"8"] = getattr(r, c[0]) maxQuery = [ "min(%ssize) as %s"% ( x.lower(), x.upper() ) for x in enableCols] #print(maxQuery) if filters is not None: if codeno != "undefined": qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery)) else: qt = "select {2} from data_ms where {1}".format(codeString, filters, ",".join(maxQuery)) else: if codeno != "undefined": qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery)) else: qt = "select {1} from data_ms".format(codeString, ",".join(maxQuery)) ''' if filters is not None: qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery)) else: qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery)) ''' cursor.execute(qt) rows = cursor.fetchall() for r in rows: #print(r.cursor_description) for c in r.cursor_description: #print(c) #print(getattr(r, c[0])) if c[0] in stdSizeMapColumn: colName = stdSizeMapColumn[c[0]] #ws[colName+"9"] = getattr(r, c[0]) #query data joinString = joinSelect = "" #print("segment = ", segment) if segment != None: joinSelect = ",lt.*" joinString = "inner join LotTracking lt on ms.id = lt.datams_id and lt.machineGroup = '{0}'" .format(segment) filters = "ms."+filters else: joinSelect = ",lt.*" joinString = "inner join LotTracking lt on ms.id = lt.datams_id" .format(segment) filters = "ms."+filters #print(joinString) if filters is not None: if codeno != "undefined": qMsData = """ select ms.* {3} from data_ms ms {2} where code='{0}' and {1} order by lot_no asc, row_no asc """.format(codeString, filters, joinString, joinSelect) else: qMsData = """ select ms.* {3} from data_ms ms {2} where {1} order by lot_no asc, row_no asc """.format(codeString, filters, joinString, joinSelect) else: if codeno != "undefined": qMsData = """ select ms.* {2} from data_ms ms {1} where code='{0}' order by lot_no asc, row_no asc """.format(codeString, joinString, joinSelect) else: qMsData = """ select ms.* {2} from data_ms ms {1} order by lot_no asc, row_no asc """.format(codeString, joinString, joinSelect) #print(qMsData) cursor.execute(qMsData) rows = cursor.fetchall() startRow = 15 styleName = "border" for r in rows: isNG = False ''' if r.dsize == 0 and r.hsize == 0 and r.tsize == 0: continue ''' if int(r.row_no) == 1: ws.cell(column = 1, row = startRow, value = r.created_at).number_format = "m/d/yy" ws.cell(column = 1, row = startRow).style = styleName ws.cell(column = 2, row = startRow, value = r.code) ws.cell(column = 2, row = startRow).style = styleName ws.cell(column = 3, row = startRow, value=r.lot_no) ws.cell(column = 3, row = startRow).style = styleName 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" ws.cell(column = 4, row = startRow).style = styleName c = ws.cell(column=5, row = startRow, value = int(r.row_no)) c.style = styleName c.number_format = "0" ws["AJ"+str(startRow)] = r.emp_id ws["AJ"+str(startRow)].style = styleName ws["AK"+str(startRow)] = r.created_at applyStyleName(ws["AK"+str(startRow)], styleName, FORMAT_DATE_DATETIME) colstr = stdSizeMapColumn.get("D") ws[colstr + str(startRow)] = r.dsize applyStyleName(ws[colstr + str(startRow)], styleName) if r.dsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("T") ws[colstr + str(startRow)] = r.tsize applyStyleName(ws[colstr + str(startRow)], styleName) if r.tsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("H") applyStyleName(ws[colstr + str(startRow)], styleName) if ( r.hdev is not None) and ( r.hsizeproxy is not None ) and (r.hsizeproxy != 'null') and ( r.hdev.strip() != "") and (r.hdev.strip() != 'null'): ws[colstr + str(startRow)] = "%s/%s" % (r.hsizeproxy.strip(), r.hdev.strip()) else: if ( r.hsizeproxy is not None ) and ( r.hsizeproxy.strip() != 'null'): proxyStrip = r.hsizeproxy.strip() if proxyStrip == "OK" or proxyStrip == "NG": ws[colstr + str(startRow)] = proxyStrip else: try: ws[colstr + str(startRow)] = float(proxyStrip.replace("+", "")) except Exception as e: pass if r.hsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("C") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr + str(startRow)] = r.csize if r.csizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("E") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr + str(startRow)] = r.esize if r.esizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("F") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr + str(startRow)] = r.fsize if r.fsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("G") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr + str(startRow)] = r.gsize if r.gsizeOk == "NG": isNG = True ws[colstr + str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("J") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.jsize if r.jsizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("K") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.ksize if r.ksizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("M") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.msize if r.msizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("N") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.nsize if r.nsizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("P") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.psize if r.psizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("U") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.usize if r.usizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("W") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.wsize if r.wsizeOk == "NG": isNG = True ws[colstr+ str(startRow)].font = Font(color = colors.RED) colstr = stdSizeMapColumn.get("Weight") applyStyleName(ws[colstr + str(startRow)], styleName) ws[colstr+str(startRow)] = r.weight colstr = stdSizeMapColumn.get("Result") applyStyleName(ws[colstr + str(startRow)], styleName) if isNG == True: ws[colstr+ str(startRow)] = "NG" ws[colstr+ str(startRow)].font = Font(color = colors.RED) else: ws[colstr+ str(startRow)] = "OK" ''' 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. #print(wb.style_names) #print(ws["AJ"+str(startRow)].style) ws["AJ"+str(startRow)] = r.emp_id ws["AJ"+str(startRow)].style = styleName ws["AK"+str(startRow)] = r.created_at applyStyleName(ws["AK"+str(startRow)], styleName, FORMAT_DATE_DATETIME) ws["AL"+str(startRow)] = "Dimension out of specs" if isNG else "" applyStyleName(ws["AL"+str(startRow)], styleName) ws["AN"+str(startRow)] = str(r.machineId) applyStyleName(ws["AN"+str(startRow)], styleName) ws["AO"+str(startRow)] = str(r.machineGroup) applyStyleName(ws["AO"+str(startRow)], styleName) startRow += 1 #print(ws._charts) if codeno != "undefined": 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 segment = None #print(len(sys.argv)) if len(sys.argv) > 3: filter = sys.argv[3] #import ast #df = ast.literal_eval(filter) #print(df['date']) df = filter if len(sys.argv) > 4: segment = sys.argv[4] 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) if code != "undefined" and code != "": wb["Daily"].sheet_state = "hidden" createExcel(code, df) createChart(code, df) createXBarChart(code, df) #createCp(code, df) else: wb["Data Measurement"].sheet_state = "hidden" wb["Chart"].sheet_state = "hidden" createExcelDaily(code, df, segment); #createXRangeChart(code, df) ''' thin_border = Border(left=Side(style='thick'), right=Side(style='thick'), top=Side(style='thick'), bottom=Side(style='thick')) wtd['C1'].value = 1234 wtd["C1"].border = thin_border wtd['D1'].value = "Hello" ''' #print wb.get_sheet_names() #print wb0.get_sheet_names() ut = int(time.time()) if code != "undefined": filename = "measurement{0}_{1}.xlsx".format(code.replace("-", ""), ut) else: filename = "measurement_{1}.xlsx".format(code.replace("-", ""), ut) outfile = './public/excel/'+filename wb.save(filename = outfile) wb.close() print(filename)