writing Excel file while using for loop

Question

I am trying to write data to an Excel file, during a for loop. But what I am getting is a single line containing the last data received by the loop.

I have tried a couple of different methods but came short.. 2 tries are list below

Any Ideas ?

def write_excel(x):
    workbook = xlsxwriter.Workbook('ID_Num.xlsx')
    worksheet = workbook.add_worksheet()

    df = pd.DataFrame(
        {'ID':[x],
         'mail_one':[Email],
         'second_mail':[second_mail],
         'Num':[Num],
         'date':[Date]})

    row_num = 0
    for key, value in df.items():
        worksheet.write(0, row_num, key)
        worksheet.write_row(1, row_num, value)
        row_num += 1

    workbook.close()

    #df = pd.DataFrame(
    #    {'ID':[x],
    #     'mail_one':[Email],
    #     'second_mail':[second_mail],
    #     'Num':[Num],
    #     'date':[Date]})

    # writer = ExcelWriter('ID_Num.xlsx')
    # df.to_excel(writer,'ID_Num',index=False)

    # writer.save()


if __name__ == "__main__":
    for x in List:
        my_dic = {}
        my_dict["ID"] = x
        my_dict["mail_one"] = Email
        my_dict["second_mail"] = second_mail
        my_dict["Num"] = str(Num)
        my_dict["date"] = Date
        print(my_dict)
        write_excel(x)

Answer 1

I don't have xlsxwriter so I cannot test. The documentation says that it cannot modify an existing file so I suspect that every iteration of for x in List: you are over-writing your file (workbook = xlsxwriter.Workbook('ID_Num.xlsx')).

You can make multiple files with these changes:

def write_excel(x,i):
    workbook = xlsxwriter.Workbook(f'ID_Num{i}.xlsx')
    ...
# and
for i,x in enumerate(List):
    ...
    write_excel(x,i)

Or you could accumulate multiple dictionaries and pass all of them to your function

data = []
for x in List:
    my_dic = {}
    ...
    data.append(my_dic)
write_excel(data)

Changing the function to iterate over those dicts; making a new sheet for each one

def write_excel(data):
    workbook = xlsxwriter.Workbook('ID_Num.xlsx')
    for sht in data:
        worksheet = workbook.add_worksheet()
        df = pd.DataFrame(...

        row_num = 0
        for key, value in df.items():
            worksheet.write(...
            worksheet.write_row(...
            row_num += 1

    workbook.close()