def filteringLatin():
excel.gotoCell("E10")
time.sleep(5)
pyautogui.hotkey("alt","down")
pyautogui.press("down")
pyautogui.press("right")
pyautogui.press("right")
pyautogui.press("t")
pyautogui.press("enter")
time.sleep(2)
def applyRatesToRoster():
nonlocal formulaCol
nonlocal ratesRange
excel.open(pars.pbRoster, r"excel\files\Roster.xlsx.PNG")
time.sleep(2)
excel.gotoCell("A2000")
excel.end( "up",0,0)
limitRow = excel.getCurrentRow()
rateCol = 9
excel.gotoCell("i2")
excel.addCol()
print ("formulaCol: " + formulaCol)
lookupFormula = "=IFERROR(VLOOKUP(A{},{}{},{},FALSE),J{})".format(limitRow,formulaFileName,ratesRange,int(excel.colNameToNum(formulaCol)),limitRow)
print(lookupFormula)
excel.gotoCell("i" + limitRow)
# fill up
excel.enterFormula(lookupFormula)
excel.copy()
excel.up()
excel.endSelect("up")
excel.paste()
excel.addCol()
excel.right()
excel.end("down")
excel.left()
excel.enterFormula("=IF(J{}>0,J{},K{})".format(limitRow,limitRow,limitRow))
# fill up
excel.copy()
excel.up()
excel.endSelect("up")
excel.paste()
excel.gotoCell("i2")
pyautogui.hotkey("ctrl", "shift","down")
excel.copy()
excel.gotoCell("K2")
excel.pasteValues()
excel.left()
excel.deleteCol()
excel.left()
excel.deleteCol()
def filteReport(self, cell, area):
self.openPBR()
excel.gotoCell(cell)
pyautogui.hotkey("alt", "down")
time.sleep(0.5)
pyautogui.typewrite(area)
time.sleep(0.5)
pyautogui.press("tab")
pyautogui.press("tab")
pyautogui.press("tab")
pyautogui.press("right")
pyautogui.press("enter")
def file_massage():
# Highlighting the columns
excel.gotoCell("H13")
pyautogui.keyDown("shift")
pyautogui.press("right")
pyautogui.press("right")
pyautogui.keyUp("shift")
pyautogui.keyDown("alt")
pyautogui.press("h")
pyautogui.press("h")
pyautogui.keyUp("alt")
pyautogui.press("right",presses=5,interval=1)
pyautogui.press("enter")
def updateDataSlide(self, area, file):
self.cleanDataFile(file)
# Copying Weekly Throughput
self.openPBR()
excel.runReportByName("Weekly Throughput")
if area != "ALL":
self.filteReport("E10", area)
excel.copyTable("D15", lr_offsetY=-1)
self.openDataSheet(file)
excel.gotoCell("A56")
excel.pasteValues()
# Copying Aging
self.openPBR()
if area == "ALL":
excel.runReportByName("Aging by Area")
else:
excel.runReportByName("Aging by Module")
self.filteReport("E10", area)
# Copying Aging
excel.copyTable("D13", lr_offsetY=-1)
self.openDataSheet(file)
excel.gotoCell("B29")
excel.pasteValues()
excel.end("right", 0, 0)
excel.deleteContent()
self.openPBR()
if area != "ALL":
excel.runReportByName("Monthly Throughput")
self.filteReport("E10", area)
excel.copyTable("D13", lr_offsetY=-1)
self.openDataSheet(file)
excel.gotoCell("U54")
excel.pasteValues()
self.openPBR()
excel.runReportByName("Monthly MTTR (INC, SR)")
if area != "ALL":
self.filteReport("E10", area)
excel.copyTable("D13", lr_offsetY=-1)
excel.copy()
self.openDataSheet(file)
excel.gotoCell("Z54")
excel.pasteValues()
def filterPivot(cell, area):
excel.gotoCell(cell)
pyautogui.hotkey("alt", "down")
time.sleep(0.5)
if area == "All":
pyautogui.typewrite("*")
time.sleep(0.5)
pyautogui.press("tab")
pyautogui.press("tab")
pyautogui.press("tab")
pyautogui.press("enter")
else:
pyautogui.typewrite(area)
time.sleep(0.5)
pyautogui.press("tab")
pyautogui.press("tab")
pyautogui.press("tab")
pyautogui.press("right")
pyautogui.press("enter")
def formatCGPReport():
nonlocal formulaCol
nonlocal ratesRange
# excel.AutofitColumns()
limitCol = excel.getlimitY("A2")
excel.right()
excel.enterFormula("New Rate")
formulaCol = excel.getCurrentCol()
limitRow = int(excel.getlimitX("A2",1))-1
rowRange = "A{}:{}{}".format(limitRow,limitCol,limitRow)
excel.gotoCell(limitCol+str(limitRow))
excel.right()
excel.enterFormula(r'=LOOKUP(2,1/({}<>""),{})'.format(rowRange,rowRange))
excel.copy()
excel.up()
excel.endSelect("up")
pyautogui.hotkey("shift","down")
excel.paste()
excel.gotoCell("a2")
ratesRange = "$A$3:${}${}".format(excel.nextColLetter(limitCol),limitRow)
def updateSLATrackerFile():
excel.openPBR(pars.pbReportsJNJ)
excel.runReportByName("SLA - Tracker")
excel.copyTable("E14",ul_offsetX=1, lr_offsetX=-1)
excel.openPBR(pars.slaTracker)
excel.gotoCell("D5")
excel.pasteValues()
excel.openPBR(pars.pbReportsJNJ)
excel.filterPivot("E9","All")
excel.copyTable("E14",ul_offsetX=1, lr_offsetX=-1)
excel.openPBR(pars.slaTracker)
excel.gotoCell("H5")
excel.pasteValues()
excel.gotoCell("A17")
def sendBPCSCategorizationReport():
template =r'"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\CIC Brazil\Automation\F0001 - Create a automatic process for Ticket Categorization in BPCS\UncatTemplate.xlsx"'
saveDir = r"C:\Users\ALESSANDROAlves\Box\Abbott Latam\Abbott Latam - Workspace\BPCS ITSM Categorization"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - Uncategorized tickets.xlsx",utils.YYmMM_DD))
dateStr = utils.getStampedStr("{}","%b %d")
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName=RPT_ITMS_CAT)
# excel.saveReportToFile(fileName)
excel.gotoCell("D14") #selecting data in the pivot table
excel.endSelect("down") #selecting data in the pivot table
excel.endSelect("right") #selecting data in the pivot table
pyautogui.hotkey("shift","left") #selecting data in the pivot table
excel.copy() #selecting data in the pivot table
excel.open(template,4)
excel.gotoCell("B2")
excel.pasteValues()
excel.AutofitColumns()
pyautogui.press("up")
excel.endSelect("right")
excel.activateAutoFilter()
excel.gotoCell("A1")
excel.setColumnSize("F",95)
utils.moveFilesToArchive(fileName)
excel.saveAs(fileName)
excel.close()
excel.runReportByName(reportName="ITSM - Uncategorized Tickets Summary - Latam")
excel.findText("Grand Total",0)
pyautogui.press("right")
pyautogui.press("right")
excel.copy()
ticketCount = pyperclip.paste().replace('\n', '').replace('\r', '')
excel.copyReportToClipBoard()
subject = ("Weekly Ticket Categorization - There are {} tickets pending categorization as of " + dateStr).format(ticketCount)
body = utils.getEmailTemplate("Categorized Tickets.txt")
sendVerseEmail(subject, pars.distroBPCS+pars.distroADAM, body.format(ticketCount), "", 20,True)
def pasteValuesToSheet(sourceFile, targetfile, cell):
excel.openDataSheet(targetfile)
excel.gotoCell(cell)
excel.pasteValues()
excel.openPBR(sourceFile)
pyautogui.press("esc")
