处理excel数据
import pandas as ps
from openpyxl import load_workbook
from openpyxl import Font
# 找到excel中有效的数据，写入到新的excel
def findValidData(source_file_path, target_file_path)
# 读取数据框架，返回DataFrame
dataframe = pd.read_excel(source_file_path)
# DataFrame数据过滤，找到接口管理中已经写了的有关联用例的接口
filtered_data = dataframe[dataframe[‘接口用例数’]+dataframe[‘场景用例数’]>0]
# DataFrame数据写入新的excel文件
filtered_data.to_excel(out_file_path, index=False)
# excel数据处理：第二列excel数据：${ddsApiHost}/dds-tms/taskRestService/getReal 变为 /dds-tms/taskRestService/getReal
# 打开excel文件，返回workbook，操作sheet
workbook = load_workbook(out_file_path)
sheet = workbook.active
# 从第二行开始到最后一行
for row in (2, sheet.max_row+1):
# 获取第二列，B列的值
url = sheet[f’B{row}’].value
sheet[f'B{row}'].value = url.split("}")[1] if url.startswith("$") else url
# workbook更新保存当前excel文件
workbook.save(target_file_path)
对比两个excel文件
def compareExcel(valid_file_path, all_api_path):
workbook1 = load_workbook(valid_file_path)
sheet1 = workbook1.active
workbook2 = load_workbook(valid_file_path)
sheet2 = workbook2.active
# 在目标excel中的【D列】，所有要写的接口urls
urlArray2 = []
for row2 in range(2, sheet2.max_row+1):
urlArray2.append(sheet2[f’D{row}’].value)
# 判断接口在源excel中自动化平台已写，但是在目标excel中没标记已自动化的接口，然后标记颜色
for row in range(2, sheet1.max_row+1):
url1 = sheet1[f’B{row}’].value
if url1 not in urlArray2:
sheet1[f’A{row}’].font = Font(color=‘0000FF, bold=True)
sheet1[f’B{row}’].font = Font(color=‘0000FF, bold=True)
# 更新保存源excel
workbook1.save(valid_file_path)
