CODEWITHSUNDEEP
pythonexcelpandasxlsxworksheet
pythonist
pythonist

Reputation: 63

Combining several SHEETS into one EXCEL

I am using this code to put all Excel files and sheets into one Excel file, and it works flawlessly.

But on some occasions, I want to put them all into a single excel file, but to keep the sheets separate.

I know there is "Copy sheet" in Excel, but I want to do it to multiple documents. I am sure pandas has such a function, so I do not have to do this manually. I also want to keep all the data as it was, with no added columns or rows, and keep the name of the sheet as well. If you have an idea, please help.

Example:

Apply code...

This code puts all the data into a single sheet.

import os
import pandas as pd
print("Combine xls and xlsx")
cwd = os.path.abspath('') 
files = os.listdir(cwd)  
## get all sheets of a given file
df_total = pd.DataFrame()
for file in files:                         # loop through Excel files
    if file.endswith('.xls') or file.endswith('.xlsx'):
        excel_file = pd.ExcelFile(file)
        sheets = excel_file.sheet_names
        for sheet in sheets:               # loop through sheets inside an Excel file
            print (file, sheet)
            df = excel_file.parse(sheet_name = sheet)
            df_total = df_total.append(df)
print("Loaded, ENTER to combine:")
dali=input()
df_total.to_excel('Combined/combined_file.xlsx')
print("Done")
dali=input()

Upvotes: 4

Views: 10172

Answers (1)

pythonist
pythonist

Reputation: 63

Here is the answer. I hope someone finds this useful. Combines all sheets from all excel files XLS od XLSX to a single excel file with all sheets.

import pandas as pd
import openpyxl
print("Copying sheets from multiple files to one file")
cwd = os.path.abspath('') 
files = os.listdir(cwd)  

df_total = pd.DataFrame()
df_total.to_excel('Combined/combined_file.xlsx') #create a new file
workbook=openpyxl.load_workbook('Combined/combined_file.xlsx')
ss_sheet = workbook['Sheet1']
ss_sheet.title = 'TempExcelSheetForDeleting'
workbook.save('Combined/combined_file.xlsx')


for file in files:                         # loop through Excel files
    if file.endswith('.xls') or file.endswith('.xlsx'):
        excel_file = pd.ExcelFile(file)
        sheets = excel_file.sheet_names
        for sheet in sheets:               # loop through sheets inside an Excel file
            print (file, sheet)
            df = excel_file.parse(sheet_name = sheet)
            with pd.ExcelWriter("Combined/combined_file.xlsx",mode='a') as writer:  
                df.to_excel(writer, sheet_name=f"{sheet}", index=False)
            #df.to_excel("Combined/combined_file.xlsx", sheet_name=f"{sheet}")

workbook=openpyxl.load_workbook('Combined/combined_file.xlsx')
std=workbook["TempExcelSheetForDeleting"]
workbook.remove(std)
workbook.save('Combined/combined_file.xlsx')
print("Loaded, press ENTER to end")
dali=input()
#df_total.to_excel('Combined/combined_file.xlsx')
print("Done")
dali=input()

Upvotes: 2

Related Questions