openpyxl 根据用例优先级随机抽取测试用例

完整代码，根据用例优先级将用例随机提取出来，再将用例重新写入文件

import random
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Alignment, PatternFill
def decision(all_rows):  # 判断bug等级，all_rows为读取到的所有用例
    Gradelist = []
    count = 0
    for i in all_rows:
        if i.get('优先级') == 2:
            count += 1
            Gradelist.append(i)
        else:
            pass
    print("符合条件{}个".format(count))
    return (Gradelist)

def toexcel(x, i, sheets):  # 写入excel，x是行，y是列
    chulilist = []
    for jj in i.values():  # 字典取出值
        chulilist.append(jj)
    # print((chulilist))
    for y in range(0, len(chulilist)):  # 写入内容
        var = sheets.cell(x, y + 1, chulilist[y]).value
        sheets.cell(x, y + 1).alignment = Alignment(horizontal='center', vertical='center', wrapText=True)#居中显示并且自动换行

def xlsx_words(maxlist, num, headers):  # maxlist要取的列表
    # sum=('30%-用例条数{}'.format(n*0.3))
    xlsx2 = openpyxl.load_workbook('1.xlsx')  # 打开要写入的xlsx文件，必须要先有1.xlsx，并且非打开状态下
    sheets = xlsx2.worksheets[0]
    for i in range(0, len(headers)):
        Color = ['CDCDCD', '9c0006']  # 灰色
        fille = PatternFill('solid', fgColor=Color[0])#给标题附加颜色
        var = sheets.cell(1, i + 1, headers[i]).value
        sheets.cell(1, i + 1).alignment = Alignment(horizontal='center', vertical='center')
        sheets.cell(1, i + 1).fill = fille  # 序列
    yongli = []
    for r in random.sample(maxlist, num):  # sample 没有重复数,要取的用例总数
        yongli.append(r)
    try:
        # 根据用例编号对随机抽取的用例重新排序
        r = sorted(yongli, key=lambda x: x['用例编号'])
    except Exception as e:
        raise '用例编号不符合'
    print('共抽取{}条'.format(len(r)))
    x = 2
    for i in r:
        x = x + 1
        toexcel(x, i, sheets)
        print('写入第{}行'.format(x))
    xlsx2.save("1.xlsx")


if __name__ == '__main__':
    book = load_workbook('..\\test抽取.xlsx')#打开要抽取的用例
    sheet = book.worksheets[0]# 获取第一页
    maxrows = sheet.rows  # 读取所有行数
    headers = [cell.value for cell in next(maxrows)]
    all_rows = []
    for row in maxrows:
        # 创建一个空字典来存放这里面的值
        data = {}
        for title, cell in zip(headers, row):
            data[title] = cell.value
        all_rows.append(data)  # 处理成字典格式，用于判断用例级别
    print('用例总行数----{}'.format(len(all_rows)))
    # print(headers)
    list3 = decision(all_rows)  # 获取符合用例条数
    xlsx_words(list3, 10, headers)#可以自动以抽取数量   其中10为10条

成果图