2.1.1.5. wc_utils.workbook package¶

2.1.1.5.2. wc_utils.workbook.core module¶

Excel utilities

Author: Jonathan Karr 2016-11-28 2016-2018, Karr Lab MIT
class `wc_utils.workbook.core.``CellDifference`[source]

Bases: `str`

Difference between values of cells

class `wc_utils.workbook.core.``Formula`(formula, value=None)[source]

Bases: `object`

Formula for a cell

`formula`[source]

formula

Type: `str`
`value`[source]

value

Type: `object`
`__eq__`(other)[source]

Determine the semantic equality of two formula

Parameters: other (`Formula`) – other formula
class `wc_utils.workbook.core.``Row`[source]

Bases: `list`

Represents a row in a table of data

`__eq__`(other)[source]

Compare rows

Parameters: other (`Row`) – other row True if rows are semantically equal `bool`
`__getitem__`(i_cell)[source]

Get a cell or a range of cells

Parameters: i_cell (`int` of `slice`) – cell index or range of cell indices cell or range of cells `object` or `Row`
`__ne__`(other)[source]

Compare rows

Parameters: other (`Row`) – other row True if rows are semantically unequal `bool`
`cell_difference`(cell_self, cell_other)[source]

Get difference between cells

Parameters: cell_self (`object`) – self cell cell_other (`object`) – other cell difference `CellDifference`
`difference`(other)[source]

Get difference with another row

Parameters: other (`Row`) – other row dictionary of differences `RowDifference` `ValueError` – if other is not an instance of Row
class `wc_utils.workbook.core.``RowDifference`[source]

Bases: `collections.OrderedDict`

Difference between values of rows

`__str__`()[source]

Get string representation

Returns: string representation `str`
class `wc_utils.workbook.core.``Workbook`[source]

Bases: `collections.OrderedDict`

Represents an Excel workbook

`__eq__`(other)[source]

Compare two workbooks

Parameters: other (`Workbook`) – other workbook true if workbooks are semantically equal `bool`
`__ne__`(other)[source]

Compare two workbooks

Parameters: other (`Workbook`) – other workbook true if workbooks are semantically unequal `bool`
`difference`(other)[source]

Get difference with another workbook

Parameters: other (`Workbook`) – other workbook dictionary of differences, grouped by worksheet `dict` `ValueError` – if other is not an instance of workbook
class `wc_utils.workbook.core.``WorkbookDifference`[source]

Bases: `dict`

Difference between values of workbook

`__str__`()[source]

Get string representation

Returns: string representation `str`
class `wc_utils.workbook.core.``Worksheet`[source]

Bases: `list`

Represents a table of data, such as an Excel worksheet or a csv/tsv file

`__eq__`(other)[source]

Compare two worksheets

Parameters: other (`Worksheet`) – other worksheet True if worksheets are semantically equal `bool`
`__getitem__`(i_row)[source]

Get a row or a range of rows

Parameters: i_row (`int` of `slice`) – row index or range of row indices row or range of rows `Row` or `Worksheet`
`__ne__`(other)[source]

Compare two worksheets

Parameters: other (`Worksheet`) – other worksheet True if worksheets are semantically unequal `bool`
`difference`(other)[source]

Get difference with another worksheet

Parameters: other (`Worksheet`) – other worksheet dictionary of differences, grouped by row `WorksheeDifference` `ValueError` – if other is not an instance of Worksheet
`remove_empty_final_cols`()[source]

Remove empty final columns

`remove_empty_final_rows`()[source]

Remove empty final rows

class `wc_utils.workbook.core.``WorksheetDifference`[source]

Bases: `collections.OrderedDict`

Difference between values of worksheets

`__str__`()[source]

Get string representation

Returns: string representation `str`

2.1.1.5.3. wc_utils.workbook.io module¶

IO utilities

Author: Jonathan Karr Arthur Goldberg 2016-11-28 2016-2018, Karr Lab MIT
class `wc_utils.workbook.io.``ExcelReader`(path)[source]

`xls_workbook`[source]

Excel workbook

Type: `Workbook`
`get_sheet_names`()[source]

Get names of sheets contained within path

Returns: list of str: list of sheet names obj
`initialize_workbook`()[source]

Initialize workbook

Returns: data `Workbook`
`read_cell`(sheet_name, xls_worksheet, i_row, i_col)[source]

Read the value of a cell

