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)

2nd_mail isnt a valid variable name. You sure its not erroring on that? Share full error stack — Hamza
– Hamza, Commented Oct 19, 2020 at 16:12
@User5 Actually it is only for example sake, none the less I fixed it in the code. — soBusted
– soBusted, Commented Oct 19, 2020 at 17:06
Ideas: Pandas and xlsxwriter: how to create a new sheet without exporting a dataframe?, docs - Working with Python Pandas and XlsxWriter — wwii
– wwii, Commented Oct 19, 2020 at 17:13
@wwii As far as I see I am already doing this in my code, but it doesnt seem to work when looping. it is only writing the last data received, not going to the next line, while when submitting the whole data as one, it does, so how do I make it loop ?... — soBusted
– soBusted, Commented Oct 20, 2020 at 6:39

wwii · Accepted Answer · 2020-10-20 16:37:49Z

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

answered Oct 20, 2020 at 16:37

wwii

23.9k7 gold badges42 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

soBusted Over a year ago

Beautifully answered! I went with the 'accumulate multiple dictionaries' and it worked like a charm. I placed the name of the dict after the variable dict_data = account_details I was actually surprised that it worked (one variable = another), but it did (-:

Collectives™ on Stack Overflow

writing Excel file while using for loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related