51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

测试开发精英班,通向高级软件测试工程师想做银行测试,你不得不知道的项目为王,自动化测试提升加速器 !企业级云端项目实战云集,晋升测试开发复合型人才
【131期】:软件系统测试之如何做数据质量管理?免费领价值398元的测试课程 测试人职业发展! 【活动】为视频UP主打CALL,互动领福利!
查看: 188|回复: 0

[selenium] Python 和Selenium统计jira

[复制链接]

该用户从未签到

发表于 2022-9-27 11:51:43 | 显示全部楼层 |阅读模式
我最近写了一个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。

```python
# -*- 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 的 类:

```python
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)

```

回复

使用道具 举报

本版积分规则

关闭

站长推荐上一条 /1 下一条

小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

GMT+8, 2023-2-7 09:08 , Processed in 0.076998 second(s), 27 queries .

Powered by Discuz! X3.2

© 2001-2023 Comsenz Inc.

快速回复 返回顶部 返回列表