LimsBackend/util_xlsx.py

from io import BytesIO

import xlrd
import xlwt
from xlwt import Workbook

from apps.task.models import TestTask


def load_xlsx(file_path, task):
    if task.state == 2:
        return {'code': 500, 'info': '测试任务已完成，无法提交'}

    sheet = xlrd.open_workbook(file_path).sheet_by_index(0)
    error = []
    if len(sheet.col_values(0)[3:]) != len(set(sheet.col_values(0)[3:])):
        error.append('文件存在重复id，请检查')

    if sheet.row_values(0)[1].replace(" ", "") != task.plan.software.name:
        error.append('软件名称错误')
    if str(sheet.row_values(1)[1]).replace(" ", "") != task.plan.version:
        error.append('软件版本错误')

    case_info = []
    rows_num = sheet.nrows
    for i in range(3, rows_num):
        # print(sheet.row_values(i))
        if not sheet.row_values(i)[0].replace(" ", ""):
            error.append('第{}行编号不能为空'.format(i+1))
        if not sheet.row_values(i)[1].replace(" ", ""):
            error.append('第{}行名称不能为空'.format(i+1))
        if not sheet.row_values(i)[6].replace(" ", ""):
            error.append('第{}行测试步骤不能为空'.format(i+1))
        if not sheet.row_values(i)[7].replace(" ", ""):
            error.append('第{}行预期结果不能为空'.format(i+1))
        if task.state == 0:
            if sheet.row_values(i)[8].replace(" ", ""):
                error.append('测试任务未审核通过，第{}行实际结果需为空'.format(i + 1))
            if sheet.row_values(i)[9].replace(" ", "") != '未执行':
                error.append('当前测试任务状态为未执行，第{}行状态只能填写"未执行"'.format(i + 1))
        elif task.state == 1:
            if not sheet.row_values(i)[8].replace(" ", ""):
                error.append('测试任务已审核通过，第{}行实际结果不能为空'.format(i + 1))
            if sheet.row_values(i)[9].replace(" ", "") not in ['未执行', '执行成功', '执行失败']:
                error.append('第{}行状态只能填写"执行成功"或"执行失败"。若执行结果有时成功，有时失败，填写"执行失败"，并在备注中写明详细信息'.format(i + 1))
        if not sheet.row_values(i)[10].replace(" ", ""):
            error.append('第{}行编写人不能为空'.format(i+1))
        if not sheet.row_values(i)[11].replace(" ", ""):
            error.append('第{}行评审员不能为空'.format(i+1))
        if task.state == 1:
            if not sheet.row_values(i)[12].replace(" ", ""):
                error.append('第{}行执行人不能为空'.format(i+1))
        case_info.append([info.replace(" ", "") for info in sheet.row_values(i)])
    if error:
        return {'code': 50001, 'info': error}
    return {'code': 200, 'info': case_info}


def write_to_xlsx(file_path, cases_info):
    ws = Workbook(encoding='UTF-8')
    w = ws.add_sheet(u'Sheet1')
    for i in range(len(cases_info)):
        for j in range(len(cases_info[i])):
            w.write(i, j, cases_info[i][j])
    ws.save(file_path)
    sio = BytesIO()
    ws.save(sio)
    return sio


# if __name__ == '__main__':
#     file = 'static/case-file/TestLaboratory_V1_Task_3/2021-07-10 17:41:13.798046/测试用例模版.xlsx'
#     task_ = TestTask.objects.filter(id='task = TestTask.objects.filter(id=task_id, delete=False)', delete=False)
#     load_xlsx(file, task_)