发行XLSXWRITER编写字典

Question

我正在使用XLSXWriter添加字典列表，但我会遇到错误：

Type error: Unsupported type <type 'list'> in write()

代码下面：

from xlsxwriter import Workbook


my_dict = [{'test_case_Id': '', 'stepdata': [{'Expected Output': '/home/softnautics/Desktop', 'stepId': 'Sid_1_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]}, {'test_case_Id': '', 'stepdata': [{'Expected Output': '/home/softnautics/Desktop/pythonExercise', 'stepId': 'Sid_2_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]}, {'test_case_Id': 'tc_04', 'stepdata': [{'Expected Output': 'No such file or directory', 'stepId': 'Sid_4_1', 'commandString': 'cd desktop', 'Actual result': "('', None)", 'arguments': 'cd desktop', 'test result': None}]}, {'test_case_Id': 'tc_06', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_6_1', 'commandString': 'cd ~', 'Actual result': "('', None)", 'arguments': 'cd ~', 'test result': None}, [{'Expected Output': '/home/softnautics', 'stepId': 'Sid_6_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_07', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_7_1', 'commandString': 'cd ~/Downloads', 'Actual result': "('', None)", 'arguments': 'cd ~/Downloads', 'test result': None}, [{'Expected Output': '/home/softnautics/Downloads', 'stepId': 'Sid_7_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_08', 'stepdata': [{'Expected Output': 'No such file or directory', 'stepId': 'Sid_8_1', 'commandString': 'cd ~/downloads', 'Actual result': "('', None)", 'arguments': 'cd ~/downloads', 'test result': None}]}, {'test_case_Id': '', 'stepdata': [{'Expected Output': '/home/softnautics', 'stepId': 'Sid_9_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]}, {'test_case_Id': 'tc_11', 'stepdata': [{'Expected Output': '/home/softnautics/Desktop/pythonExercise\n', 'stepId': 'Sid_11_1', 'commandString': 'cd -', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'cd -', 'test result': None}]}, {'test_case_Id': 'tc_13', 'stepdata': [{'Expected Output': 'bash: cd: too many arguments', 'stepId': 'Sid_13_1', 'commandString': 'cd - Downloads', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'cd - Downloads', 'test result': None}]}, {'test_case_Id': 'tc_15', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_15_1', 'commandString': 'cd ..', 'Actual result': "('', None)", 'arguments': 'cd ..', 'test result': None}, [{'Expected Output': '/home/softnautics/Desktop', 'stepId': 'Sid_15_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_16', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_16_1', 'commandString': 'cd ../Downloads', 'Actual result': "('', None)", 'arguments': 'cd ../Downloads', 'test result': None}, [{'Expected Output': '/home/softnautics/Downloads', 'stepId': 'Sid_16_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_17', 'stepdata': [{'Expected Output': 'bash: cd: ../Downloads: No such file or directory', 'stepId': 'Sid_17_1', 'commandString': 'cd ../Downloads', 'Actual result': "('', None)", 'arguments': 'cd ../Downloads', 'test result': None}]}, {'test_case_Id': 'tc_19', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_19_1', 'commandString': 'cd Downloads/OOPs-Python-Notes', 'Actual result': "('', None)", 'arguments': 'cd Downloads/OOPs-Python-Notes', 'test result': None}, [{'Expected Output': '/home/softnautics/Downloads/OOPs-Python-Notes', 'stepId': 'Sid_19_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_20', 'stepdata': [{'Expected Output': 'bash: cd: Downloads/OOPs-Python-Notes/OOPs-Part-4.pdf: Not a directory', 'stepId': 'Sid_20_1', 'commandString': 'cd Downloads/OOPs-Python-Notes/OOPs - Part - 4.pdf', 'Actual result': "('', None)", 'arguments': 'cd Downloads/OOPs-Python-Notes/OOPs - Part - 4.pdf', 'test result': None}]}]

ordered_list=["test_case_Id", "stepdata", "Actual result", "test result"]  

wb=Workbook("data.xlsx")
ws=wb.add_worksheet("Sheet 1")

first_row=0

for header in ordered_list:
    col=ordered_list.index(header) 
    ws.write(first_row,col) 

row = 1


for dict in my_dict:
    for key,value in dict.items():
        col=ordered_list.index(key)
        ws.write(row,col,value)

    row += 1 #enter the next row


wb.close()

Answer 1

由于ws.write（）函数不会将列表作为参数，您将拥有单独的嵌套列表组件
我能想到的唯一方法是检查键，然后在嵌套词典中迭代一种

from xlsxwriter import Workbook

my_dict = [{'test_case_Id': '', 'stepdata': [{'Expected Output': '/home/softnautics/Desktop', 'stepId': 'Sid_1_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]}, {'test_case_Id': '', 'stepdata': [{'Expected Output': '/home/softnautics/Desktop/pythonExercise', 'stepId': 'Sid_2_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]}, {'test_case_Id': 'tc_04', 'stepdata': [{'Expected Output': 'No such file or directory', 'stepId': 'Sid_4_1', 'commandString': 'cd desktop', 'Actual result': "('', None)", 'arguments': 'cd desktop', 'test result': None}]}, {'test_case_Id': 'tc_06', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_6_1', 'commandString': 'cd ~', 'Actual result': "('', None)", 'arguments': 'cd ~', 'test result': None}, [{'Expected Output': '/home/softnautics', 'stepId': 'Sid_6_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_07', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_7_1', 'commandString': 'cd ~/Downloads', 'Actual result': "('', None)", 'arguments': 'cd ~/Downloads', 'test result': None}, [{'Expected Output': '/home/softnautics/Downloads', 'stepId': 'Sid_7_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_08', 'stepdata': [{'Expected Output': 'No such file or directory', 'stepId': 'Sid_8_1', 'commandString': 'cd ~/downloads', 'Actual result': "('', None)", 'arguments': 'cd ~/downloads', 'test result': None}]}, {'test_case_Id': '', 'stepdata': [{'Expected Output': '/home/softnautics', 'stepId': 'Sid_9_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]}, {'test_case_Id': 'tc_11', 'stepdata': [{'Expected Output': '/home/softnautics/Desktop/pythonExercise\n', 'stepId': 'Sid_11_1', 'commandString': 'cd -', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'cd -', 'test result': None}]}, {'test_case_Id': 'tc_13', 'stepdata': [{'Expected Output': 'bash: cd: too many arguments', 'stepId': 'Sid_13_1', 'commandString': 'cd - Downloads', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'cd - Downloads', 'test result': None}]}, {'test_case_Id': 'tc_15', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_15_1', 'commandString': 'cd ..', 'Actual result': "('', None)", 'arguments': 'cd ..', 'test result': None}, [{'Expected Output': '/home/softnautics/Desktop', 'stepId': 'Sid_15_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_16', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_16_1', 'commandString': 'cd ../Downloads', 'Actual result': "('', None)", 'arguments': 'cd ../Downloads', 'test result': None}, [{'Expected Output': '/home/softnautics/Downloads', 'stepId': 'Sid_16_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_17', 'stepdata': [{'Expected Output': 'bash: cd: ../Downloads: No such file or directory', 'stepId': 'Sid_17_1', 'commandString': 'cd ../Downloads', 'Actual result': "('', None)", 'arguments': 'cd ../Downloads', 'test result': None}]}, {'test_case_Id': 'tc_19', 'stepdata': [{'Expected Output': '', 'stepId': 'Sid_19_1', 'commandString': 'cd Downloads/OOPs-Python-Notes', 'Actual result': "('', None)", 'arguments': 'cd Downloads/OOPs-Python-Notes', 'test result': None}, [{'Expected Output': '/home/softnautics/Downloads/OOPs-Python-Notes', 'stepId': 'Sid_19_2', 'commandString': 'pwd', 'Actual result': "('/home/softnautics/Documents/project folder\\n', None)", 'arguments': 'pwd', 'test result': None}]]}, {'test_case_Id': 'tc_20', 'stepdata': [{'Expected Output': 'bash: cd: Downloads/OOPs-Python-Notes/OOPs-Part-4.pdf: Not a directory', 'stepId': 'Sid_20_1', 'commandString': 'cd Downloads/OOPs-Python-Notes/OOPs - Part - 4.pdf', 'Actual result': "('', None)", 'arguments': 'cd Downloads/OOPs-Python-Notes/OOPs - Part - 4.pdf', 'test result': None}]}]
ordered_list=["test_case_Id", "stepdata", "Actual result", "test result"]  

wb=Workbook("data.xlsx")

ws=wb.add_worksheet("Sheet 1")

first_row=0

for header in ordered_list:
    col=ordered_list.index(header) 
    ws.write(first_row, col, ordered_list[col]) 

row=1

for dict in my_dict:
    for key,value in dict.items():
        col=ordered_list.index(key)
        if isinstance(value,list) and key == 'stepdata':
            ws.write(row,col,str(value))
            col=col+1
            result=value[0]
            for key_result,value_result in result.items():
                if key_result in ordered_list:
                    ws.write(row,col,value_result)
                    col= col+1
            continue
        ws.write(row,col,value)
        col= col+1
    row += 1 #enter the next row

wb.close()

使您的数据打印数据的一种方法，以便您可以更好地理解结构更好的

import json

print(json.dumps(my_dict, indent=4))

编辑：更正ws.write（）对于Stepdata