Combining multiple Excel sheets into one in Python

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

Combining multiple Excel sheets into one in Python

Prerequisites

  1. Reading Excel data with Openpyxl
  2. Writing to Excel with Openpyxl

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

  1. Find the absolute path of the Excel files.
  2. Iterate through each file and create a sheet of the same name in your destination file “yearly_sales.xlsx”.
  3. 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”.

Subscribe to Pylenin

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe