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 一下懒得装就算了