对用例编号进行排序

用例和标题太多,每次手动拉取编号太累,写个脚本直接通过编号的关键字对用例进行编号,然后再手动把脚本编好的序号复制到源excel里面,轻松点了!

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment

# file_patch = input('文件路径:')
file_patch = r'文件路径'
wb = openpyxl.load_workbook(file_patch)
red = wb.create_sheet('sheet')
# sheets = input('sheet名称:')
sheets = '用例'
sheet = wb[sheets]

font1 = Font(name="宋体", size=10)
fills = PatternFill("solid", fgColor="92D050")
align = Alignment(horizontal='left', vertical='center')
num = 0
for i in range(8, sheet.max_row + 1):  # 8行之前不是用例,是一些说明
	# 编号在 A 列
    if 'test' in sheet.cell(i, 1).value:
        num = num + 1
        red.cell(i, 1).value = 'test-1.1.1-' + str(num).rjust(3, '0')
        red.cell(i, 1).font = font1
        red.cell(i, 1).alignment = align
    else:
        red.cell(i, 1).value = sheet.cell(i, 1).value
        red.cell(i, 1).font = font1
        red.cell(i, 1).fill = fills
        # red.merge_cells("A%s:K%s" % (i, i))
        red.row_dimensions[i].height = 30
        red.cell(i, 1).alignment = align

wb.save(file_patch)