API 参考 ¶

xlrd ¶

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'} ¶

descriptions of the file types xlrd can inspect .

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

Inspect the content at the supplied path or the bytes content provided and return the file’s type as a str ，或 None if it cannot be determined.

参数

• path – A string path containing the content to inspect. ~ will be expanded.

• content – The bytes content to inspect.

返回

A str ，或 None if the format cannot be determined. The return value can always be looked up in FILE_FORMAT_DESCRIPTIONS to return a human-readable description of the format found.

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 ) ¶

Open a spreadsheet file for data extraction.

参数

• filename – The path to the spreadsheet file to be opened.

• logfile – An open file to which messages and diagnostics are written.

• verbosity – Increases the volume of trace material written to the logfile.

• 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.

• file_contents – A string or an mmap.mmap object or some other behave-alike object. If file_contents is supplied, filename will not be used, except (possibly) in messages.

• encoding_override – Used to overcome missing or bad codepage information in older-version files. See Unicode 的处理 .

• formatting_info –

  默认为 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 cell_value() and cell_type() are available.

  当 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.

• on_demand – Governs whether sheets are all loaded initially or when demanded by the caller. See 按需加载工作表 .

• 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 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 row_len() 方法。

• 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.

返回

An instance of the Book 类。

xlrd. 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.

参数

• filename – The path to the file to be dumped.

• outfile – An open file, to which the dump is written.

• unnumbered – If true, omit offsets (for meaningful diffs).

xlrd. 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) .

参数

• filename – The path to the file to be summarised.

• outfile – An open file, to which the summary is written.

xlrd.biffh ¶

exception xlrd.biffh. XLRDError ¶

An exception indicating problems reading data from an Excel file.

class xlrd.biffh. BaseObject ¶

Parent of almost all other classes in the package. Defines a common dump() method for debugging.

dump ( f = None , header = None , footer = None , indent = 0 ) ¶

参数

• f – open file object, to which the dump is written

• header – text to write before the dump

• footer – text to write after the dump

• indent – number of leading spaces (for recursive calls)

xlrd.biffh. error_text_from_code = {0: '#NULL!', 7: '#DIV/0!', 15: '#VALUE!', 23: '#REF!', 29: '#NAME?', 36: '#NUM!', 42: '#N/A'} ¶

This dictionary can be used to produce a text version of the internal codes that Excel uses for error cells.

xlrd.biffh. unpack_unicode ( data , pos , lenlen = 2 ) ¶

Return unicode_strg

xlrd.biffh. unpack_unicode_update_pos ( data , pos , lenlen = 2 , known_len = None ) ¶

Return (unicode_strg, updated value of pos)

xlrd.book ¶

class xlrd.book. Name ¶

Information relating to a named reference, formula, macro, etc.

注意

Name information is not extracted from files older than Excel 5.0 ( Book.biff_version < 50 )

hidden = 0 ¶

0 = Visible; 1 = Hidden

func = 0 ¶

0 = Command macro; 1 = Function macro. Relevant only if macro == 1

vbasic = 0 ¶

0 = Sheet macro; 1 = VisualBasic macro. Relevant only if macro == 1

macro = 0 ¶

0 = Standard name; 1 = Macro name

complex = 0 ¶

0 = Simple formula; 1 = Complex formula (array formula or user defined).

注意

No examples have been sighted.

builtin = 0 ¶

0 = User-defined name; 1 = Built-in name

Common examples: Print_Area , Print_Titles ; see OOo docs for full list

funcgroup = 0 ¶

Function group. Relevant only if macro == 1; see OOo docs for values.

binary = 0 ¶

0 = Formula definition; 1 = Binary data

注意

No examples have been sighted.

name_index = 0 ¶

The index of this object in book.name_obj_list

raw_formula = b'' ¶

An 8-bit string.

scope = -1 ¶

-1 :

The name is global (visible in all calculation sheets).

-2 :

The name belongs to a macro sheet or VBA sheet.

-3 :

The name is invalid.

0 <= scope < book.nsheets :

The name is local to the sheet whose index is scope.

cell ( ) ¶

This is a convenience method for the frequent use case where the name refers to a single cell.

返回

An instance of the Cell 类。

引发

xlrd.biffh.XLRDError – The name is not a constant absolute reference to a single cell.

