Python酷库之旅-比翼双飞情侣库(02)

​

# ['Book', 'FILE_FORMAT_DESCRIPTIONS', 'FMLA_TYPE_ARRAY', 'FMLA_TYPE_CELL', 'FMLA_TYPE_COND_FMT', 'FMLA_TYPE_DATA_VAL',
# 'FMLA_TYPE_NAME', 'FMLA_TYPE_SHARED', 'Operand', 'PEEK_SIZE', 'Ref3D', 'XLDateError', 'XLRDError', 'XLS_SIGNATURE',
# 'XL_CELL_BLANK', 'XL_CELL_BOOLEAN', 'XL_CELL_DATE', 'XL_CELL_EMPTY', 'XL_CELL_ERROR', 'XL_CELL_NUMBER', 'XL_CELL_TEXT', 'ZIP_SIGNATURE', 
# '__VERSION__', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', 
# '__spec__', '__version__', 
# 'biff_text_from_num', 'biffh', 'book', 'cellname', 'cellnameabs', 'colname', 'compdoc', 'count_records', 'decompile_formula', 
# 'dump', 'dump_formula', 'empty_cell', 'error_text_from_code', 'evaluate_name_formula', 'formatting', 'formula', 'info', 
# 'inspect_format', 'oBOOL', 'oERR', 'oNUM', 'oREF', 'oREL', 'oSTRG', 'oUNK', 'okind_dict', 'open_workbook', 'open_workbook_xls', 
# 'os', 'pprint', 'rangename3d', 'rangename3drel', 'sheet', 'sys', 'timemachine', 'xldate', 'xldate_as_datetime', 'xldate_as_tuple', 'zipfile']

Help on package xlrd:
 
NAME
    xlrd
 
DESCRIPTION
    # Copyright (c) 2005-2012 Stephen John Machin, Lingfo Pty Ltd
    # This module is part of the xlrd package, which is released under a
    # BSD-style licence.
 
PACKAGE CONTENTS
    biffh
    book
    compdoc
    formatting
    formula
    info
    sheet
    timemachine
    xldate
 
FUNCTIONS
    count_records(filename, outfile=<_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>)
        For debugging and analysis: summarise the file's BIFF records.
        ie: produce a sorted file of ``(record_name, count)``.
        
        :param filename: The path to the file to be summarised.
        :param outfile: An open file, to which the summary is written.
    
    dump(filename, outfile=<_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>, unnumbered=False)
        For debugging: dump an XLS file's BIFF records in char & hex.
        
        :param filename: The path to the file to be dumped.
        :param outfile: An open file, to which the dump is written.
        :param unnumbered: If true, omit offsets (for meaningful diffs).
    
    inspect_format(path=None, content=None)
        Inspect the content at the supplied path or the :class:`bytes` content provided
        and return the file's type as a :class:`str`, or ``None`` if it cannot
        be determined.
        
        :param path:
          A :class:`string <str>` path containing the content to inspect.
          ``~`` will be expanded.
        
        :param content:
          The :class:`bytes` content to inspect.
        
        :returns:
           A :class:`str`, or ``None`` if the format cannot be determined.
           The return value can always be looked up in :data:`FILE_FORMAT_DESCRIPTIONS`
           to return a human-readable description of the format found.
    
    open_workbook(filename=None, logfile=<_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>, verbosity=0, use_mmap=True, file_contents=None, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False, ignore_workbook_corruption=False)
        Open a spreadsheet file for data extraction.
        
        :param filename: The path to the spreadsheet file to be opened.
        
        :param logfile: An open file to which messages and diagnostics are written.
        
        :param verbosity: Increases the volume of trace material written to the
                          logfile.
        
        :param use_mmap:
        
          Whether to use the mmap module is determined heuristically.
          Use this arg to override the result.
        
          Current heuristic: mmap is used if it exists.
        
        :param file_contents:
        
          A string or an :class:`mmap.mmap` object or some other behave-alike
          object. If ``file_contents`` is supplied, ``filename`` will not be used,
          except (possibly) in messages.
        
        :param encoding_override:
        
          Used to overcome missing or bad codepage information
          in older-version files. See :doc:`unicode`.
        
        :param formatting_info:
        
          The default is ``False``, which saves memory.
          In this case, "Blank" cells, which are those with their own formatting
          information but no data, are treated as empty by ignoring the file's
          ``BLANK`` and ``MULBLANK`` records.
          This cuts off any bottom or right "margin" of rows of empty or blank
          cells.
          Only :meth:`~xlrd.sheet.Sheet.cell_value` and
          :meth:`~xlrd.sheet.Sheet.cell_type` are available.
        
          When ``True``, formatting information will be read from the spreadsheet
          file. This provides all cells, including empty and blank cells.
          Formatting information is available for each cell.
        
          Note that this will raise a NotImplementedError when used with an
          xlsx file.
        
        :param on_demand:
        
          Governs whether sheets are all loaded initially or when demanded
          by the caller. See :doc:`on_demand`.
        
        :param ragged_rows:
        
          The default of ``False`` means all rows are padded out with empty cells so
          that all rows have the same size as found in
          :attr:`~xlrd.sheet.Sheet.ncols`.
        
          ``True`` means that there are no empty cells at the ends of rows.
          This can result in substantial memory savings if rows are of widely
          varying sizes. See also the :meth:`~xlrd.sheet.Sheet.row_len` method.
        
        
        :param ignore_workbook_corruption:
        
          This option allows to read corrupted workbooks.
          When ``False`` you may face CompDocError: Workbook corruption.
          When ``True`` that exception will be ignored.
        
        :returns: An instance of the :class:`~xlrd.book.Book` class.
 
