python操作excel

• 第三方库，pandas、openpyxl

处理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)