• 第三方庫，pandas、openpyxl

處理excel數(shù)據(jù)

import pandas as ps
from openpyxl import load_workbook
from openpyxl import Font


# 找到excel中有效的數(shù)據(jù)，寫入到新的excel
def findValidData(source_file_path, target_file_path)
    # 讀取數(shù)據(jù)框架，返回DataFrame
    dataframe = pd.read_excel(source_file_path)

    # DataFrame數(shù)據(jù)過濾，找到接口管理中已經(jīng)寫了的有關(guān)聯(lián)用例的接口
    filtered_data = dataframe[dataframe[‘接口用例數(shù)’]+dataframe[‘場景用例數(shù)’]>0]

    # DataFrame數(shù)據(jù)寫入新的excel文件
    filtered_data.to_excel(out_file_path, index=False)

    # excel數(shù)據(jù)處理：第二列excel數(shù)據(jù)：${ddsApiHost}/dds-tms/taskRestService/getReal  變?yōu)?/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更新保存當(dāng)前excel文件
  workbook.save(target_file_path)

對比兩個(gè)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
    
    # 在目標(biāo)excel中的【D列】，所有要寫的接口urls
    urlArray2 = []
    for row2 in range(2, sheet2.max_row+1):
     urlArray2.append(sheet2[f’D{row}’].value)
    
    # 判斷接口在源excel中自動(dòng)化平臺(tái)已寫，但是在目標(biāo)excel中沒標(biāo)記已自動(dòng)化的接口，然后標(biāo)記顏色
     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)