python怎么遍历并读取excel,遍历excel文件和表格并在Python中连接

Say I have a folder which have multiple excel files with extension xlsx or xls, they share same header column a, b, c, d, e except some empty sheet in several files.

I want to iterate all the files and sheets (except for empty sheets) and concatenate them into one sheet of one file output.xlsx.

I have iterated through all excel files and append them to one file, but how could I iterate through all the sheets of each files if they have more than one sheets?

I need to integrate two block of code below into one. Thanks for your help.

import pandas as pd

import numpy as np

import glob

path = os.getcwd()

files = os.listdir(path)

files

df = pd.DataFrame()

# method 1

excel_files = [f for f in files if f[-4:] == 'xlsx' or f[-3:] == 'xls']

excel_files</