bcftbx.simple_xls and bcftbx.Spreadsheet

simple_xls

Simple spreadsheet module intended to provide a nicer programmatic interface to Excel spreadsheet generation.

It is currently built on top of SpreadSheet.py, which itself uses the xlwt, xlrd and xlutils modules. In future the relevant parts may be rewritten to remove the dependence on Spreadsheet.py and call the appropriate xl* classes and functions directly.

Example usage

Start by making a workbook, represented by an XLSWorkBook object:

>>> wb = XLSWorkBook("Test")

Then add worksheets to this:

>>> wb.add_work_sheet('test')
>>> wb.add_work_sheet('data',"My Data")

Worksheets have an id and an optional title. Ids must be unique and can be used to fetch the XLSWorkSheet object that represent the worksheet:

>>> data = wb.worksheet['data']

Cells can be addressed directly using various notations:

>>> data['A1'] = "Column 1"
>>> data['A']['1'] = "Updated value"
>>> data['AZ']['3'] = "Another value"

The extent of the sheet is defined by the outermost populated rows and columns

>>> data.last_column # outermost populated column
>>> data.last_row    # outermost populated row

There are various other methods for returning the next row or column; see the documentation for the XLSWorkSheet class.

Data can be added cell-wise (i.e. referencing individual cells as above), row-wise, column-wise and block-wise.

Column-wise operations include inserting a column (shifting columns above it along one to make space):

>>> data.insert_column('B',data=['hello','goodbye','whatev'])

Append a column (writing data to the first empty column at the end of the sheet):

>>> data.append_column(data=['hello','goodbye','whatev'])

Write data to a column, overwriting any existing values:

>>> data.write_column(data=['hello','goodbye','whatev'])

Data can be specified as a list, text or as a single value which is repeated for each cell (i.e. a “fill” value).

Similar row-wise operations also exist:

>>> data.insert_row(4,data=['Dozy','Beaky','Mick','Titch'])
>>> data.append_row(data=['Dozy','Beaky','Mick','Titch'])
>>> data.write_row(4,data=['Dozy','Beaky','Mick','Titch'])

Block-wise data can be added via a tab and newline-delimited string:

