Read each excel sheet as a different dataframe in Python-CodePudding

I have an excel file with 40 sheet_names. I want to read each sheet to a different dataframe, so I can export an xlsx file for each sheet. Instead of writing all the sheet names one by one, I want to create a loop that will get all sheet names and add them as a variable in the "sheet_name" option of "pandas_read_excel"

I am trying to avoid this:

df1 = pd.read_excel(r'C:\Users\filename.xlsx', sheet_name= 'Sheet1');
df2 = pd.read_excel(r'C:\Users\filename.xlsx', sheet_name= 'Sheet2');
....
df40 = pd.read_excel(r'C:\Users\filename.xlsx', sheet_name= 'Sheet40');

thank you all guys

CodePudding user response：

Specifying sheet_name as None with read_excel reads all worksheets and returns a dict of DataFrames.

A dataframe will be created for each sheet and can be referenced by the sheet name.

import pandas as pd

file = 'C:\Users\filename.xlsx'
xl = pd.read_excel(file, sheet_name=None)
sheets = xl.keys()

for sheet in sheets:
    print(sheet)
    exec(f"{sheet} = xl.get('{sheet}')")

CodePudding user response：

I think this is what you are looking for.

import pandas as pd
xlsx = pd.read_excel('file.xlsx', sheet_name=None, header=None)
for sheet in xlsx.keys(): xlsx[sheet].to_excel(sheet '.xlsx', header=False, index=False)