rusty.exceltable – ExcelTable directive

Platforms: Unix, Windows

New in version 0.3.0.

Note

The module depends on xlrd -module. Install it using setuptools:

sudo easy_install xlrd

Usage

Define exceltable -directive into your document. The path to document is given with file option, and it is relative to RST -document path. The directive argument is reserved for the optional table caption.

Show part of the excel -document as a table within document:

.. exceltable:: caption
   :file: path/to/document.xls
   :header: 1
   :selection: A1:B2
   :sheet: 1


See further information about the possible parameters from documentation.

Following options and arguments are available. Directive has no content:

caption (optional argument)

Optional table can be provided next to directive definition. If caption is not provided, no caption is set for the table.

.. exceltable:: Caption for the table
   :file: document.xls

file (required)

Relative path (based on document) to excel -document. Compulsory option. Use forward slash also in Windows environments.

.. exceltable::
   :file: path/to/document.xls

selection (optional)

Selection defines from and to the selection reaches. If value is not defined, the whole data from sheet is taken into table. Following definitons are supported:

• Complete name selection: A1:B2
• Starting name selection: C4:
• Ending name selection: :C4 (selecting all the cells til C4)
• Numeric selection: 0,0:2,2 (indexing start from 0 and first value denotes the column, next row)

Note

• If the selection is bigger than the actual data, the biggest possible field (row and/or column) is taken

• On the numeric selection, the order of values is: colindex,rowindex, making the complete selection to be:

  start-c-indx,start-r-indx:end-c-indx,end-r-indx
  

sheet (optional)

Defines the name or index number of the sheet. The index value is numeric and it starts from zero (0). The first sheet is also the default value if option is not defined. Examples:

.. exceltable::
   :file: document.xls
   :sheet: SheetName

.. exceltable::
   :file: document.xls
   :sheet: 0

header (optional)

Header option can used either for providing the header fields:

.. exceltable::
   :header: Name1, Name2, Name3
   :file: document.xls

or as a numeric value, it defines the number of rows considerer header fields in the data:

.. exceltable::
   :header: 1
   :file: document.xls

The default value is 0, meaning no header is generated/considered to be found from data

widths

By default, the column widths are taken from the content (excel sheet): Directive counts relative sizes for the columns. However, it is also possible to define custom widths for the table:

.. exceltable:: Automatic column widths
   :file: document.xls
   :header: A,B,C

.. exceltable:: Manual column widths
   :file: document.xls
   :header: A,B,C
   :widths: 20,20,60

Note

When defining the widths manually, remember following:

• Separate the widths with comma (,)
• The number of width values must match with the columns
• The sum of the widths should be: 100

Example

This section shows few examples how the directive can be used and what are the options with it. For a reference, see source Excel -document used with the examples .

Directive definition:

.. exceltable:: Cartoon listing
   :file: example/cartoons.xls
   :header: 1

Output of the processed document:

Cartoon listing

Title	Author	Since	Added
Garfield	Jim Davis	1978	2009-06-21
Get Fuzzy	Darby Conley	1999	2009-06-21
The Incredible Hulk	Stan Lee and Larry Lieber	1979-1982	2009-06-21

Selection can be limited using selection option, we can take the sub-set of the data:

.. exceltable:: Cartoon listing (subset)
   :file: example/cartoons.xls
   :header: 1
   :selection: A1:B3

.. exceltable:: Only entry dates
   :file: example/cartoons.xls
   :header: 1
   :selection: D1:

Output of the processed document:

Cartoon listing

Title	Author
Garfield	Jim Davis
Get Fuzzy	Darby Conley

Only entry dates

Added
2009-06-21
2009-06-21
2009-06-21

The sheet can be selected by using sheet -option. The value can be either the name of the sheet or the numeric index of the sheet, starting from zero (0,1,2...):

.. exceltable:: Sheet example
   :file: example/cartoons.xls
   :sheet: 1
   :selection: B2:C3

Output of the processed document:

Sheet example

A	B
C	D

Module in detail

This section provides some further information about internals of the module:

XMLTableDirective implements the exceltable -directive.

class rusty.exceltable.ExcelTableDirective(name, arguments, options, content, lineno, content_offset, block_text, state, state_machine)

ExcelTableDirective implements the directive. Directive allows to create RST tables from the contents of the Excel sheet. The functionality is very similar to csv-table (docutils) and xmltable (rusty.xmltable).

Example of the directive:

.. exceltable::
   :file: path/to/document.xls
   :header: 1

class rusty.exceltable.ExcelTable(fobj, encoding='utf-8')

Class generates the list based table from the given excel-document, suitable for the directive.

Class also implements the custom query format, is to use for the directive.:

>>> import os
>>> from rusty import exceltable
>>>
>>> fo = open(os.path.join(os.path.dirname(exceltable.__file__),'../doc/example/cartoons.xls'), 'r+b')
>>> et = exceltable.ExcelTable(fo)
>>> table = et.create_table(fromcell='A1', tocell='C4')

ExcelTable.create_table(fromcell=None, tocell=None, nheader=0, sheet=0)

Creates a table (as a list) based on given query and columns

fromcell:

The index of the cell where to begin. The default is from the beginning of the data set (0,0).

tocell:

The index of the cell where to end the selection. Default is in the end of the data set.

nheader:

Number of lines which are considered as a header lines. Normally, the value is 0 (default) or 1.

sheet:

Name or index of the sheet as string/unicode. The index starts from the 0 and is the default value. If numeric value is given, provide it in format:

et.create_table(fromcell='A1', tocell='B2', sheet='2')