2023年11月30日发(作者：本田雅阁二手车价格)
Python之yaml与excel互相转换
python3环境下运?，使?openpyxl模块实现
调?逻辑关系，yaml to excel脚本调??件，?成excel表格，然后excel to yaml脚本调??成的表格转换为yaml?件
1
- 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
# -*- coding:utf8 -*-
from ruamel import yaml
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font,PatternFill,Alignment
# /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(\'\', 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\', \'d
escription\',\'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 \'\')).alignm
ent = 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(\"\")
if __name__ == \'__main__\':
c = YamlToExcel()
c.save_excel()
3 excel to
# -*- coding:utf8 -*-
from openpyxl import load_workbook
class ExcelToYaml(object):
def __init__(self):
#
打开已有表格
self.workbook = load_workbook(\'\')
# print(workbook)
self.worksheet = self.workbook[\'CarSale\']
@classmethod
def create_file(cls,content):
with open(\'Excel表格转换\', \'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 (row=row,column=13).value and (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()
更多推荐
宝马328li报价
发布评论