>>> data.insert_block_data("This        is              some
        random
        data")
>>> data.insert_block_data("This        is              some
        MORE    random
        data",
...                        col='M',row=7)

Formulae can be specified by prefixing a ‘=’ symbol to the start of the cell contents, e.g.:

>>> data['A3'] = '=A1+A2'

‘?’ and ‘#’ are special characters that can be used to indicate ‘current row’ and ‘current column’ respectively, e.g.:

>>> data.fill_column('A','=B?+C?') # evaluates to 'B1+C1' (A1), 'B2+C2' (A2) etc

Styling and formatting information can be associated with a cell, either when adding column, row or block data or by using the ‘set_style’ method. In each case the styling information is passed via an XLSStyle object, e.g.

>>> data.set_style(XLSStyle(number_format=NumberFormats.PERCENTAGE),'A3')

The workbook can be saved to file:

>>> wb.save_as_xls('test.xls')

Alternatively the contents of a sheet (or a subset) can be rendered as text:

>>> data.render_as_text(include_columns_and_rows=True,
...                     eval_formulae=True,
...                     include_styles=True)
>>> data.render_as_text(start='B1',end='C6',include_columns_and_rows=True)
class bcftbx.simple_xls.CellIndex(idx)

Convenience class for handling XLS-style cell indices

The CellIndex class provides a way of handling XLS-style cell indices i.e. ‘A1’, ‘BZ112’ etc.

Given a putative cell index it extracts the column and row which can then be accessed via the ‘column’ and ‘row’ attributes respectively.

The ‘is_full’ property reports whether the supplied index is actually a ‘full’ index with both column and row specifiers. If it is just a column or just a row then only the appropriate ‘column’ or ‘row’ attributes will be set.

property is_full

Return True if index has both column and row information

class bcftbx.simple_xls.ColumnRange(i, j=None, include_end=True, reverse=False)

Iterator for a range of column indices

Range-style iterator for iterating over alphabetical column indices, e.g.

>>> for c in ColumnRange('A','Z'):
...   print(c)
next()

Implements Iterator subclass ‘next’ method (Python 2 only)

class bcftbx.simple_xls.Limits

Limits for XLS files (kept for backwards compatibility)

class bcftbx.simple_xls.XLSColumn(column_index, parent=None)

Class representing a column in a XLSWorkSheet

An XLSColumn object provides access to data in a column from a XLSWorkSheet object. Typically one can be returned by doing something like:

>>> colA = ws['A']

and individual cell values then accessed by row number alone, e.g.:

>>> value = colA['1']
>>> colA['2'] = "New value"
full_index(row)

Return the full index for a cell in the column

Given a row index, returns the index of the cell that this addresses within the column (e.g. if the column is ‘A’ then row 2 addresses cell ‘A2’).

class bcftbx.simple_xls.XLSLimits

Limits for XLS files

class bcftbx.simple_xls.XLSStyle(bold=False, color=None, bgcolor=None, wrap=False, border=None, number_format=None, font_size=None, centre=False, shrink_to_fit=False)

Class representing a set of styling and formatting data

An XLSStyle object represents a collection of data used for styling and formatting cell values on output to an Excel file.

The style attributes can be set on instantiation, or queried and modified afterwards.

The attributes are:

bold: whether text is bold or not (boolean) color: text color (name) bgcolor: background color (name) wrap: whether text in a cell should wrap (boolean) border: style of cell border (thick, medium, thin etc) number_format: a format code from the NumbersFormat class font_size: font size in points (integer) centre: whether text is centred in the cell (boolean) shrink_to_fit: whether to shrink cell to fit the contents.

The ‘name’ property can be used to generate a name for the style based on the attributes that have been set, for example:

>>> XLSStyle(bold=true).name
... '__bold__'
property excel_number_format

Return an Excel-style equivalent of the stored number format

Returns an Excel-style number format, or None if the format isn’t set or is unrecognised.

property name

Return a name based on the attributes

style(item)

Wrap ‘item’ with <style…>…</style> tags

Given a string (or object that can be rendered as a string) return the string representation surrounded by <style…> </style> tags, where the tag attributes describe the style information stored in the XLSStyle object:

font=bold color=(color) bgcolor=(color) wrap border=(border) number_format=(format) font_size=(size) centre shrink_to_fit

class bcftbx.simple_xls.XLSWorkBook(title=None)

Class for creating an Excel (xls) spreadsheet

An XLSWorkBook instance provides an interface to creating an Excel spreadsheet.

It consists of a collection of XLSWorkSheet objects, each of which represents a sheet in the workbook.

Sheets are created and appended using the add_work_sheet method:

>>> xls = XLSWorkBook()
>>> sheet = xls('example')

Sheets are kept in the ‘worksheet’ property and can be acquired by name:

>>> sheet = xls.worksheet['example']

Once the worksheet(s) have been populated an XLS file can be created using the ‘save_as_xls’ method:

>>> xls.save_as_xls('example.xls')
add_work_sheet(name, title=None)

Create and append a new worksheet

Creates a new XLSWorkSheet object and appends it to the workbook.

Parameters:
  • name – unique name for the worksheet

  • title – optional, title for the worksheet - defaults to the name.

Returns:

New XLSWorkSheet object.

save_as_xls(filen)

Output the workbook contents to an Excel-format file

Parameters:

filen – name of the file to write the workbook to.

save_as_xlsx(filen)

Output the workbook contents to an XLSX-format file

Parameters:

filen – name of the file to write the workbook to.

class bcftbx.simple_xls.XLSWorkSheet(title)

Class for creating sheets within an XLS workbook.

XLSWorkSheet objects represent a sheet within an Excel workbook.

Cells are addressed within the sheet using Excel notation i.e. <column><row> (columns start at index ‘A’ and rows at ‘1’, examples are ‘A1’ or ‘D19’):

>>> ws = XLSWorkSheet('example')
>>> ws['A1'] = 'some data'
>>> value = ws['A1']

If there is no data stored for the cell then ‘None’ is returned. Any cell can addressed without errors.

Data can also be added column-wise, row-wise or as a “block” of tab- and new-line delimited data:

>>> ws.insert_column_data('B',[1,2,3])
>>> ws.insert_row_data(4,['x','y','z'])
>>> ws.insert_block_data("This\tis\nthe\tdata")

A column can be “filled” with a single repeating value:

>>> ws.fill_column('D','single value')

The extent of the sheet can be determined from the ‘last_column’ and last_row’ properties; the ‘next_column’ and ‘next_row’ properties report the next empty column and row respectively.

Cells can contain Excel-style formula by adding an equals sign to the start of the value. Typically formulae reference other cells and perform mathematical operations on them, e.g.:

>>> ws['E11'] = "=A1+A2"

Wildcard characters can be used which will be automatically translated into the cell column (‘#’) or row (‘?’), for example:

>>> ws['F46'] = "=#47+#48"

will be transformed to “=F47+F48”.

Styles can be applied to cells, using either the ‘set_style’ method or via the ‘style’ argument of some methods, to associate an XLSStyle object. Associated XLSStyle objects can be retrieved using the ‘get_style’ method.

The value of an individual cell can be ‘rendered’ for output using the ‘render_cell’ method:

>>> print(ws.render_cell('F46'))

All or part of the sheet can be rendered as a tab- and newline-delimited string by using the ‘render_as_text’ method:

>>> print(ws.render_as_text())
append_column(data=None, text=None, fill=None, from_row=None, style=None)

Create a new column at the end of the sheet

Appends a new column at the end of the worksheet i.e. in the first available empty column.

By default the appended column is empty, however data can be specified to populate the column.

Parameters:
  • data – optional, list of data items to populate the inserted column

  • text – optional, tab-delimited string of text to be used to populate the inserted column

  • fill – optional, single data item to be repeated to fill the inserted column

  • from_row – optional, if specified then inserted column is populated from that row onwards

  • style – optional, an XLSStyle object to associate with the data being inserted

Returns:

The index of the appended column.

append_row(data=None, text=None, fill=None, from_column=None, style=None)

Create a new row at the end of the sheet

Appends a new row at the end of the worksheet i.e. in the first available empty row.

By default the appended row is empty, however data can be specified to populate the row.

Parameters:
  • data – optional, list of data items to populate the inserted row

  • text – optional, newline-delimited string of text to be used to populate the inserted row

  • fill – optional, single data item to be repeated to fill the inserted row

  • from_row – optional, if specified then inserted row is populated from that column onwards

  • style – optional, an XLSStyle object to associate with the data being inserted

Returns:

The index of the inserted row.

column_is_empty(col)

Determine whether a column is empty

Returns False if any cells in the column are populated, otherwise returns True.

columnof(s, row=1)

Return column index for cell which matches string

Return index of first column where the content matches the specified string ‘s’.

Parameters:
  • s – string to search for

  • row – row to search in (defaults to 1)

Returns:

Column index of first matching cell. Raises LookUpError if no match is found.

fill_column(column, item, start=None, end=None, style=None)

Fill a column with a single repeated data item

A single data item is inserted into all rows in the specified column which have at least one data item already in any column in the worksheet. A different range of rows can be specified via the ‘start’ and ‘end’ arguments.

* THIS METHOD IS DEPRECATED *

Consider using insert_column, append_column or write_data.

Parameters:
  • column – index of column to insert the item into (e.g. ‘A’,’MZ’)

  • item – data item to be repeated

  • start – (optional) first row to insert data into

  • end – (optional) last row to insert data into

  • style – (optional) XLSStyle object to be associated with each cell that has data inserted into it

get_style(idx)

Return the style information associated with a cell

Returns an XLSStyle object associated with the specific cell.

If no style was previously associated then return a new XLSStyle object.

Parameters:

idx – cell index e.g ‘A1’

Returns:

XLSStyle object.

insert_block_data(data, col=None, row=None, style=None)

Insert data items from a block of text

Data items are supplied via a block of tab- and newline-delimited text. Each tab-delimited item is inserted into the next column in a row; newlines indicate that subsequent items are inserted into the next row.

By default items are inserted starting from cell ‘A1’; a different starting cell can be explicitly specified via the ‘col’ and ‘row’ arguments.

Parameters:
  • data – block of tab- and newline-delimited data

  • col – (optional) first column to insert data into

  • row – (optional) first row to insert data into

  • style – (optional) XLSStyle object to be associated with each cell that has data inserted into it

insert_column(position, data=None, text=None, fill=None, from_row=None, style=None)

Create a new column at the specified column position

Inserts a new column at the specified column position, pushing up the column currently at that position plus all higher positioned columns.

By default the inserted column is empty, however data can be specified to populate the column.

Parameters:
  • position – column index specifying position to insert the column at

  • data – optional, list of data items to populate the inserted column

  • text – optional, tab-delimited string of text to be used to populate the inserted column

  • fill – optional, single data item to be repeated to fill the inserted column

  • from_row – optional, if specified then inserted column is populated from that row onwards

  • style – optional, an XLSStyle object to associate with the data being inserted

Returns:

The index of the inserted column.

insert_column_data(col, data, start=None, style=None)

Insert list of data into a column

Data items are supplied as a list, with each item in the list being inserted into the next row in the column.

By default items are inserted starting from row 1, unless a starting row is explicitly specified via the ‘start’ argument.

* THIS METHOD IS DEPRECATED *

Consider using insert_column, append_column or write_data.

Parameters:
  • col – index of column to insert the data into (e.g. ‘A’,’MZ’)

  • data – list of data items

  • start – (optional) first row to insert data into

  • style – (optional) XLSStyle object to be associated with each cell that has data inserted into it

insert_row(position, data=None, text=None, fill=None, from_column=None, style=None)

Create a new row at the specified row position

Inserts a new row at the specified row position, pushing up the row currently at that position plus all higher positioned row.

By default the inserted row is empty, however data can be specified to populate the column.

Parameters:
  • position – row index specifying position to insert the row at

  • data – optional, list of data items to populate the inserted row

  • text – optional, newline-delimited string of text to be used to populate the inserted row

  • fill – optional, single data item to be repeated to fill the inserted row

  • from_row – optional, if specified then inserted row is populated from that column onwards

  • style – optional, an XLSStyle object to associate with the data being inserted

Returns:

The index of the inserted row.

insert_row_data(row, data, start=None, style=None)

Insert list of data into a row

Data items are supplied as a list, with each item in the list being inserted into the next column in the row.

By default items are inserted starting from column ‘A’, unless a starting column is explicitly specified via the ‘start’ argument.

* THIS METHOD IS DEPRECATED *

Consider using insert_row, append_row or write_row.

Parameters:
  • row – index of row to insert the data into (e.g. 1, 112)

  • data – list of data items

  • start – (optional) first column to insert data into

  • style – (optional) XLSStyle object to be associated with each cell that has data inserted into it

property last_column

Return index of last column with data

property last_row

Return index of last row with data

property next_column

Index of first empty column after highest index with data

property next_row

Index of first empty row after highest index with data

render_as_text(include_columns_and_rows=False, include_styles=False, eval_formulae=False, apply_format=False, start=None, end=None)

Text representation of all or part of the worksheet

All or part of the sheet can be rendered as a tab- and newline-delimited string.

Parameters:
  • include_columns_and_rows – (optional) if True then also output a header row of column indices, and a column of row indices (default is to not output columns and rows).

  • include_styles – (optional) if True then also render the styling information associated with the cell (default is not to apply styling).

  • apply_format – (optional) if True then format numbers according to the formatting information associated with the cell (default is not to apply formatting).

  • eval_formulae – (optional) if True then if the cell contains a formula, attempt to evaluate it and return the result. Otherwise return the formula itself (this is the default)

  • start – (optional) specify the top-lefthand most cell index to start rendering from (default is ‘A1’).

  • end – (optional) specify the bottom-righthand most cell index to finish rendering at (default is the cell corresponding to the highest column and row indices. Note that this cell may be empty.)

Returns:

String containing the rendered sheet or sheet subset, with items within a row separated by tabs, and rows separated by newlines.

render_cell(idx, eval_formulae=False, apply_format=False)

Text representation of value stored in a cell

Create a text representation of a cell’s contents. If the cell contains a formula then ‘?’s will be replaced with the row index and ‘#’s with the column index. Optionally the formula can also be evaluated, and any style information associated with the cell can also be rendered.

Parameters:
  • idx – cell index e.g. ‘A1’

  • eval_formulae – (optional) if True then if the cell contains a formula, attempt to evaluate it and return the result. Otherwise return the formula itself (this is the default)

  • apply_format – (optional) if True then format numbers according to the formatting information associated with the cell (default is not to apply formatting).

Returns:

String representing the cell contents.

row_is_empty(row)

Determine whether a row is empty

Returns False if any cells in the row are populated, otherwise returns True.

rowof(s, column='A')

Return row index for cell which matches string

Return index of first row where the content matches the specified string ‘s’.

Parameters:
  • s – string to search for

  • column – column to search in (defaults to ‘A’)

Returns:

Row index of first matching cell. Raises LookUpError if no match is found.

set_style(cell_style, start, end=None)

Associate style information with one or more cells

Associates a specified XLSStyle object with a single cell, or with a range of cells (if a second cell index is supplied).

The style associated with a cell can be fetched using the ‘get_style’ method.

Parameters:
  • cell_style – XLSStyle object

  • start – cell index e.g. ‘A1’

  • end – (optional) second cell index; together with ‘start’ this defines a range of cells to associate the style with.

write_column(col, data=None, text=None, fill=None, from_row=None, style=None)

Write data to rows in a column

Data can be specified as a list, a newline-delimited string, or as a single repeated data item.

Parameters:
  • data – optional, list of data items to populate the inserted column

  • text – optional, newline-delimited string of text to be used to populate the inserted column

  • fill – optional, single data item to be repeated to fill the inserted column

  • from_row – optional, if specified then inserted column is populated from that row onwards

  • style – optional, an XLSStyle object to associate with the data being inserted

write_row(row, data=None, text=None, fill=None, from_column=None, style=None)

Write data to rows in a column

Data can be specified as a list, a tab-delimited string, or as a single repeated data item.

Parameters:
  • row – row index specifying which row

  • data – optional, list of data items to populate the inserted row

  • text – optional, tab-delimited string of text to be used to populate the inserted row

  • from_column – optional, if specified then inserted row is populated from that column onwards

  • style – optional, an XLSStyle object to associate with the data being inserted

class bcftbx.simple_xls.XLSXLimits

Limits for XLSX files

bcftbx.simple_xls.cell(col, row)

Return XLS cell index for column and row

E.g. cell(‘A’,3) returns ‘A3’

bcftbx.simple_xls.cmp_column_indices(x, y)

Comparision function for column indices

x and y are XLS-style column indices e.g. ‘A’, ‘B’, ‘AA’ etc.

Returns -1 if x is a column index less than y, 1 if it is greater than y, and 0 if it’s equal.

bcftbx.simple_xls.column_index_to_integer(col)

Convert XLS-style column index into equivalent integer

Given a column index e.g. ‘A’, ‘BZ’ etc, converts it to the integer equivalent using zero-based counting system (so ‘A’ is equivalent to zero, ‘B’ to 1 etc).

bcftbx.simple_xls.column_integer_to_index(idx)

Convert integer column index to XLS-style equivalent

Given an integer index, converts it to the XLS-style equivalent e.g. ‘A’, ‘BZ’ etc, using a zero-based counting system (so zero is equivalent to ‘A’, 1 to ‘B’ etc).

bcftbx.simple_xls.convert_to_number(s)

Convert a number to float or int as appropriate

Raises ValueError if neither conversion is possible.

bcftbx.simple_xls.eval_formula(item, worksheet)

Evaluate a formula using the contents of a worksheet

Given an item, attempts an Excel-style evaluation.

If the item doesn’t start with ‘=’ then it is returned as-is. Otherwise the function attempts to evaluate the formula, including looking up (and if necessary also evaluating) the contents of any cells that are referenced.

Note

The implementation of the evaluation is very simplistic and cannot handle complex formulae or functions, it can only deal with basic mathematical operations (i.e. +, -, * and /)

bcftbx.simple_xls.format_value(value, number_format=None)

Format a cell value based on the specified number format

bcftbx.simple_xls.incr_col(col, incr=1)

Return column index incremented by specific number of positions

Parameters:
  • col – index of column to be incremented

  • incr – optional, number of cells to shift by. Can be negative to go backwards. Defaults to 1 i.e. next column along.

bcftbx.simple_xls.is_float(s)

Test if a number is a float

bcftbx.simple_xls.is_int(s)

Test if a number is an integer

Spreadsheet

Provides classes for writing data to an Excel spreadsheet, using the 3rd party modules xlrd, xlwt and xlutils.

The basic classes are ‘Workbook’ (representing an XLS spreadsheet) and ‘Worksheet’ (representing a sheet within a workbook). There is also a ‘Spreadsheet’ class which is built on top of the other two classes and offers a simplified interface to writing line-by-line XLS spreadsheets.

Simple usage examples

  1. Writing a new XLS spreadsheet using the Workbook class

>>> wb = Workbook()
>>> ws = wb.addSheet('test1')
>>> ws.addText("Hello   Goodbye
Goodbye Hello")
>>> wb.save('test2.xls')
  1. Appending to an existing XLS spreadsheet using the Workbook class

>>> wb = Workbook('test2.xls')
>>> ws = wb.getSheet('test1')
>>> ws.addText("Some more data for you")
>>> ws = wb.addSheet('test2')
>>> ws.addText("<style font=bold bgcolor=gray25>Hahahah</style>")
>>> wb.save('test3.xls')
  1. Creating or appending to an XLS spreadsheet using the Spreadsheet class

>>> wb = Spreadsheet('test.xls','test')
>>> wb.addTitleRow(['File','Total reads','Unmapped reads'])
>>> wb.addEmptyRow()
>>> wb.addRow(['DR_1',875897,713425])
>>> wb.write()

Module constants

MAX_LEN_WORKSHEET_TITLE: maximum length allowed by xlwt for worksheet titles MAX_LEN_WORKSHEET_CELL_VALUE: maximum number of characters allowed for cell value MAX_NUMBER_ROWS_PER_WORKSHEET: maximum number of rows allowed per worksheet by xlwt

Dependencies

The Spreadsheet module depends on the xlwt, xlrd and xlutils libraries which can be found at:

Note that xlutils also needs functools: http://pypi.python.org/pypi/functools

but if you’re using Python<2.5 then you need a backported version of functools, try:

https://github.com/dln/pycassa/blob/90736f8146c1cac8287f66e8c8b64cb80e011513/pycassa/py25_functools.py

class bcftbx.Spreadsheet.Spreadsheet(name, title)

Class for creating and writing a spreadsheet.

This creates a very simple single-sheet workbook.

addEmptyRow(color=None)

Add an empty row to the spreadsheet.

Inserts an empty row into the next position in the spreadsheet.

Parameters:

color – optional background color for the empty row

Returns:

Integer index of (empty) row just written

addRow(data, set_widths=False, bold=False, wrap=False, bg_color='')

Add a row of data to the spreadsheet.

Parameters:
  • data – list of data items to be added.

  • set_widths – (optional) Boolean; if True then set the column width to the length of the cell contents for each cell in the new row

  • bold – (optional) use bold font for cells

  • wrap – (optional) wrap the cell content

  • bg_color – (optional) set the background color for the cell

Returns:

Integer index of row just written

addTitleRow(headers)

Add a title row to the spreadsheet.

The title row will have the font style set to bold for all cells.

Parameters:

headers – list of titles to be added.

Returns:

Integer index of row just written

write()

Write the spreadsheet to file.

class bcftbx.Spreadsheet.Styles

Class for creating and caching EasyXfStyle objects.

XLS files have a limit of 4,000 styles, so cache and reuse EasyXfStyle objects to avoid exceeding this limit.

getXfStyle(bold=False, wrap=False, color=None, bg_color=None, border_style=None, num_format_str=None, font_size=None, centre=False, shrink_to_fit=False)

Return EasyXf object to apply styles to spreadsheet cells.

Parameters:
  • bold – indicate whether font should be bold face

  • wrap – indicate whether text should wrap in the cell

  • color – set text colo(u)r

  • bg_color – set colo(u)r for cell background.

  • border_style – set line type for cell borders (thin, medium, thick, etc)

  • font_size – font size (in points)

  • centre – centre the cell content horizontally

  • shrink_to_fit – shrink cell to fit contents

Note that colours must be a valid name as recognised by xlwt.

class bcftbx.Spreadsheet.Workbook(xls_name='')

Class for writing data to an XLS spreadsheet.

A Workbook represents an XLS spreadsheet, which conists of sheets (represented by Worksheet instances).

addSheet(title, xlrd_sheet=None, xlrd_index=None)

Add a new sheet to the spreadsheet.

Parameters:
  • title – title for the sheet

  • xlrd_sheet – (optional) an xlrd sheet from an existing XLS workbook.

getSheet(title)

Retrieve a sheet from the spreadsheet.

save(xls_name)

Finish adding data and write the spreadsheet to disk.

Note that for a spreadsheet based on an existing XLS file, this doesn’t have to be the same name.

Parameters:

xls_name – the file name to write the spreadsheet to. Note that if a file already exists with this name then it will be overwritten.

class bcftbx.Spreadsheet.Worksheet(workbook, title, xlrd_index=None, xlrd_sheet=None)

Class for writing to a sheet in an XLS spreadsheet.

A Worksheet object represents a sheet in an XLS spreadsheet.

Adding data

Data can be inserted into the worksheet in a variety of ways:

  • addTabData: a Python list of tab-delimited lines; each line forms a line in the output XLS, with each field forming a column.

  • addText: a string representing arbitrary text, with newlines delimiting lines and tabs (if any) in each line delimiting fields.

Each can be called multiple times in any order on the same spreadsheet before it is saved, and the data will be appended.

For new Worksheet objects (i.e. those which weren’t read from a pre-existing XLS file), it is also possible to insert new columns:

  • insertColumn: if a single value is specified then all columns are filled with that value; alternatively a list of values can be supplied which are written one-per-row.

Formulae

Formulae can be specified using a variation on Excel’s ‘=’ notation, e.g.

=A1+B2

adds the values from cells A1 and B2 in the final spreadsheet.

Formulae are written directly as supplied unless they contain special characters ‘?’ (indicates the current line number) or ‘#’ (indicates the current column).

Using ‘?’ allows simple row-wise formulae to be added, e.g.

=A?+B?

will be converted to substitute the row index (e.g. ‘=A1+B1’ for row 1, ‘=A2+B2’ for row 2 etc).

Using ‘#’ allows simple column-wise formulae to be added, e.g.

=#1-#2

will be converted to substitue the column id (e.g. ‘=A1-A2’ for column A, ‘=B1-B2’ for column B etc).

Note that the substitution occurs when the spreadsheet is saved.

Styles

Individual items can have basic styles applied to them by wrapping them in <style …>…</style> tags. Within the leading style tag the following attributes can be specified:

font=bold (sets bold face) color=<color> (sets the text colour) bgcolor=<color> (sets the background colour) border=<style> (sets the cell border style to ‘thin’, ‘medium’, ‘thick’ etc) wrap (specifies that text should wrap) number_format=<format_string> (specifies how to display numbers, see below) font_height=<height> (sets font size in points) centre (specifies that text should be centred) shrink_to_fit (specifies that cells should shrink to fit their contents)

For example <style font=bold bgcolor=gray25>…</style>

Note that styles can also be applied to formulae.

Number display formats

The ‘number_format’ style attribute allows the calling program to specify how numbers should be displayed, for example:

number_format=0.00 (displays values to 2 decimal places) number_format=0.0% (displays values as percentages to 1 decimal place) number_format=#,### (displays values with , as the delimiter for thousands)

Internal representation

The spreadsheet data is held internally as a list of rows, with each row represented by a tab-delimited string.

addTabData(rows)

Write a list of tab-delimited data rows to the sheet.

Given a list of rows with tab-separated data items, append the data to the worksheet.

Parameters:

data – Python list representing rows of tab-separated data items

addText(text)

Append and populate rows from text.

Given some arbitrary text as a string, the data it contains will be appended to the worksheet using newlines to indicate multiple rows and tabs to delimit data items.

This method is useful for turning tab-delimited data read from a CSV-type file into a spreadsheet.

Parameters:

text – a string representing the data to add: rows are delimited by newlines, and items by tabs

column_id_from_index(i)

Get XLS column id from index of column

cindex is the zero-based column index (an integer); this method returns the matching XLS column identifier (i.e. ‘A’, ‘B’, ‘AA’, ‘BA’ etc).

freezePanes(row=None, column=None)

Split panes and mark as frozen

‘row’ and ‘column’ are integer indices specifying the cell which defines the pane to be marked as frozen

getColumnId(name)

Lookup XLS column id from name of column.

If there is no data, or if the name isn’t in the header row of the data, then an exception is raised.

Returns the column identifier (i.e. ‘A’, ‘B’ etc) for the column with the matching name.

insertColumn(position, insert_items=None, title=None)

Insert a new column into the spreadsheet.

This inserts a new column into each row of data, at the specified positional index (starting from 0).

Note: at present columns can only be inserted into worksheets that have been created from scratch via Worksheet class (i.e. cannot insert into an existing worksheet read in from a file).

Parameters:
  • position – positional index for the column to be inserted at (0=A, 1=B etc)

  • title – (optional) value to be written to the first row (i.e. a column title)

  • insert_items – value(s) to be inserted; either a single item, or a list of items. Each item can be blank, a constant value, or a formula.

save()

Write the new data to the spreadsheet.

setCellValue(row, col, value)

Set the value of a cell

Given row and column coordinates (using integer indices starting from zero for both), replace the existing value with a new one.

The new value can include style information.

Parameters:
  • row – integer row index (starting at zero)

  • col – integer column index (starting at zero, i.e. 0=A, 1=B etc)

  • value – new value to be written into the cell