Combining multiple Excel sheets into one in Python
Combine multiple Excel files into one using Openpyxl module in Python 3.

Prerequisites
Let’s say you have a directory with multiple Excel files containing sales data of every month.

Objective - You would like to store all those separate Excel files as separate sheets in one Excel sheet named “yearly_sales.csv”.
Steps to achieve the objective
- Find the absolute path of the Excel files.
- Iterate through each file and create a sheet of the same name in your destination file “yearly_sales.xlsx”.
- Copy the data from the Excel file to the sheet.
Step 1 - Finding the absolute path of the Excel files
There are multiple ways to find the absolute path of files in a directory in Python. For this article, we will use os.walk()
function.
Code
import os
dir_containing_files = "C:\\Users\\91824\\PycharmProjects\\pythonProject\\sales_2020"
for root, dir, filenames in os.walk(dir_containing_files):
for file in filenames:
file_name = file.split('.')[0]
# Absolute Path for Excel files
file_path = os.path.abspath(os.path.join(root, file))
Step 2 - Creating sheets
First you need to create an Excel workbook - “yearly_sales.xlsx”. Use the file_name
from the above step to create new sheets in “yearly_sales.xlsx”.
Code
import os
#=====New Code====#
from openpyxl import Workbook
#=================#
dir_containing_files = "C:\\Users\\91824\\PycharmProjects\\pythonProject\\sales_2020"
#=====New Code====#
dest_wb = Workbook()
#=================#
for root, dir, filenames in os.walk(dir_containing_files):
for file in filenames:
file_name = file.split('.')[0]
# Absolute Path for Excel files
file_path = os.path.abspath(os.path.join(root, file))
#=====New Code====#
# Create new sheet in destination Workbook
dest_wb.create_sheet(file_name)
dest_ws = dest_wb[file_name]
#=================#
#=====New Code====#
dest_wb.save("yearly_sales.xlsx")
#=================#
Output

Step 3 - Copying data to sheets
The final step is to copy data from each of those Excel files to the newly created sheets in “yearly_sales.xlsx”.
Code
import os
from openpyxl import Workbook
#=====New Code====#
from openpyxl import load_workbook
#=================#
dir_containing_files = "C:\\Users\\91824\\PycharmProjects\\pythonProject\\sales_2020"
dest_wb = Workbook()
for root, dir, filenames in os.walk(dir_containing_files):
for file in filenames:
file_name = file.split('.')[0]
# Absolute Path for Excel files
file_path = os.path.abspath(os.path.join(root, file))
# Create new sheet in destination Workbook
dest_wb.create_sheet(file_name)
dest_ws = dest_wb[file_name]
# =====New Code====#
# Read source data
source_wb = load_workbook(file_path)
source_sheet = source_wb.active
for row in source_sheet.rows:
for cell in row:
dest_ws[cell.coordinate] = cell.value
# =================#
dest_wb.save("yearly_sales.xlsx")
All the data should be copied to the sheets in “yearly_sales.xlsx”.