Parameters: sheet_name (`str`) – worksheet name xls_worksheet (`openpyxl.Worksheet`) – worksheet i_row (`int`) – row number i_col (`int`) – column number value of cell `object`
`read_worksheet`(sheet_name, ignore_empty_final_rows=True, ignore_empty_final_cols=True)[source]

Parameters: sheet_name (`str`) – sheet name ignore_empty_final_rows (`bool`, optional) – if `True`, ignore empty final rows ignore_empty_final_cols (`bool`, optional) – if `True`, ignore empty final columns data `Worksheet` `ValueError`
class `wc_utils.workbook.io.``ExcelWriter`(path, title=None, description=None, keywords=None, version=None, language=None, creator=None)[source]

Write data to Excel file

`xls_workbook`[source]

Excel workbook

Type: `xlsxwriter.Workbook`
`finalize_workbook`()[source]

Finalize workbook

`initialize_workbook`()[source]

Initialize workbook

`write_cell`(xls_worksheet, sheet_name, i_row, i_col, value, format)[source]

Write a value to a cell

Parameters: xls_worksheet (`xlsxwriter.Worksheet`) – Excel worksheet sheet_name (`str`) – sheet name i_row (`int`) – row of cell to write i_col (`int`) – column of cell to write value (`object`) – value to write format (`xlsxwriter.Format`) – format for the cell
`write_worksheet`(sheet_name, data, style=None, validation=None)[source]

Write worksheet to file

Parameters: sheet_name (`str`) – sheet name data (`Worksheet`) – python representation of data; each element must be a string, boolean, integer, float, or NoneType style (`WorksheetStyle`, optional) – worksheet style validation (`WorksheetValidation`, optional) – worksheet validation
class `wc_utils.workbook.io.``FieldValidation`(input_title='', input_message='', show_input=True, type=<FieldValidationType.any: 8>, criterion=None, allowed_scalar_value=None, minimum_scalar_value=None, maximum_scalar_value=None, allowed_list_values=None, show_dropdown=True, ignore_blank=True, error_type=<FieldValidationErrorType.warning: 2>, error_title='', error_message='', show_error=True)[source]

Bases: `object`

Validation for row- or column-oriented field

`input_title`[source]

title of input dialog box

Type: `str`
`input_message`[source]

message in input dialog box

Type: `str`
`show_input`[source]

if `True`, show input dialog box

Type: `bool`
`type`[source]

type of validation

`criterion`[source]

validation criterion

`allowed_scalar_value`[source]

allowable scalar value

Type: `bool`, `int`, `float`, or `str`
`minimum_scalar_value`[source]

minimum allowable value

Type: `int` or `float`
`maximum_scalar_value`[source]

maximum allowable value

Type: `int` or `float`
`allowed_list_values`[source]

allowable list values

Type: `str` or `list` of `str`
`show_dropdown`[source]

if `True`, show dropdown menu for list validations

Type: `bool`
`ignore_blank`[source]

if `True`, don’t validate blank cells

Type: `bool`
`error_type`[source]

type of error dialog to display

Type: `FieldErrorType`
`error_title`[source]

title of error dialog box

Type: `str`
`error_message`[source]

message in error dialog box

Type: `str`
`show_error`[source]

if `True`, show error dialog box

Type: `bool`
`apply_help_comment`(ws, i_row, i_col)[source]

Apply help comment to cell

Parameters: ws (`xlsxwriter.Worksheet`) – worksheet i_row (`int`) – row i_col (`int`) – column
`apply_validation`(ws, first_row, first_col, last_row, last_col)[source]

Apply validation to cells

Parameters: ws (`xlsxwriter.Worksheet`) – worksheet first_row (`int`) – first row first_col (`int`) – first column last_row (`int`) – last row last_col (`int`) – last column
`get_options`()[source]

Get options for `xlsxwriter.Worksheet.data_validation`

Returns
`dict`: dictonary of options for `xlsxwriter.Worksheet.data_validation`
class `wc_utils.workbook.io.``FieldValidationCriterion`[source]

Bases: `str`, `enum.Enum`

An enumeration.

`between` = 'between'[source]
class `wc_utils.workbook.io.``FieldValidationErrorType`[source]

Bases: `int`, `enum.Enum`

Type of error dialog to display

`information` = 3[source]
`stop` = 1[source]
`warning` = 2[source]
class `wc_utils.workbook.io.``FieldValidationType`[source]

Bases: `int`, `enum.Enum`

Field validation type

