Python与Jira的搭配使用详解(3)
一、将bug数据写入Excel中
经过前面两篇文章的学习,我们已经知道如何提交issue、获取issue信息,本次我们将介绍如何将获取的信息保存到Excel中,并自动得到数据。
1.1 保存数据到Excel中
使用xlwt、xlrd、xlutils等模块,来进行对数据的处理,并设置单元格的格式。
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# 2022/7/21 14:44
import xlrd,xlwt
from xlutils.copy import copy
import time
class ReadWrite():
def __init__(self,path=""):
self.book = xlrd.open_workbook(path)
self.book_copy = copy(self.book)
def read_ex(self,params):
"""
:param params: 表的名字或者索引
:return: 返回表的对象
"""
try:
sheet = self.book.sheet_by_index(params)
return sheet
except:
sheet = self.book.sheet_by_name(params)
return sheet
def write_ex(self,row,col,values,style = ""):
"""
:param row: 行(0)
:param col: 列(0)
:param values: 值
:param style: 格式
:return:
"""
sheet = self.book_copy.get_sheet(0)
sheet.row(row).height_mismatch = True
sheet.row(row).height = 500
sheet.col(col).width = 256 * 16
if style == "":
sheet.write(row,col,values)
else:
sheet.write(row,col,values,style = style)
1.2 设置单元格的其他属性
设置单元格边框、垂直水平居中、自动换行、背景颜色
以下是所有的边框颜色、背景颜色和对应的颜色序号。
def set_border(self,value=1,color=22):
borders = xlwt.Borders() # 创建边框对象
# 上下左右都添加边框
borders.left = value
borders.right = value
borders.top = value
borders.bottom = value
# 设置边框颜色
borders.left_colour = color
borders.right_colour = color
borders.top_colour = color
borders.bottom_colour = color
return borders
def set_aligment(self,h_value=2,v_value=1,wrap = 1):
alignment = xlwt.Alignment()
alignment.horz = h_value # 设置水平位置,1是左对齐,2是居中,3是右对齐
alignment.vert = v_value # 设置垂直位置,0是左对齐,1是居中,2是右对齐
alignment.wrap = wrap # 设置自动换行
return alignment
def set_patten(self,color=21):
patten = xlwt.Pattern()
patten.pattern = xlwt.Pattern.SOLID_PATTERN
patten.pattern_fore_colour = color # 给背景颜色赋值
return patten
def save_excel(self,path =f'{time.strftime("%Y%m%d")}.xls'):
self.book_copy.save(path)
得到不同的边框颜色和背景颜色。
if __name__ == '__main__':
r = ReadWrite()
style = xlwt.XFStyle()
style.alignment = r.set_aligment()
sum = -1
for i in range(10):
for j in range(10):
sum = sum+1
style.borders = r.set_border(color=sum)
# style.pattern = r.set_patten(color=sum)
r.write_ex(i,j,str(sum),style)
r.save_excel()
二、将附件插入Excel中
本次使用的openpyxl的模块的方法来对图片进行操作。将图片嵌入单元格
from openpyxl import Workbook,load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
class InsertImage():
def __init__(self,path,sheet_name):
self.path = path
self.wb = load_workbook(path)
# self.ws = self.wb.active
self.ws =self.wb[sheet_name]
# self.ws.title = "1"
def insert_image(self,col,row,path):
img = Image(path)
_from = AnchorMarker(col, 50000, row, 50000)
to = AnchorMarker(col+1, -50000, row+1, -50000)
img.anchor = TwoCellAnchor('twoCell', _from, to)
self.ws.add_image(img)
def save_excel(self):
self.wb.save(self.path)