记一次python同步jira上的bug到excel表

场景需求：每天需要更新jira上的bug到“基地问题统计”Excel表，新bug的添加以及原bug的状态（开发中、测试中、完成）更新

可解决办法：使用jira上的导出功能。不方便之处：Excel表里的内容会人为修改，需要保留，所以对于今日更新的bug是追加而不是全部统一更新

可解决办法：python写脚本

 

import re
import sys
import os
import openpyxl
from jira import JIRA
from openpyxl.styles import Font, colors, Alignment, Side, Border, PatternFill
from dateutil.parser import parse

wb = openpyxl.load_workbook('D:\\mattermost\\基地问题.xlsx')
sheet = wb['进行中']

# 在软件程序目录下有个account.txt记录了jira登录的用户名和密码, 格式yy.bb|123456
BASE_DIR = os.path.dirname(os.path.realpath(sys.argv[0]))
path = os.path.join(BASE_DIR, "account.txt")
with open(path, mode = 'r', encoding = 'utf-8') as f:
    data = f.read()
strlist = data.split('|')
name = strlist[0]
password = strlist[1]
jira = JIRA('http://10.10.1.25:8080/', basic_auth = (name, password)

# 获取excel表的最大行(有字的行，去除最后面的空白行)
def get_max_row(sheet):
    i = sheet.max_row
    real_max_row = 0
    while i>0:
        row_dict = {i.value for i in sheet[i]}
        if row_dict =  = {None}:
            i = i-1
        else:
            real_max_row = i
            break
            
        return real_max_row
        
def get_comment(issue):
    for comment in jira.comments(issue):
        if(re.match('(\S|\s)*预计.*时间', comment.body)! = None):
            return comment.body
            
# 主程序
# 1.获取最大行
max_row = get_max_row(sheet)
# 2.定位到Excel表中的最大行第一列
cell = sheet.cell(row = max_row, column = 1)
# 3.取出当前已录入到的最大基地问题编号
id = cell.value
# 4.将Excel表的当前行定位到最大行，便于追加行的时候从这一行往后（而不是从第一行往后追加）
sheet._current_row = max_row

# 获取jira数据
all_issues = jira.search_issues('project = BASE ORDER BY key ASC', maxResults = -1)#如果不加 = -1, 只获取几行
issues = jira.search_issues('project = BASE AND key>id ORDER BY key ASC', maxResults = -1)#如果不加 = -1, 只获取几行

# 更新excel表之前的bug状态（测试中、结束等）
for row in sheet.rows:
    for issue in all_issues:
        if(row[0].value =  = issue.key)#取当前行的第一列，第一列是key问题编号列
            row[8].value = issus.fields.status.name#给该问题的状态更新，表示“状态”的在第8列
            break

# 更新Excel表追加新产生的问题
for issus in issues:
    z = {'A':issue.key, 'B':issue.fields.summary}
    sheet.append(z)

# 设置格式
side = Side(style = 'medium', color = 'a3a3a3')
border = Border(left = side, right = side, top = side, bottom = side)
font = Font(name = '华文新魏', size = 12)
alignment = Alignment(wrapText = True, horizontal = 'left', vertical = 'center')#自动换行
fill = PatternFill(fill_type = 'solid', fgColor = 'bfbfbf')
for row in sheet.iter_rows(min_row = 4, max_row = get_max_row(sheet), max_col = 11):
    for cell in row:
        cell.alignment = alignment
        cell.border = border
        cell.font = font
        cell.fill = fill

wb.save('D:\\基地问题.xlsx')
wb.close()