How to concatenate three excels files xlsx using python?

Question

Hello I would like to concatenate three excels files xlsx using python.

I have tried using openpyxl, but I don't know which function could help me to append three worksheet into one.

Do you have any ideas how to do that ?

Thanks a lot

Answer 1

Here's a pandas-based approach. (It's using openpyxl behind the scenes.)

import pandas as pd

# filenames
excel_names = ["xlsx1.xlsx", "xlsx2.xlsx", "xlsx3.xlsx"]

# read them in
excels = [pd.ExcelFile(name) for name in excel_names]

# turn them into dataframes
frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels]

# delete the first row for all frames except the first
# i.e. remove the header row -- assumes it's the first
frames[1:] = [df[1:] for df in frames[1:]]

# concatenate them..
combined = pd.concat(frames)

# write it out
combined.to_excel("c.xlsx", header=False, index=False)