Python读取Excel多表时，如何调整首表表头行并修正行数统计？

我用Python循环统计压缩包内Excel各工作表行数时，结果和手动在Excel里计数不一致。问题原因是首表Sheet1的表头在第3行，需要忽略前2行统计数据行数；其余工作表没有表头，从首行开始统计。预期Sheet1行数为65520（原65522减2），其余表行数为65520，但当前代码没得到正确结果，附上完整代码求排查和修正：

from io import BytesIO
from pathlib import Path
from zipfile import ZipFile
import os
import pandas as pd
from os import walk


def process_files(files: list) -> pd.DataFrame:
    file_mapping = {}
    for file in files:
        #data_mapping = pd.read_excel(BytesIO(ZipFile(file).read(Path(file).stem)), sheet_name=None)
        
        archive = ZipFile(file)

        # find file names in the archive which end in `.xls`, `.xlsx`, `.xlsb`, ...
        files_in_archive = archive.namelist()
        excel_files_in_archive = [
            f for f in files_in_archive if Path(f).suffix[:4] == ".xls"
        ]
        # ensure we only have one file (otherwise, loop or choose one somehow)
        assert len(excel_files_in_archive) == 1

        # read in data
        data_mapping = pd.read_excel(
            BytesIO(archive.read(excel_files_in_archive[0])),
            sheet_name=None, header=None,
        )

        
        
               row_counts = []
    for sheet in list(data_mapping.keys()):
        if sheet == 'Sheet1':
            df = data_mapping.get(sheet)[3:]
         
        else:
              df = data_mapping.get(sheet)
        row_counts.append(len(df))
        print(len(data_mapping.get(sheet)))


      
        

        file_mapping.update({file: sum(row_counts)})

    frame = pd.DataFrame([file_mapping]).transpose().reset_index()
    frame.columns = ["file_name", "row_counts"]

    return frame



dir_path = r'D:\test\2022 - 10'




zip_files = []
for root, dirs, files in os.walk(dir_path):
    for file in files:
        if file.endswith('.zip'):
            zip_files.append(os.path.join(root, file))
df = process_files(zip_files)   #function

排查出的问题 #

• 缩进错误：统计工作表行数的for循环缩进层级错误，跑到了遍历zip文件的外层循环外面，导致只会处理最后一个zip文件的Excel表，前面的文件完全没统计
• 行索引错误：Sheet1需要忽略前2行，Python是0索引，第3行对应的索引是2，应该用[2:]而非[3:]，否则会多跳过一行数据
• 调试信息无效：打印的是原工作表的行数，不是处理后的行数，无法验证是否正确跳过了表头行
• 统计逻辑错位：file_mapping.update的缩进错误，导致只有最后一个zip文件的统计结果会被存入字典

修正后的代码 #

from io import BytesIO
from pathlib import Path
from zipfile import ZipFile
import os
import pandas as pd


def process_files(files: list) -> pd.DataFrame:
    file_mapping = {}
    for file in files:
        archive = ZipFile(file)
        # 筛选压缩包内的Excel文件
        files_in_archive = archive.namelist()
        excel_files_in_archive = [
            f for f in files_in_archive if Path(f).suffix in ('.xls', '.xlsx', '.xlsb', '.xlsm')
        ]
        assert len(excel_files_in_archive) == 1, "压缩包内存在多个或无Excel文件"

        # 读取所有工作表，不设置表头
        data_mapping = pd.read_excel(
            BytesIO(archive.read(excel_files_in_archive[0])),
            sheet_name=None, header=None,
        )

        row_counts = []
        for sheet_name, df in data_mapping.items():
            if sheet_name == 'Sheet1':
                # 忽略前2行（0索引，取第3行及以后）
                processed_rows = len(df[2:])
            else:
                processed_rows = len(df)
            row_counts.append(processed_rows)
            # 打印调试信息：工作表名+处理后的行数
            print(f"{file} - {sheet_name}: {processed_rows}")
        
        # 统计当前zip文件的总行数并存入字典
        file_mapping[file] = sum(row_counts)

    # 转换为DataFrame返回
    frame = pd.DataFrame(list(file_mapping.items()), columns=["file_name", "row_counts"])
    return frame


dir_path = r'D:\test\2022 - 10'
zip_files = []
for root, dirs, files in os.walk(dir_path):
    for file in files:
        if file.endswith('.zip'):
            zip_files.append(os.path.join(root, file))

df = process_files(zip_files)
print(df)

修正说明 #

1. 修复了所有缩进错误，确保每个zip文件的处理逻辑完整闭环
2. 调整Sheet1的行切片为[2:]，正确跳过前2行表头
3. 优化调试打印信息，显示文件名、工作表名和处理后的行数，方便验证
4. 简化Excel文件后缀判断，直接匹配常见的Excel格式后缀，避免原代码中suffix[:4]的潜在错误
5. 优化DataFrame的创建方式，直接用list(file_mapping.items())更简洁

内容的提问来源于stack exchange，提问作者Jonnyboi