python - 使用xlsxwriter将自定义类类型数据写入excel TypeError : Unsupported type in write()

Question

我正在使用xlsxwriter将自定义类型的数据写入excel文件，但出现错误:

TypeError: Unsupported type <class '__main__.node'> in write()

代码:

import xlsxwriter

class node(object):
    def __init__(self, value):
        self.value = value
        self.children = []
    def __repr__(self, level=0):
        ret = "\t"*level+repr(self.value)+"\n"
        for child in self.children:
            ret += child.__repr__(level+1)
        return ret
    def add(self, nod):
        self.children.append(node(nod))
        
leaf_1 = ['AA','BB','CC','DD']


workbook = xlsxwriter.Workbook('print_def.xlsx')
worksheet = workbook.add_worksheet()    

tree = parent = node(leaf_1[0]) #### code 1
parent.add(leaf_1[1])
parent.add(leaf_1[2])
print(tree)
worksheet.write(1, 0, tree)

预期结果(将 tree 与特定单元格中的缩进空格放在一起):

问题 :
有没有人有这个错误的经验？或建议？提前致谢!
更新 :
我在 print(tree) 之后添加了以下部分, 而不是使用 worksheet.write(1, 0, tree)

def write_node(worksheet, row, col, tree, format = None):
    return worksheet.write_string(row, col, str(tree), format)
worksheet.add_write_handler(tree.children, write_node)
# worksheet.add_write_handler(tree.node, write_node)
worksheet.write('A1', tree)

但是，得到如下错误:

Traceback (most recent call last):
  File "/Users.../pythonProject/2021-05-16.py", line 137, in <module>
    worksheet.add_write_handler(tree.children, write_node)
  File "/Users.../pythonProject/lib/python3.9/site-packages/xlsxwriter/worksheet.py", line 1365, in add_write_handler
    self.write_handlers[user_type] = user_function
TypeError: unhashable type: 'list'

Answer 1

XlsxWriter 不写入任意数据类型，因此 __repr__在你的类里面被忽略。
如果你想编写用户定义的类型，你需要使用 Xlsxwriter add_write_handler()机制。请参阅 Writing user defined types 上的 XlsxWriter 文档.它有详细的解释和几个例子。
另请注意，您应该首先验证您是否可以让 Excel 以您想要的方式显示数据。您至少需要添加一个 textwrap 格式。
更新 .这是一个基于您的代码的小型工作示例:

import xlsxwriter


class node(object):
    def __init__(self, value):
        self.value = value
        self.children = []

    def __repr__(self, level=0):
        ret = "    " * level + repr(self.value) + "\n"
        for child in self.children:
            ret += child.__repr__(level+1)
        return ret

    def add(self, nod):
        self.children.append(node(nod))


def write_node(worksheet, row, col, tree, format=None):
    return worksheet.write_string(row, col, str(tree), format)

workbook = xlsxwriter.Workbook('print_def.xlsx')
worksheet = workbook.add_worksheet()

text_wrap = workbook.add_format({'text_wrap': True})

worksheet.add_write_handler(node, write_node)

leaf_1 = ['AA', 'BB', 'CC', 'DD']
tree = parent = node(leaf_1[0])
parent.add(leaf_1[1])
parent.add(leaf_1[2])

worksheet.write(0, 0, tree, text_wrap)

workbook.close()

输出 :

请注意，我用 4 个空格替换了“\t”，因为它在 Excel 中显示得更好。此外，您需要添加文本换行格式，如图所示。