I have multiple lists of dict that converge to one list of dict to an excel file. The idea is to make an excel sheet for each dict, key(web1, web2), name and have the correct info in each sheet. The problem of the code below is that it make a excel file of one sheet. Any idea on how should I do it?
import pandas as pd web1 = [{ 'country': 'NL', 'id': '56655', 'title': 'some 1', 'date': 'today', 'description': 'something else here', 'web': 'http://' }, { 'country': 'IE', 'jobid': '45862', 'title': 'some 2', 'date': 'today', 'description': 'something else here', 'web': 'http://' }] web2 = [{ 'country': 'IT', 'id': '77878', 'title': 'some 3', 'date': 'today', 'description': 'something else here', 'web': 'http://' }, { 'country': 'NE', 'id': '45862', 'title': 'some 4', 'date': 'today', 'description': 'something else here', 'web': 'http://' }] data =[{ 'website1': web1 }, { 'website1': web2 }] for x in data: z = str(*x).capitalize() for y in x.values(): cx = pd.DataFrame(y, columns = ['country', 'title', 'date', 'id', 'web','description']) writer = pd.ExcelWriter('test.xlsx') cx.to_excel(writer, sheet_name=f'{z}') workbook = writer.book worksheet = writer.sheets[f'{z}'] align_r = workbook.add_format({'align': 'right'}) bold = workbook.add_format({'bold': True}) color_format = workbook.add_format({'bg_color': 'green'}) condi_color = worksheet.conditional_format('G:G', { 'type': 'text', 'criteria': 'containing', 'value': 'red', 'format': color_format}) # set column spacing, format worksheet.set_column('A:A', 3) worksheet.set_column('B:B', 2, bold) worksheet.set_column('C:C', 40, bold) worksheet.set_column('D:D', 10, align_r) worksheet.set_column('G:G') writer.save() 
