Save data to a new tab in the .xlsx file

Question

My program generates a data_list using a while loop (the code for generating the data_list is not important, and I have attached the main code below). At the end of the loop, after the data_list is generated, I want to write this data_list to the test.xlsx file. However, for each new data_list to be saved in a new tab (that is, if I had 10 iterations in the loop, there would be 10 different tabs in the test.xlsx file).

At the moment, my code works in such a way that each new tab overwrites the previous one, that is, at the end of the loop, there is one tab in the test.xlsx file.

Tell me how to do it in such a way that each new generated data_list is written in a new tab.

start=0
finish=1
datas = response.json()


while finish < 11:
    data = datas[start:finish]

    data_list = []
    # some code for generating the data_list

    df = pd.DataFrame(data_list)
    writer = pd.ExcelWriter('test.xlsx')
    df.to_excel(writer, sheet_name=f'{finish}', index=False)
    writer._save()

    finish +=1
    start +=1

Answer 1

Instead of saving each sheet use with to create the ExcelWriter

with pd.ExcelWriter('test.xlsx') as writer:
    while finish < 11:
        data = datas[start:finish]

        data_list = []

        df = pd.DataFrame(data_list)
        df.to_excel(writer, sheet_name=f'{finish}', index=False)

        finish += 1
        start += 1