Python之yaml与excel互相转换

python3环境下运行，使用openpyxl模块实现

调用逻辑关系，yaml to excel脚本调用source.yaml文件，生成excel表格，然后excel to yaml脚本调用生成的表格转换为yaml文件

1 source.yaml

- manufacturer: Audi
  PowerType: Gasoline
  Price: 2136000
  CarModel: RS7
  IsOnSale: False
  hubs:
  - PDKmodel: 带保时捷动态照明系统 (PDLS) 的 LED 主大灯
    description: 灰色巴新胡桃木内饰组件
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
  - PDKmodel: Audi328Li
    description: 带加热功能的烟灰色阿帕奇木运动型方向盘
    PDKnumber: 1
    IsPurchase: True
  Interior:
  - PDKmodel: 选配
    description: 裸车很少有4S店会卖
    PDKnumber: 4
    IsPurchase: False
  skylight:
  - PDKmodel: 金属漆和特殊颜色根据车型不同，价格也不同
    description: 金属漆的选配价格在0-13500元不等
    PDKnumber: 1
  - PDKmodel: 保时捷Panamera在车漆的选择上相对简单
    description: 因为它连金属车漆也是免费的
    PDKnumber: 4
    detailsInfo: 那相对标准车漆，金属漆肯定要更好一些了。
  - PDKmodel: 21寸911 Turbo Design
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性

- manufacturer: BMW
  PowerType: Electric
  CarModel: III series
  Price: 367000
  IsOnSale: True
  hubs:
  - PDKmodel: 宝马328Li
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
  Interior:
  - PDKmodel: 宝马328Li
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
    IsPurchase: True
  CarSpoilers:
  - PDKmodel: 宝马328Li
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
  security:
  - PDKmodel: 宝马328Li
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
    domesticVersion: [2,3,4,5]
  - PDKmodel: 宝马328Li
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
  skylight:
  - PDKmodel: 宝马328Li
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
    importVersion: [1,3,4,6]
    IsPurchase: True

- manufacturer: Porsche
  PowerType: Gasoline
  Price: 2136000
  CarModel: panamera
  IsOnSale: False
  hubs:
  - PDKmodel: 带保时捷动态照明系统 (PDLS) 的 LED 主大灯
    description: 灰色巴新胡桃木内饰组件
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性
  - PDKmodel: Audi328Li
    description: 带加热功能的烟灰色阿帕奇木运动型方向盘
    PDKnumber: 1
    IsPurchase: True
  Interior:
  - PDKmodel: 选配
    description: 裸车很少有4S店会卖
    PDKnumber: 4
    IsPurchase: False
  skylight:
  - PDKmodel: 金属漆和特殊颜色根据车型不同，价格也不同
    description: 金属漆的选配价格在0-13500元不等
    PDKnumber: 1
  - PDKmodel: 保时捷Panamera在车漆的选择上相对简单
    description: 因为它连金属车漆也是免费的
    PDKnumber: 4
    detailsInfo: 那相对标准车漆，金属漆肯定要更好一些了。
  - PDKmodel: 21寸911 Turbo Design
    description: 百公里加速4.1S
    PDKnumber: 4
    detailsInfo: 软件版本要求为:加速特性

2 yaml to excel.py

# -*- coding:utf8 -*-
from ruamel import yaml
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font,PatternFill,Alignment
# 表格颜色对照网址： https://zhuanlan.zhihu.com/p/154206853


