From 0ca5520bbc1474dbd872a3f330c681a5201336fd Mon Sep 17 00:00:00 2001 From: Claude Paroz Date: Mon, 20 Feb 2017 21:29:22 +0100 Subject: Replaced vendored xlrd/xlwt by dependencies (#277) Refs #273. --- tablib/packages/xlrd/doc/xlrd.html | 1845 ------------------------------------ 1 file changed, 1845 deletions(-) delete mode 100644 tablib/packages/xlrd/doc/xlrd.html (limited to 'tablib/packages/xlrd/doc/xlrd.html') diff --git a/tablib/packages/xlrd/doc/xlrd.html b/tablib/packages/xlrd/doc/xlrd.html deleted file mode 100644 index f982e0e..0000000 --- a/tablib/packages/xlrd/doc/xlrd.html +++ /dev/null @@ -1,1845 +0,0 @@ - - - - -The xlrd Module - - -

The xlrd Module

A Python module for extracting data from MS Excel ™ spreadsheet files. -

-Version 0.7.1 -- 2009-05-31 -

- -

General information

- -

Acknowledgements

- -

-Development of this module would not have been possible without the document -"OpenOffice.org's Documentation of the Microsoft Excel File Format" -("OOo docs" for short). -The latest version is available from OpenOffice.org in - PDF format -and - ODT format. -Small portions of the OOo docs are reproduced in this -document. A study of the OOo docs is recommended for those who wish a -deeper understanding of the Excel file layout than the xlrd docs can provide. -

- -

Backporting to Python 2.1 was partially funded by - - Journyx - provider of timesheet and project accounting solutions. - -

- -

Provision of formatting information in version 0.6.1 was funded by - - Simplistix Ltd. - -

- -

Unicode

- -

This module presents all text strings as Python unicode objects. -From Excel 97 onwards, text in Excel spreadsheets has been stored as Unicode. -Older files (Excel 95 and earlier) don't keep strings in Unicode; -a CODEPAGE record provides a codepage number (for example, 1252) which is -used by xlrd to derive the encoding (for same example: "cp1252") which is -used to translate to Unicode.

- -

If the CODEPAGE record is missing (possible if the file was created -by third-party software), xlrd will assume that the encoding is ascii, and keep going. -If the actual encoding is not ascii, a UnicodeDecodeError exception will be raised and -you will need to determine the encoding yourself, and tell xlrd: -

-    book = xlrd.open_workbook(..., encoding_override="cp1252")
-

If the CODEPAGE record exists but is wrong (for example, the codepage -number is 1251, but the strings are actually encoded in koi8_r), -it can be overridden using the same mechanism. -The supplied runxlrd.py has a corresponding command-line argument, which -may be used for experimentation: -

-    runxlrd.py -e koi8_r 3rows myfile.xls
-

The first place to look for an encoding ("codec name") is - -the Python documentation. -

- - -

Dates in Excel spreadsheets

- -

In reality, there are no such things. What you have are floating point -numbers and pious hope. -There are several problems with Excel dates:

- -

(1) Dates are not stored as a separate data type; they are stored as -floating point numbers and you have to rely on -(a) the "number format" applied to them in Excel and/or -(b) knowing which cells are supposed to have dates in them. -This module helps with (a) by inspecting the -format that has been applied to each number cell; -if it appears to be a date format, the cell -is classified as a date rather than a number. Feedback on this feature, -especially from non-English-speaking locales, would be appreciated.

- -

(2) Excel for Windows stores dates by default as the number of -days (or fraction thereof) since 1899-12-31T00:00:00. Excel for -Macintosh uses a default start date of 1904-01-01T00:00:00. The date -system can be changed in Excel on a per-workbook basis (for example: -Tools -> Options -> Calculation, tick the "1904 date system" box). -This is of course a bad idea if there are already dates in the -workbook. There is no good reason to change it even if there are no -dates in the workbook. Which date system is in use is recorded in the -workbook. A workbook transported from Windows to Macintosh (or vice -versa) will work correctly with the host Excel. When using this -module's xldate_as_tuple function to convert numbers from a workbook, -you must use the datemode attribute of the Book object. If you guess, -or make a judgement depending on where you believe the workbook was -created, you run the risk of being 1462 days out of kilter.

- -

Reference: -http://support.microsoft.com/default.aspx?scid=KB;EN-US;q180162

- - -

(3) The Excel implementation of the Windows-default 1900-based date system works on the -incorrect premise that 1900 was a leap year. It interprets the number 60 as meaning 1900-02-29, -which is not a valid date. Consequently any number less than 61 is ambiguous. Example: is 59 the -result of 1900-02-28 entered directly, or is it 1900-03-01 minus 2 days? The OpenOffice.org Calc -program "corrects" the Microsoft problem; entering 1900-02-27 causes the number 59 to be stored. -Save as an XLS file, then open the file with Excel -- you'll see 1900-02-28 displayed.

- -

Reference: http://support.microsoft.com/default.aspx?scid=kb;en-us;214326

- -

(4) The Macintosh-default 1904-based date system counts 1904-01-02 as day 1 and 1904-01-01 as day zero. -Thus any number such that (0.0 <= number < 1.0) is ambiguous. Is 0.625 a time of day (15:00:00), -independent of the calendar, -or should it be interpreted as an instant on a particular day (1904-01-01T15:00:00)? -The xldate_* functions in this module -take the view that such a number is a calendar-independent time of day (like Python's datetime.time type) for both -date systems. This is consistent with more recent Microsoft documentation -(for example, the help file for Excel 2002 which says that the first day -in the 1904 date system is 1904-01-02). - -

(5) Usage of the Excel DATE() function may leave strange dates in a spreadsheet. Quoting the help file, -in respect of the 1900 date system: "If year is between 0 (zero) and 1899 (inclusive), -Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108)." -This gimmick, semi-defensible only for arguments up to 99 and only in the pre-Y2K-awareness era, -means that DATE(1899, 12, 31) is interpreted as 3799-12-31.

- -

For further information, please refer to the documentation for the xldate_* functions.

- -

Named references, constants, formulas, and macros

- -

-A name is used to refer to a cell, a group of cells, a constant -value, a formula, or a macro. Usually the scope of a name is global -across the whole workbook. However it can be local to a worksheet. -For example, if the sales figures are in different cells in -different sheets, the user may define the name "Sales" in each -sheet. There are built-in names, like "Print_Area" and -"Print_Titles"; these two are naturally local to a sheet. -

-To inspect the names with a user interface like MS Excel, OOo Calc, -or Gnumeric, click on Insert/Names/Define. This will show the global -names, plus those local to the currently selected sheet. -

-A Book object provides two dictionaries (name_map and -name_and_scope_map) and a list (name_obj_list) which allow various -ways of accessing the Name objects. There is one Name object for -each NAME record found in the workbook. Name objects have many -attributes, several of which are relevant only when obj.macro is 1. -

-In the examples directory you will find namesdemo.xls which -showcases the many different ways that names can be used, and -xlrdnamesAPIdemo.py which offers 3 different queries for inspecting -the names in your files, and shows how to extract whatever a name is -referring to. There is currently one "convenience method", -Name.cell(), which extracts the value in the case where the name -refers to a single cell. More convenience methods are planned. The -source code for Name.cell (in __init__.py) is an extra source of -information on how the Name attributes hang together. -

- -

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

- -

Formatting

- -

Introduction

- -

This collection of features, new in xlrd version 0.6.1, is intended -to provide the information needed to (1) display/render spreadsheet contents -(say) on a screen or in a PDF file, and (2) copy spreadsheet data to another -file without losing the ability to display/render it.

- -

The Palette; Colour Indexes

- -

A colour is represented in Excel as a (red, green, blue) ("RGB") tuple -with each component in range(256). However it is not possible to access an -unlimited number of colours; each spreadsheet is limited to a palette of 64 different -colours (24 in Excel 3.0 and 4.0, 8 in Excel 2.0). Colours are referenced by an index -("colour index") into this palette. - -Colour indexes 0 to 7 represent 8 fixed built-in colours: black, white, red, green, blue, -yellow, magenta, and cyan.

- -The remaining colours in the palette (8 to 63 in Excel 5.0 and later) -can be changed by the user. In the Excel 2003 UI, Tools/Options/Color presents a palette -of 7 rows of 8 colours. The last two rows are reserved for use in charts.
-The correspondence between this grid and the assigned -colour indexes is NOT left-to-right top-to-bottom.
-Indexes 8 to 15 correspond to changeable -parallels of the 8 fixed colours -- for example, index 7 is forever cyan; -index 15 starts off being cyan but can be changed by the user.
- -The default colour for each index depends on the file version; tables of the defaults -are available in the source code. If the user changes one or more colours, -a PALETTE record appears in the XLS file -- it gives the RGB values for *all* changeable -indexes.
-Note that colours can be used in "number formats": "[CYAN]...." and "[COLOR8]...." refer -to colour index 7; "[COLOR16]...." will produce cyan -unless the user changes colour index 15 to something else.
- -

In addition, there are several "magic" colour indexes used by Excel:
-0x18 (BIFF3-BIFF4), 0x40 (BIFF5-BIFF8): System window text colour for border lines -(used in XF, CF, and WINDOW2 records)
-0x19 (BIFF3-BIFF4), 0x41 (BIFF5-BIFF8): System window background colour for pattern background -(used in XF and CF records )
-0x43: System face colour (dialogue background colour)
-0x4D: System window text colour for chart border lines
-0x4E: System window background colour for chart areas
-0x4F: Automatic colour for chart border lines (seems to be always Black)
-0x50: System ToolTip background colour (used in note objects)
-0x51: System ToolTip text colour (used in note objects)
-0x7FFF: System window text colour for fonts (used in FONT and CF records)
-Note 0x7FFF appears to be the *default* colour index. It appears quite often in FONT -records.
- -

Default Formatting

- -Default formatting is applied to all empty cells (those not described by a cell record). -Firstly row default information (ROW record, Rowinfo class) is used if available. -Failing that, column default information (COLINFO record, Colinfo class) is used if available. -As a last resort the worksheet/workbook default cell format will be used; this -should always be present in an Excel file, -described by the XF record with the fixed index 15 (0-based). By default, it uses the -worksheet/workbook default cell style, described by the very first XF record (index 0). - -

Formatting features not included in xlrd version 0.6.1

Rich text i.e. strings containing partial bold italic - and underlined text, change of font inside a string, etc. - See OOo docs s3.4 and s3.2
Asian phonetic text (known as "ruby"), used for Japanese furigana. See OOo docs - s3.4.2 (p15)
Conditional formatting. See OOo docs - s5.12, s6.21 (CONDFMT record), s6.16 (CF record)
Miscellaneous sheet-level and book-level items e.g. printing layout, screen panes.
Modern Excel file versions don't keep most of the built-in - "number formats" in the file; Excel loads formats according to the - user's locale. Currently xlrd's emulation of this is limited to - a hard-wired table that applies to the US English locale. This may mean - that currency symbols, date order, thousands separator, decimals separator, etc - are inappropriate. Note that this does not affect users who are copying XLS - files, only those who are visually rendering cells.

- -

Loading worksheets on demand

- -

This feature, new in version 0.7.1, is governed by the on_demand argument -to the open_workbook() function and allows saving memory and time by loading -only those sheets that the caller is interested in, and releasing sheets -when no longer required.

- -

on_demand=False (default): No change. open_workbook() loads global data -and all sheets, releases resources no longer required (principally the -str or mmap object containing the Workbook stream), and returns.

- -

on_demand=True and BIFF version < 5.0: A warning message is emitted, -on_demand is recorded as False, and the old process is followed.

- -

on_demand=True and BIFF version >= 5.0: open_workbook() loads global -data and returns without releasing resources. At this stage, the only -information available about sheets is Book.nsheets and Book.sheet_names().

- -

Book.sheet_by_name() and Book.sheet_by_index() will load the requested -sheet if it is not already loaded.

- -

Book.sheets() will load all/any unloaded sheets.

- -

The caller may save memory by calling -Book.unload_sheet(sheet_name_or_index) when finished with the sheet. -This applies irrespective of the state of on_demand.

- -

The caller may re-load an unloaded sheet by calling Book.sheet_by_xxxx() - -- except if those required resources have been released (which will -have happened automatically when on_demand is false). This is the only -case where an exception will be raised.

- -

The caller may query the state of a sheet: -Book.sheet_loaded(sheet_name_or_index) -> a bool

- -

Module Contents

BaseObject (class) [#]

Parent of almost all other classes in the package.

For more information about this class, see The BaseObject Class.

Book() (class) [#]

Contents of a "workbook".

For more information about this class, see The Book Class.

Cell(ctype, value, xf_index=None) (class) [#]

Contains the data for one cell.

For more information about this class, see The Cell Class.

cellname(rowx, colx) [#]

Utility function: (5, 7) => 'H6'

cellnameabs(rowx, colx) [#]

Utility function: (5, 7) => '$H$6'

Colinfo (class) [#]

Width and default formatting information that applies to one or -more columns in a sheet.

For more information about this class, see The Colinfo Class.

colname(colx) [#]

Utility function: 7 => 'H', 27 => 'AB'

count_records(filename, outfile=sys.stdout) [#]

For debugging and analysis: summarise the file's BIFF records. -I.e. 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.

dump(filename, outfile=sys.stdout, unnumbered=False) [#]

For debugging: dump the 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).

empty_cell (variable) [#]

There is one and only one instance of an empty cell -- it's a singleton. This is it. -You may use a test like "acell is empty_cell".

EqNeAttrs (class) [#]

This mixin class exists solely so that Format, Font, and XF....

For more information about this class, see The EqNeAttrs Class.

error_text_from_code (variable) [#]

This dictionary can be used to produce a text version of the internal codes -that Excel uses for error cells. Here are its contents: -

-0x00: '#NULL!',  # Intersection of two cell ranges is empty
-0x07: '#DIV/0!', # Division by zero
-0x0F: '#VALUE!', # Wrong type of operand
-0x17: '#REF!',   # Illegal or deleted cell reference
-0x1D: '#NAME?',  # Wrong function or range name
-0x24: '#NUM!',   # Value range overflow
-0x2A: '#N/A!',   # Argument or function not available
-

Font (class) [#]

An Excel "font" contains the details of not only what is normally -considered a font, but also several other display attributes.

For more information about this class, see The Font Class.

Format(format_key, ty, format_str) (class) [#]

"Number format" information from a FORMAT record.

For more information about this class, see The Format Class.

Name (class) [#]

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

For more information about this class, see The Name Class.

open_workbook(filename=None, -logfile=sys.stdout, verbosity=0, pickleable=True, use_mmap=USE_MMAP, -file_contents=None, -encoding_override=None, -formatting_info=False, on_demand=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.
pickleable: -Default is true. In Python 2.4 or earlier, setting to false -will cause use of array.array objects which save some memory but can't be pickled. -In Python 2.5, array.arrays are used unconditionally. Note: if you have large files that -you need to read multiple times, it can be much faster to cPickle.dump() the xlrd.Book object -once, and use cPickle.load() multiple times.
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: -... as 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. Refer to discussion in the Unicode section above. -
-- New in version 0.6.0 - -
formatting_info: -Governs provision of a reference to an XF (eXtended Format) object -for each cell in the worksheet. -
Default is False. This is backwards compatible and saves memory. -"Blank" cells (those with their own formatting information but no data) are treated as empty -(by ignoring the file's BLANK and MULBLANK records). -It cuts off any bottom "margin" of rows of empty (and blank) cells and -any right "margin" of columns of empty (and blank) cells. -Only cell_value and cell_type are available. -
True provides all cells, including empty and blank cells. -XF information is available for each cell. -
-- New in version 0.6.1 - -
on_demand: -Governs whether sheets are all loaded initially or when demanded -by the caller. Please refer back to the section "Loading worksheets on demand" for details. --- New in version 0.7.1
Returns:: -An instance of the Book class.

Operand(akind=None, avalue=None, arank=0, atext='?') (class) [#]

Used in evaluating formulas.

For more information about this class, see The Operand Class.

rangename3d(book, ref3d) [#]

Utility function: -
Ref3D((1, 4, 5, 20, 7, 10)) => 'Sheet2:Sheet3!$H$6:$J$20' -

rangename3drel(book, ref3d) [#]

Utility function: -
Ref3D(coords=(0, 1, -32, -22, -13, 13), relflags=(0, 0, 1, 1, 1, 1)) -=> 'Sheet1![@-13,#-32]:[@+12,#-23]' -where '@' refers to the current or base column and '#' -refers to the current or base row. -

Ref3D(atuple) (class) [#]

Represents an absolute or relative 3-dimensional reference to a box -of one or more cells.

For more information about this class, see The Ref3D Class.

Rowinfo (class) [#]

Height and default formatting information that applies to a row in a sheet.

For more information about this class, see The Rowinfo Class.

Sheet(book, position, name, number) (class) [#]

Contains the data for one worksheet.

For more information about this class, see The Sheet Class.

XF (class) [#]

eXtended Formatting information for cells, rows, columns and styles.

For more information about this class, see The XF Class.

XFAlignment (class) [#]

A collection of the alignment and similar attributes of an XF record.

For more information about this class, see The XFAlignment Class.

XFBackground (class) [#]

A collection of the background-related attributes of an XF record.

For more information about this class, see The XFBackground Class.

XFBorder (class) [#]

A collection of the border-related attributes of an XF record.

For more information about this class, see The XFBorder Class.

XFProtection (class) [#]

A collection of the protection-related attributes of an XF record.

For more information about this class, see The XFProtection Class.

xldate_as_tuple(xldate, datemode) [#]

Convert an Excel number (presumed to represent a date, a datetime or a time) into -a tuple suitable for feeding to datetime or mx.DateTime constructors.

xldate: -The Excel number
datemode: -0: 1900-based, 1: 1904-based. -
WARNING: when using this function to -interpret the contents of a workbook, you should pass in the Book.datemode -attribute of that workbook. Whether -the workbook has ever been anywhere near a Macintosh is irrelevant. -
Returns:: -Gregorian (year, month, day, hour, minute, nearest_second). -
Special case: if 0.0 <= xldate < 1.0, it is assumed to represent a time; -(0, 0, 0, hour, minute, second) will be returned. -
Note: 1904-01-01 is not regarded as a valid date in the datemode 1 system; its "serial number" -is zero. -
Raises XLDateNegative:: -xldate < 0.00 -
Raises XLDateAmbiguous:: -The 1900 leap-year problem (datemode == 0 and 1.0 <= xldate < 61.0) -
Raises XLDateTooLarge:: -Gregorian year 10000 or later
Raises XLDateBadDatemode:: -datemode arg is neither 0 nor 1
Raises XLDateError:: -Covers the 4 specific errors

xldate_from_date_tuple((year, month, day), datemode) [#]

Convert a date tuple (year, month, day) to an Excel date.

year: -Gregorian year.
month: -1 <= month <= 12 -
day: -1 <= day <= last day of that (year, month) -
datemode: -0: 1900-based, 1: 1904-based.
Raises XLDateAmbiguous:: -The 1900 leap-year problem (datemode == 0 and 1.0 <= xldate < 61.0) -
Raises XLDateBadDatemode:: -datemode arg is neither 0 nor 1
Raises XLDateBadTuple:: -(year, month, day) is too early/late or has invalid component(s)
Raises XLDateError:: -Covers the specific errors

xldate_from_datetime_tuple(datetime_tuple, datemode) [#]

Convert a datetime tuple (year, month, day, hour, minute, second) to an Excel date value. -For more details, refer to other xldate_from_*_tuple functions.

datetime_tuple: -(year, month, day, hour, minute, second)
datemode: -0: 1900-based, 1: 1904-based.

xldate_from_time_tuple((hour, minute, second)) [#]

Convert a time tuple (hour, minute, second) to an Excel "date" value (fraction of a day).

hour: -0 <= hour < 24 -
minute: -0 <= minute < 60 -
second: -0 <= second < 60 -
Raises XLDateBadTuple:: -Out-of-range hour, minute, or second

The BaseObject Class

BaseObject (class) [#]

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)

The Book Class

Book() (class) [#]

Contents of a "workbook". -

WARNING: You don't call this class yourself. You use the Book object that -was returned when you called xlrd.open_workbook("myfile.xls").

biff_version [#]

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 [#]

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'

colour_map [#]

This provides definitions for colour indexes. Please refer to the -above section "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. -
-- New in version 0.6.1. Extracted only if open_workbook(..., formatting_info=True) -

countries [#]

A tuple containing the (telephone system) country code for:
- [0]: the user-interface setting when the file was created.
- [1]: the regional settings.
-Example: (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. -

datemode [#]

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

encoding [#]

The encoding that was derived from the codepage.

font_list [#]

A list of Font class instances, each corresponding to a FONT record. -
-- New in version 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) pyExcelerator, -use this list. -The collection to be used for all visual rendering purposes is format_map. -
-- New in version 0.6.1 -

format_map [#]

The mapping from XF.format_key to Format object. -
-- New in version 0.6.1 -

load_time_stage_1 [#]

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

load_time_stage_2 [#]

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

name_and_scope_map [#]

A mapping from (lower_case_name, scope) to a single Name object. -
-- New in version 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. -
-- New in version 0.6.0 -

name_obj_list [#]

List containing a Name object for each NAME record in the workbook. -
-- New in version 0.6.0 -

nsheets [#]

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

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. -
-- New in version 0.6.1. Extracted only if open_workbook(..., formatting_info=True) -

sheet_by_index(sheetx) [#]

sheetx: -Sheet index in range(nsheets)
Returns:: -An object of the Sheet class

sheet_by_name(sheet_name) [#]

sheet_name: -Name of sheet required
Returns:: -An object of the Sheet class

sheet_loaded(sheet_name_or_index) [#]

sheet_name_or_index: -Name or index of sheet enquired upon
Returns:: -true if sheet is loaded, false otherwise -
-- New in version 0.7.1 -

sheet_names() [#]

Returns:: -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.

sheets() [#]

Returns:: -A list of all sheets in the book. -All sheets not already loaded will be loaded.

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), where:
-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 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 -
-- New in version 0.6.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 -

user_name [#]

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

xf_list [#]

A list of XF class instances, each corresponding to an XF record. -
-- New in version 0.6.1 -

The Cell Class

Cell(ctype, value, xf_index=None) (class) [#]

Contains the data for one cell.

- -

WARNING: You don't call this class yourself. You access Cell objects -via methods of the Sheet object(s) that you found in the Book object that -was returned when you called xlrd.open_workbook("myfile.xls").

Cell objects have three attributes: ctype is an int, value -(which depends on ctype) and xf_index. -If "formatting_info" is not enabled when the workbook is opened, xf_index will be None. -The following table describes the types of cells and how their values -are represented in Python.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Type symbol	Type number	Python value
XL_CELL_EMPTY	0	empty string u''
XL_CELL_TEXT	1	a Unicode string
XL_CELL_NUMBER	2	float
XL_CELL_DATE	3	float
XL_CELL_BOOLEAN	4	int; 1 means TRUE, 0 means FALSE
XL_CELL_ERROR	5	int representing internal Excel codes; for a text representation, -refer to the supplied dictionary error_text_from_code
XL_CELL_BLANK	6	empty string u''. Note: this type will appear only when -open_workbook(..., formatting_info=True) is used.

The Colinfo Class

Colinfo (class) [#]

Width and default formatting information that applies to one or -more columns in a sheet. Derived from COLINFO records. - -

Here is the default hierarchy for width, according to the OOo docs: - -
"""In BIFF3, if a COLINFO record is missing for a column, -the width specified in the record DEFCOLWIDTH is used instead. - -
In BIFF4-BIFF7, the width set in this [COLINFO] record is only used, -if the corresponding bit for this column is cleared in the GCW -record, otherwise the column width set in the DEFCOLWIDTH record -is used (the STANDARDWIDTH record is always ignored in this case [see footnote!]). - -
In BIFF8, if a COLINFO record is missing for a column, -the width specified in the record STANDARDWIDTH is used. -If this [STANDARDWIDTH] record is also missing, -the column width of the record DEFCOLWIDTH is used instead.""" -
- -Footnote: The docs on the GCW record say this: -"""
-If a bit is set, the corresponding column uses the width set in the STANDARDWIDTH -record. If a bit is cleared, the corresponding column uses the width set in the -COLINFO record for this column. -
If a bit is set, and the worksheet does not contain the STANDARDWIDTH record, or if -the bit is cleared, and the worksheet does not contain the COLINFO record, the DEFCOLWIDTH -record of the worksheet will be used instead. -
"""
-At the moment (2007-01-17) xlrd is going with the GCW version of the story. -Reference to the source may be useful: see the computed_column_width(colx) method -of the Sheet class. -
-- New in version 0.6.1 -

bit1_flag [#]

Value of a 1-bit flag whose purpose is unknown -but is often seen set to 1

collapsed [#]

1 = column is collapsed

hidden [#]

1 = column is hidden

outline_level [#]

Outline level of the column, in range(7). -(0 = no outline)

width [#]

Width of the column in 1/256 of the width of the zero character, -using default font (first FONT record in the file).

xf_index [#]

XF index to be used for formatting empty cells.

The EqNeAttrs Class

EqNeAttrs (class) [#]: -
This mixin class exists solely so that Format, Font, and XF.... objects -can be compared by value of their attributes.
-

The Font Class

Font (class) [#]: -
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. -
-- New in version 0.6.1 -
bold [#]: -
1 = Characters are bold. Redundant; see "weight" attribute.
-
character_set [#]: -
Values: 0 = ANSI Latin, 1 = System default, 2 = Symbol, -77 = Apple Roman, -128 = ANSI Japanese Shift-JIS, -129 = ANSI Korean (Hangul), -130 = ANSI Korean (Johab), -134 = ANSI Chinese Simplified GBK, -136 = ANSI Chinese Traditional BIG5, -161 = ANSI Greek, -162 = ANSI Turkish, -163 = ANSI Vietnamese, -177 = ANSI Hebrew, -178 = ANSI Arabic, -186 = ANSI Baltic, -204 = ANSI Cyrillic, -222 = ANSI Thai, -238 = ANSI Latin II (Central European), -255 = OEM Latin I
-
colour_index [#]: -
An explanation of "colour index" is given in the Formatting -section at the start of this document.
-
escapement [#]: -
1 = Superscript, 2 = Subscript.
-
family [#]: -
0 = None (unknown or don't care)
-1 = Roman (variable width, serifed)
-2 = Swiss (variable width, sans-serifed)
-3 = Modern (fixed width, serifed or sans-serifed)
-4 = Script (cursive)
-5 = Decorative (specialised, for example Old English, Fraktur) -
font_index [#]: -
The 0-based index used to refer to this Font() instance. -Note that index 4 is never used; xlrd supplies a dummy place-holder.
-
height [#]: -
Height of the font (in twips). A twip = 1/20 of a point.
-
italic [#]: -
1 = Characters are italic.
-
name [#]: -
The name of the font. Example: u"Arial"
-
outline [#]: -
1 = Font is outline style (Macintosh only)
-
shadow [#]: -
1 = Font is shadow style (Macintosh only)
-
struck_out [#]: -
1 = Characters are struck out.
-
underline_type [#]: -
0 = None
-1 = Single; 0x21 (33) = Single accounting
-2 = Double; 0x22 (34) = Double accounting -
underlined [#]: -
1 = Characters are underlined. Redundant; see "underline_type" attribute.
-
weight [#]: -
Font weight (100-1000). Standard values are 400 for normal text -and 700 for bold text.
-

The Format Class

Format(format_key, ty, format_str) (class) [#]: -
"Number format" information from a FORMAT record. -
-- New in version 0.6.1 -
format_key [#]: -
The key into Book.format_map
-
format_str [#]: -
The format string
-
type [#]: -
A classification that has been inferred from the format string. -Currently, this is used only to distinguish between numbers and dates. -
Values: -
FUN = 0 # unknown -
FDT = 1 # date -
FNU = 2 # number -
FGE = 3 # general -
FTX = 4 # text -

The Name Class

Name (class) [#]

Information relating to a named reference, formula, macro, etc. -
-- New in version 0.6.0 -
-- Name information is not extracted from files older than -Excel 5.0 (Book.biff_version < 50) -

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. -
Returns:: -a tuple (sheet_object, rowxlo, rowxhi, colxlo, colxhi).
Raises XLRDError:: -The name is not a constant absolute reference -to a single area in a single sheet.

binary [#]

0 = Formula definition; 1 = Binary data
No examples have been sighted. -

builtin [#]

0 = User-defined name; 1 = Built-in name -(common examples: Print_Area, Print_Titles; see OOo docs for full list)

cell() [#]

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

Returns:: -An instance of the Cell class.
Raises XLRDError:: -The name is not a constant absolute reference -to a single cell.

complex [#]

0 = Simple formula; 1 = Complex formula (array formula or user defined)
-No examples have been sighted. -

func [#]

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

funcgroup [#]

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

hidden [#]

0 = Visible; 1 = Hidden

macro [#]

0 = Standard name; 1 = Macro name

name [#]

A Unicode string. If builtin, decoded as per OOo docs.

name_index [#]

The index of this object in book.name_obj_list

raw_formula [#]

An 8-bit string.

result [#]

The result of evaluating the formula, if any. -If no formula, or evaluation of the formula encountered problems, -the result is None. Otherwise the result is a single instance of the -Operand class.

scope [#]

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

vbasic [#]

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

The Operand Class

Operand(akind=None, avalue=None, arank=0, atext='?') (class) [#]

Used in evaluating formulas. -The following table describes the kinds and how their values -are represented.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Kind symbol	Kind number	Value representation
oBOOL	3	integer: 0 => False; 1 => True
oERR	4	None, or an int error code (same as XL_CELL_ERROR in the Cell class). -
oMSNG	5	Used by Excel as a placeholder for a missing (not supplied) function -argument. Should not appear as a final formula result. Value is None.
oNUM	2	A float. Note that there is no way of distinguishing dates.
oREF	-1	The value is either None or a non-empty list of -absolute Ref3D instances. -
oREL	-2	The value is None or a non-empty list of -fully or partially relative Ref3D instances. -
oSTRG	1	A Unicode string.
oUNK	0	The kind is unknown or ambiguous. The value is None

kind [#]

oUNK means that the kind of operand is not known unambiguously.

text [#]

The reconstituted text of the original formula. Function names will be -in English irrespective of the original language, which doesn't seem -to be recorded anywhere. The separator is ",", not ";" or whatever else -might be more appropriate for the end-user's locale; patches welcome.

value [#]

None means that the actual value of the operand is a variable -(depends on cell data), not a constant.

The Ref3D Class

Ref3D(atuple) (class) [#]

Represents an absolute or relative 3-dimensional reference to a box -of one or more cells.
--- New in version 0.6.0 -

- -

The coords attribute is a tuple of the form:
-(shtxlo, shtxhi, rowxlo, rowxhi, colxlo, colxhi)
-where 0 <= thingxlo <= thingx < thingxhi.
-Note that it is quite possible to have thingx > nthings; for example -Print_Titles could have colxhi == 256 and/or rowxhi == 65536 -irrespective of how many columns/rows are actually used in the worksheet. -The caller will need to decide how to handle this situation. -Keyword: IndexError :-) -

- -

The components of the coords attribute are also available as individual -attributes: shtxlo, shtxhi, rowxlo, rowxhi, colxlo, and colxhi.

- -

The relflags attribute is a 6-tuple of flags which indicate whether -the corresponding (sheet|row|col)(lo|hi) is relative (1) or absolute (0).
-Note that there is necessarily no information available as to what cell(s) -the reference could possibly be relative to. The caller must decide what if -any use to make of oREL operands. Note also that a partially relative -reference may well be a typo. -For example, define name A1Z10 as $a$1:$z10 (missing $ after z) -while the cursor is on cell Sheet3!A27.
-The resulting Ref3D instance will have coords = (2, 3, 0, -16, 0, 26) -and relflags = (0, 0, 0, 1, 0, 0).
-So far, only one possibility of a sheet-relative component in -a reference has been noticed: a 2D reference located in the "current sheet". -
This will appear as coords = (0, 1, ...) and relflags = (1, 1, ...). -

The Rowinfo Class

Rowinfo (class) [#]: -
Height and default formatting information that applies to a row in a sheet. -Derived from ROW records. -
-- New in version 0.6.1 -
additional_space_above [#]: -
This flag is set, if the upper border of at least one cell in this row -or if the lower border of at least one cell in the row above is -formatted with a thick line style. Thin and medium line styles are not -taken into account.
-
additional_space_below [#]: -
This flag is set, if the lower border of at least one cell in this row -or if the upper border of at least one cell in the row below is -formatted with a medium or thick line style. Thin line styles are not -taken into account.
-
has_default_height [#]: -
0 = Row has custom height; 1 = Row has default height
-
has_default_xf_index [#]: -
1 = the xf_index attribute is usable; 0 = ignore it
-
height [#]: -
Height of the row, in twips. One twip == 1/20 of a point
-
height_mismatch [#]: -
1 = Row height and default font height do not match
-
hidden [#]: -
1 = Row is hidden (manually, or by a filter or outline group)
-
outline_group_starts_ends [#]: -
1 = Outline group starts or ends here (depending on where the -outline buttons are located, see WSBOOL record [TODO ??]), -and is collapsed -
outline_level [#]: -
Outline level of the row
-
xf_index [#]: -
Index to default XF record for empty cells in this row. -Don't use this if has_default_xf_index == 0.
-

The Sheet Class

Sheet(book, position, name, number) (class) [#]

Contains the data for one worksheet.

- -

In the cell access functions, "rowx" is a row index, counting from zero, and "colx" is a -column index, counting from zero. -Negative values for row/column indexes and slice positions are supported in the expected fashion.

- -

For information about cell types and cell values, refer to the documentation of the Cell class.

- -

WARNING: You don't call this class yourself. You access Sheet objects via the Book object that -was returned when you called xlrd.open_workbook("myfile.xls").

cell(rowx, colx) [#]

Cell object in the given row and column.

cell_type(rowx, colx) [#]

Type of the cell in the given row and column. -Refer to the documentation of the Cell class.

cell_value(rowx, colx) [#]

Value of the cell in the given row and column.

cell_xf_index(rowx, colx) [#]

XF index of the cell in the given row and column. -This is an index into Book.xf_list. -
-- New in version 0.6.1 -

col(colx) [#]

Returns a sequence of the Cell objects in the given column.

col_label_ranges [#]

List of address ranges of cells containing column labels. -These are set up in Excel by Insert > Name > Labels > Columns. -
-- New in version 0.6.0 -
How to deconstruct the list: -

-for crange in thesheet.col_label_ranges:
-    rlo, rhi, clo, chi = crange
-    for rx in xrange(rlo, rhi):
-        for cx in xrange(clo, chi):
-            print "Column label at (rowx=%d, colx=%d) is %r" \
-                (rx, cx, thesheet.cell_value(rx, cx))
-

col_slice(colx, start_rowx=0, end_rowx=None) [#]

Returns a slice of the Cell objects in the given column.

col_types(colx, start_rowx=0, end_rowx=None) [#]

Returns a slice of the types of the cells in the given column.

col_values(colx, start_rowx=0, end_rowx=None) [#]

Returns a slice of the values of the cells in the given column.

colinfo_map [#]

The map from a column index to a Colinfo object. Often there is an entry -in COLINFO records for all column indexes in range(257). -Note that xlrd ignores the entry for the non-existent -257th column. On the other hand, there may be no entry for unused columns. -
-- New in version 0.6.1 -

computed_column_width(colx) [#]

Determine column display width. -
-- New in version 0.6.1 -
-

colx: -Index of the queried column, range 0 to 255. -Note that it is possible to find out the width that will be used to display -columns with no cell information e.g. column IV (colx=255).
Returns:: -The column width that will be used for displaying -the given column by Excel, in units of 1/256th of the width of a -standard character (the digit zero in the first font).

default_additional_space_above [#]