area2d ( clipped = True ) ¶

This is a convenience method for the use case where the name refers to one rectangular area in one worksheet.

参数

clipped – If True , the default, the returned rectangle is clipped to fit in (0, sheet.nrows, 0, sheet.ncols) . it is guaranteed that 0 <= rowxlo <= rowxhi <= sheet.nrows and that the number of usable rows in the area (which may be zero) is rowxhi - rowxlo ; likewise for columns.

返回

a tuple (sheet_object, rowxlo, rowxhi, colxlo, colxhi) .

引发

xlrd.biffh.XLRDError – The name is not a constant absolute reference to a single area in a single sheet.

class xlrd.book. Book ¶

Contents of a “workbook”.

警告

You should not instantiate this class yourself. You use the Book object that was returned when you called open_workbook() .

datemode = 0 ¶

Which date system was in force when this file was last saved.

0:

1900 system (the Excel for Windows default).

1:

1904 system (the Excel for Macintosh default).

Defaults to 0 in case it’s not specified in the file.

biff_version = 0 ¶

Version of BIFF (Binary Interchange File Format) used to create the file. Latest is 8.0 (represented here as 80), introduced with Excel 97. Earliest supported by this module: 2.0 (represented as 20).

codepage = None ¶

An integer denoting the character set used for strings in this file. For BIFF 8 and later, this will be 1200, meaning Unicode; more precisely, UTF_16_LE. For earlier versions, this is used to derive the appropriate Python encoding to be used to convert to Unicode. Examples: 1252 -> 'cp1252' , 10000 -> 'mac_roman'

encoding = None ¶

The encoding that was derived from the codepage.

countries = (0, 0) ¶

A tuple containing the telephone country code for:

[0] :

the user-interface setting when the file was created.

[1] :

the regional settings.

范例： (1, 61) meaning (USA, Australia) .

This information may give a clue to the correct encoding for an unknown codepage. For a long list of observed values, refer to the OpenOffice.org documentation for the COUNTRY record.

user_name = '' ¶

What (if anything) is recorded as the name of the last user to save the file.

font_list = [] ¶

A list of Font class instances, each corresponding to a FONT record.

0.6.1 版新增。

format_list = [] ¶

A list of Format objects, each corresponding to a FORMAT record, in the order that they appear in the input file. It does not contain builtin formats.

If you are creating an output file using (for example) xlwt , use this list.

The collection to be used for all visual rendering purposes is format_map .

0.6.1 版新增。

format_map = {} ¶

The mapping from format_key to Format 对象。

0.6.1 版新增。

load_time_stage_1 = -1.0 ¶

Time in seconds to extract the XLS image as a contiguous string (or mmap equivalent).

load_time_stage_2 = -1.0 ¶

Time in seconds to parse the data from the contiguous string (or mmap equivalent).

sheets ( ) ¶

返回

A list of all sheets in the book.

All sheets not already loaded will be loaded.

sheet_by_index ( sheetx ) ¶

参数

sheetx – Sheet index in range(nsheets)

返回

A Sheet .

sheet_by_name ( sheet_name ) ¶

参数

sheet_name – Name of the sheet required.

返回

A Sheet .

sheet_names ( ) ¶

返回

A list of the names of all the worksheets in the workbook file. This information is available even when no sheets have yet been loaded.

sheet_loaded ( sheet_name_or_index ) ¶

参数

sheet_name_or_index – Name or index of sheet enquired upon

返回

True if sheet is loaded, False 否则。

New in version 0.7.1.

unload_sheet ( sheet_name_or_index ) ¶

参数

sheet_name_or_index – Name or index of sheet to be unloaded.

New in version 0.7.1.

release_resources ( ) ¶

This method has a dual purpose. You can call it to release memory-consuming objects and (possibly) a memory-mapped file ( mmap.mmap object) when you have finished loading sheets in on_demand mode, but still require the Book object to examine the loaded sheets. It is also called automatically (a) when open_workbook() raises an exception and (b) if you are using a with statement, when the with block is exited. Calling this method multiple times on the same object has no ill effect.

name_and_scope_map = {} ¶

A mapping from (lower_case_name, scope) to a single Name

对象。

0.6.0 版新增。

name_map = {} ¶

A mapping from lower_case_name to a list of Name objects. The list is sorted in scope order. Typically there will be one item (of global scope) in the list.

