def LifeCardDownloadingExcel(self): ''' How it works: -- Get a list of Testcases already in plan -- Get a list of the generic Testcases, since some fields will be needed -- For this second list make "title" indexed dictionary to correlate with testcases in plan -- Get the Catalog tree and generate a dictionariy of id -> (Category, SubCategory) labels -- Generate a list of lists (range or row, cols data in Excel Terms) -- Each row has the the 11 fields -- Open Excel, the file and get the sheet -- Calculate offsets and write the data to the range -- Save, Close and Quit Excel ''' wkbook = None tc_row_offset = 2 tc_col_offset = 1 ticket_row_offset = 2 ticket_col_offset = 4 try: self.LogAppend('Creating an RPC interface to the server') rpc = RpcInterface(self.serverurl, self.serverusername, self.serverpassword) # Get Test Cases In Plan - and Order Them self.LogAppend('Compiling list of TestCases in Plan from server') tcips = rpc.listTestCasesExt(self.lifecardcatalog[0], self.lifecardtestplan[0], True) self.LogAppend('Got %d testcases, processing them' % len(tcips)) tcips = map(TestCaseInPlan, tcips) # build a list of TestCaseInPlan tcips.sort(key=attrgetter('title')) # sort the list in place using title (our test_id) # Get Test Cases (for additional needed fields) self.LogAppend('Compiling list of TestCases from server') tcases = rpc.listTestCasesExt(self.lifecardcatalog[0], '', True) # Get from server it_tcases = map(TestCase, tcases) # Create the objects tcases = dict(map(lambda x: (x.title, x), it_tcases)) # make a title indexed tcasesb = dict(map(lambda x: (x.page_name, x), it_tcases)) # make a page_name indexed dict # Get the Test Catalogs and build a dictionary of ids / we only have two levels self.LogAppend('Start processing catalogs') testcats = dict() for catalog in map(TestCatalog, rpc.listSubCatalogsExt(self.lifecardcatalog[0])): testcats[catalog.id] = (catalog.title, '') for subcat in map(TestCatalog, rpc.listSubCatalogsExt(catalog.id)): testcats[subcat.id] = (catalog.title, subcat.title) self.LogAppend('Creating Excel Range Data') lrange = list() # Create a the range input for Excel (list of lists) for tcip in tcips: ltcase = list() tcase = tcases[tcip.title] ltcase.extend(testcats[tcase.get_catalog_id()]) # category - subcategory ltcase.extend([tcase.title, tcase.cpeid, tcase.headline]) ltcase.extend([tcip.get_status(), tcip.comment, tcip.tracefile, tcip.testednetwork, tcip.author, tcip.timestamp.split('T')[0]]) lrange.append(ltcase) self.LogAppend('Compiling ticket list') since = datetime.datetime(year=2014, month=1, day=1) ticket_ids = rpc.getRecentChanges(since) multicall = rpc.multicall() self.LogAppend('Getting tickets') for ticket_id in ticket_ids: multicall.ticket.get(ticket_id) tickets = map(Ticket, multicall()) tickets.sort(key=attrgetter('id')) self.LogAppend('Getting tickets changelog') multicall = rpc.multicall() for ticket_id in ticket_ids: multicall.ticket.changeLog(ticket_id) # create the ticketchangelog objects from call result tlogs = multicall() tlogs = dict(zip(ticket_ids, tlogs)) for ticket_id, clogs in tlogs.iteritems(): tloglist = list() for clog in clogs: tloglist.append(TicketChangeLog(clog)) tlogs[ticket_id] = tloglist tstatusdown = list() tstatusdown.append('new') if self.lcdownnew else None tstatusdown.append('open') if self.lcdownopen else None tstatusdown.append('closed') if self.lcdownclosed else None tstatusdown.append('fixed') if self.lcdownfixed else None tstatusdown.append('rejected') if self.lcdownreject else None tstatusdown.append('investigation') if self.lcdowninvest else None self.LogAppend('Will download following ticket types: %s' % str(tstatusdown)) self.LogAppend('Generating Excel Data Range for tickets') ltrange = list() if self.lcdowncopywithvendorcomments: ltrange2 = list() for ticket in tickets: if ticket.status not in tstatusdown: self.LogAppend('Skipping ticket %d with status %s' % (ticket.id, ticket.status)) continue lticket = list() lticket.extend([ticket.id, ticket.version, ticket.reporter]) # lticket.append('%s\n%s' % (ticket.summary, ticket.description)) lticket.append('%s\n---------------------\n%s' % (ticket.summary, ticket.description)) if ticket.testcaseid in tcasesb: lticket.append(tcasesb[ticket.testcaseid].title) else: lticket.append('Exploratory Testing') lticket.append(ticket.status.capitalize()) lticket.append(ticket.priority) # lticket.append('') # skip 'Test Comment Column' lticket.append(ticket.created) tlog = tlogs[ticket.id] owncomment = '' vencomment = '' for comment in filter(lambda x: x.name == 'comment', tlog): # format timestamp author and comment if not comment.new: continue if comment.author == self.lifecardauthorup: vencomment += '[%s]\n' % (comment.tstamp.strftime('%Y-%m-%dT%H:%M'),) vencomment += comment.new vencomment += '\n' else: owncomment += '[%s] %s\n' % (comment.tstamp.strftime('%Y-%m-%dT%H:%M'), comment.author) owncomment += comment.new owncomment += '\n' lticket.append(owncomment) # lticket.extend(['', '', ]) # skip 2 columnts if not self.lcdownfiltervendorcomments: lticket.append(vencomment) else: lticket.append('') resoltxt = '' for resolution in filter(lambda x: x.name == 'resolution', tlog): if not resolution.new: continue resoltxt += '[%s]\n' % (resolution.tstamp.strftime('%Y-%m-%dT%H:%M'),) resoltxt += resolution.new resoltxt += '\n' lticket.append(resoltxt) ltrange.append(lticket) if self.lcdowncopywithvendorcomments: lticket2 = copy.deepcopy(lticket) lticket2[-2] = vencomment ltrange2.append(lticket2) # raise Exception('Ticket List Compiled') if not lrange and not ltrange: self.LogAppend('Nothing to download to excel') else: if self.lcdownmakecopy: curdate = datetime.date.today() prepdate = curdate.strftime('%Y-%m-%d') appcw = int(curdate.strftime('%W')) curdate.replace(month=1, day=1) if curdate.weekday != 0: # Jan 1st is not monday, so all days until # first monday are isoweek = 0 and we want # calendar week 1 appcw += 1 appcw = 'cw%02d' % appcw dirname, basename = os.path.split(self.lifecardexcel) excelbase, excelext = os.path.splitext(basename) basename = prepdate + '-' + excelbase + '-' + appcw + excelext excelfile = os.path.join(dirname, basename) self.LogAppend('Copying %s -> %s' % (self.lifecardexcel, excelfile)) shutil.copyfile(self.lifecardexcel, excelfile) else: excelfile = self.lifecardexcel if self.lcdowncopywithvendorcomments: vendirname, venbasename = os.path.split(excelfile) venname, venext = os.path.splitext(venbasename) venname += '-vendor' venbasename = venname + venext venexcelfile = os.path.join(vendirname, venbasename) shutil.copyfile(excelfile, venexcelfile) exinstances = [(excelfile, ltrange),] if self.lcdowncopywithvendorcomments: exinstances.append((venexcelfile, ltrange2)) wkbooks = list() xls = list() for excel_file, ticket_range in exinstances: self.LogAppend('Opening Workbook %s' % excelfile) pythoncom.CoInitialize() # xl = Dispatch('Excel.Application') # xl = win32com.client.gencache.EnsureDispatchEx("Excel.Application") xl = DispatchEx('Excel.Application') # Opens different instance xls.append(xl) xl.Visible = 1 # xl.Interactive = True if self.lcdownkeepexcelopen else False xl.Interactive = True wkbook = xl.Workbooks.Open(excel_file) wkbooks.append(wkbook) if not lrange: self.LogAppend('No test cases to write down to Excel') else: lempty = [[''] * len(lrange[0])] * len(lrange) wksheet = wkbook.Sheets('Test Cases') if False and wksheet.AutoFilterMode: wksheet.ShowAllData() topleft = wksheet.Cells(tc_row_offset, tc_col_offset) botright = wksheet.Cells(tc_row_offset + len(lrange) - 1, tc_col_offset + len(lrange[0]) - 1) self.LogAppend('Writing Data to Worksheet') wksheet.Range(topleft, botright).Value = lempty wksheet.Range(topleft, botright).Value = lrange self.LogAppend('End downloading test-case-in-plan information') if not ticket_range: self.LogAppend('No tickets to write down to Excel') else: self.LogAppend('Writing Data to Worksheet') # FIXME: if there are no tickets ... I would also need to clear the list # The property .UsedRange should give access to cells that have already # been used (I can delete the range) lempty = [['',] * len(ticket_range[0])] * len(tickets) wksheet = wkbook.Sheets('Bug Tracking') if False and wksheet.AutoFilterMode: wksheet.ShowAllData() topleft = wksheet.Cells(ticket_row_offset, ticket_col_offset) botright = wksheet.Cells(ticket_row_offset + len(ticket_range) - 1, ticket_col_offset + len(ticket_range[0]) - 1) botrightempty = wksheet.Cells(ticket_row_offset + len(tickets) - 1, ticket_col_offset + len(ticket_range[0]) - 1) wksheet.Range(topleft, botrightempty).Value = lempty wksheet.Range(topleft, botright).Value = ticket_range self.LogAppend('End downloading tickets information to %s' % excel_file) try: if not self.lcdownexcelnotsave: self.LogAppend('Saving workbook') wkbook.Save() if not self.lcdownkeepexcelopen: self.LogAppend('Closing workbook and Quitting Excel') wkbook.Close(False) wkbooks.remove(wkbook) xl.Quit() xls.remove(xl) except Exception, e: self.LogAppend('Error saving/closing/quitting Excel file: %s' % str(e)) self.LogAppend('End downloading to Lifecard') except Exception, e: self.LogAppend('Error during Download to LifeCard: %s' % str(e))