pip install pandas
pip install pyyaml

import pandas as pd
import yaml


def excel_to_yaml(excel_path, output_yaml_path, excel_sheet_name=0):
    """
    將Excel文件轉(zhuǎn)換為YAML文件
    :param excel_path: Excel文件路徑
    :param output_yaml_path: 輸出YAML文件路徑
    :param excel_sheet_name: Excel文件中的表名，默認(rèn)為0（第一個(gè)表）
    """
    df = pd.read_excel(excel_path, sheet_name=excel_sheet_name)
    data_list = df.to_dict('records')
    for record in data_list:
        for key, value in record.items():
            if pd.isna(value):  # 處理空值
                record[key] = None
            elif hasattr(value, 'strftime'):  # 處理日期時(shí)間對(duì)象
                record[key] = value.strftime('%Y-%m-%d')
    with open(output_yaml_path, 'w', encoding='utf-8') as yaml_file:
        yaml.dump(data_list, yaml_file, allow_unicode=True, sort_keys=False)


def main():
    excel_path = "指數(shù)列表.xlsx"
    output_yaml_path = "out_yaml.yaml"
    excel_to_yaml(excel_path, output_yaml_path)


if __name__ == '__main__':
    main()

import openpyxl
import yaml
 
 
xlsx_name = "data.xlsx"
 
# 打開(kāi)Excel文件
wb = openpyxl.load_workbook(xlsx_name)
sheet = wb.active
 
# 讀取Excel表格數(shù)據(jù)并存儲(chǔ)為Python對(duì)象
data = {'data': []}
for row in sheet.iter_rows(min_row=2, values_only=True):
    item = {'name': row[0], 'age': row[1], 'email': row[2]}
    data['data'].append(item)
 
 
for i in data['data']:
    print(i)
 
# 將Python對(duì)象轉(zhuǎn)換成YAML字符串
yaml_data = yaml.dump(data)
 
# 將YAML字符串寫(xiě)入YAML文件
with open('data.yaml', 'w') as file:
    file.write(yaml_data)
 
# 讀取yaml文件內(nèi)容
with open('data.yaml', 'r') as file:
    dataR = yaml.load(file, Loader=yaml.FullLoader)
print(dataR)
 
 
'''
{'name': 's1', 'age': 22, 'email': 's1@xcel.com'}
{'name': 's2', 'age': 23, 'email': 's3@xcel.com'}
{'name': '宋', 'age': 24, 'email': 's4@xcel.com'}
{'data': [{'age': 22, 'email': 's1@xcel.com', 'name': 's1'}, {'age': 23, 'email': 's3@xcel.com', 'name': 's2'}, {'age': 24, 'email': 's4@xcel.com', 'name': '宋'}]}
'''

import yaml
import os
from collections import OrderedDict
import openpyxl


class TestCases_protocol:

    """This script is to convert excel file to yaml file."""

    def __init__(self,file_name,yaml_name):
        self.file_name = file_name
        self.yaml_name = yaml_name
        self.final_dict = OrderedDict()
        self.new_pltform_dic = OrderedDict()


    def create_dict(self):
        wb = openpyxl.load_workbook(self.file_name)
        sheet_names = wb.sheetnames
        count_cases=[]
        for sheet_name in sheet_names:
            print(sheet_name)
            ws = wb[sheet_name]
            max_row = ws.max_row
            count_cases.append(max_row-2)
            for n_row in range(3,max_row+1):
                script_type = ws.cell(row=n_row, column=1).value
                script_name = ws.cell(row=n_row, column=2).value
                Description = ws.cell(row=n_row, column=3).value
                script_path = ws.cell(row=n_row, column=4).value

                if not script_type:
                    continue
                else:
                    self.new_pltform_dic[script_name] = OrderedDict()
                    self.new_pltform_dic[script_name].update({
                        "type":script_type,
                        "loop":'1',
                        "duration":'60',
                        "description":Description,
                        "script_path":script_path,
                        "parameters": "default"
                    })
                # for k,v in new_pltform_dic.items():
                #   print(k,':',v)
        with open(self.yaml_name, 'w') as f:
            yaml.dump(self.new_pltform_dic, f)
            print(sum(count_cases))


if __name__=='__main__':
    case1 = TestCases_protocol('Test.xlsx','new.yaml')
    case1.create_dict()