0.6.0 版新增。

nsheets = 0 ¶

The number of worksheets present in the workbook file. This information is available even when no sheets have yet been loaded.

name_obj_list = [] ¶

List containing a Name object for each NAME record in the workbook.

0.6.0 版新增。

colour_map = {} ¶

This provides definitions for colour indexes. Please refer to The Palette; Colour Indexes for an explanation of how colours are represented in Excel.

Colour indexes into the palette map into (red, green, blue) tuples. “Magic” indexes e.g. 0x7FFF map to None .

colour_map is what you need if you want to render cells on screen or in a PDF file. If you are writing an output XLS file, use palette_record .

注意

Extracted only if open_workbook(..., formatting_info=True)

0.6.1 版新增。

palette_record = [] ¶

If the user has changed any of the colours in the standard palette, the XLS file will contain a PALETTE record with 56 (16 for Excel 4.0 and earlier) RGB values in it, and this list will be e.g. [(r0, b0, g0), ..., (r55, b55, g55)] . Otherwise this list will be empty. This is what you need if you are writing an output XLS file. If you want to render cells on screen or in a PDF file, use colour_map .

注意

Extracted only if open_workbook(..., formatting_info=True)

0.6.1 版新增。

xf_list = [] ¶

A list of XF class instances, each corresponding to an XF record.

0.6.1 版新增。

style_name_map = {} ¶

This provides access via name to the extended format information for both built-in styles and user-defined styles.

It maps name to (built_in, xf_index) ，其中 name is either the name of a user-defined style, or the name of one of the built-in styles. Known built-in names are Normal, RowLevel_1 to RowLevel_7, ColLevel_1 to ColLevel_7, Comma, Currency, Percent, “Comma [0]”, “Currency [0]”, Hyperlink, and “Followed Hyperlink”.

built_in has the following meanings

1:

built-in style

0:

user-defined

xf_index is an index into Book.xf_list .

References: OOo docs s6.99 ( STYLE record); Excel UI Format/Style

0.6.1 版新增。

Extracted only if open_workbook(..., formatting_info=True)

New in version 0.7.4.

xlrd.book. unpack_SST_table ( datatab , nstrings ) ¶

Return list of strings

xlrd.compdoc ¶

Implements the minimal functionality required to extract a “Workbook” or “Book” stream (as one big string) from an OLE2 Compound Document file.

xlrd.compdoc. SIGNATURE = b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1' ¶

Magic cookie that should appear in the first 8 bytes of the file.

exception xlrd.compdoc. CompDocError ¶

class xlrd.compdoc. CompDoc ( mem , logfile=<_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'> , DEBUG=0 , ignore_workbook_corruption=False ) ¶

Compound document handler.

参数

mem – The raw contents of the file, as a string, or as an mmap.mmap object. The only operation it needs to support is slicing.

get_named_stream ( qname ) ¶

Interrogate the compound document’s directory; return the stream as a string if found, otherwise return None .

参数

qname – Name of the desired stream e.g. 'Workbook' . Should be in Unicode or convertible thereto.

locate_named_stream ( qname ) ¶

Interrogate the compound document’s directory.

If the named stream is not found, (None, 0, 0) 将被返回。

If the named stream is found and is contiguous within the original byte sequence ( mem ) used when the document was opened, then (mem, offset_to_start_of_stream, length_of_stream) 被返回。

Otherwise a new string is built from the fragments and (new_string, 0, length_of_stream) 被返回。

参数

qname – Name of the desired stream e.g. 'Workbook' . Should be in Unicode or convertible thereto.

xlrd.formatting ¶

Module for formatting information.

xlrd.formatting. nearest_colour_index ( colour_map , rgb , debug = 0 ) ¶

General purpose function. Uses Euclidean distance. So far used only for pre-BIFF8 WINDOW2 record. Doesn’t have to be fast. Doesn’t have to be fancy.

class xlrd.formatting. EqNeAttrs ¶

This mixin class exists solely so that Format , Font ，和 XF objects can be compared by value of their attributes.

class xlrd.formatting. Font ¶

An Excel “font” contains the details of not only what is normally considered a font, but also several other display attributes. Items correspond to those in the Excel UI’s Format -> Cells -> Font tab.

0.6.1 版新增。