Good News
【原创】Python自动统计Jira数据，并写入Excel
我最近写了一个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。
# -*- 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[i].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)
```
TAG:
