【原创】Python自动统计Jira数据，并写入Excel

TomChen

我最近写了一个Python程序，自动提取Excel数据，统计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)

```

applepen

点个赞！
我之前，也这么爬过Jira上的数据。
后来发现jira自带的分析统计功能很强大。
直接就能分析导出结果