class YamlToExcel(object):

    def __init__(self):
        # 实例化workbook
        self.work_book = Workbook()
        # 激活 worksheet
        self.work_sheet = self.work_book.active

    # 设置sheet表名
    def set_worksheet_title(self,manufacturer):
        self.work_sheet.title = manufacturer

    @classmethod
    def parse(cls):
        with open('source.yaml', encoding='UTF8') as f:
            data = f.read()
        return yaml.load(data, Loader=yaml.Loader)

    # 按照逻辑写入excel
    def write_excel(self):

        # 设置列宽
        self.work_sheet.column_dimensions['A'].width = 17.0
        self.work_sheet.column_dimensions['B'].width = 11.0
        self.work_sheet.column_dimensions['C'].width = 14.0
        self.work_sheet.column_dimensions['D'].width = 11.0
        self.work_sheet.column_dimensions['E'].width = 15.0
        self.work_sheet.column_dimensions['F'].width = 15.0
        self.work_sheet.column_dimensions['G'].width = 40.0
        self.work_sheet.column_dimensions['H'].width = 12.0
        self.work_sheet.column_dimensions['I'].width = 17.0
        self.work_sheet.column_dimensions['J'].width = 17.0
        self.work_sheet.column_dimensions['K'].width = 16.0
        self.work_sheet.column_dimensions['L'].width = 37.0
        self.work_sheet.column_dimensions['M'].width = 40.0

        align = Alignment(horizontal='center', vertical='center', wrap_text=True) # 设置表格居中

        self.set_worksheet_title('CarSale')

        # 第一列到第十三列 合并单元格
        self.work_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=13)
        # 设置字体颜色
        font_color = Font(color=colors.BLACK, size=12)
        self.work_sheet.cell(row=1, column=1, value='选配指南').font = font_color
        self.work_sheet.cell(row=1, column=1, value='选配指南').alignment = align
        # 填充背景颜色（橙色）
        background_color = PatternFill("solid", fgColor="FFBB02")
        self.work_sheet.cell(row=1, column=1).fill = background_color

        # 第二行描述信息
        description_list = [
            '动力类型', '售卖价格', '汽车厂商', '是否停售','汽车型号', '选配配件', '组件型号', '数量', '国产', '进口', '购买资格', '描述', '备注'
        ]
        column_number = 1
        for title_name in description_list:

            self.work_sheet.cell(row=2, column=column_number, value='{}'.format(title_name)).alignment = align
            column_number = column_number + 1

        # 第三行字段信息
        field_list = [
            'PowerType', 'Price', 'manufacturer', 'IsOnSale', 'CarModel', 'Selection', 'PDKmodel','PDKnumber','domesticVersion','importVersion', 'IsPurchase', 'description','detailsInfo'
        ]
        column_number = 1
        for field_name in field_list:
            self.work_sheet.cell(row=3, column=column_number, value='{}'.format(field_name)).alignment = align
            column_number = column_number + 1

        # 列对照表
        column_compare_dict = {
            "PDKmodel": 7, 'PDKnumber': 8, 'domesticVersion': 9, 'importVersion': 10,'IsPurchase': 11,'description':12,'detailsInfo':13
        }

        # 表格内容写入
        component_start_row = 4
        for component in self.parse():

            # Title ： 角色/套餐ID/厂商/是否停售/型号
            self.work_sheet.cell(row=component_start_row, column=1, value='{}'.format(component.get('PowerType'))).alignment = align
            self.work_sheet.cell(row=component_start_row, column=2, value='{}'.format(component.get('Price'))).alignment = align
            self.work_sheet.cell(row=component_start_row, column=3, value='{}'.format(component.get('manufacturer'))).alignment = align
            self.work_sheet.cell(row=component_start_row, column=4,value='{}'.format(component.get('IsOnSale') if component.get('IsOnSale') else '')).alignment = align
            self.work_sheet.cell(row=component_start_row, column=5, value='{}'.format(component.get('CarModel'))).alignment = align

            # 占用行数汇总
            employ_sum = list()
            for key,value in component.items():
                title_list = ['PowerType', 'manufacturer', 'Price', 'CarModel', 'IsOnSale']
                if set(title_list) > set(component.keys()):
                    employ_sum.append(1)
                    break
                elif key not in title_list:
                    employ_sum.append(len(component.get(key)))

            # 元素值
            component_keys = [ key for key in component.keys() if key not in ['PowerType', 'manufacturer', 'Price', 'CarModel', 'IsOnSale'] ]
            temp_start_row = component_start_row
            for keys in component_keys:

                self.work_sheet.cell(row=temp_start_row, column=6, value='{}'.format(keys))

                if len(component.get(keys)) == 1:
                    for k, v in component.get(keys)[0].items():
                        if k in column_compare_dict.keys():
                            self.work_sheet.cell(row=temp_start_row, column=column_compare_dict.get(k),value='{}'.format(v))
                    temp_start_row = temp_start_row + 1
                    print(keys,temp_start_row)
                else:
                    part_merge_row = temp_start_row
                    for part in component.get(keys):
                        print(component)
                        print(part_merge_row,part)
                        for k, v in part.items():
                            if k in column_compare_dict.keys():
                                self.work_sheet.cell(row=part_merge_row, column=column_compare_dict.get(k),value='{}'.format(v))
                        part_merge_row = part_merge_row + 1
                    temp_start_row = temp_start_row + len(component.get(keys))

            # 合并单元格
            component_end_row = sum(employ_sum) + component_start_row - 1
            print(component_start_row,component_end_row)
            # 合并单元格
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=1, end_row=component_end_row, end_column=1)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=2, end_row=component_end_row, end_column=2)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=3, end_row=component_end_row, end_column=3)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=4, end_row=component_end_row, end_column=4)
            self.work_sheet.merge_cells(start_row=component_start_row, start_column=5, end_row=component_end_row, end_column=5)

            # component数据之间添加空格,空格填充背景颜色(绿色)
            if component != self.parse()[-1]:
                background_color = PatternFill("solid", fgColor="00CCFFFF")
                self.work_sheet.cell(row=component_end_row + 1, column=1).fill = background_color
                self.work_sheet.cell(row=component_end_row + 1, column=1, value='{}'.format(''))
                self.work_sheet.merge_cells(start_row=component_end_row + 1, start_column=1, end_row=component_end_row + 1, end_column=13)
            # print('end row',component_end_row)

            # 下一个起始值
            component_start_row = sum(employ_sum) + component_start_row + 1

    def save_excel(self):
        self.write_excel()
        self.work_book.save("Porsche.xlsx")


