接口自动化测试实战：Excel用例管理+unittest+xlrd+xlsxwriter

1. 为什么接口自动化测试绕不开Excel这关做接口自动化测试的人迟早会撞上一个很现实的问题测试用例到底放哪儿。有人用YAML有人用JSON有人直接写死在代码里还有人上数据库。这些方案各有各的道理但在国内大量中小团队和外包项目里Excel才是真正的“通用语言”。原因很简单——写用例的人不一定是会写代码的人但几乎所有人都会用Excel。我最早接触接口自动化的时候也是把用例硬编码在Python文件里一个接口一个函数参数直接写在代码里。刚开始十几个接口还行等到接口数量涨到七八十个参数组合稍微一多整个测试文件就变成了一坨谁都不敢动的面条。改一个参数要翻半天代码产品经理临时加一个边界值我得重新跑一遍整个脚本。后来换成Excel管理用例测试同学自己就能往里加行我只需要保证读取逻辑稳定就行分工一下子就清晰了。这套方案的核心思路其实很朴素用Excel当用例仓库用unittest当执行引擎用xlrd读用例用xlsxwriter写报告。四个东西各司其职拼在一起就是一套能跑、能看、能维护的接口自动化框架。它不追求什么高大上的架构但胜在落地快、门槛低、谁都能接手。这篇文章适合几类人看一是刚接触接口自动化、不知道从哪儿下手的新手二是手里有一堆接口但用例管理混乱、想找个轻量方案的老手三是团队里需要给非技术同学提供用例编辑入口的测试负责人。我会把整个搭建过程拆开讲包括为什么这么选型、每一步的坑在哪、参数怎么配、报告怎么生成尽量让你看完就能照着搭一套出来。提醒一句这套方案适合接口数量在几十到几百这个量级、团队规模不大的场景。如果你的项目接口上千、需要分布式执行、需要和CI深度集成那Excel方案会在并发和版本管理上遇到瓶颈那时候该上数据库或者专业测试平台就得上别硬扛。2. 四个核心组件到底各自解决什么问题在动手之前先把这四个东西的定位理清楚不然后面写代码容易糊成一团。很多人搭框架失败不是因为代码写错了而是因为没想明白每个组件该干什么、不该干什么。2.1 unittest执行调度和断言的主力unittest是Python标准库自带的测试框架不需要额外安装这是它最大的优势。它的核心价值在于三点用例组织、断言机制、测试夹具。用例组织方面unittest通过TestCase类把一组相关的测试方法聚在一起每个以test_开头的方法就是一个独立用例。执行的时候框架会自动发现这些方法并逐个运行你不用自己写循环去遍历。断言机制是unittest的另一个重点。它提供了assertEqual、assertTrue、assertIn、assertRaises等一系列断言方法每个断言失败时会抛出带有详细信息的异常告诉你期望值和实际值分别是什么。这比你自己写if判断然后print要规范得多因为断言失败会直接标记用例为失败而不是悄悄跳过。测试夹具指的是setUp和tearDown这两个方法。setUp在每个用例执行前运行tearDown在每个用例执行后运行。接口测试里setUp通常用来做登录、获取token、准备测试数据tearDown用来清理数据、关闭连接。这个机制保证了用例之间的独立性不会因为上一个用例改了数据导致下一个用例失败。我见过有人不用unittest自己写个for循环遍历用例列表然后try-except包住每个请求。这样也能跑但一旦用例多了失败定位、用例跳过、批量执行这些需求都得自己实现最后写出来的东西就是个残缺版的unittest。既然标准库已经有了没必要重复造轮子。2.2 xlrd把Excel用例读进内存xlrd是一个专门读取Excel文件的Python库支持.xls和.xlsx两种格式。它的工作方式很直接打开工作簿选中工作表然后按行按列读取单元格的值。为什么选xlrd而不是pandas因为pandas太重了。pandas读Excel确实方便一行read_excel就搞定但它会把整个文件加载成一个DataFrame对于用例管理这种只需要按行遍历的场景来说属于杀鸡用牛刀。而且pandas对Excel格式的兼容性有时候会出问题特别是遇到合并单元格、空行、特殊字符的时候。xlrd更轻量控制粒度更细出问题也更容易定位。不过这里有个版本坑必须提前说。xlrd从2.0.0版本开始不再支持.xlsx格式只支持.xls。如果你装的是最新版xlrd然后去读.xlsx文件会直接报错。解决办法有两个要么把xlrd降级到1.2.0要么把用例文件存成.xls格式。我个人的建议是降级到1.2.0因为.xls格式有行数限制65536行虽然用例一般到不了这个量级但.xlsx毕竟是更现代的格式。pip install xlrd1.2.0降级之后xlrd就能正常读取.xlsx了。这个坑我踩过当时排查了半天以为是文件路径问题结果是版本不兼容。2.3 xlsxwriter把测试结果写成报告xlsxwriter是一个专门用来写Excel文件的库注意是“写”它不能读。它的强项在于格式控制字体、颜色、边框、单元格宽度、条件格式、图表基本上Excel里能手动做的格式它都能用代码实现。用它来生成测试报告的好处是直观。测试跑完之后打开一个Excel文件通过的用例标绿色失败的标红色还能带上请求参数、响应结果、耗时、失败原因这些信息。对于不习惯看命令行输出或者HTML报告的同事来说Excel报告是最容易接受的。xlsxwriter有一个限制它只能创建新文件不能修改已有文件。这意味着每次生成报告都是覆盖式的不能追加。对于测试报告来说这不是问题因为每次执行都是一次完整的记录覆盖旧的反而更清晰。2.4 三者如何串成一条流水线把上面三个组件串起来整个流程是这样的xlrd读取Excel用例文件把每一行解析成一个字典字典的key是表头value是单元格的值。unittest的TestCase接收这些字典每个字典生成一个测试方法或者在一个测试方法里遍历所有字典。测试方法里发请求、拿响应、做断言。断言结果和响应数据收集起来交给xlsxwriter写入一个新的Excel报告文件。这个流水线里Excel既是输入也是输出中间的执行逻辑由unittest和requests发请求用完成。整个架构没有数据库、没有消息队列、没有复杂的依赖注入就是一条直线。对于中小项目来说这种简单直接的结构反而最不容易出问题。组件角色关键点unittest执行引擎用例组织、断言、夹具xlrd用例读取注意版本兼容.xlsxxlsxwriter报告生成只写不读格式灵活requests接口调用实际发HTTP请求3. Excel用例表的结构设计与字段约定Excel用例表设计得好不好直接决定了后面代码写起来顺不顺。我见过一些用例表字段随意加有的列叫“url”有的列叫“接口地址”有的列叫“请求地址”读的时候还得做映射纯属给自己找麻烦。所以第一步就是把字段约定死。3.1 必填字段与选填字段的划分一个接口用例最少需要这些信息用例编号、用例名称、请求方法、请求地址、请求参数、期望结果。这六个是必填的。选填的包括请求头、超时时间、是否执行、前置条件、备注。用例编号用来唯一标识一条用例报告里显示失败用例的时候靠它定位。用例名称是给人看的描述这条用例在测什么。请求方法就是GET、POST、PUT、DELETE这些。请求地址可以是完整URL也可以是相对路径如果是相对路径需要在代码里拼上base_url。请求参数是发给接口的数据GET请求会拼在URL后面POST请求会放在body里。期望结果用来做断言可以是一个状态码也可以是一段JSON片段。选填字段里“是否执行”这个字段很实用。有时候某条用例暂时不想跑但又不想删掉就可以把它标记为“否”代码里读到这个字段就跳过。前置条件可以用来描述这条用例依赖哪些数据虽然代码不一定自动处理但至少给人看的时候清楚。3.2 参数格式的统一约定请求参数这一列最容易出问题。有人写{username:test,password:123}有人写usernametestpassword123还有人写test,123。格式不统一代码就没法统一解析。我的做法是统一用JSON字符串。不管什么请求方法参数都写成JSON格式的字符串。代码里读到之后用json.loads解析成字典然后根据请求方法决定是拼URL还是放body。这样只需要一套解析逻辑不用为每种格式写分支。import json param_str row[请求参数] if param_str: params json.loads(param_str) else: params {}如果某个接口的参数不是JSON格式比如是表单或者XML那就在请求头里指定Content-Type代码里根据Content-Type做不同处理。但这种情况尽量少能统一就统一。3.3 多环境配置的分离思路测试环境、预发布环境、生产环境的base_url不一样这个不能写死在用例表里。我的做法是在代码里维护一个环境配置字典用例表里只写相对路径。ENV_CONFIG { test: http://test.example.com, staging: http://staging.example.com, prod: http://prod.example.com }执行的时候通过命令行参数指定用哪个环境代码里把base_url和用例表里的相对路径拼起来。这样同一份用例表可以在不同环境跑不用改任何用例数据。注意生产环境的接口自动化测试要格外谨慎特别是涉及写操作的接口。建议在生产环境只跑读接口写接口用专门的测试数据或者直接跳过。3.4 一个实际的用例表长什么样下面是一个简化版的用例表示例字段名和顺序可以按自己的习惯调整但一旦定下来就不要随便改因为代码里的列名映射是写死的。用例编号用例名称请求方法请求地址请求头请求参数期望状态码期望响应是否执行TC001正常登录POST/api/login{Content-Type:application/json}{username:admin,password:123456}200{code:0}是TC002密码错误POST/api/login{Content-Type:application/json}{username:admin,password:wrong}200{code:1001}是TC003用户名为空POST/api/login{Content-Type:application/json}{username:,password:123456}200{code:1002}是这个表里期望状态码和期望响应是分开的。状态码是HTTP层面的响应是业务层面的。有些接口HTTP状态码永远是200业务错误通过响应体里的code字段区分这种情况就要靠期望响应来断言。4. 从读表到发请求的完整代码链路这一部分把代码拆开讲从读取Excel到发请求到断言每一步都给出可运行的代码和解释。代码用Python 3写依赖requests、xlrd、xlsxwriter三个库。4.1 用xlrd把每一行变成字典先写一个读取Excel的工具函数。它的职责很简单打开文件找到指定的工作表把第一行当表头从第二行开始每一行组装成一个字典。import xlrd def read_excel(file_path, sheet_name): workbook xlrd.open_workbook(file_path) sheet workbook.sheet_by_name(sheet_name) headers [sheet.cell_value(0, col) for col in range(sheet.ncols)] cases [] for row_idx in range(1, sheet.nrows): row_data {} for col_idx in range(sheet.ncols): cell_value sheet.cell_value(row_idx, col_idx) row_data[headers[col_idx]] cell_value cases.append(row_data) return cases这段代码有几个细节要注意。sheet.cell_value返回的是单元格的值如果是数字会返回float如果是文本返回str。有时候用例编号写成纯数字读出来会变成1.0这种浮点数显示的时候很难看。解决办法是在写Excel的时候把编号列设成文本格式或者在代码里做转换。另外空行的处理也要考虑。如果Excel中间有空行sheet.nrows会把空行也算进去读出来就是一堆空字典。可以在组装字典之后加一个判断如果所有值都是空字符串就跳过。if any(str(v).strip() for v in row_data.values()): cases.append(row_data)4.2 请求参数的解析与请求发送拿到字典之后下一步是解析参数并发请求。这里用requests库它是Python里发HTTP请求最常用的库API简洁文档齐全。import requests import json def send_request(case, base_url): method case[请求方法].upper() url base_url case[请求地址] headers json.loads(case[请求头]) if case[请求头] else {} params json.loads(case[请求参数]) if case[请求参数] else {} if method GET: response requests.get(url, paramsparams, headersheaders, timeout10) elif method POST: response requests.post(url, jsonparams, headersheaders, timeout10) elif method PUT: response requests.put(url, jsonparams, headersheaders, timeout10) elif method DELETE: response requests.delete(url, paramsparams, headersheaders, timeout10) else: raise ValueError(f不支持的请求方法: {method}) return response这里有几个实操经验。第一timeout一定要设不设的话遇到接口卡死整个测试就挂在那里不动了。10秒是个比较合理的值具体可以根据接口性能调整。第二POST请求用jsonparams而不是dataparams前者会自动设置Content-Type为application/json后者需要手动设置。第三headers从Excel读出来是字符串要用json.loads转成字典。4.3 断言逻辑状态码和响应体的双重校验发完请求拿到响应接下来是断言。断言分两层先看HTTP状态码再看响应体里的业务字段。def assert_response(response, case): expected_status int(case[期望状态码]) assert response.status_code expected_status, \ f状态码不匹配期望{expected_status}实际{response.status_code} expected_body case[期望响应] if expected_body: expected_json json.loads(expected_body) actual_json response.json() for key, value in expected_json.items(): assert key in actual_json, f响应中缺少字段: {key} assert actual_json[key] value, \ f字段{key}不匹配期望{value}实际{actual_json[key]}这个断言逻辑是“包含式”的只检查期望响应里列出的字段不要求响应体和期望完全一致。这样更灵活因为接口返回的字段往往很多没必要每个都断言。如果某个字段是动态的比如时间戳、token就不写进期望响应里。4.4 把用例包装成unittest测试方法前面写的都是普通函数现在要把它们塞进unittest的框架里。有两种做法一种是一个测试方法遍历所有用例另一种是动态生成测试方法每个用例一个方法。第一种做法简单但缺点是所有用例算一个测试结果一条失败整个方法就失败报告里看不出具体哪条用例挂了。第二种做法复杂一点但报告更清晰。我推荐第二种。import unittest class TestApi(unittest.TestCase): classmethod def setUpClass(cls): cls.base_url http://test.example.com cls.cases read_excel(cases.xlsx, Sheet1) def _run_case(self, case): response send_request(case, self.base_url) assert_response(response, case) return response然后动态生成测试方法def generate_test_method(case): def test_method(self): self._run_case(case) return test_method for case in cases: if case[是否执行] 是: method_name ftest_{case[用例编号]} setattr(TestApi, method_name, generate_test_method(case))这样每个用例都会变成一个独立的test_方法unittest执行的时候会分别报告通过还是失败。用例编号作为方法名的一部分报告里一眼就能看出是哪条用例。注意动态生成方法的时候方法名不能重复也不能包含特殊字符。用例编号如果是TC-001这种带横杠的要替换成下划线否则setattr会出问题。5. 用xlsxwriter生成一份能看的测试报告测试跑完结果都在内存里接下来要把它们写成Excel报告。xlsxwriter的API不算复杂但要写出一份清晰易读的报告还是有一些细节要处理。5.1 报告表头的设计报告至少要有这些列用例编号、用例名称、请求方法、请求地址、请求参数、期望结果、实际结果、执行状态、耗时、失败原因。执行状态用颜色区分通过是绿色失败是红色跳过是灰色。import xlsxwriter def write_report(results, output_path): workbook xlsxwriter.Workbook(output_path) worksheet workbook.add_worksheet(测试报告) header_format workbook.add_format({ bold: True, bg_color: #D9E1F2, border: 1, align: center, valign: vcenter }) pass_format workbook.add_format({bg_color: #C6EFCE, border: 1}) fail_format workbook.add_format({bg_color: #FFC7CE, border: 1}) headers [用例编号, 用例名称, 请求方法, 请求地址, 请求参数, 期望结果, 实际结果, 执行状态, 耗时(ms), 失败原因] for col, header in enumerate(headers): worksheet.write(0, col, header, header_format)表头格式用浅蓝色背景加粗这是比较通用的做法看起来清爽。通过和失败的格式分别用浅绿和浅红这是Excel里约定俗成的颜色不用额外解释。5.2 逐行写入结果与条件格式写入数据的时候根据执行状态选择不同的格式。for row_idx, result in enumerate(results, start1): status_format pass_format if result[status] PASS else fail_format worksheet.write(row_idx, 0, result[case_id]) worksheet.write(row_idx, 1, result[case_name]) worksheet.write(row_idx, 2, result[method]) worksheet.write(row_idx, 3, result[url]) worksheet.write(row_idx, 4, result[params]) worksheet.write(row_idx, 5, result[expected]) worksheet.write(row_idx, 6, result[actual]) worksheet.write(row_idx, 7, result[status], status_format) worksheet.write(row_idx, 8, result[elapsed]) worksheet.write(row_idx, 9, result.get(error, ))列宽也要设置一下不然默认宽度太窄内容显示不全。worksheet.set_column(0, 0, 12) worksheet.set_column(1, 1, 20) worksheet.set_column(2, 2, 10) worksheet.set_column(3, 3, 30) worksheet.set_column(4, 4, 40) worksheet.set_column(5, 5, 30) worksheet.set_column(6, 6, 30) worksheet.set_column(7, 7, 10) worksheet.set_column(8, 8, 10) worksheet.set_column(9, 9, 40)5.3 汇总统计与失败用例的突出显示报告的最后可以加一个汇总区域统计通过多少、失败多少、通过率是多少。这个信息放在报告顶部或者底部都行我习惯放在底部因为看报告的人通常先看具体用例再看整体情况。total len(results) passed sum(1 for r in results if r[status] PASS) failed total - passed pass_rate f{passed / total * 100:.2f}% if total 0 else 0% summary_row total 2 worksheet.write(summary_row, 0, 总计, header_format) worksheet.write(summary_row, 1, total) worksheet.write(summary_row, 2, 通过, header_format) worksheet.write(summary_row, 3, passed) worksheet.write(summary_row, 4, 失败, header_format) worksheet.write(summary_row, 5, failed) worksheet.write(summary_row, 6, 通过率, header_format) worksheet.write(summary_row, 7, pass_rate) workbook.close()失败用例的失败原因那一列如果内容很长可以设置自动换行。xlsxwriter的format支持text_wrap属性加上之后单元格内容会自动折行不会撑得太宽。6. 实际跑起来才会遇到的坑与应对代码写完了能跑通不代表没问题。下面这些坑都是我实际踩过的有些排查起来还挺费劲提前知道能省不少时间。6.1 xlrd版本导致的读取失败前面提过xlrd 2.0.0不支持.xlsx这里再强调一次因为这个问题太常见了。报错信息大概是xlrd.biffh.XLRDError: Excel xlsx file; not supported。看到这个报错第一反应就是检查xlrd版本。pip show xlrd如果版本是2.0.0或更高降级到1.2.0即可。降级之后如果还报错检查一下是不是有多个Python环境pip装到了另一个环境里。6.2 中文乱码与编码问题Excel里的中文读出来有时候会变成乱码特别是.xls格式的文件。这通常是因为文件编码和xlrd的默认编码不一致。解决办法是在打开文件的时候指定encoding。workbook xlrd.open_workbook(file_path, encoding_overrideutf-8)如果utf-8不行试试gbk。另外写报告的时候xlsxwriter默认用utf-8一般不会有乱码问题。但如果用例数据里有emoji或者特殊符号可能会出问题建议在写入之前做一次清洗。6.3 接口依赖与执行顺序有些接口有依赖关系比如必须先登录拿到token才能调用其他接口。Excel用例表是按行排列的unittest默认按方法名的字母顺序执行不一定和Excel里的顺序一致。如果用例之间有依赖执行顺序乱了就会失败。解决办法有两种。一种是让用例自包含每条用例自己登录拿token不依赖其他用例。这样最稳妥但会增加重复请求。另一种是在setUpClass里统一登录把token存成类变量所有用例共用。这样效率高但要求登录接口稳定。我倾向于第一种虽然多几次登录请求但用例之间完全独立不会因为某条用例改了数据导致后面的用例连锁失败。接口自动化测试里用例独立性比执行效率更重要。6.4 断言失败后的报告记录unittest的断言失败会抛AssertionError如果不在测试方法里捕获这个异常会被unittest框架捕获并标记用例失败但异常信息不会自动传到报告生成逻辑里。所以需要在测试方法里做一层包装把断言结果和异常信息都收集起来。def _run_case(self, case): result { case_id: case[用例编号], case_name: case[用例名称], method: case[请求方法], url: case[请求地址], params: case[请求参数], expected: case[期望响应], status: PASS, error: } start time.time() try: response send_request(case, self.base_url) assert_response(response, case) result[actual] response.text[:500] except AssertionError as e: result[status] FAIL result[error] str(e) result[actual] response.text[:500] if response in dir() else except Exception as e: result[status] FAIL result[error] f请求异常: {str(e)} result[actual] finally: result[elapsed] int((time.time() - start) * 1000) return result这样不管断言失败还是请求异常都能记录到结果里最后统一写进报告。注意response.text[:500]只截取前500个字符避免响应体太大导致报告文件臃肿。6.5 并发执行时的资源竞争如果用例数量多串行执行太慢可能会想用多线程。但多线程写同一个Excel报告会有资源竞争问题xlsxwriter不是线程安全的。解决办法是每个线程把结果写到自己的列表里最后合并成一个列表再写报告。或者干脆不用多线程接口测试的瓶颈通常在网络IO用线程池发请求可以但结果收集要加锁。from concurrent.futures import ThreadPoolExecutor import threading lock threading.Lock() results [] def run_and_collect(case): result run_case(case) with lock: results.append(result) with ThreadPoolExecutor(max_workers5) as executor: executor.map(run_and_collect, cases)线程数不要设太大5到10个就够了。设太大反而会因为频繁切换上下文导致性能下降而且有些接口服务端有限流并发太高会被拒绝。7. 让这套框架更好用的几个扩展方向基础版本跑通之后可以根据实际需求做一些扩展。这些扩展不是必须的但做了之后维护起来会更省心。7.1 用例参数化的几种做法同一个接口往往需要测多组参数比如登录接口要测正常密码、错误密码、空密码、超长密码。如果每组参数写一行用例表会很长。参数化可以把这些组合并成一行用特殊符号分隔。比如请求参数写成{username:admin,password:[123456,wrong,]}代码里检测到值是列表就展开成多条用例。这样用例表更紧凑但解析逻辑会复杂一点。另一种做法是在Excel里用多行每行一组参数代码不做特殊处理。我倾向于后者因为简单直观不容易出错。7.2 把报告推送到团队常用的地方Excel报告生成之后默认是存在本地。如果想让团队其他人也能看到可以加一个发送邮件的功能把报告作为附件发出去。Python的smtplib和email库可以搞定。import smtplib from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email import encoders def send_report(report_path, to_addrs): msg MIMEMultipart() msg[Subject] 接口自动化测试报告 msg[From] senderexample.com msg[To] , .join(to_addrs) with open(report_path, rb) as f: part MIMEBase(application, octet-stream) part.set_payload(f.read()) encoders.encode_base64(part) part.add_header(Content-Disposition, fattachment; filename{report_path}) msg.attach(part) server smtplib.SMTP(smtp.example.com, 25) server.sendmail(msg[From], to_addrs, msg.as_string()) server.quit()邮件里的SMTP服务器地址和端口要根据实际环境配置有些公司用内部邮件服务器有些用外部服务。如果SMTP需要认证还要加login步骤。7.3 和持续集成工具的对接如果团队用Jenkins或者类似的CI工具可以把这套脚本配成定时任务或者代码提交触发。CI工具执行的时候通过命令行参数传入环境名和用例文件路径脚本跑完生成报告CI工具负责归档和展示。python run_tests.py --env test --cases cases.xlsx --report report.xlsxrun_tests.py里用argparse解析参数然后调用前面写的读取、执行、报告逻辑。这样一套代码可以在本地跑也可以在CI上跑不用改任何东西。7.4 用例表的版本管理Excel文件是二进制格式用git管理的时候没法看diff两个人同时改还容易冲突。如果团队用git建议把用例表拆成多个小文件按模块分减少冲突概率。或者干脆把用例数据导出成CSVCSV是文本格式git能看diff冲突也好解决。但CSV不支持多sheet和格式取舍看团队习惯。我在实际项目里的做法是用例表用Excel维护但每次修改之后导出一份CSV提交到gitExcel文件本身也提交但主要看CSV的diff来追踪变更。这样既保留了Excel的编辑便利又有了版本管理的可追溯性。最后分享一个小技巧用例表里加一列“最后修改人”和“最后修改时间”每次改动用例的时候顺手填一下。时间长了之后哪条用例是谁加的、什么时候加的、为什么加一目了然。这个习惯在团队协作里特别有用尤其是人员流动比较频繁的项目。