Merge two excel files using openpyxl in Python

In this tutorial, we will learn how to merge two excel files into one file using openpyxl in Python. To achieve this we are using ExcelWriter() method of pandas. It has a parameter called ‘engine’ which takes ‘openpyxl’ as a value.

Merging two excel files

Consider two excel files called ‘file1.xlsx‘ and ‘file2.xlsx‘ with some data as shown below.

Now, we will merge these two files based on the first column and store the result in the first file named ‘file1.xlsx’.

Code:

import pandas as pd
df=pd.read_excel("file2.xlsx")
df=df.iloc[:,1:]
with pd.ExcelWriter("file1.xlsx",mode="a",engine="openpyxl",if_sheet_exists="overlay") as writer:
    df.to_excel(writer, sheet_name="Sheet1",columns=None, startcol=writer.sheets["Sheet1"].max_column,index=False)

Output:

• Here, we are importing pandas library and reading the file ‘file2.xlsx‘ as ‘df‘.
• Now, using ExcelWriter() method we are opening ‘file1.xlsx‘ file in append mode and ‘openpyxl’ as the engine.
• if_sheet_exists="overlay" indicates that the data should be appended to the existing data.
• Finally, to_excel() method is used to merge the data of ‘file2.xlsx‘ file after the data of ‘file1.xlsx‘ file with the final result in ‘file1.xlsx‘ file.
• startcol=writer.sheets["sheet1"].maxcolumn gives the number of columns of the previous data.

Continue reading,

• Find row number that contains a specific value using Openpyxl
• Count total number of rows and columns in a sheet using Openpyxl