def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import Shop data as an array (list of lists) rowSHO = cF.findHeader(pmpDataSht, 'SHOP GROUP') rowEnd = cF.findHeader(pmpDataSht, 'SHAFT') colEnd = pmpDataSht[rowSHO + 1, 0].end('right').column rngSHO = pmpDataSht[rowSHO + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colItemN = rngSHO[0].index("Item Notes") colDesc = rngSHO[0].index("Description") colItemID = rngSHO[0].index("Item ID") # Build BOM elements strShopItemID = rngSHO[1][colItemID] strShopDescription = rngSHO[1][colDesc] strShopItemNotes = rngSHO[1][colItemN] # Write to TempSheet tmpSht[0, 0].value = strShopItemID tmpSht[0, 1].value = strShopDescription tmpSht[0, 2].value = strShopItemNotes
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import Shipping data as an array (list of lists) rowSHP = cF.findHeader(pmpDataSht, 'SHIPPING GROUP') rowEnd = cF.findHeader(pmpDataSht, 'COAT/PAINT GROUP') colEnd = pmpDataSht[rowSHP+1,0].end('right').column rngSHP = pmpDataSht[rowSHP+1:rowEnd,0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngSHP[0].index("Series") colItemN = rngSHP[0].index("Item Notes") colDesc = rngSHP[0].index("Description") # Define strings to match in description of the SHIPPING group (and item notes) searchPattern = re.compile("T.") # Main loop that populates the array (list of lists) for SHIPPING group data for rowi in range(1,len(rngSHP)): desc = rngSHP[rowi][colDesc] itemN = rngSHP[rowi][colItemN] shipExtDesc = desc + " " + itemN # Search using regular expressions descMatch = re.search(searchPattern, shipExtDesc) if descMatch: # Series rngSHP[rowi][colSeries] = descMatch.group(0) # Export modified SHIPPING data back to worksheet pmpDataSht[rowSHP+2,0].value = rngSHP[1:]
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import EXISTING ESP PUMP data as an array (list of lists) rowPMP = cF.findHeader(pmpDataSht, 'EXISTING ESP PUMPS') rowEnd = cF.findHeader(pmpDataSht, 'COMMON') colEnd = pmpDataSht[rowPMP + 1, 0].end('right').column rngPMP = pmpDataSht[rowPMP + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngPMP[0].index("Series") colBPD = rngPMP[0].index("BPD") colType = rngPMP[0].index("Type") colHsgNum = rngPMP[0].index("Housing Number") colStgCount = rngPMP[0].index("Stage Count") colDesc = rngPMP[0].index("Description") # Define strings to match in description of pumps searchPattern1 = re.compile( "PU?MP,(?P<series>T.) ?(?P<BPD>\d+) (?P<stgtype>[a-zA-Z]+) (?P<stgcount>\d+)STG \#(?P<hsgnum>\d+)" ) searchPattern2 = re.compile( "PU?MP,(?P<series>T.) ?(?P<BPD>\d+) (?P<stgtype1>[a-zA-Z]+)[ -](?P<stgtype2>[a-zA-z]+) (?P<stgcount>\d+)/\d+B \#(?P<hsgnum>\d+)" ) SPs = [searchPattern1, searchPattern2] # Main loop that populates the array (list of lists) for PUMP data for rowi in range(1, len(rngPMP)): desc = rngPMP[rowi][colDesc] # Search using regular expressions descMatch = [re.search(SP, desc) for SP in SPs] if any(descMatch): ind = [i for i, v in enumerate(descMatch) if v][0] # Series rngPMP[rowi][colSeries] = descMatch[ind].group('series') # BPD rngPMP[rowi][colBPD] = descMatch[ind].group('BPD') # Type if ind == 0: rngPMP[rowi][colType] = descMatch[ind].group('stgtype') else: rngPMP[rowi][colType] = descMatch[ind].group('stgtype1') + "-" + \ descMatch[ind].group('stgtype2') # Housing number rngPMP[rowi][colHsgNum] = descMatch[ind].group('hsgnum') # Stage count rngPMP[rowi][colStgCount] = descMatch[ind].group('stgcount') # Export modified PUMP data back to worksheet pmpDataSht[rowPMP + 2, 0].value = rngPMP[1:]
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import H&B GROUP data as an array (list of lists) rowOR = cF.findHeader(pmpDataSht, 'O-RING GROUP') rowEnd = cF.findHeader(pmpDataSht, 'SPACER GROUP') colEnd = pmpDataSht[rowOR+1,0].end('right').column rngOR = pmpDataSht[rowOR+1:rowEnd,0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngOR[0].index("Series") colMat = rngOR[0].index("Material") colDuro = rngOR[0].index("Durometer") colTyp = rngOR[0].index("Type Designation") colApp = rngOR[0].index("Application") colItemN = rngOR[0].index("Item Notes") colDesc = rngOR[0].index("Description") # Define strings to match in description & item notes of Oring group searchPattern = re.compile("(GROUP,P.*?MP) (T.)") matList = ["AFL","HSN","VIT","CHEM","FFKM"] duroList = ["75","80","90"] typeList = ["CMP","FLT"] appList = ["UHP","XHT"] # Main loop that populates the array (list of lists) for oring group data for rowi in range(1,len(rngOR)): desc = rngOR[rowi][colDesc] itemN = rngOR[rowi][colItemN] orExtDesc = desc + " " + itemN # Search using regular expressions descMatch = re.search(searchPattern, orExtDesc) if descMatch: # Series rngOR[rowi][colSeries] = descMatch.group(2) # Material rngOR[rowi][colMat] = ", ".join(orMat for orMat in matList if orMat in orExtDesc) # Durometer rngOR[rowi][colDuro] = ", ".join(dur for dur in duroList if dur in orExtDesc) # Type designation rngOR[rowi][colTyp] = ", ".join(typ for typ in typeList if typ in orExtDesc) # Application rngOR[rowi][colApp] = ", ".join(app for app in appList if app in orExtDesc) # Export modified O-RING GROUP data back to worksheet pmpDataSht[rowOR+2,0].value = rngOR[1:]
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import Coat/paint data as an array (list of lists) rowFIN = cF.findHeader(pmpDataSht, 'COAT/PAINT GROUP') rowEnd = cF.findHeader(pmpDataSht, 'SHOP GROUP') colEnd = pmpDataSht[rowFIN + 1, 0].end('right').column rngFIN = pmpDataSht[rowFIN + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colType = rngFIN[0].index("Category") colItemN = rngFIN[0].index("Item Notes") colDesc = rngFIN[0].index("Description") colItemID = rngFIN[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpFinish = tmpSht[0, 0].value tmpSht[0, 0].value = "" # Finish type designation if pmpFinish == "Paint": strFinishType = "PAINT" elif pmpFinish == "Monel Coating": strFinishType = "COAT" # Initialize BOM elements strFinishItemID = "NEW" strFinishDescription = strFinishType strFinishItemNotes = "" # Find a part number matching the coat/paint group (if any existing) for rowi in range(1, len(rngFIN)): if strFinishType == rngFIN[rowi][colType]: strFinishItemID = rngFIN[rowi][colItemID] strFinishDescription = rngFIN[rowi][colDesc] strFinishItemNotes = rngFIN[rowi][colItemN] break # Write to TempSheet tmpSht[0, 0].value = strFinishItemID tmpSht[0, 1].value = strFinishDescription tmpSht[0, 2].value = strFinishItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import COMMON (com parts) data as an array (list of lists) rowCOM = cF.findHeader(pmpDataSht, 'COMMON') rowEnd = cF.findHeader(pmpDataSht, 'STAGE GROUP') colEnd = pmpDataSht[rowCOM+1,0].end('right').column rngCOM = pmpDataSht[rowCOM+1:rowEnd,0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngCOM[0].index("Series") colStgType = rngCOM[0].index("Stage Type") colShaftSize = rngCOM[0].index("Shaft Size") colDesignation = rngCOM[0].index("Designation") colItemN = rngCOM[0].index("Item Notes") colDesc = rngCOM[0].index("Description") # Define strings to match in description of the COM PART group (and item notes) searchPattern = re.compile("(COM PART,PU?MP) (?P<series>T.) (?P<stgtype1>[a-zA-Z]+)[ -](?P<stgtype2>[a-zA-Z]+) (?P<size>\.\d+)") listDesig = ["UHP","NLP","WAG"] # Main loop that populates the array (list of lists) for COM PART group data for rowi in range(1,len(rngCOM)): desc = rngCOM[rowi][colDesc] itemN = rngCOM[rowi][colItemN] comExtDesc = desc + " " + itemN # Search using regular expressions descMatch = re.search(searchPattern, comExtDesc) if descMatch: # Series rngCOM[rowi][colSeries] = descMatch.group('series') # Stage type rngCOM[rowi][colStgType] = descMatch.group('stgtype1') + "-" + descMatch.group('stgtype2') # Shaft size rngCOM[rowi][colShaftSize] = descMatch.group('size') # Designation if descMatch.group('series') == "TA": rngCOM[rowi][colDesignation] = "NLP" else: rngCOM[rowi][colDesignation] = ", ".join(dTyp for dTyp in listDesig if dTyp in comExtDesc) # Export modified COMMON data back to worksheet pmpDataSht[rowCOM+2,0].value = rngCOM[1:]
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import SPACER data as an array (list of lists) rowSPC = cF.findHeader(pmpDataSht, 'SPACER GROUP') rowEnd = cF.findHeader(pmpDataSht, 'SHIPPING GROUP') colEnd = pmpDataSht[rowSPC + 1, 0].end('right').column rngSPC = pmpDataSht[rowSPC + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colShaftSize = rngSPC[0].index("Shaft Size") colMat = rngSPC[0].index("Material") colItemN = rngSPC[0].index("Item Notes") colDesc = rngSPC[0].index("Description") # Useful info shaftList = ["5/8", "11/16", "1/2", "7/8", ".50", ".62", ".69", ".88"] shaftdict = {"5/8":0.62, "11/16":0.69, "1/2":0.5, "7/8":0.88, ".50":0.5, \ ".62": 0.62, ".69": 0.69, ".88": 0.88} strMaterial = ["304", "N1"] # Main loop that populates the array (list of lists) for COM PART group data for rowi in range(1, len(rngSPC)): desc = rngSPC[rowi][colDesc] itemN = rngSPC[rowi][colItemN] spcExtDesc = desc + " " + itemN # Shaft size for siz in shaftList: if siz in spcExtDesc and siz in shaftdict: rngSPC[rowi][colShaftSize] = shaftdict[siz] # Material (standard is 304 going forward) for mat in strMaterial: if mat in spcExtDesc: rngSPC[rowi][colMat] = mat # Export modified O-RING GROUP data back to worksheet pmpDataSht[rowSPC + 2, 0].value = rngSPC[1:]
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import Shipping data as an array (list of lists) rowSHP = cF.findHeader(pmpDataSht, 'SHIPPING GROUP') rowEnd = cF.findHeader(pmpDataSht, 'COAT/PAINT GROUP') colEnd = pmpDataSht[rowSHP + 1, 0].end('right').column rngSHP = pmpDataSht[rowSHP + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngSHP[0].index("Series") colItemN = rngSHP[0].index("Item Notes") colDesc = rngSHP[0].index("Description") colItemID = rngSHP[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value tmpSht[0, 0].value = "" # Initialize BOM elements strShippingItemID = "NEW" strShippingDescription = "GROUP,PMP " + pmpSeries + " SHIPPING COMPONENTS" strShippingItemNotes = "" # Find a part number matching the Shipping group (if any existing) for rowi in range(1, len(rngSHP)): if pmpSeries == rngSHP[rowi][colSeries]: strShippingItemID = rngSHP[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strShippingItemID tmpSht[0, 1].value = strShippingDescription tmpSht[0, 2].value = strShippingItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import COUPLING data as an array (list of lists) rowCPLG = cF.findHeader(pmpDataSht, 'COUPLING') rowEnd = pmpDataSht[rowCPLG + 1, 0].end('down').row colEnd = pmpDataSht[rowCPLG + 1, 0].end('right').column rngCPLG = pmpDataSht[rowCPLG + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngCPLG[0].index("Series") colStgType = rngCPLG[0].index("Stage Type") colSize = rngCPLG[0].index("Size") colMat = rngCPLG[0].index("Material") colMS = rngCPLG[0].index("MS") colItemN = rngCPLG[0].index("Item Notes") colDesc = rngCPLG[0].index("Description") # Define strings to match description of coupling (and item notes) searchPattern = re.compile( "CO?U?PLI?N?G,PU?MP (?P<series>T.) (?P<stgtype>\w+) (?P<size>\d+/\d+)") matList = ["MNL", "NIT", "INC"] # Main loop that populates the array (list of lists) for coupling data for rowi in range(1, len(rngCPLG)): desc = rngCPLG[rowi][colDesc] itemN = rngCPLG[rowi][colItemN] if itemN is None: itemN = "" cplgExtDesc = desc + " " + itemN # Search description using regular expressions descMatch = re.search(searchPattern, cplgExtDesc) if descMatch: # Series rngCPLG[rowi][colSeries] = descMatch.group('series') # Stage type rngCPLG[rowi][colStgType] = descMatch.group('stgtype') # Shaft size rngCPLG[rowi][colSize] = descMatch.group('size') # Material rngCPLG[rowi][colMat] = ", ".join(cMat for cMat in matList if cMat in cplgExtDesc) # Export modified COUPLING data back to the worksheet pmpDataSht[rowCPLG + 2, 0].value = rngCPLG[1:]
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import Shaft data as an array (list of lists) rowShft = cF.findHeader(pmpDataSht, 'SHAFT') rowEnd = cF.findHeader(pmpDataSht, 'HOUSING') colEnd = pmpDataSht[rowShft + 1, 0].end('right').column rngShft = pmpDataSht[rowShft + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngShft[0].index("Series") colStgType = rngShft[0].index("Stage Type") colShaftSize = rngShft[0].index("Shaft Size") colShaftNum = rngShft[0].index("Shaft Number") colMat = rngShft[0].index("Material") colConn = rngShft[0].index("Connection Type") colItemN = rngShft[0].index("Item Notes") colDesc = rngShft[0].index("Description") colItemID = rngShft[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpShaftSize = tmpSht[2, 0].value pmpShaftNum = tmpSht[3, 0].options(numbers=int).value pmpShaftMat = tmpSht[4, 0].value pmpConn = tmpSht[5, 0].value tmpSht[0:6, 0].value = "" # Useful transformations and associations # 1. Stage type association if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]: strStageType = "AR-FLT" else: strStageType = "AR-CMP" # 2. Shaft size to fraction format if pmpShaftSize == 0.62: strShaftSize = "5/8" elif pmpShaftSize == 0.69: strShaftSize = "11/16" elif pmpShaftSize == 0.50: strShaftSize = "1/2" elif pmpShaftSize == 0.88: strShaftSize = "7/8" # 3. Shaft number to correct format if pmpShaftNum < 10: strShaftNum = "0" + str(pmpShaftNum) else: strShaftNum = str(pmpShaftNum) # Initialize BOM elements strShaftItemID = "NEW" strShaftDescription = "SHAFT,PMP " + pmpSeries + " " + strStageType + " " + \ strShaftSize + " #" + strShaftNum + " " + pmpShaftMat + "*" strShaftItemNotes = pmpConn # Find a part number matching the SHAFT (if any existing) for rowi in range(1, len(rngShft)): if pmpSeries == rngShft[rowi][colSeries] and \ strStageType == rngShft[rowi][colStgType] and \ pmpShaftSize == rngShft[rowi][colShaftSize] and \ pmpShaftNum == rngShft[rowi][colShaftNum] and \ pmpShaftMat == rngShft[rowi][colMat] and \ pmpConn == rngShft[rowi][colConn]: strShaftItemID = rngShft[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strShaftItemID tmpSht[0, 1].value = strShaftDescription tmpSht[0, 2].value = strShaftItemNotes
def main(): # Define PumpData sheet object wb = xw.Book('ESP_config.xlsm') #wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import H&B GROUP data as an array (list of lists) rowHB = cF.findHeader(pmpDataSht, 'H&B GROUP') rowEnd = cF.findHeader(pmpDataSht, 'O-RING GROUP') colEnd = pmpDataSht[rowHB + 1, 0].end('right').column rngHB = pmpDataSht[rowHB + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngHB[0].index("Series") colStgType = rngHB[0].index("Stage Type") colShaftSize = rngHB[0].index("Shaft Size") colHBMaterial = rngHB[0].index("H&B Material") colConnectionType = rngHB[0].index("Connection Type") colBSMaterial = rngHB[0].index("Bearing Support Material") #colCoat = rngHB[0].index("Coating Type") # NO COATING DONE ON H&B ANYMORE colTemp = rngHB[0].index("Temperature") colItemN = rngHB[0].index("Item Notes") colDesc = rngHB[0].index("Description") colItemID = rngHB[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpShaftSize = str(tmpSht[2, 0].value) pmpHBMaterial = tmpSht[3, 0].value pmpConnectionType = tmpSht[4, 0].value pmpStagesMaterial = tmpSht[5, 0].value pmpAppType = tmpSht[6, 0].value tmpSht[0:7, 0].value = "" # Stage type association for H&B group if pmpStageType in ["FLT", "AR-FLT"]: strHBStageType = "AR-FLT" elif pmpStageType in ["CMP", "AR-CMP"]: strHBStageType = "AR-CMP" else: strHBStageType = "AR-MDLR" # Temperature rating string if pmpAppType == "XHT": strTemp = "XHT" else: strTemp = "" # Shaft size converted to a string of the correct format for H&B group shaftSizeMatch = re.search("\.\d+", pmpShaftSize) strShaftSize = shaftSizeMatch.group(0) # Initialize the BOM elements strHBItemID = "NEW" strHBDescription = "GROUP,PMP " + pmpSeries + " " + strHBStageType + " " + \ "H&B " + strShaftSize + "*" strHBItemNotes = pmpHBMaterial + " " + pmpConnectionType + " " + pmpStagesMaterial # Find a part number matching the H&B group (if any existing) for rowi in range(1, len(rngHB)): if pmpSeries == rngHB[rowi][colSeries] and \ strHBStageType[3:] in rngHB[rowi][colStgType] and \ strShaftSize in str(rngHB[rowi][colShaftSize]) and \ pmpHBMaterial == rngHB[rowi][colHBMaterial] and \ pmpConnectionType == rngHB[rowi][colConnectionType] and \ pmpStagesMaterial == rngHB[rowi][colBSMaterial] and \ strTemp == rngHB[rowi][colTemp]: strHBItemID = rngHB[rowi][colItemID] oldStgType = rngHB[rowi][colStgType] if oldStgType == "AR-CMP/FLT": newStgType = "AR-C/F" else: newStgType = oldStgType strHBDescription = "GROUP,PMP " + pmpSeries + " " + newStgType \ + " " + "H&B " + str(rngHB[rowi][colShaftSize]) + "*" break # Write to TempSheet tmpSht[0, 0].value = strHBItemID tmpSht[0, 1].value = strHBDescription tmpSht[0, 2].value = strHBItemNotes
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import H&B GROUP data as an array (list of lists) rowHB = cF.findHeader(pmpDataSht, 'H&B GROUP') rowEnd = cF.findHeader(pmpDataSht, 'O-RING GROUP') colEnd = pmpDataSht[rowHB + 1, 0].end('right').column rngHB = pmpDataSht[rowHB + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngHB[0].index("Series") colStgType = rngHB[0].index("Stage Type") colShaftSize = rngHB[0].index("Shaft Size") colHBMaterial = rngHB[0].index("H&B Material") colConnectionType = rngHB[0].index("Connection Type") colBSMaterial = rngHB[0].index("Bearing Support Material") #colCoat = rngHB[0].index("Coating Type") NO COATING DONE ON H&B anymore colTemp = rngHB[0].index("Temperature") colItemN = rngHB[0].index("Item Notes") colDesc = rngHB[0].index("Description") # Define strings to match in description of the H&B group (and item notes) searchPattern1 = re.compile( "(GROUP,P.*?MP) (?P<series>T.) (AR).(?P<stgtyp>[a-zA-Z/]+)") searchPattern2 = re.compile("\.\d+") HBMatList = ["4SS", "STL", "17-4", "DPX"] BSM = ["N1", "N2", "N3", "N4", "DPX"] coatType = ["NDP", "FPS", "CRP", "SF2"] # Main loop that populates the array (list of lists) for H&B group data for rowi in range(1, len(rngHB)): desc = rngHB[rowi][colDesc] itemN = rngHB[rowi][colItemN] hbExtDesc = desc + " " + itemN # Search using regular expressions descMatch = re.search(searchPattern1, hbExtDesc) if descMatch: # Series rngHB[rowi][colSeries] = descMatch.group('series') # Stage type sTyp = "AR-" + descMatch.group('stgtyp') if sTyp == "AR-C/F": sTyp = "AR-CMP/FLT" rngHB[rowi][colStgType] = sTyp # Shaft size rngHB[rowi][colShaftSize] = "/".join( re.findall(searchPattern2, hbExtDesc)) # H&B material rngHB[rowi][colHBMaterial] = ", ".join(hbMat for hbMat in HBMatList if hbMat in hbExtDesc) # Connection type if "UHP" in hbExtDesc: connTyp = "UHP" elif "BUT" in hbExtDesc: connTyp = "BUT" else: connTyp = "" rngHB[rowi][colConnectionType] = connTyp # Bearing support material rngHB[rowi][colBSMaterial] = ", ".join(bsType for bsType in BSM if bsType in hbExtDesc) # Temperature rating if "XHT" in hbExtDesc: rngHB[rowi][colTemp] = "XHT" # Export modified H&B GROUP data back to worksheet pmpDataSht[rowHB + 2, 0].value = rngHB[1:]
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import H&B GROUP data as an array (list of lists) rowOR = cF.findHeader(pmpDataSht, 'O-RING GROUP') rowEnd = cF.findHeader(pmpDataSht, 'SPACER GROUP') colEnd = pmpDataSht[rowOR + 1, 0].end('right').column rngOR = pmpDataSht[rowOR + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngOR[0].index("Series") colMat = rngOR[0].index("Material") colDuro = rngOR[0].index("Durometer") colTyp = rngOR[0].index("Type Designation") colApp = rngOR[0].index("Application") colItemN = rngOR[0].index("Item Notes") colDesc = rngOR[0].index("Description") colItemID = rngOR[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpOringMaterial = tmpSht[2, 0].value pmpDuro = tmpSht[3, 0].options(numbers=int).value pmpApp = tmpSht[4, 0].value tmpSht[0:4, 0].value = "" # Type designation & App designations # 1. Type designation if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]: strTypeDesig = "FLT" else: strTypeDesig = "CMP" # 2. Application type if pmpApp in ["UHP", "XHT"]: strApp = pmpApp else: strApp = "" # Initialize the BOM elements strOringItemID = "NEW" strOringDescription = "GROUP,PMP " + pmpSeries + " O-RING " + pmpOringMaterial + \ " " + str(pmpDuro) + "*" strOringItemNotes = strTypeDesig + " " + strApp # Find a part number matching the O-RING group (if any existing) for rowi in range(1, len(rngOR)): if pmpSeries == rngOR[rowi][colSeries] and \ strTypeDesig == rngOR[rowi][colTyp] and \ pmpOringMaterial == rngOR[rowi][colMat] and \ pmpDuro == rngOR[rowi][colDuro] and \ strApp == rngOR[rowi][colApp]: strOringItemID = rngOR[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strOringItemID tmpSht[0, 1].value = strOringDescription tmpSht[0, 2].value = strOringItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] keySht = wb.sheets["KeystockTable"] # Import KEYSTOCK data as an array (list of lists) rowKEY = cF.findHeader(pmpDataSht, 'KEYSTOCK') rowEnd = cF.findHeader(pmpDataSht, 'COUPLING') colEnd = pmpDataSht[rowKEY + 1, 0].end('right').column rngKEY = pmpDataSht[rowKEY + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colMat = rngKEY[0].index("Material") colItemID = rngKEY[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpHousingNum = tmpSht[2, 0].options(numbers=int).value pmpShaftSize = tmpSht[3, 0].value pmpAppType = tmpSht[4, 0].value tmpSht[0:5, 0].value = "" # Useful associations # 1. Keystock Material if pmpStageType in ["AR-MDLR", "Q-PLUS"]: strMat = "INC" elif pmpAppType in ["UHP", "XHT"]: strMat = "INC" else: strMat = "MNL" # 2. Keystock length tables lrow = keySht[0, 0].end('down').row lcol = keySht[0, 0].end('right').column rngTAB = keySht[0:lrow, 0:lcol].value colSeries = rngTAB[0].index("Series") colType = rngTAB[0].index("Type") colSize = rngTAB[0].index("Size") colHsgNum = rngTAB[0].index("Hsg Num") colAval = rngTAB[0].index("A value") if pmpSeries == "TA": if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]: strType = "FLT" if pmpShaftSize == 0.62: subtract_len = 2.028 + 2.580 else: subtract_len = 2.225 + 2.830 else: strType = "CMP" if pmpShaftSize == 0.62: subtract_len = 1.69 + 0.255 + 0.382 + 2.179 else: subtract_len = 1.69 + 0.382 + 0.382 + 2.142 # FIX THIS!!!! (JUST TEMPORARY TO AVOID PYTHON ERRORS) else: subtract_len = 5 # Calculate keystock quantity Aval = 10000 # default value for distinction for rowi in range(1, len(rngTAB)): if pmpSeries == rngTAB[rowi][colSeries] and \ strType == rngTAB[rowi][colType] and \ pmpHousingNum == rngTAB[rowi][colHsgNum] and \ pmpShaftSize == rngTAB[rowi][colSize]: Aval = rngTAB[rowi][colAval] break intKeystockQuantity = ceil(Aval - subtract_len) # Initialize BOM elements strKeystockItemID = "NEW" strKeystockDescription = "KEYSTOCK," + strMat + " 0.062 SQ (PMP)" strKeystockItemNotes = "" # Find a part number for KEYSTOCK data (if any existing) for rowi in range(1, len(rngKEY)): if strMat == rngKEY[rowi][colMat]: strKeystockItemID = rngKEY[rowi][colItemID] # Write to TempSheet tmpSht[0, 0].value = strKeystockItemID tmpSht[0, 1].value = strKeystockDescription tmpSht[0, 2].value = strKeystockItemNotes tmpSht[0, 3].value = intKeystockQuantity
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import LOWER DIFFUSER data as an array (list of lists) rowLDF = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER') rowEnd = cF.findHeader(pmpDataSht, 'KEYSTOCK') colEnd = pmpDataSht[rowLDF + 1, 0].end('right').column rngLDF = pmpDataSht[rowLDF + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngLDF[0].index("Series") colBPD = rngLDF[0].index("BPD") colType1 = rngLDF[0].index("Type 1") colType2 = rngLDF[0].index("Type 2") colMat = rngLDF[0].index("Material") colCoat = rngLDF[0].index("Coating Material") colMS = rngLDF[0].index("MS") colItemN = rngLDF[0].index("Item Notes") colDesc = rngLDF[0].index("Description") colItemID = rngLDF[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpCapacity = str(tmpSht[1, 0].options(numbers=int).value) pmpStageType = tmpSht[2, 0].value pmpStagesMaterial = tmpSht[3, 0].value pmpCoatingMaterial = tmpSht[4, 0].value tmpSht[0:5, 0].value = "" # Other useful associations # 1. Stage type if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR"]: strStageType = "FLT" else: strStageType = "CMP" # 2. Coating type (if applicable) if pmpCoatingMaterial != "N/A": boolCoat = True strCoating = pmpCoatingMaterial else: boolCoat = False strCoating = None # Initialize BOM elements strLowerDiffuserItemID = "NEW" strLowerDiffuserDescription = "DIFFUSER, LWR " + pmpSeries + pmpCapacity + \ " " + strStageType + " Type2 " + pmpStagesMaterial + "*" if boolCoat: strLowerDiffuserItemNotes = strCoating else: strLowerDiffuserItemNotes = "" # Find a part number for LOWER DIFFUSER data (if any existing) for rowi in range(1, len(rngLDF)): if pmpSeries == rngLDF[rowi][colSeries] and \ pmpCapacity in rngLDF[rowi][colBPD] and \ strStageType in rngLDF[rowi][colType1] and \ pmpStagesMaterial == rngLDF[rowi][colMat] and \ strCoating == rngLDF[rowi][colCoat]: strLowerDiffuserItemID = rngLDF[rowi][colItemID] strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\ pmpCapacity, rngLDF[rowi][colBPD]) oldStgType = rngLDF[rowi][colType1] if oldStgType == "CMP/FLT": newStgType = "C/F" strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\ strStageType, newStgType) type2Val = rngLDF[rowi][colType2] if type2Val != None: strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\ "Type2", type2Val) break # If no part number found, replace Type2 in description with appropriate Type 2 value if strLowerDiffuserItemID == "NEW": if boolCoat and strCoating != "NDP": strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\ "Type2", "P-MACH") else: strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\ "Type2", "MACH") # Write to TempSheet tmpSht[0, 0].value = strLowerDiffuserItemID tmpSht[0, 1].value = strLowerDiffuserDescription tmpSht[0, 2].value = strLowerDiffuserItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import HOUSING data as an array (list of lists) rowHSG = cF.findHeader(pmpDataSht, 'HOUSING') rowEnd = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER') colEnd = pmpDataSht[rowHSG + 1, 0].end('right').column rngHSG = pmpDataSht[rowHSG + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngHSG[0].index("Series") colType = rngHSG[0].index("Type Association") colHsgNum = rngHSG[0].index("Housing Number") colMat = rngHSG[0].index("Material") colMS = rngHSG[0].index("MS") colConn = rngHSG[0].index("Connection Type") colItemN = rngHSG[0].index("Item Notes") colDesc = rngHSG[0].index("Description") # Define strings to match in description of the HOUSING group (and item notes) searchPattern = re.compile( "(HOUSING,P.?MP) (?P<series>T.) (?P<typ>\w+)? ?\#(?P<hsg_num>\d+)") hsgMatList = ["T-9", "13cr", "STL", "DPX"] hsgMatDict = {'MS-1111-03': 'STL', 'MS-1122-02': 'T9'} connList = ["BUT", "UHP"] # Main loop that populates the array (list of lists) for HOUSING data for rowi in range(1, len(rngHSG)): desc = rngHSG[rowi][colDesc] itemN = rngHSG[rowi][colItemN] if itemN is None: itemN = "" hsgExtDesc = desc + " " + itemN # Search using regular expressions descMatch = re.search(searchPattern, hsgExtDesc) if descMatch: # Series rngHSG[rowi][colSeries] = descMatch.group('series') # Type association if descMatch.group('typ') is not None: rngHSG[rowi][colType] = descMatch.group('typ') else: rngHSG[rowi][colType] = "FLT" # Housing number rngHSG[rowi][colHsgNum] = descMatch.group('hsg_num') # Material if rngHSG[rowi][colMS] != "": matKey = rngHSG[rowi][colMS][0:10] rngHSG[rowi][colMat] = hsgMatDict[matKey] else: rngHSG[rowi][colMat] = ", ".join(hsgMat for hsgMat in hsgMatList if hsgMat in hsgExtDesc) # Connection type (type of housing) if any(conn in hsgExtDesc for conn in connList): rngHSG[rowi][colConn] = ", ".join(conn for conn in connList if conn in hsgExtDesc) # Export modified HOUSING data back to worksheet pmpDataSht[rowHSG + 2, 0].value = rngHSG[1:]
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import SHAFT data as an array (list of lists) rowShft = cF.findHeader(pmpDataSht, 'SHAFT') rowEnd = cF.findHeader(pmpDataSht, 'HOUSING') colEnd = pmpDataSht[rowShft + 1, 0].end('right').column rngShft = pmpDataSht[rowShft + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngShft[0].index("Series") colStgType = rngShft[0].index("Stage Type") colShaftSize = rngShft[0].index("Shaft Size") colShaftNum = rngShft[0].index("Shaft Number") colMat = rngShft[0].index("Material") colMS = rngShft[0].index("MS") colConn = rngShft[0].index("Connection Type") colItemN = rngShft[0].index("Item Notes") colDesc = rngShft[0].index("Description") # Define strings to match in description of the SHAFT group (and item notes) searchPattern = re.compile( "(SHAFT,P.?MP) (?P<series>T.) (AR.)?(?P<remStgType>\w+) (?P<frac1>\d+)/(?P<frac2>\d+) \#(?P<shft_num>\d+)" ) shftMatList = ["INC", "MNL", "NIT", "UHS"] shftMatDict = { 'MS-1121-04': 'NIT', 'MS-1131-01': 'INC', 'MS-1131-02': 'UHS', 'MS-1241-03': 'MNL' } shaftSizeDict = {"5/8": 0.62, "11/16": 0.69, "1/2": 0.5, "7/8": 0.88} connList = ["BUT", "UHP"] # Main loop that populates the array (list of lists) for SHAFT data for rowi in range(1, len(rngShft)): desc = rngShft[rowi][colDesc] itemN = rngShft[rowi][colItemN] shftExtDesc = desc + " " + itemN # Search using regular expressions descMatch = re.search(searchPattern, shftExtDesc) if descMatch: # Series rngShft[rowi][colSeries] = descMatch.group('series') # Type rngShft[rowi][colStgType] = "AR-" + descMatch.group('remStgType') # Shaft size shaftsizefrac = descMatch.group('frac1') + "/" + \ descMatch.group('frac2') if shaftsizefrac in shaftSizeDict: rngShft[rowi][colShaftSize] = shaftSizeDict[shaftsizefrac] # Shaft number rngShft[rowi][colShaftNum] = descMatch.group('shft_num') # Material if rngShft[rowi][colMS] != "": matKey = rngShft[rowi][colMS][0:10] if matKey in shftMatDict: rngShft[rowi][colMat] = shftMatDict[matKey] else: rngShft[rowi][colMat] = ", ".join(shftMat for shftMat in shftMatList if shftMat in shftExtDesc) # Connection type (type of shaft) if any(conn in shftExtDesc for conn in connList): rngShft[rowi][colConn] = ", ".join(conn for conn in connList if conn in shftExtDesc) elif rngShft[rowi][colSeries] == "TA": rngShft[rowi][colConn] = "BUT" # Export modified SHAFT data back to worksheet pmpDataSht[rowShft + 2, 0].value = rngShft[1:]
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import HOUSING data as an array (list of lists) rowHSG = cF.findHeader(pmpDataSht, 'HOUSING') rowEnd = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER') colEnd = pmpDataSht[rowHSG + 1, 0].end('right').column rngHSG = pmpDataSht[rowHSG + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngHSG[0].index("Series") colType = rngHSG[0].index("Type Association") colHsgNum = rngHSG[0].index("Housing Number") colMat = rngHSG[0].index("Material") colMS = rngHSG[0].index("MS") colConn = rngHSG[0].index("Connection Type") colItemN = rngHSG[0].index("Item Notes") colDesc = rngHSG[0].index("Description") colItemID = rngHSG[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpHousingNum = tmpSht[2, 0].options(numbers=int).value pmpHousingMat = tmpSht[3, 0].value pmpConn = tmpSht[4, 0].value tmpSht[0:5, 0].value = "" # Useful transformations and associations # 1. Type association if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR"]: strType = "FLT" else: strType = "CMP" # 2. Housing number to correct format if pmpHousingNum < 10: strHousingNum = "0" + str(pmpHousingNum) else: strHousingNum = str(pmpHousingNum) # Initialize BOM elements strHousingItemID = "NEW" strHousingDescription = "HOUSING,PMP " + pmpSeries + " " + strType + " #" + \ strHousingNum + " " + pmpHousingMat + "*" strHousingItemNotes = pmpConn # Find a part number matching the HOUSING (if any existing) for rowi in range(1, len(rngHSG)): if pmpSeries == rngHSG[rowi][colSeries] and \ strType == rngHSG[rowi][colType] and \ pmpHousingNum == rngHSG[rowi][colHsgNum] and \ pmpHousingMat == rngHSG[rowi][colMat] and \ pmpConn == rngHSG[rowi][colConn]: strHousingItemID = rngHSG[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strHousingItemID tmpSht[0, 1].value = strHousingDescription tmpSht[0, 2].value = strHousingItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() #wb = xw.Book("ESP_config.xlsm") pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import STAGE GROUP data as an array (list of lists) rowSTG = cF.findHeader(pmpDataSht, 'STAGE GROUP') rowEnd = cF.findHeader(pmpDataSht, 'H&B GROUP') colEnd = pmpDataSht[rowSTG + 1, 0].end('right').column rngSTG = pmpDataSht[rowSTG + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngSTG[0].index("Series") colBPD = rngSTG[0].index("BPD") colStgType = rngSTG[0].index("Stage Type") colTemp = rngSTG[0].index("Temperature") colUPR = rngSTG[0].index("UPR") colOring = rngSTG[0].index("O-Ring Material") colMat = rngSTG[0].index("Material") colCoat = rngSTG[0].index("Coating Type") colItemN = rngSTG[0].index("Item Notes") colDesc = rngSTG[0].index("Description") colItemID = rngSTG[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpCapacity = tmpSht[1, 0].options(numbers=int).value pmpStageType = tmpSht[2, 0].value pmpStageCount = tmpSht[3, 0].options(numbers=int).value if "AR" in pmpStageType or "Q" in pmpStageType: pmpBearingCount = tmpSht[4, 0].options(numbers=int).value pmpStagesMaterial = tmpSht[5, 0].value pmpOringMaterial = tmpSht[6, 0].value pmpCoatingMaterial = tmpSht[7, 0].value pmpAppType = tmpSht[8, 0].value tmpSht[0:9, 0].value = "" # Other useful info noOringList = ["TA400", "TA550", "TA900", "TA1200", "TA1500"] # ------------------------------------------------------------------------- # Initialize REGULAR stage group (for both AR and non-AR pumps) # ------------------------------------------------------------------------- # Item ID initialized as "NEW" strStgGrpItemID = "NEW" # REGULAR stage group description strStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) # Check whether pump configuration is AR/Q-plus, which then requires modification # in the REGULAR stage group description of stage type if "AR" in pmpStageType: strStgGrpDescription = strStgGrpDescription + " " + pmpStageType[3:] strStgType = pmpStageType[3:] # For use when matching part numbers elif "Q" in pmpStageType: strStgGrpDescription = strStgGrpDescription + " " + "MDLR" strStgType = "MDLR" else: strStgGrpDescription = strStgGrpDescription + " " + pmpStageType strStgType = pmpStageType # Add O-ring material to description if not in the noOringList # ****************EXCEPTIONS: pumps with Quad Rings**************** if pmpSeries == "TA": if pmpSeries + str(pmpCapacity) in noOringList: strStgGrpDescription = strStgGrpDescription + "*" strOring = "" # For use when matching part numbers else: strStgGrpDescription = strStgGrpDescription + " " + pmpOringMaterial + "*" strOring = pmpOringMaterial elif pmpSeries == "TD": strStgGrpDescription = strStgGrpDescription + " EPDM*" strOring = "EPDM" # Item notes strStgGrpItemNotes = pmpStagesMaterial strCoating = "" # For use when matching part numbers # Add coating material to item notes if applicable if pmpCoatingMaterial != "N/A": strStgGrpItemNotes = strStgGrpItemNotes + " " + pmpCoatingMaterial strCoating = pmpCoatingMaterial #--------------------------------O----------------------------------------- # Determine REGULAR and AR (bearing) stage counts (if any) if "AR" in pmpStageType: strStgCount = pmpStageCount - pmpBearingCount + 2 strARStgCount = pmpBearingCount - 2 elif "Q" in pmpStageType: strStgCount = pmpStageCount - pmpBearingCount + 2 strQpStgCount = pmpBearingCount - 3 strQpUPRStgCount = 1 else: strStgCount = pmpStageCount # ------------------------------------------------------------------------- # Initialize AR stage group (ONLY for AR pumps) # ------------------------------------------------------------------------- if "AR" in pmpStageType: # Item ID initialized as "NEW" strARStgGrpItemID = "NEW" # AR stage group description strARStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) + " " \ + pmpStageType # Add O-ring material to description if not in the noOringList # ****************EXCEPTIONS: pumps with Quad Rings**************** if pmpSeries == "TA": if pmpSeries + str(pmpCapacity) in noOringList: strARStgGrpDescription = strARStgGrpDescription + "*" strAROring = "" # For use when matching part numbers else: strARStgGrpDescription = strARStgGrpDescription + " " + pmpOringMaterial + "*" strAROring = pmpOringMaterial elif pmpSeries == "TD": strARStgGrpDescription = strARStgGrpDescription + " EPDM*" strAROring = "EPDM" # Item notes strARStgGrpItemNotes = pmpStagesMaterial strARCoating = "" # For use when matching part numbers # Add coating material to item notes if applicable if pmpCoatingMaterial != "N/A": strARStgGrpItemNotes = strARStgGrpItemNotes + " " + pmpCoatingMaterial strARCoating = pmpCoatingMaterial # ------------------------------------------------------------------------- # Initialize Q+ & Q+ UPR stage group (ONLY for Q+ pumps) # ------------------------------------------------------------------------- if "Q" in pmpStageType: # Item ID initialized as "NEW" strQpStgGrpItemID = "NEW" # Q+ stage group description strQpStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) + " " \ + pmpStageType # Add O-ring material to description if not in the noOringList # ****************EXCEPTIONS: pumps with Quad Rings**************** if pmpSeries == "TA": if pmpSeries + str(pmpCapacity) in noOringList: strQpStgGrpDescription = strQpStgGrpDescription + "*" strQpOring = "" # For use when matching part numbers else: strQpStgGrpDescription = strQpStgGrpDescription + " " + pmpOringMaterial + "*" strQpOring = pmpOringMaterial elif pmpSeries == "TD": strQpStgGrpDescription = strQpStgGrpDescription + " EPDM*" strQpOring = "EPDM" # Item notes strQpStgGrpItemNotes = pmpStagesMaterial strQpCoating = "" # For use when matching part numbers # Add coating material to item notes if applicable if pmpCoatingMaterial != "N/A": strQpStgGrpItemNotes = strQpStgGrpItemNotes + " " + pmpCoatingMaterial strQpCoating = pmpCoatingMaterial # Q+ UPR stage Item ID initialized as "NEW" strQpUPRStgGrpItemID = "NEW" # Q+ UPRstage group description strQpUPRStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) + " " \ + pmpStageType + "-UPR" # Add O-ring material to description if not in the noOringList # ****************EXCEPTIONS: pumps with Quad Rings**************** if pmpSeries == "TA": if pmpSeries + str(pmpCapacity) in noOringList: strQpUPRStgGrpDescription = strQpUPRStgGrpDescription + "*" strQpUPROring = "" # For use when matching part numbers else: strQpUPRStgGrpDescription = strQpUPRStgGrpDescription + " " + pmpOringMaterial + "*" strQpUPROring = pmpOringMaterial elif pmpSeries == "TD": strQpUPRStgGrpDescription = strQpUPRStgGrpDescription + " EPDM*" strQpUPROring = "EPDM" # Item notes strQpUPRStgGrpItemNotes = pmpStagesMaterial strQpUPRCoating = "" # For use when matching part numbers # Add coating material to item notes if applicable if pmpCoatingMaterial != "N/A": strQpUPRStgGrpItemNotes = strQpUPRStgGrpItemNotes + " " + pmpCoatingMaterial strQpUPRCoating = pmpCoatingMaterial # Application type designation if pmpAppType == "XHT": strApp = "XHT" else: strApp = "" #--------------------------------O----------------------------------------- # Find a part number for REGULAR stage group matching the STAGE GROUP data (if any existing) for rowi in range(1, len(rngSTG)): if pmpSeries == rngSTG[rowi][colSeries] and \ pmpCapacity == rngSTG[rowi][colBPD] and \ strStgType in rngSTG[rowi][colStgType] and \ "AR" not in rngSTG[rowi][colStgType] and \ "Q" not in rngSTG[rowi][colStgType] and \ strOring == rngSTG[rowi][colOring] and \ pmpStagesMaterial == rngSTG[rowi][colMat] and \ strCoating == rngSTG[rowi][colCoat] and \ strApp == rngSTG[rowi][colTemp]: strStgGrpItemID = rngSTG[rowi][colItemID] oldStgType = rngSTG[rowi][colStgType] if oldStgType == "CMP/MDLR": newStgType = "C/MDLR" strStgGrpDescription = strStgGrpDescription.replace(strStgType,\ newStgType) elif oldStgType == "CMP/FLT": newStgType = "C/F" strStgGrpDescription = strStgGrpDescription.replace(strStgType,\ newStgType) break # If no matching part number found and if pump type is a TA series AR-MDLR, # try to find a match with CMP stage type (done CURRENTLY for TA550, TA900 # and TA1200) if pmpSeries == "TA" and pmpStageType == "AR-MDLR" and strStgGrpItemID == "NEW": for rowi in range(1, len(rngSTG)): if pmpSeries == rngSTG[rowi][colSeries] and \ pmpCapacity == rngSTG[rowi][colBPD] and \ "CMP" == rngSTG[rowi][colStgType] and \ strOring == rngSTG[rowi][colOring] and \ pmpStagesMaterial == rngSTG[rowi][colMat] and \ strCoating == rngSTG[rowi][colCoat] and \ strApp == rngSTG[rowi][colTemp]: strStgGrpItemID = rngSTG[rowi][colItemID] strStgGrpDescription = strStgGrpDescription.replace( "MDLR", "CMP") break # Find a part number for AR stage group matching the STAGE GROUP data (if any existing) if "AR" in pmpStageType: for rowi in range(1, len(rngSTG)): if pmpSeries == rngSTG[rowi][colSeries] and \ pmpCapacity == rngSTG[rowi][colBPD] and \ pmpStageType[3:] in rngSTG[rowi][colStgType] and \ "AR" in rngSTG[rowi][colStgType] and \ strAROring == rngSTG[rowi][colOring] and \ pmpStagesMaterial == rngSTG[rowi][colMat] and \ strARCoating == rngSTG[rowi][colCoat] and \ strApp == rngSTG[rowi][colTemp]: strARStgGrpItemID = rngSTG[rowi][colItemID] oldStgType = rngSTG[rowi][colStgType] if oldStgType == "AR-CMP/FLT": newStgType = "AR-C/F" strStgGrpDescription = strStgGrpDescription.replace(pmpStageType,\ newStgType) break # Find a part number for Q+ stage group matching the STAGE GROUP data (if any existing) if "Q" in pmpStageType: # For Q+ stage for rowi in range(1, len(rngSTG)): if pmpSeries == rngSTG[rowi][colSeries] and \ pmpCapacity == rngSTG[rowi][colBPD] and \ "Q-PLUS" == rngSTG[rowi][colStgType] and \ "" == rngSTG[rowi][colUPR] and \ strQpOring == rngSTG[rowi][colOring] and \ pmpStagesMaterial == rngSTG[rowi][colMat] and \ strQpCoating == rngSTG[rowi][colCoat] and \ strApp == rngSTG[rowi][colTemp]: strQpStgGrpItemID = rngSTG[rowi][colItemID] break # For Q+ UPR stage for rowi in range(1, len(rngSTG)): if pmpSeries == rngSTG[rowi][colSeries] and \ pmpCapacity == rngSTG[rowi][colBPD] and \ "Q-PLUS" == rngSTG[rowi][colStgType] and \ "UPR" == rngSTG[rowi][colUPR] and \ strQpUPROring == rngSTG[rowi][colOring] and \ pmpStagesMaterial == rngSTG[rowi][colMat] and \ strQpUPRCoating == rngSTG[rowi][colCoat] and \ strApp == rngSTG[rowi][colTemp]: strQpUPRStgGrpItemID = rngSTG[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strStgGrpItemID tmpSht[0, 1].value = strStgCount tmpSht[0, 2].value = strStgGrpDescription tmpSht[0, 3].value = strStgGrpItemNotes if "AR" in pmpStageType: tmpSht[1, 0].value = strARStgGrpItemID tmpSht[1, 1].value = strARStgCount tmpSht[1, 2].value = strARStgGrpDescription tmpSht[1, 3].value = strARStgGrpItemNotes elif "Q" in pmpStageType: tmpSht[1, 0].value = strQpStgGrpItemID tmpSht[1, 1].value = strQpStgCount tmpSht[1, 2].value = strQpStgGrpDescription tmpSht[1, 3].value = strQpStgGrpItemNotes tmpSht[2, 0].value = strQpUPRStgGrpItemID tmpSht[2, 1].value = strQpUPRStgCount tmpSht[2, 2].value = strQpUPRStgGrpDescription tmpSht[2, 3].value = strQpUPRStgGrpItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import STAGE GROUP data as an array (list of lists) rowSTG = cF.findHeader(pmpDataSht, 'STAGE GROUP') rowEnd = cF.findHeader(pmpDataSht, 'H&B GROUP') colEnd = pmpDataSht[rowSTG+1,0].end('right').column rngSTG = pmpDataSht[rowSTG+1:rowEnd,0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngSTG[0].index("Series") colBPD = rngSTG[0].index("BPD") colStgType = rngSTG[0].index("Stage Type") colTemp = rngSTG[0].index("Temperature") colUPR = rngSTG[0].index("UPR") colOring = rngSTG[0].index("O-Ring Material") colMat = rngSTG[0].index("Material") colCoat = rngSTG[0].index("Coating Type") colItemN = rngSTG[0].index("Item Notes") colDesc = rngSTG[0].index("Description") # Define strings to match in description of the stage group (and item notes) seriesPattern = re.compile("(T.) *?(\d+)") StageTypes = [re.compile("AR.CMP"),re.compile("AR.FLT"),re.compile("AR.MDLR"),\ re.compile("AR.C/F"),"CMP","FLT","C/F","C/MDLR","MDLR","MODULAR","Q PLUS"] noOringList = ["TA400","TA550","TA900","TA1200","TA1500"] OringMatList = ["AFL","HSN","VIT","CHEM","FFKM","EPDM"] MatList = ["N1","N2","N3","N4","DPX","SDX","3SS","CI"] coatType = ["NDP","FPS","CRP","SF2","DGS","BDP","GET"] tempInd = ["HTP","XHT"] # Main loop that populates the array (list of lists) for stage group data for rowi in range(1,len(rngSTG)): desc = rngSTG[rowi][colDesc] itemN = rngSTG[rowi][colItemN] stgExtDesc = desc + " " + itemN # Series and BPD seriesMatch = re.search(seriesPattern, stgExtDesc) if seriesMatch: rngSTG[rowi][colSeries] = seriesMatch.group(1) rngSTG[rowi][colBPD] = seriesMatch.group(2) # Stage type sTypMatch = [re.search(s, stgExtDesc) for s in StageTypes] if any(sTypMatch): firstMatchIndex = [i for i,v in enumerate(sTypMatch) if v][0] sTyp = sTypMatch[firstMatchIndex].group(0) # If AR type stages are found, make sure that there is a hyphen after the AR if sTyp[0:2] == "AR": listSType = list(sTyp) listSType[0:3] = ['A','R','-'] sTyp = "".join(listSType) elif sTyp[0] == "Q": listSType = list(sTyp) listSType[0:3] = ['Q','-','P'] sTyp = "".join(listSType) # If combo stage type found, then spell out the types if sTyp == "AR-C/F": sTyp = "AR-CMP/FLT" elif sTyp == "C/F": sTyp = "CMP/FLT" elif sTyp == "C/MDLR": sTyp = "CMP/MDLR" elif sTyp == "MODULAR": sTyp = "MDLR" rngSTG[rowi][colStgType] = sTyp # Determine if a Q PLUS stage is UPR if sTyp == "Q-PLUS": if "UPR" in stgExtDesc: rngSTG[rowi][colUPR] = "UPR" # Oring material seriesBPD = rngSTG[rowi][colSeries] + rngSTG[rowi][colBPD] if seriesBPD in noOringList: rngSTG[rowi][colOring] = "" elif any(oMat in stgExtDesc for oMat in OringMatList): rngSTG[rowi][colOring] = ", ".join(oMat for oMat in OringMatList if oMat in stgExtDesc) elif seriesMatch.group(1) == "TA": rngSTG[rowi][colOring] = "HSN" # seems to be the default if TA Stage O-Ring Material not specified elif seriesMatch.group(1) == "TD": rngSTG[rowi][colOring] = "EPDM" # all TDs by default have EPDM material quad rings in the stages # Stage Material if any(sMat in stgExtDesc for sMat in MatList): rngSTG[rowi][colMat] = ", ".join(sMat for sMat in MatList if sMat in stgExtDesc) else: rngSTG[rowi][colMat] = "N1" # Coating type rngSTG[rowi][colCoat] = ", ".join(cTyp for cTyp in coatType if cTyp in stgExtDesc) # Temperature if any(tInd in stgExtDesc for tInd in tempInd): rngSTG[rowi][colTemp] = "XHT" # Export modified STAGE GROUP data back to worksheet pmpDataSht[rowSTG+2,0].value = rngSTG[1:]
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import COMMON (com parts) data as an array (list of lists) rowCOM = cF.findHeader(pmpDataSht, 'COMMON') rowEnd = cF.findHeader(pmpDataSht, 'STAGE GROUP') colEnd = pmpDataSht[rowCOM + 1, 0].end('right').column rngCOM = pmpDataSht[rowCOM + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colSeries = rngCOM[0].index("Series") colStgType = rngCOM[0].index("Stage Type") colShaftSize = rngCOM[0].index("Shaft Size") colDesignation = rngCOM[0].index("Designation") colItemN = rngCOM[0].index("Item Notes") colDesc = rngCOM[0].index("Description") colItemID = rngCOM[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpShaftSize = tmpSht[2, 0].value pmpExternal = tmpSht[3, 0].value pmpAppType = tmpSht[4, 0].value tmpSht[0:5, 0].value = "" # Stage type association for COM PART group if pmpStageType in ["FLT", "AR-FLT"]: strComStageType = "AR-FLT" elif pmpStageType in ["CMP", "AR-CMP"]: strComStageType = "AR-CMP" elif pmpStageType == "AR-MDLR": strComStageType = "AR-MDLR" elif pmpStageType == "Q-PLUS": strComStageType = "Q-PLUS" # Shaft size converted to a string of the correct format for COM PART group shaftSizeMatch = re.search("\.\d+", str(pmpShaftSize)) strShaftSize = shaftSizeMatch.group(0) # Special designation based on desired pump configuration if pmpExternal == "NLP": strDesignation = "NLP" elif pmpAppType in ["UHP", "WAG"]: strDesignation = pmpAppType else: strDesignation = "" # Initialize the BOM elements strComItemID = "NEW" strComDescription = "COM PART,PMP " + pmpSeries + " " + strComStageType + " " + \ strShaftSize if strDesignation != "": strComDescription = strComDescription + "*" strComItemNotes = strDesignation # Find a part number matching the COM PART group (if any existing) for rowi in range(1, len(rngCOM)): if pmpSeries == rngCOM[rowi][colSeries] and \ strComStageType == rngCOM[rowi][colStgType] and \ pmpShaftSize == rngCOM[rowi][colShaftSize] and \ strDesignation == rngCOM[rowi][colDesignation]: strComItemID = rngCOM[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strComItemID tmpSht[0, 1].value = strComDescription tmpSht[0, 2].value = strComItemNotes
def main(): # Define PumpData sheet object #wb = xw.Book('ESP_config.xlsm') wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import SPACER data as an array (list of lists) rowSPC = cF.findHeader(pmpDataSht, 'SPACER GROUP') rowEnd = cF.findHeader(pmpDataSht, 'SHIPPING GROUP') colEnd = pmpDataSht[rowSPC + 1, 0].end('right').column rngSPC = pmpDataSht[rowSPC + 1:rowEnd, 0:colEnd].options(empty='').value # Determine values for necessary column colShaftSize = rngSPC[0].index("Shaft Size") colMat = rngSPC[0].index("Material") colItemN = rngSPC[0].index("Item Notes") colDesc = rngSPC[0].index("Description") colItemID = rngSPC[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpShaftSize = tmpSht[0, 0].value tmpSht[0, 0].value = "" # Material for SPACER (304 is standard going forward) if pmpShaftSize == 0.50: strMat = "N1" elif pmpShaftSize in [0.62, 0.69, 0.88]: strMat = "304" # Shaft size association to fraction if pmpShaftSize == 0.62: strShaftSize = "5/8" elif pmpShaftSize == 0.69: strShaftSize = "11/16" elif pmpShaftSize == 0.5: strShaftSize = "1/2" elif pmpShaftSize == 0.88: strShaftSize = "7/8" # Initialize the BOM elements strSpacerItemID = "NEW" strSpacerDescription = "GROUP,SPACER IMPELLER " + strShaftSize + " " + \ strMat strSpacerItemNotes = "" # Find a part number matching the SPACER group (if any existing) for rowi in range(1, len(rngSPC)): if pmpShaftSize == rngSPC[rowi][colShaftSize]: dataMat = rngSPC[rowi][colMat] if str(int(dataMat)) == "304": compDataMat = "304" else: compDataMat = dataMat if strMat == compDataMat: strSpacerItemID = rngSPC[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strSpacerItemID tmpSht[0, 1].value = strSpacerDescription tmpSht[0, 2].value = strSpacerItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] tmpSht = wb.sheets["TempSheet"] # Import COUPLING data as an array (list of lists) rowCPLG = cF.findHeader(pmpDataSht, 'COUPLING') rowEnd = pmpDataSht[rowCPLG + 1, 0].end('down').row colEnd = pmpDataSht[rowCPLG + 1, 0].end('right').column rngCPLG = pmpDataSht[rowCPLG + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngCPLG[0].index("Series") colStgType = rngCPLG[0].index("Stage Type") colSize = rngCPLG[0].index("Size") colMat = rngCPLG[0].index("Material") colMS = rngCPLG[0].index("MS") colItemN = rngCPLG[0].index("Item Notes") colDesc = rngCPLG[0].index("Description") colItemID = rngCPLG[0].index("Item ID") # Extract pump info from TempSheet and clear TempSheet pmpSeries = tmpSht[0, 0].value pmpStageType = tmpSht[1, 0].value pmpCplgMat = tmpSht[2, 0].value pmpShaftSize = tmpSht[3, 0].value tmpSht[0:4, 0].value = "" # Useful transformations and associations # 1. Stage type association if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]: strStageType = "FLT" else: strStageType = "CMP" # 2. Shaft size to fraction format if pmpShaftSize == 0.62: strShaftSize = "5/8" elif pmpShaftSize == 0.69: strShaftSize = "11/16" elif pmpShaftSize == 0.50: strShaftSize = "1/2" elif pmpShaftSize == 0.88: strShaftSize = "7/8" # 3. Series assocation if pmpSeries == "TA" and strStageType == "FLT" and strShaftSize == "5/8": strSeries = "TA" elif pmpSeries == "TA": strSeries = "TD" else: strSeries = pmpSeries # Initialize BOM elements strCouplingItemID = "NEW" strCouplingDescription = "COUPLING,PMP " + strSeries + " " + strStageType + \ " " + strShaftSize + " S/A " + pmpCplgMat strCouplingItemNotes = "" # Find a part number matching the COUPLING (if any existing) for rowi in range(1, len(rngCPLG)): if strSeries == rngCPLG[rowi][colSeries] and \ strStageType == rngCPLG[rowi][colStgType] and \ strShaftSize == rngCPLG[rowi][colSize] and \ pmpCplgMat == rngCPLG[rowi][colMat]: strCouplingItemID = rngCPLG[rowi][colItemID] break # Write to TempSheet tmpSht[0, 0].value = strCouplingItemID tmpSht[0, 1].value = strCouplingDescription tmpSht[0, 2].value = strCouplingItemNotes
def main(): # Define PumpData sheet object wb = xw.Book.caller() pmpDataSht = wb.sheets["PumpData"] # Import LOWER DIFFUSER data as an array (list of lists) rowLDF = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER') rowEnd = cF.findHeader(pmpDataSht, 'KEYSTOCK') colEnd = pmpDataSht[rowLDF + 1, 0].end('right').column rngLDF = pmpDataSht[rowLDF + 1:rowEnd, 0:colEnd].value # Determine values for necessary column colSeries = rngLDF[0].index("Series") colBPD = rngLDF[0].index("BPD") colType1 = rngLDF[0].index("Type 1") colType2 = rngLDF[0].index("Type 2") colMat = rngLDF[0].index("Material") colCoat = rngLDF[0].index("Coating Material") colMS = rngLDF[0].index("MS") colItemN = rngLDF[0].index("Item Notes") colDesc = rngLDF[0].index("Description") # Define strings to match in description of the lower diffuser (and item notes) searchPattern = re.compile( "DIFFUSER,LO?WE?R (?P<series>T.) ?(?P<BPD1>\d+)/?(?P<BPD2>\d+)?") StageTypes = ["CMP", "FLT", "C/F"] type2List = [ re.compile("P-MA?CH"), re.compile("CA?ST"), re.compile("MA?CH") ] matList = ["N1", "N2", "N3", "N4", "DPX", "SDX", "3SS", "CI"] coatType = ["NDP", "FPS", "CRP", "SF2", "DGS", "BDP"] # Main loop that populates the array (list of lists) for lower diffuser data for rowi in range(1, len(rngLDF)): desc = rngLDF[rowi][colDesc] itemN = rngLDF[rowi][colItemN] if itemN is None: itemN = "" ldfExtDesc = desc + " " + itemN # Search description using regular expressions descMatch = re.search(searchPattern, ldfExtDesc) if descMatch: # Series rngLDF[rowi][colSeries] = descMatch.group('series') # BPD1 rngLDF[rowi][colBPD] = descMatch.group('BPD1') # BPD2 (if at all) if descMatch.group('BPD2') is not None: rngLDF[rowi][colBPD] = rngLDF[rowi][ colBPD] + "/" + descMatch.group('BPD2') # Type 1 (stage type) type1Match = [re.search(s, ldfExtDesc) for s in StageTypes] if any(type1Match): firstMatchIndex = [i for i, v in enumerate(type1Match) if v][0] sTyp = type1Match[firstMatchIndex].group(0) # If combo stage type found, then spell out the types if sTyp == "C/F": sTyp = "CMP/FLT" rngLDF[rowi][colType1] = sTyp # Type 2 type2Match = [re.search(t, ldfExtDesc) for t in type2List] if any(type2Match): firstMatchIndex = [i for i, v in enumerate(type2Match) if v][0] tTyp = type2Match[firstMatchIndex].group(0) # correct abbreviations if needed if tTyp == "P-MCH": tTyp = "P-MACH" elif tTyp == "CAST": tTyp = "CST" elif tTyp == "MCH": tTyp = "MACH" rngLDF[rowi][colType2] = tTyp # Material (stg material) if any(sMat in ldfExtDesc for sMat in matList): rngLDF[rowi][colMat] = ", ".join(sMat for sMat in matList if sMat in ldfExtDesc) else: rngLDF[rowi][colMat] = "N1" # Coating type rngLDF[rowi][colCoat] = ", ".join(cTyp for cTyp in coatType if cTyp in ldfExtDesc) # Export modified LOWER DIFFUSER data back to worksheet pmpDataSht[rowLDF + 2, 0].value = rngLDF[1:]