你需要的只是一个正确的逻辑来识别第一个非空行：

record_counts = []
dfs=[]
folder_path = Path.cwd()
filenames = sorted(glob.glob(str(folder_path)+"\*.xlsx"))

for f in filenames:
    filename = os.path.basename(f)
    df = pd.read_excel(filename, header=None)
    
    # Identify the header row as the first non-empty row
    header_row = df.apply(lambda row: row.notnull().any(), axis=1).idxmax()

    # Set the selected row as the header
    df.columns = df.iloc[header_row]

    # Drop the rows before the header row
    df = df.iloc[header_row + 1:]
    dfs.append(df)
combined_df = pd.concat(dfs, ignore_index=True)

试试这个：

import os
import glob
from pathlib import Path
import pandas as pd

record_counts = []
dfs = []
folder_path = Path.cwd()
filenames = sorted(glob.glob(str(folder_path) + "/*.xlsx"))

def find_header_row(df):
    for i, row in df.iterrows():
        if 'ColumnName' in row.values:  
            return i
    return 0

for f in filenames:
    filename = os.path.basename(f)
    df_temp = pd.read_excel(f, header=None)
    header_row = find_header_row(df_temp)
    
    df = pd.read_excel(f, header=header_row)
    count = len(df.index)
    record_counts.append(count)
    dfs.append(df)
    
combined_df = pd.concat(dfs, ignore_index=True)

我设法找到了另一种方法来解决这个问题，通过识别first valid index

for f in filenames:
    filename = os.path.basename(f)
    df = pd.read_excel(filename,header=None,sheet_name='Sheet1')
    header_row = df.apply(lambda x: x.first_valid_index()).min()
    df = pd.read_excel(filename, header=header_row,sheet_name='Sheet1')
    count = len(df.index)
    record_counts.append(count)
    print(f"{filename}: {count} records")
    dfs.append(df)

这帮助我确定了第一个非空行