CODEWITHSUNDEEP
pythonexcelpandas
Amy
Amy

Reputation: 25

Importing and writing multiple excel sheets with Panda

I am trying to import excel files which have multiple sheets. Currently, my code (below) is only importing the first sheet. The remainder of the code is preforming calculations from only one sheet (currently the first since I moved it there to make it work-but bonus if I can avoid this step).

Ideally, I would like to import all the sheets, preform calculations on the one sheet, and export all sheets again in an excel file. A majority of the sheets would be import/export with no changes, while the one sheet with a specific/consistent name would have calculations preformed on it and also exported. Not sure what functions to look into. Thanks!

df = pd.read_excel("excelfilename.xlsx")
df.head() 

#other code present here preforming calculations

df.to_excel(r'newfilename.xlsx', index = False)

Upvotes: 0

Views: 356

Answers (1)

Amir saleem
Amir saleem

Reputation: 1496

Load Excel file using pandas, then get sheet names using xlrd, and then save modified data back.

import xlrd
file_name = "using_excel.xlsx"
sheet_names_ = xlrd.open_workbook(file_name, on_demand=True).sheet_names()
for sheet_name in  sheet_names_:
    df_sheet = pd.read_excel(file_name, sheet_name=sheet_name)
    # do something
    if you_want_to_write_back_to_same_sheet_in_same_file:
        writer = pd.ExcelWriter(file_name)
        df_sheet.to_excel(writer, sheet_name=sheet_name)
        writer.save()

Upvotes: 1

Related Questions