python excel转xmind多层嵌套 (解决xmind2020及之后版本打开报错)
python excel转xmind多层嵌套 (解决xmind2020及之后版本打开报错)
解决xmind2020及之后版本打开报错参照的这篇文章:https://blog.csdn.net/weixin_40105587/article/details/117557740
多层嵌套:
excel中第1列为路径,格式为"root/分类1/分类2/名称",如下图。
生成xmind
用xmind打开后,再手工调整格式就行了(没再研究代码怎样实现)
代码如下:
import openpyxl
import xmind
import os
import shutil
import zipfile
from xmind.core.topic import TopicElement
def extract(d_path, f_path, mode="zip"):
"""
zip解压缩乱码问题处理
:param d_path:
:param f_path:
:return:
"""
root = d_path
if not os.path.exists(root):
os.makedirs(root)
if mode == 'zip':
zf = zipfile.ZipFile(f_path,"r")
elif mode == 'rar':
zf = rarfile.RarFile(f_path,"r")
for n in zf.infolist():
srcName = n.filename
try:
decodeName = srcName.encode("cp437").decode("utf-8")
except:
try:
decodeName = srcName.encode("cp437").decode("gbk")
except:
decodeName = srcName
spiltArr = decodeName.split("\\")
path = root
for temp in spiltArr:
path = os.path.join(path, temp)
if decodeName.endswith("\\"):
if not os.path.exists(path):
os.makedirs(path)
else:
if not os.path.exists(os.path.dirname(path)):
os.makedirs(os.path.dirname(path))
f = open(path, "wb")
f.write(zf.read(srcName))
f.close()
zf.close()
def aftertreatment(path):
"""
**解决 xmind8 可以打开 xmind2020 报错
"""
# 修改名字
retval = os.path.dirname(os.path.abspath(__file__))
folder = os.path.dirname(path)
name = os.path.basename(path)
unzip_folder = os.path.splitext(name)[0]
zip_name = unzip_folder + ".zip"
os.chdir(folder)
os.rename(name, zip_name)
os.chdir(retval)
# 解压
unzip_path = os.path.join(folder, unzip_folder)
if not os.path.exists(unzip_path):
os.mkdir(unzip_path)
inf_folder = os.path.join(unzip_path, "META-INF")
if not os.path.exists(inf_folder):
os.mkdir(inf_folder)
extract(unzip_path, os.path.join(folder, zip_name))
# 2020以后格式中有manifest.xml,需要以下代码建立这个文件
xmlfilepath = os.path.join(inf_folder, "manifest.xml")
xml = '''<?xml version="1.0" encoding="UTF-8" standalone="no"?><manifest xmlns="urn:xmind:xmap:xmlns:manifest:1.0"><file-entry full-path="comments.xml" media-type=""/><file-entry full-path="content.xml" media-type="text/xml"/><file-entry full-path="markers/" media-type=""/><file-entry full-path="markers/markerSheet.xml" media-type=""/><file-entry full-path="META-INF/" media-type=""/><file-entry full-path="META-INF/manifest.xml" media-type="text/xml"/><file-entry full-path="meta.xml" media-type="text/xml"/><file-entry full-path="styles.xml" media-type=""/></manifest>'''
with open(xmlfilepath, 'w', encoding='utf-8') as f:
f.write(xml)
os.remove(os.path.join(folder, zip_name))
shutil.make_archive(unzip_path, 'zip', unzip_path)
file_path = unzip_path + '.zip'
print(file_path)
os.chdir(os.path.dirname(file_path))
os.rename(os.path.basename(file_path), name)
os.chdir(retval)
shutil.rmtree(unzip_path)
def gen_xmind_file(title,excel_path, xmind_path):
# load an existing file or create a new workbook if nothing is found
workbook = xmind.load(xmind_path)
# get the first sheet(a new workbook has a blank sheet by default)
xmindsheet = workbook.getPrimarySheet()
# data_only=True 读取的是单元格中的公式计算值,否则读取的是单元格中的公式
xlsbook = openpyxl.load_workbook(excel_path,data_only=True)
xlssheet = xlsbook.active
xmindsheet.setTitle(title)
# 遍历Excel行,创建层级
# excel列名: 路径 名称 应用场景 产品说明等等,关键是路径和名称
# 路径举例:root/安全服务/名称1 root/网络安全/安全云/名称2
# titles 对应 row 的各字段说明
def setnode(node, nodes, row, titles):
if len(nodes)>1:
node.setdefault(nodes[0],{'data':{'text':nodes[0]}})
setnode(node[nodes[0]],nodes[1:len(nodes)], row, titles)
elif len(nodes)==1:
node.setdefault(nodes[0],{'data':{'text':nodes[0]}})
for i in range(0,len(row)):
node[nodes[0]].setdefault(i,{'data':{'text':'%s:%s' % (titles[i], row[i])}})
return
data = {}
# 注意修改最小列等参数
for row in xlssheet.iter_rows(min_row=2, max_row=3, min_col=1, max_col=8, values_only=True): # 假设第一行也是数据行
# 按列构建层级关系
# 第一列是路径
nodes = row[0].split('/')
setnode(data, nodes, row[3:],['适用场景','产品简介','交付方式','供应商','是否重点'])
# 遍历创建topic
def recursive(items, xnode, xworkbook):
if isinstance(items.get('data', 0),dict):
if items['data']["text"] == 'root':
items.pop('data','')
for v in items.values():
recursive(v, xnode, xworkbook)
else:
t = TopicElement(ownerWorkbook=xworkbook)
t.setTitle(items['data']["text"])
items.pop('data','')
for v in items.values():
recursive(v, t, xworkbook)
xnode.addSubTopic(t)
else:
return
# a sheet has a blank sheet by default
root_topic2 = xmindsheet.getRootTopic()
root_topic2.setTitle(title)
#print(data)
recursive(data['root'], root_topic2, workbook)
# now we save as test.xmind
xmind.save(workbook, path=xmind_path)
# 修复
aftertreatment(xmind_path)
if __name__ == '__main__':
xlspath = 'D:\\xxx\\test.xlsx'
xmindpath='C:\\Users\\xxx\\test.xmind'
gen_xmind_file('datax', excel_path=xlspath, xmind_path=xmindpath)
代码在win环境没问题,在linux中需要修改extract函数中操作系统路径相关的。
另外注意要修改打开EXCEL时min_row max_row等参数。
解决xmind2020及之后版本打开报错原文:https://blog.csdn.net/weixin_40105587/article/details/117557740