python involves the test case data encapsulation of excel, and there are several third-party libraries. For example, the relationship between lxlwt and xlwt

1. Package excel test case reading function

Encapsulate the operation steps of reading function in excel test case
1. Open workbook
workBook =xlrd. open_ Workbook (file path)
2. Get worksheet
workBook_ sheet1=workBook. get_ Sheet (index)
workBook_sheet2=workBook.sheet_by_name('worksheet name ')
workBook_ sheet3=workBook. sheet_ by_ Index
3. Get the number of whole rows
workBook_ sheet1. row_ Values (line number)
workBook_sheet3.nrows

Example 1
import xlrd,pprint
#1. Open the workBook, open the workBook in the file path according to the path, and obtain the [workBook] object
workBook =xlrd.open_workbook(r'C:\Users\hlhs_cqb\Downloads\test-test case V1.2.xls')
#2. Get the [workBook_sheet *] object from the workbook
# workBook_sheet1=workBook.get_sheet(1)
workBook_sheet4=workBook.sheets()[1]
workBook_sheet2=workBook.sheet_by_name('test')
workBook_sheet3=workBook.sheet_by_index(0)
#3. Obtain the whole row of data, and obtain the value or number of rows
pprint.pprint(workBook_sheet2.row_values(1))
pprint.pprint(workBook_sheet3.nrows)
pprint.pprint(workBook_sheet4.row_values(1))

2. Call excel to execute the test case

Call excel to execute test case operation steps
1. Read the test cases in excel and return the python list (we encapsulated a function)
2. Copy a new excel
3. Send the request circularly to the read test case list and return the request result in dictionary format (we encapsulated a function)
4. Compare the result returned by the request with the expected result in Excel. If it is consistent, the test passes. If it is inconsistent, the test fails. The comparison result is written back to [copied] excel
5. Save the copied excel

import xlrd,json,time
from xlutils.copy import copy
from lib.caseLib import CaseManage
#1-1 open Excel and get the [workBook] object
workBook=xlrd.open_workbook(r'../../data/test-test case.xls',formatting_info=True)
#1-2 get the [workSheet] object from the workbook
workSheet=workBook.sheet_by_index(0)
#2 - object instantiation. CaseManage is mainly used to request interfaces.
cam=CaseManage()
cam.login('test','123456')
#3-1 copy a workbook
workBookNew=copy(workBook)
#3-2 open worksheet
workSheetNew=workBookNew.sheet_by_name('test')
#1-3 cycle the worksheet
for i in range(1,workSheet.nrows):
    # 1-4 get a specific line of data in the cycle
    row = workSheet.row_values(i)
    #1-5 get the values of columns 1, 5, 6, 7 and.
    if row[4]=='add':
        #Get the fifth column of data and convert it into a dictionary
        data=json.loads(row[5])
        name=data['name']#Get the use case name attribute from the dictionary
        #Variable of use case name, replaced by timestamp
        name=name.replace('{{caseName}}',str(int(time.time()*1000)))
        #Call case add interface
        dictBody = cm.add(name, data['desc'], data['display_idx'])
        test = json.loads(row[6])
        if (dictBody["retcode"]==test["code"]):
            print(">>>>>Test passed, case No.:",row[4],row[0])
            workSheetNew.write(i,7,'PASS')
        else:
            print(">>>>>Test failed, case No.:",row[4], row[0],dictBody["reason"])
            workSheetNew.write(i, 7, 'FAIL')
            # workSheetNew.write(i, 8, dictBody['reason'])
    elif row[4]=='delete':
        data = json.loads(row[5])
        dictBody = cm.delete(data['id'])
        test = json.loads(row[6])
        if (dictBody["retcode"] == test["code"]):
            print(">>>>>Test passed, case No.:",row[4], row[0])
            workSheetNew.write(i, 7, 'PASS')
        else:
            print(">>>>>Test failed, case No.:",row[4], row[0])
            workSheetNew.write(i, 7, 'FAIL')
    elif row[4]=='list':
        # Get the fifth column of data and convert it into a dictionary
        data=json.loads(row[5])
        #Parameters in excel
        dictBody=cm.list(data["pagenum"],data["pagesize"])

        #Get the seventh column data assertion
        test=json.loads(row[6])
        if(dictBody["retcode"]==test["code"]):
            print(">>>>>Test passed, case No.:",row[4], row[0])
            workSheetNew.write(i, 7, 'PASS')
        else:
            print(">>>>>Test failed, case No.:",row[4], row[0])
            workSheetNew.write(i, 7, 'FAIL')
            # workSheetNew.write(i, 8, dictBody['reason'])
    elif row[4] == 'modify':
        pass
    else:
        print('Undefined:'+row[4])

workBookNew.save(r'../../report/test result.xls')

Finally, in order to facilitate everyone's learning and testing, we specially prepared a 13G super practical dry goods learning resource, which involves a very comprehensive content.

Including the software learning roadmap, more than 50 days of class videos, 16 assault actual combat projects, more than 80 software testing software, 37 test documents, 70 software testing related problems, 40 testing experience articles, thousands of test questions to share, 2021 software testing face-to-face test collection, and various selected resumes for software testing job hunting, hoping to help you

Pay attention to my official account: programmer two black can get this information.

If you don't want to experience the feeling that you can't find information during self-study, no one answers questions and give up after a few days, you can join our group: 785128166. We can discuss and exchange together. There are also various software testing materials and technical exchanges.