python pandas获取可见表（排除隐藏表）（pandas v1.5.0）

excel表第一个sheet可能被隐藏，所以导致pandas读取到的数据和打开的数据不一致

import openpyxl
import pandas as pd
import xlrd
from pandas import DataFrame

with pd.ExcelFile(file_path) as xl:
    sheets = []
    if isinstance(xl.book, openpyxl.workbook.workbook.Workbook):
        sheets = xl.book.worksheets
    elif isinstance(xl.book, xlrd.book.Book):
        sheets = xl.book.sheets()
    for sheet in sheets:
        if (hasattr(sheet, 'visibility') and sheet.visibility == 0) or (
                hasattr(sheet, 'sheet_state') and sheet.sheet_state == 'visible'):
            if hasattr(sheet, 'title'):
                sheet.name = sheet.title
            df: DataFrame = xl.parse(sheet_name=sheet.name)
            print(df.head)

源码分析

    _engines: Mapping[str, Any] = {
        "xlrd": XlrdReader,
        "openpyxl": OpenpyxlReader,
        "odf": ODFReader,
        "pyxlsb": PyxlsbReader,
    }

文件头获取excel文件格式

XLS_SIGNATURES = (
    b"\x09\x00\x04\x00\x07\x00\x10\x00",  # BIFF2
    b"\x09\x02\x06\x00\x00\x00\x10\x00",  # BIFF3
    b"\x09\x04\x06\x00\x00\x00\x10\x00",  # BIFF4
    b"\xD0\xCF\x11\xE0\xA1\xB1\x1A\xE1",  # Compound File Binary
)
ZIP_SIGNATURE = b"PK\x03\x04"
PEEK_SIZE = max(map(len, XLS_SIGNATURES + (ZIP_SIGNATURE,)))


@doc(storage_options=_shared_docs["storage_options"])
def inspect_excel_format(
    content_or_path: FilePath | ReadBuffer[bytes],
    storage_options: StorageOptions = None,
) -> str | None:
    if isinstance(content_or_path, bytes):
        content_or_path = BytesIO(content_or_path)

    with get_handle(
        content_or_path, "rb", storage_options=storage_options, is_text=False
    ) as handle:
        stream = handle.handle
        stream.seek(0)
        buf = stream.read(PEEK_SIZE)
        if buf is None:
            raise ValueError("stream is empty")
        else:
            assert isinstance(buf, bytes)
            peek = buf
        stream.seek(0)

        if any(peek.startswith(sig) for sig in XLS_SIGNATURES):
            return "xls"
        elif not peek.startswith(ZIP_SIGNATURE):
            return None

        with zipfile.ZipFile(stream) as zf:
            # Workaround for some third party files that use forward slashes and
            # lower case names.
            component_names = [
                name.replace("\\", "/").lower() for name in zf.namelist()
            ]

        if "xl/workbook.xml" in component_names:
            return "xlsx"
        if "xl/workbook.bin" in component_names:
            return "xlsb"
        if "content.xml" in component_names:
            return "ods"
        return "zip"

通过文件类型获取engine

    _default_readers = {
        "xlsx": "openpyxl",
        "xlsm": "openpyxl",
        "xlsb": "pyxlsb",
        "xls": "xlrd",
        "ods": "odf",
    }
    
   	_engines: Mapping[str, Any] = {
        "xlrd": XlrdReader,
        "openpyxl": OpenpyxlReader,
        "odf": ODFReader,
        "pyxlsb": PyxlsbReader,
    }

我只用到了 XlrdReader ，OpenpyxlReader其他的要pip install 一下懒得装就算了