0

I was adding a list of dictionaries into a dictionary using xlsxwriter but i am getting errors:

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

Code below:

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()
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
MRINMOY DEB
  • 21
  • 3

1 Answers1

1

as ws.write() function does not take a list as a parameter you will have separate the nested list components the only way i could think of is checking the key and then iterating within the nested dictionary

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

one way to pretty print your data would be suggested so you can understand the structure better

import json

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

edit: corrected ws.write() for stepdata

Kapil
  • 165
  • 1
  • 9