if __name__ == '__main__':
    c = YamlToExcel()
    c.save_excel()

3 excel to yaml.py

# -*- coding:utf8 -*-
from openpyxl import load_workbook


class ExcelToYaml(object):

    def __init__(self):
        # 打开已有表格
        self.workbook = load_workbook('Porsche.xlsx')
        # print(workbook)
        self.worksheet = self.workbook['CarSale']

    @classmethod
    def create_file(cls,content):
        with open('Excel表格转换Yaml.yaml', 'a+') as file:
            file.write('{}{}'.format(content,'\n') )

    def interval(self):
        # 范围区间
        interval_list = []
        temporary = 0
        for row in range(4, self.worksheet.max_row + 1):
            if self.worksheet.cell(row=row, column=1).value:
                interval_list.append([temporary, row - 1])
                temporary = row
        interval_list.append([temporary, self.worksheet.max_row + 1])
        return interval_list[1:]

    def run(self):
        components = list()
        for interval in self.interval():
            start = interval[0]
            end = interval[1]
            title = {}
            details_merge = {}
            has_value_row = None

            for row in range(start, end):
                title.update({'manufacturer': self.worksheet.cell(row=row, column=3).value}) if self.worksheet.cell(row=row,column=3).value else None
                title.update({'PowerType': self.worksheet.cell(row=row, column=1).value}) if self.worksheet.cell(row=row,column=1).value else None
                if self.worksheet.cell(row=row, column=2).value and self.worksheet.cell(row=row, column=2).value != 'None':
                    title.update({'Price': int(self.worksheet.cell(row=row, column=2).value)})
                title.update({'IsOnSale': True}) if self.worksheet.cell(row=row,column=4).value else None
                title.update({'CarModel': self.worksheet.cell(row=row, column=5).value}) if self.worksheet.cell(row=row,column=5).value else None

                details1 = {}
                details2 = {}

                if self.worksheet.cell(row=row, column=6).value:
                    has_value_row = row
                    if self.worksheet.cell(row=row, column=7).value:
                        details1.update({'PDKmodel': self.worksheet.cell(row=row, column=7).value})
                    if self.worksheet.cell(row=row, column=8).value:
                        if int(self.worksheet.cell(row=row, column=8).value.isdigit()):
                            details1.update({'PDKnumber': int(self.worksheet.cell(row=row, column=8).value)})
                        else:
                            details1.update({'PDKnumber': self.worksheet.cell(row=row,column=8).value})
                    if self.worksheet.cell(row=row, column=9).value:
                        details1.update({'domesticVersion': self.worksheet.cell(row=row, column=9).value})
                    if self.worksheet.cell(row=row, column=10).value:
                        details1.update({'excludeVersion': self.worksheet.cell(row=row, column=10).value})
                    if self.worksheet.cell(row=row, column=11).value:
                        details1.update({'IsPurchase': self.worksheet.cell(row=row, column=11).value})
                    if self.worksheet.cell(row=row, column=12).value:
                        details1.update({'description': self.worksheet.cell(row=row, column=12).value})
                    if self.worksheet.cell(row=row, column=13).value and self.worksheet.cell(row=row, column=13).value.strip().lower() != 'none':
                        details1.update({'detailsInfo': self.worksheet.cell(row=row, column=13).value})
                    previous = self.worksheet.cell(row=row, column=6).value
                    details_merge.update({previous: [details1]})

                else:
                    details2.update({'PDKmodel': self.worksheet.cell(row=row, column=7).value}) if self.worksheet.cell(row=row,column=7).value else None
                    details2.update({'PDKnumber': int(self.worksheet.cell(row=row, column=8).value) if int(self.worksheet.cell(row=row, column=8).value.isdigit()) else self.worksheet.cell(row=row,column=8).value}) if self.worksheet.cell(row=row, column=8).value else None
                    details2.update({'domesticVersion': self.worksheet.cell(row=row, column=9).value}) if self.worksheet.cell(row=row, column=9).value else None
                    details2.update({'importVersion': self.worksheet.cell(row=row, column=10).value}) if self.worksheet.cell(row=row, column=10).value else None
                    details2.update({'IsPurchase': self.worksheet.cell(row=row, column=11).value}) if self.worksheet.cell(row=row,column=11).value else None
                    details2.update({'description': self.worksheet.cell(row=row, column=12).value}) if self.worksheet.cell(row=row, column=12).value else None
                     # if self.worksheet.cell(row=row,column=13).value and self.worksheet.cell(row=row,column=13).value != None else None
                    if self.worksheet.cell(row=row, column=13).value and self.worksheet.cell(row=row, column=13).value.strip().lower()!='none':
                        details2.update({'detailsInfo': self.worksheet.cell(row=row, column=13).value})

                    if details2:
                        previous = self.worksheet.cell(row=has_value_row, column=6).value
                        temp_list = details_merge.get(previous)
                        temp_list.append(details2)
                        details_merge[previous] = temp_list

            title.update(details_merge)

            components.append(title)

        # 文件写入
        for i in components:
            for key,value in i.items():
                if isinstance(value,list):
                    print('  {}:'.format(key))
                    self.create_file('  {}:'.format(key))
                    for part in value:
                        for k,v in part.items():
                            print('    {}: {}'.format(k,v).replace('    {}'.format(list(part.keys())[0]),'  - {}'.format(list(part.keys())[0])) )
                            self.create_file('    {}: {}'.format(k,v).replace('    {}'.format(list(part.keys())[0]),'  - {}'.format(list(part.keys())[0])) )
                else:
                    print('  {}: {}'.format(key,value).replace('  {}'.format(list(i.keys())[0]),'- {}'.format(list(i.keys())[0]).strip()))
                    self.create_file('  {}: {}'.format(key,value).replace('  {}'.format(list(i.keys())[0]),'- {}'.format(list(i.keys())[0]).strip()))
            # break


if __name__ == '__main__':
    c = ExcelToYaml()
    c.run()