python小脚本，整理项目中接口

今天需要汇总一下接口数据，但是数据量有点多，cv100次之后，感觉心慌气喘，头昏眼花。遂决定用python脚本搞搞

第一步

拷贝接口文件到txt文档，

接口格式类似这样 编写脚本

#!/usr/bin/python
# -*- coding: UTF-8 -*- 
import re
import json
import openpyxl #excel相关框架
#打开接口文件
file = open("/Users/admin/Documents/个人/竞拍app.txt",'r')
list1 = []
#逐行读取数据到list1列表
for i in file:
	list1.append(i)
list2 = list1


#遍历列表
for (indexj,j) in enumerate(list1):
	#正则判断是否有接口定义，类似SXAPIType***
	searchObj = re.search(r'SXAPIType([a-zA-Z0-9]*)',j)
	if searchObj:
		# print(str(searchObj.group()))
		#若有，遍历列表，查找下面数据对应的url，注释，getpost方法，添加到前面
		for (indexi,i) in enumerate(list1):
			if indexi != indexj:
				searchObji = re.search(r'SXAPIType([a-zA-Z0-9]*)',i)
				if searchObji:
					if searchObj.group() in searchObji.group():
						#“     ”分割符好分割，做写入excel操作
						list1[indexi] = j+"        "+i
						#添加完删除
						list1.remove(list1[indexj])
#写入app1文件						
file2 = open("/Users/admin/Documents/个人/竞拍app1.txt",'w')
for i in list1:
	i=i.strip('\n')
	file2.write(json.dumps(i, ensure_ascii=False))
	file2.write("\n")

第二步

写入excel。这里用openpyxl框架，其他框架没试

file3 = open("/Users/admin/Documents/个人/竞拍app1.txt",'r')
#读取上一步获取的txt文件
list3 = []
for i in file3:
	print(i.split())
	list3.append(i)



def write_excel(path,sheet_name,list):
	#打开对应xlsx文件
	excel = openpyxl.load_workbook("/Users/admin/Documents/个人/app接口.xlsx")


	# excel = openpyxl.Workbook("/Users/admin/Documents/个人/app接口.xlsx")
	sheet = excel.get_active_sheet()  # 获取当前活动sheet
	print(sheet)
	for i in range(0,len(list)):
		#strList 分割每一行元素
		strL = list[i].split()
		for j in range(0,len(strL)):
			#单元格赋值
			sheet.cell(row=i+1,column=j+1).value = strL[j]
			# sheet.write(i,j,strL[j])
	#保存excel
	excel.save(path)

write_excel("/Users/admin/Documents/个人/app接口.xlsx","app接口.xlsx",list3)

第三步

上一步生成的excel不是很好看，优化下

#!/usr/bin/python
# -*- coding: UTF-8 -*- 
import re
import json
import openpyxl 
from openpyxl import load_workbook
from openpyxl import Workbook
#从旧excel复制到新excel
def zl_excel(path):
	try:
		excel = openpyxl.load_workbook("/Users/admin/Documents/个人/app接口.xlsx",data_only=True)
	except FileNotFoundError:
		print("File could not be found.")
	excel1 = openpyxl.load_workbook("/Users/admin/Documents/个人/app接口1.xlsx",data_only=True)


	# excel = openpyxl.Workbook("/Users/admin/Documents/个人/app接口.xlsx")
	sheet = excel.get_sheet_by_name("sheet")  
	sheet1 = excel1.get_sheet_by_name("111") 
	dic = getDic()
	# print(dic.keys())

	for row in sheet.iter_rows():
		for cell in row:
			if cell.value == None:
				continue
			if "SXAPIType" in cell.value:

				# print(cell.value)
				sheet1.cell(row=cell.row,column = 5).value = cell.value
				searchObj = re.search(r'SXAPIType([a-zA-Z0-9]*)',cell.value)
				if searchObj:
					#添加匹配参数
					if searchObj.group() in dic.keys() :
						sheet1.cell(row=cell.row,column = 6).value = str(dic[searchObj.group()])
			if "POST" in cell.value or "GET" in cell.value:
				# print(cell.value)
				sheet1.cell(row=cell.row,column = 4).value = cell.value
			if "{@" in cell.value:
				# print(cell.value)
				sheet1.cell(row=cell.row,column = 3).value = cell.value
			if "//" in cell.value:
				# print(cell.value)
				sheet1.cell(row=cell.row,column = 2).value = cell.value
	excel1.save(path)

#获取匹配参数
def getDic():
	file = open("/Users/admin/Documents/个人/竞拍app参数.txt",'r')
	text = file.read()
	list1 = text.split('/**',-1)
	paramDic={}
	for item in list1:
		#匹配参数  SXAPIType
		searchObj = re.search(r'SXAPIType([a-zA-Z0-9]*)',item)
		params = re.compile(r'@"([a-zA-Z0-9]*)"').findall(item)
		if searchObj:
			if params:
				print("\n")
				paramDic[str(searchObj.group())]=str(params)
				print("key:"+str(searchObj.group()))

				print("value:"+paramDic[str(searchObj.group())])

	return paramDic

zl_excel("/Users/admin/Documents/个人/app接口1.xlsx")

结果获取的excel

心满意足