python excel转xmind多层嵌套 (解决xmind2020及之后版本打开报错)

最新推荐文章于 2024-08-22 07:51:42 发布

hsmaster

最新推荐文章于 2024-08-22 07:51:42 发布

阅读量237

点赞数 9

分类专栏： python 文章标签： python excel xmind

本文链接：https://blog.csdn.net/hsmaster/article/details/141191092

版权

python 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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)