python 根据树型结构生成指定格式的excel数据

数据

tree = {
    'a': {
        'a1': [('a1a', 1)],
        'a2': [
                ('a2a', 1),
                ('a2b', 2),
               ]
    },
    'b': {
        'b1': [('b1b', 1)],
        'b2': [('b2b', 1)]
    }
}

excel 数据格式

代码实现

import xlrd
from xlutils.copy import copy

old_excel = xlrd.open_workbook('1.xls')
new_excel = copy(old_excel)
ws = new_excel.get_sheet(0)

def write_tree_to_excel(tree, start_col=0, start_row=0):
    if isinstance(tree, dict):
        lines = 0
        row = start_row
        for k, item in tree.items():
            lines += write_tree_to_excel(item, start_col+1, start_row)
            end_row = row+lines
            print(start_col*'\t', k, ':', start_row, end_row, start_col)
            ws.write_merge(start_row, end_row-1, start_col, start_col)
            ws.write(start_row, start_col, k)
            start_row = end_row
        return lines

    elif isinstance(tree, list):
        row = start_row
        for item in tree:
            print(start_col*'\t', item[0], ':', row, start_col)
            ws.write(row, start_col, item[0])
            row += 1
        return len(tree)
 
write_tree_to_excel(tree)
new_excel.save('2.xls')