DATA
    FILE_FORMAT_DESCRIPTIONS = {'xls': 'Excel xls', 'xlsb': 'Excel 2007 xl...
    FMLA_TYPE_ARRAY = 4
    FMLA_TYPE_CELL = 1
    FMLA_TYPE_COND_FMT = 8
    FMLA_TYPE_DATA_VAL = 16
    FMLA_TYPE_NAME = 32
    FMLA_TYPE_SHARED = 2
    PEEK_SIZE = 8
    XLS_SIGNATURE = b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1'
    XL_CELL_BLANK = 6
    XL_CELL_BOOLEAN = 4
    XL_CELL_DATE = 3
    XL_CELL_EMPTY = 0
    XL_CELL_ERROR = 5
    XL_CELL_NUMBER = 2
    XL_CELL_TEXT = 1
    ZIP_SIGNATURE = b'PK\x03\x04'
    __VERSION__ = '2.0.1'
    biff_text_from_num = {0: '(not BIFF)', 20: '2.0', 21: '2.1', 30: '3', ...
    empty_cell = empty:''
    error_text_from_code = {0: '#NULL!', 7: '#DIV/0!', 15: '#VALUE!', 23: ...
    oBOOL = 3
    oERR = 4
    oNUM = 2
    oREF = -1
    oREL = -2
    oSTRG = 1
    oUNK = 0
    okind_dict = {-2: 'oREL', -1: 'oREF', 0: 'oUNK', 1: 'oSTRG', 2: 'oNUM'...
 
VERSION
    2.0.1
 
FILE
    e:\python_workspace\pythonproject\lib\site-packages\xlrd\__init__.py

xlrd.FILE_FORMAT_DESCRIPTIONS= {'xls': 'Excel xls', 'xlsb': 'Excel 2007 xlsb file', 'xlsx': 'Excel xlsx file', 'ods': 'Openoffice.org ODS file', 'zip': 'Unknown ZIP file', None: 'Unknown file type'}

# 4、xlrd.FILE_FORMAT_DESCRIPTIONS字典
# 4-1、查看字典信息
import xlrd
# 查看 FILE_FORMAT_DESCRIPTIONS 的内容
print(xlrd.FILE_FORMAT_DESCRIPTIONS)
# 输出：{'xls': 'Excel xls', 'xlsb': 'Excel 2007 xlsb file', 'xlsx': 'Excel xlsx file', 'ods': 'Openoffice.org ODS file', 'zip': 'Unknown ZIP file', None: 'Unknown file type'}
 
# 4-2、应用示例
import xlrd
import os
def open_excel_file(filename):
    # 获取文件的扩展名
    _, ext = os.path.splitext(filename)
    ext = ext.lower()[1:]  # 去掉点号，并转换为小写
    # 检查文件扩展名是否在FILE_FORMAT_DESCRIPTIONS中
    if ext not in xlrd.FILE_FORMAT_DESCRIPTIONS:
        print(f"Unsupported file type: {ext}.")
        print(f"Supported file types: {', '.join(xlrd.FILE_FORMAT_DESCRIPTIONS.keys())}")
        return None
    try:
        # 尝试打开文件
        workbook = xlrd.open_workbook(filename)
        print(f"Opened {xlrd.FILE_FORMAT_DESCRIPTIONS[ext]} successfully.")
        return workbook
    except Exception as e:
        print(f"Error opening file: {e}")
        return None
if __name__ == '__main__':
    filename = r'E:\360Downloads\test.xls'  # 替换为你的Excel文件路径，我的路径为：E:\360Downloads\test.xls
    workbook = open_excel_file(filename)
    if workbook:
        # 如果文件成功打开，你可以在这里继续处理 workbook 对象
        pass

xlrd.inspect_format(path=None, content=None)

#  5、xlrd.inspect_format函数
import xlrd
import os
# FILE_FORMAT_DESCRIPTIONS字典信息
FILE_FORMAT_DESCRIPTIONS = {'xls': 'Excel xls', 'xlsb': 'Excel 2007 xlsb file', 'xlsx': 'Excel xlsx file',
                            'ods': 'Openoffice.org ODS file', 'zip': 'Unknown ZIP file', None: 'Unknown file type'}
def inspect_format(path=None, content=None):
    # 为了简单起见，我们仅检查文件扩展名(这在实际中并不准确)
    if path is not None:
        # 假设 ~ 已被展开(在实际代码中，你可能需要使用os.path.expanduser来处理~)
        _, ext = os.path.splitext(path)
        ext = ext.lower()
        if ext in ['.xls', '.xlsx']:
            return ext[1:]  # 去掉点号，返回 'xls' 或 'xlsx'
    elif content is not None:
        # 在这里，我们可以添加更复杂的逻辑来检查字节内容
        # 但为了简单起见，我们仅返回一个占位符值
        return 'unknown'  # 假设我们无法仅通过内容确定格式
    return None  # 如果 path 和 content 都未提供，返回 None
if __name__ == '__main__':
    # 使用文件路径
    file_path = r'E:\360Downloads\test.xls'
    file_type = inspect_format(path=file_path)
    print(f"File type: {file_type}")
    print(f"Description: {FILE_FORMAT_DESCRIPTIONS.get(file_type, FILE_FORMAT_DESCRIPTIONS['zip'])}")
    # 使用字节内容(这通常不是实际用法，但为了演示)
    # 注意：在实际中，你通常不会直接传递字节内容来确定文件类型
    content_bytes = b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1...'  # 假设的XLS文件开头字节
    file_type_from_content = inspect_format(content=content_bytes)
    print(f"File type from content: {file_type_from_content}")
    print(f"Description: {FILE_FORMAT_DESCRIPTIONS.get(file_type_from_content, FILE_FORMAT_DESCRIPTIONS['zip'])}")

xlrd.open_workbook(filename=None, logfile=<_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>, verbosity=0, use_mmap=True, file_contents=None, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False, ignore_workbook_corruption=False)

# 6、xlrd.open_workbook函数
import xlrd
# 定义Excel文件的路径
file_path = r'E:\360Downloads\test.xls' # 替换为你的Excel文件路径，我的路径为：E:\360Downloads\test.xls
# 使用xlrd.open_workbook打开Excel文件
workbook = xlrd.open_workbook(file_path)
# 获取第一个工作表（在Excel中通常是Sheet1）
sheet = workbook.sheet_by_index(0)  # 或者使用sheet_by_name('Sheet1')
# 获取工作表的行数和列数
nrows = sheet.nrows
ncols = sheet.ncols
# 打印工作表的行数和列数
print(f"工作表有 {nrows} 行和 {ncols} 列")
# 遍历工作表的每一行和每一列，并打印内容
for row_idx in range(nrows):
    row = sheet.row_values(row_idx)  # 获取当前行的所有单元格值
    for col_idx, cell_value in enumerate(row):
        # 打印单元格的值（注意：这里只是简单打印，实际使用中可能需要进行更复杂的处理）
        print(f"行 {row_idx + 1}, 列 {col_idx + 1}: {cell_value}")
    print()  # 打印一个空行，以便区分不同的行
# 注意：在大多数情况下，不需要显式关闭workbook，因为Python的垃圾回收机制会处理它
# 但如果你需要确保资源被立即释放，可以调用workbook.release_resources()
# workbook.release_resources()