How to Add an Excel File Sheet into Another Excel File as a New Sheet Using Python and Excel Formula

Excel files are composed of worksheets, which are collections of cells that store data and formulas. Each worksheet has a name and can be accessed by its index or name. To add a worksheet from one excel file to another, we need to use a Python library that can read and write excel files, such as openpyxl or pandas. These libraries allow us to load an existing excel file into a Python object, create a new worksheet in it, copy the data and formatting from another worksheet, and save the updated excel file.

Procedures

The general steps to add an excel file sheet into another excel file as a new sheet without changing formats are:

1. Import the Python library that can handle excel files, such as openpyxl or pandas.
2. Load the excel files that contain the source and destination worksheets into Python objects, such as workbooks or dataframes.
3. Get the worksheet that you want to copy from the source excel file by its index or name.
4. Create a new worksheet in the destination excel file with a desired name.
5. Copy the values and formatting from the source worksheet to the new worksheet using a loop or a function.
6. Save the updated destination excel file to a specified path.

Comprehensive explanation

To illustrate the procedures in more detail, we will use openpyxl as the Python library to handle excel files. Openpyxl is a library that allows us to read and write excel files with the .xlsx extension. It has a Workbook class that represents an excel file, and a Worksheet class that represents a worksheet in a workbook. We can use the load_workbook function to load an existing excel file into a Workbook object, and use the create_sheet method to create a new Worksheet object in a Workbook object. We can also use the worksheets attribute to access the worksheets in a Workbook object by their index or name. To copy the values and formatting from one Worksheet object to another, we can use the dataframe_to_rows function from the openpyxl.utils.dataframe module, which converts a Worksheet object into a generator of rows. We can then use the append method to add each row to the new Worksheet object. Finally, we can use the save method to save the updated Workbook object to a new excel file.

Scenario

Suppose we have two excel files: file1.xlsx and file2.xlsx. File1.xlsx has one worksheet named “Data”, which contains some numerical data in a table format. File2.xlsx has two worksheets named “Summary” and “Chart”, which contain some summary statistics and a chart based on the data in file1.xlsx. We want to add the worksheet “Chart” from file2.xlsx to file1.xlsx as a new worksheet named “Appended_Chart” without changing the format of the chart. Here is how we can do it using Python and openpyxl:

# Import the openpyxl library
from openpyxl import load_workbook
# Import the dataframe_to_rows function
from openpyxl.utils.dataframe import dataframe_to_rows

# Load the excel files into Workbook objects
file1_path = 'path/to/file1.xlsx'
file2_path = 'path/to/file2.xlsx'
wb1 = load_workbook(file1_path)
wb2 = load_workbook(file2_path)

# Get the worksheet "Chart" from file2.xlsx by its name
sheet2 = wb2['Chart']

# Create a new worksheet in file1.xlsx with the name "Appended_Chart"
new_sheet = wb1.create_sheet(title='Appended_Chart')

# Copy the values and formatting from sheet2 to the new sheet
for row in dataframe_to_rows(sheet2, index=False, header=True):
    new_sheet.append(row)

# Save the updated file1.xlsx to a new file
wb1.save('path/to/updated_file1.xlsx')

Result

The result of running the above code is a new excel file named updated_file1.xlsx, which contains two worksheets: “Data” and “Appended_Chart”. The worksheet “Appended_Chart” has the same chart as the worksheet “Chart” in file2.xlsx, with the same format and data. Here is a screenshot of the new excel file:

Other approaches

Another possible approach to add an excel file sheet into another excel file as a new sheet without changing formats is to use pandas, which is a library that provides high-performance data analysis tools for Python. Pandas has a DataFrame class that represents a tabular data structure, and a ExcelWriter class that allows us to write DataFrames to excel files. We can use the read_excel function to load an excel file into a DataFrame object, and use the to_excel method to write a DataFrame object to an excel file. We can also use the mode and if_sheet_exists parameters of the ExcelWriter class to append a new sheet to an existing excel file and replace an existing sheet if needed. Here is how we can use pandas to achieve the same result as above:

# Import the pandas library
import pandas as pd

# Load the excel files into DataFrame objects
file1_path = 'path/to/file1.xlsx'
file2_path = 'path/to/file2.xlsx'
df1 = pd.read_excel(file1_path)
df2 = pd.read_excel(file2_path, sheet_name='Chart')

# Create an ExcelWriter object with the mode 'a' (append) and the if_sheet_exists 'replace'
writer = pd.ExcelWriter(file1_path, mode='a', if_sheet_exists='replace')

# Write the DataFrame df2 to the excel file with the sheet name 'Appended_Chart'
df2.to_excel(writer, sheet_name='Appended_Chart', index=False)

# Save and close the ExcelWriter object
writer.save()
writer.close()