Writing multiple data frames to multiple excel sheets but they turn up empty

Question

I have the following code where I want to read data from first sheet of an excel file, and then, according to some category, split each category in a separate sheet. All is good and the program doesn't show an error, but all the sheets it produces are empty.

import pandas
import os

from openpyxl import load_workbook
import pandas as pd
import xlsxwriter

path = r"C:\Users\acer pc\Desktop\rrrr.xlsx"
os.chdir(r"C:\Users\acer pc\Desktop")


data = pandas.read_excel("rrrr.xlsx")

FileNumber = data["number"].unique()

print(FileNumber)


wb2 = load_workbook('rrrr.xlsx')

for i in FileNumber:
    wb2.create_sheet(f'{i}')
    wb2.save(r"C:\Users\acer pc\Desktop\rrrr.xlsx")


for i in FileNumber:

    rslt_df = data[data['number'] == i]
    print(rslt_df)
    writer = pd.ExcelWriter(r"C:\Users\acer pc\Desktop\rrrr.xlsx", engine='xlsxwriter')
    rslt_df.to_excel(writer, sheet_name=f'{i}', index=False)

    wb2.save(r"C:\Users\acer pc\Desktop\rrrr.xlsx")

wb2.close()

Why do you have `wb2.save(r"C:\Users\acer pc\Desktop\rrrr.xlsx")` in your second `for i` loop? Actually, what is the point of the first `for i` loop at all? — BigBen, Mar 01 '22 at 20:41
The first for I loop is to create new sheets with the same "number" that is to be displayed. Ohh and i did 2 for I just because I was experimenting with my code to find a way to fix it before posting it here — Daniel, Mar 01 '22 at 21:32
Yes but you don't need a loop to create new sheets, `to_excel` will do that for you. [This thread](https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas) may also be relevant. — BigBen, Mar 01 '22 at 21:38

Writing multiple data frames to multiple excel sheets but they turn up empty

0 Answers0