python使用xlsxwriter实现有向无环图到Excel的转换

(py3) [root@7-o-1 py-dag]# cat test.py 
from dag import DAG
dag = DAG()
dag.from_dict({\'a\': [\'b\', \'c\',\'e\'],
          \'b\': [\'d\',\'g\'],
          \'c\': [\'d\'],
          \'g\':[\'i\'],
          \'i\':[],
          \'e\':[\'gh\',\'ox\',\'wer\'],
          \'gh\':[],
          \'ox\':[],
          \'wer\':[],
          \'d\': []})

def generate_excel(dag, row_start,col,excel_file):#参数分别为有向无环图图、开始写入的行,开始写入列，以及文件名
  import xlsxwriter#导入Excel操作模块
  workbook = xlsxwriter.Workbook(excel_file)#创建Excel文件
  worksheet = workbook.add_worksheet(\'testsheet\')#创建工作簿
  #以下为部分格式的预定义（包含合并行格式，数字日期格式等）
  merge_format = workbook.add_format({
  \'bold\': 1,
  \'border\': 1,
  \'align\': \'left\',
  \'valign\': \'vcenter\'})

  cell_format = workbook.add_format({
  \'bold\': 1,
  \'border\': 1,
  \'align\': \'top\',
  \'valign\': \'left\'})

  date_format = workbook.add_format({
  \'bold\': 1,
  \'border\': 1,
  \'align\': \'top\',
  \'valign\': \'left\',
  \'num_format\': \'d mmmm yyyy\'})

  num_format = workbook.add_format({
  \'bold\': 1,
  \'border\': 1,
  \'align\': \'top\',
  \'valign\': \'left\',
  \'num_format\': \'0.0000%\'})

  def get_node_leaves(node,ret = []):#用来返回一个节点的所有最终叶子结点
      downs = dag.downstream(node)#先获取当前节点的下一级节点
      if len(downs) < 1:#如果无下一级节点将直接将此节点添加到列表
        ret.append(node)
      else:#负责循环递归，将所有叶子结点添加到列表
        for inner_node in downs:
          get_node_leaves(inner_node,ret)
      return ret#返回节点列表

  def dag_visit(dag, node, r_idx,col):
    if node not in all_leaves:
      downstreams = dag.downstream(node)
      node_leaves = len(get_node_leaves(node,[]))#获取所有叶子的个数，用来判断是否进行合并行操作
      r_idx_end = r_idx+node_leaves-1
      if len(downstreams) == 1:#对于只有一个子节点的node应用write
        worksheet.write(col+str(r_idx), node,cell_format)
      else:#对于多余一个子节点的node应用merge_range
        worksheet.merge_range(col+str(r_idx)+\':\'+col+str(r_idx_end), node,merge_format)
      col = chr(ord(col)+1)#列+1，开始进入子节点遍历
      downstreams_len = len(downstreams)
      if len(downstreams) >0:
        row_next = r_idx
        for downstream in downstreams:
          subnode_leaves = len(get_node_leaves(downstream,[]))#获取当前节点的所有叶子用来为下一个节点的填充确定位移
          if downstream in all_leaves:
            subnode_leaves = 1
            if col == merge_to:#对于子节点为叶子并且跨度为1应用write
              worksheet.write(col+str(row_next), downstream,cell_format)
            else:#对于子节点为叶子结点但是跨度大于1应用merge_range
              merge_rg = col+str(row_next) +\':\'+merge_to+str(row_next)
              worksheet.merge_range(merge_rg, downstream,merge_format)
          else:#子节点非叶子结点进行递归遍历
            dag_visit(dag, downstream, row_next,col)
          #每访问一个子结点需要将row值增加方便下一个node正确填充，row的值是前一个结点的子节点个数
          row_next += subnode_leaves

    else:
      print(node)

  head_node = dag.ind_nodes()[0]
  all_leaves = dag.all_leaves()
  dag_depth = dag.dag_depth(head_node,0)
  merge_to= chr(ord(col)+dag_depth)#对于合并列的终结列index
  dag_visit(dag, head_node, row_start,col)#传递根节点进行遍历
  workbook.close()
generate_excel(dag,1,\'A\',\'test.xlsx\')