`any` = 8[source]
`custom` = 7[source]
`date` = 4[source]
`decimal` = 2[source]
`integer` = 1[source]
`length` = 6[source]
`list` = 3[source]
`time` = 5[source]

Bases: `object`

`i_row`[source]

row

Type: `int`
`i_row`[source]

column

Type: `col`
`url`[source]

URL

Type: `str`
`tip`[source]

text of tooltip

Type: `str`
class `wc_utils.workbook.io.``Reader`(path)[source]

Bases: `object`

`path`[source]

path to file(s)

Type: `str`
`get_sheet_names`()[source]

Get names of sheets contained within path

Returns: list of str: list of sheet names obj
`initialize_workbook`()[source]

Initialize workbook

Returns: data `Workbook`
`read_worksheet`(sheet_name, ignore_empty_final_rows=True, ignore_empty_final_cols=True)[source]

Parameters: sheet_name (`str`) – sheet name ignore_empty_final_rows (`bool`, optional) – if `True`, ignore empty final rows ignore_empty_final_cols (`bool`, optional) – if `True`, ignore empty final columns data `Worksheet`
`run`()[source]

Returns: python representation of data `Workbook`
class `wc_utils.workbook.io.``SeparatedValuesReader`(path)[source]

`get_sheet_names`()[source]

Get names of files contained within path glob

Returns: list of str: list of file names obj `ValueError` – if glob does not find any matching files
`initialize_workbook`()[source]

Initialize workbook

Returns: data `Workbook`
`read_cell`(value)[source]

Read the value of a cell

Parameters: value (`object`) – value value `object`
`read_worksheet`(sheet_name, ignore_empty_final_rows=True, ignore_empty_final_cols=True)[source]

Parameters: sheet_name (`str`) – sheet name ignore_empty_final_rows (`bool`, optional) – if `True`, ignore empty final rows ignore_empty_final_cols (`bool`, optional) – if `True`, ignore empty final columns data `Worksheet`
class `wc_utils.workbook.io.``SeparatedValuesWriter`(path, title=None, description=None, keywords=None, version=None, language=None, creator=None)[source]

Write data to csv/tsv file(s)

`finalize_workbook`()[source]

Finalize workbook

`initialize_workbook`()[source]

Initialize workbook

`run`(data, style=None, validation=None)[source]

Write workbook to file(s)

Parameters: data (`Workbook`) – python representation of data; each element must be a string, boolean, integer, float, or NoneType style (`WorkbookStyle`, optional) – workbook style validation (`WorkbookValidation`, optional) – validation
`write_worksheet`(sheet_name, data, style=None, validation=None)[source]

Write worksheet to file

Parameters: sheet_name (`str`) – sheet name data (`Worksheet`) – python representation of data; each element must be a string, boolean, integer, float, or NoneType style (`WorksheetStyle`, optional) – worksheet style validation (`WorksheetValidation`, optional) – worksheet validation
class `wc_utils.workbook.io.``WorkbookStyle`[source]

Bases: `dict`

Workbook style: dictionary of worksheet styles

class `wc_utils.workbook.io.``WorkbookValidation`[source]

Bases: `dict`

Workbook validation: dictionary of worksheet validations

class `wc_utils.workbook.io.``WorksheetStyle`(title_rows=0, title_row_font_bold=True, title_row_fill_pattern='solid', title_row_fill_fgcolor='888888', head_rows=0, head_columns=0, head_row_font_bold=True, head_row_fill_pattern='solid', head_row_fill_fgcolor='CCCCCC', blank_head_fill_fgcolor='', merged_head_fill_fgcolor='AAAAAA', extra_rows=inf, extra_columns=inf, font_family='Arial', font_size=11.0, row_height=15.01, col_width=15.0, auto_filter=True, merge_ranges=None, hyperlinks=None)[source]

Bases: `object`

Worksheet style

`title_rows`[source]

number of title rows

Type: `int`
`title_row_font_bold`[source]

title row bold

Type: `bool`
`title_row_fill_pattern`[source]

title row fill pattern

Type: `str`
`title_row_fill_fgcolor`[source]

title row background color

Type: `str`
`head_rows`[source]

Type: `int`
`head_columns`[source]

Type: `int`
`head_row_font_bold`[source]

Type: `bool`
`head_row_fill_pattern`[source]

Type: `str`
`head_row_fill_fgcolor`[source]

Type: `str`
`blank_head_fill_fgcolor`[source]

