Write a self-defined class type data to excel using xlsxwriter TypeError: Unsupported type <class '__main__.node'> in write()

user_15786355 0
Viewed 181

I am writing data with self-defined type to an excel file by using xlsxwriter, but got an error:

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

Code:

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)

Expected result(put the tree with indent spaces in the specific cell):

enter image description here

Question:

Does anyone have experience on this error? or suggestion? Thanks in advance!

Update:

I added the following part after the print(tree), instead of using 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)

But, got error as follows:

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'
xlsxwriter pandas excel python
1 Answers

XlsxWriter doesn't write arbitrary data types so the __repr__ in your class is ignored.

If you want to write user defined types you will need to use the Xlsxwriter add_write_handler() mechanism. See the XlsxWriter documentation on Writing user defined types. It has a detailed explanation and several examples.

Also note, you should first verify that you can get Excel to display the data in the way you want it. You will need to add a textwrap format as a minimum.

Update. Here is a small working example based on your code:

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()

Output:

enter image description here

Note, I replaced the "\t" with 4 spaces since that shows up better in Excel. Also, you need to add a text wrap format, as shown.

user_102380
Related