Python 和Selenium统计jira
2022/1/30 11:05:00
本文主要是介绍Python 和Selenium统计jira,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
我最近写了一个Python程序,自动提取Excel数据,用Selenium统计Jiras数据,并写入Excel。
1.openPage
打开并登录Jira,打开需统计的jira的页面,页面的search文本框,输入查询条件,就是sql语句,在jira中,叫做 jql.
2.statistic
统计jira数据,并写入Excel。
从Excel读取要搜索的条件:读取这个 sheet :“JiraData”
读取jira页面的记录总数,写入 Excel的 sheet:Sum
遍历jira中的bug 表格,并写入Excel的各个 sheet。
# -*- coding: utf-8 -*- from selenium import webdriver from time import sleep from selenium.webdriver.common.by import By import Write_excel wr = Write_excel.Write_excel('SasaiBugStatistic.xlsx') sheetNames=['PaymentProduct','LiveProduct','OthersProduct','AllProduct','PaymentPreProduct','LivePreProduct','OthersPreProduct','AllPreProduct'] driver = webdriver.Chrome() def openPage(): i = 2 driver.get("http://***:36808/login.jsp") sleep(2) el = driver.find_element(By.ID, "login-form-username") el.send_keys("***") el = driver.find_element(By.ID, "login-form-password") el.send_keys("***") el = driver.find_element(By.ID, "login-form-submit") el.click() sleep(5) urlSearch = "http://202.104.66.150:36808/issues/?jql=project%20in%20(CUB%2C%20ANDROIDUAT%2C%20IOSUAT%2C%20ANDROIDPRO%2C%20BACKENDPRO%2C%20IOSPROD)%20AND%20status%20in%20(Open%2C%20%22To%20Do%22%2C%20Reopened)%20AND%20affectedVersion%20in%20(%222.9.17%20production%22%2C%20%222.9.18%20%20production%22%2C%20%222.9.18%20production%22%2C%20%222.9.19%20%20production%22%2C%20%222.9.19%20production%22%2C%20%222.9.20%20%20production%22%2C%20%222.9.20%20production%22%2C%20%222.9.21%20%20production%22%2C%20%222.9.21%20production%22%2C%20%222.9.22%20%20production%22%2C%20%222.9.22%20production%22%2C%20%222.9.23%20%20production%22%2C%20%222.9.23%20production%22%2C%20%222.9.24%20Production%22%2C%20%222.9.25%20Production%22%2C%20%222.9.26%20production%22%2C%20%222.9.27%20production%22%2C%20%222.9.28%20Production%22%2C%20%222.9.29%20Production%22%2C%20%222.9.30%20production%22)%20AND%20component%20in%20(%22EcoCash%20Wallet%20APP%22%2C%20Remittances%2C%20%22Sasai%20Wallet%20APP%22%2C%20%22Sasai%20Wallet%20Management%20portal%22%2C%20%22Sasai%20Wallet%20Merchant%20portal%22%2C%20Wallet)%20ORDER%20BY%20created%20ASC%2C%20status%20ASC%2C%20summary%20ASC%2C%20affectedVersion%20ASC" driver.get(urlSearch) def statistic(): row = 3 sheetIndex = 0 '''get jira page,search condition (jql)''' searchs = wr.getExcelData("JiraData",2) for search in searchs: sleep(3) condition = driver.find_element(By.ID,"advanced-search") condition.clear() sleep(2) condition.send_keys(search) button = driver.find_element(By.XPATH,'//*[@id="content"]/div[1]/div[4]/div/form/div[1]/div[1]/div[1]/div[2]/button') button.click() sleep(4) total = driver.find_element(By.XPATH,"//*[@id=\"content\"]/div[1]/div[4]/div/div/div/div/div/div/div[1]/div[1]/div/div/span/span[3]") print("total:" + total.text) '''set excel, Sum sheet , total data''' wr.setExcelData('Sum',row,4,total.text) row=row+1 table = driver.find_element(By.ID,"issuetable") #table的总行数,包含标题 table_rows = table.find_elements(By.TAG_NAME,"tr") #tabler的总列数 table_cols = table_rows[0].find_elements(By.TAG_NAME,"th") for i in range(1,len(table_rows)): for j in range(0,len(table_cols)-1): cell = table_rows.find_elements(By.TAG_NAME,"td")[j].text wr.writeOnly(sheetNames[sheetIndex],i,j+1,cell) # print("第"+str(i) +"行第"+str(j)+"列的text: "+ cell) wr.save() sheetIndex = sheetIndex + 1 '''main''' openPage() wr.clearSheet(sheetNames) statistic()
操作Excel 的 类:
import requests from openpyxl import load_workbook from openpyxl import Workbook # from openpyxl.chart import BarChart, Series, Reference, BarChart3D # from openpyxl.styles import Color, Font, Alignment # from openpyxl.styles.colors import BLUE, RED, GREEN, YELLOW class Write_excel(object): def __init__(self,filename): self.filename = filename self.wb = load_workbook(self.filename) self.sheets = self.wb.sheetnames self.ws = self.wb.active def write(self, row,col, value): self.ws.cell(row,col).value = value self.wb.save(self.filename) def merge(self, rangstring): # eg: rangstring:A1:E1 self.ws.merge_cells(rangstring) self.wb.save(self.filename) def cellstyle(self, coord, font, align): cell = self.ws.cell(coord) cell.font = font cell.alignment = align def writeOnly(self,sheetName, row,col, value): sheet = self.wb.get_sheet_by_name(sheetName) sheet.cell(row,col).value = value def save(self): self.wb.save(self.filename) def clearSheet(self,sheetNames): for i in range(3, 11): index = i-3 sheet = self.wb.get_sheet_by_name(sheetNames[index]) sheet.delete_cols(1, 9) self.wb.save(self.filename) def makechart(self, title, pos, width, height, col1, row1, col2, row2, col3, row3, row4): ''':param title:图表名 pos:图表位置 width:图表宽度 height:图表高度 ''' data = Reference(self.ws, min_col=col1, min_row=row1, max_col=col2, max_row=row2) cat = Reference(self.ws, min_col=col3, min_row=row3, max_row=row4) chart = BarChart3D() chart.title = title chart.width = width chart.height = height chart.add_data(data=data, titles_from_data=True) chart.set_categories(cat) self.ws.add_chart(chart, pos) self.wb.save(self.filename) def callAPI(self,id,method,url, params,i): # url = "https://www.baidu.com/s" # params = {'wd': '早上好'} # 该字典键值对的形式可以通过form data中查询 headers = { "User-Agent": "Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Mobile Safari/537.36" } if (method=='get'): res = requests.get(url, params=params, headers=headers) elif(method=='post'): res = requests.post(url, data=params, headers=headers) if res.status_code == 200: self.write(i, 6, 'passed') print(str(id) +' . ' + url + ": Test passed") else: self.write(i, 6, 'failed') print(str(id) +' . ' + url + ": Test failed") def getExcelData(self,sheetName,col): # 获取最大行数 self.ws.max_row sheet = self.wb.get_sheet_by_name(sheetName) cells = [] for i in range(1,sheet.max_row+1): cell = sheet.cell(i, col).value cells.append(cell) # print(cells) return cells def setExcelData(self,sheetName,row,col,value): # 获取最大行数 self.ws.max_row sheet = self.wb.get_sheet_by_name(sheetName) sheet.cell(row,col).value = value self.wb.save(self.filename)
这篇关于Python 和Selenium统计jira的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-21Python编程基础教程
- 2024-11-20Python编程基础与实践
- 2024-11-20Python编程基础与高级应用
- 2024-11-19Python 基础编程教程
- 2024-11-19Python基础入门教程
- 2024-11-17在FastAPI项目中添加一个生产级别的数据库——本地环境搭建指南
- 2024-11-16`PyMuPDF4LLM`:提取PDF数据的神器
- 2024-11-16四种数据科学Web界面框架快速对比:Rio、Reflex、Streamlit和Plotly Dash
- 2024-11-14获取参数学习:Python编程入门教程
- 2024-11-14Python编程基础入门