background color of blank header cells

Type: `str`
`merged_head_fill_fgcolor`[source]

background color of merged header cells

Type: `str`
`extra_rows`[source]

number of additional rows to show

Type: `float`
`extra_columns`[source]

number of additional columns to show

Type: `float`
`font_family`[source]

font family

Type: `str`
`font_size`[source]

font size

Type: `float`
`row_height`[source]

row height

Type: `float`
`col_width`[source]

column width

Type: `float`
`auto_filter`[source]

whether or not to activate auto filters for row

Type: `bool`
`merge_ranges`[source]

list of tuples of the start row, start column, end row, and end column (0-based) of each range to merge

Type: `list` of `tuple` of `int`

Type: `list` of `Hyperlink`
class `wc_utils.workbook.io.``WorksheetValidation`(orientation=<WorksheetValidationOrientation.row: 1>, fields=None)[source]

Bases: `object`

List of field validations

`orientation`[source]

row or col

Type: `str`
`fields`[source]

field validations

Type: `list` of `FieldValidation`
`apply`(ws, first_row, first_col, last_row, last_col)[source]

Apply validation to worksheet

Parameters: ws (`xlsxwriter.Worksheet`) – worksheet first_row (`int`) – first row first_col (`int`) – first column last_row (`int`) – last row last_col (`int`) – last column
class `wc_utils.workbook.io.``WorksheetValidationOrientation`[source]

Bases: `int`, `enum.Enum`

Worksheet validation orientation

`column` = 2[source]
`row` = 1[source]
class `wc_utils.workbook.io.``Writer`(path, title=None, description=None, keywords=None, version=None, language=None, creator=None)[source]

Bases: `object`

Write data to file(s)

`path`[source]

path to file(s)

Type: `str`
`finalize_workbook`()[source]

Finalize workbook

`initialize_workbook`()[source]

Initialize workbook

`run`(data, style=None, validation=None)[source]

Write workbook to file(s)

Parameters: data (`Workbook`) – python representation of data; each element must be a string, boolean, integer, float, or NoneType style (`WorkbookStyle`, optional) – workbook style validation (`WorkbookValidation`, optional) – validation
`write_worksheet`(sheet_name, data, style=None, validation=None)[source]

Write worksheet to file

Parameters: sheet_name (`str`) – sheet name data (`Worksheet`) – python representation of data; each element must be a string, boolean, integer, float, or NoneType style (`WorksheetStyle`, optional) – worksheet style validation (`WorksheetValidation`, optional) – worksheet validation
`wc_utils.workbook.io.``convert`(source, destination, worksheet_order=None, style=None, ignore_extra_sheets=True)[source]

Convert among Excel (.xlsx), comma separated (.csv), and tab separated formats (.tsv)

Parameters: source (`str`) – path to source file destination (`str`) – path to save converted file worksheet_order (`list` of `str`) – worksheet order style (`WorkbookStyle`, optional) – workbook style for Excel ignore_extra_sheets (`bool`, optional) – true/false should extra sheets in worksheet_order be ignored or should an error be thrown `ValueError` – if file extensions are not supported or file names are equal
`wc_utils.workbook.io.``get_reader`(extension)[source]

Parameters: extension (`str`) – extension reader class `class` `ValueError` – if extension is not one of “.xlsx”, “.csv”, or “.tsv”
`wc_utils.workbook.io.``get_writer`(extension)[source]

Get writer

Parameters: extension (`str`) – extension writer class `class` `ValueError` – if extension is not one of “.xlsx”, “.csv”, or “.tsv”
`wc_utils.workbook.io.``read`(path)[source]

Read data from Excel (.xlsx) file or collection of comma separated (.csv) or tab separated (.tsv) file(s)

Parameters: path (`str`) – path to file(s) python representation of data `Workbook`
`wc_utils.workbook.io.``write`(path, workbook, title=None, description=None, keywords=None, version=None, language=None, creator=None, style=None)[source]

Write data to Excel (.xlsx) file or collection of comma separated (.csv) or tab separated (.tsv) file(s)

Parameters: path (`str`) – path to file(s) workbook (`Workbook`) – python representation of data; each element must be a string, boolean, integer, float, or NoneType title (`str`, optional) – title description (`str`, optional) – description keywords (`str`, optional) – keywords version (`str`, optional) – version language (`str`, optional) – language creator (`str`, optional) – creator style (`WorkbookStyle`, optional) – workbook style