2.1.1.5. wc_utils.workbook package

2.1.1.5.1. Submodules

2.1.1.5.2. wc_utils.workbook.core module

Excel utilities

Author:Jonathan Karr <karr@mssm.edu>
Date:2016-11-28
Copyright:2016-2018, Karr Lab
License:MIT
class wc_utils.workbook.core.CellDifference[source]

Bases: str

Difference between values of cells

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
Returns:True if rows are semantically equal
Return type: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
Returns:cell or range of cells
Return type:object or Row
__ne__(other)[source]

Compare rows

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

Get difference between cells

Parameters:
  • cell_self (object) – self cell
  • cell_other (object) – other cell
Returns:

difference

Return type:

CellDifference

difference(other)[source]

Get difference with another row

Parameters:other (Row) – other row
Returns:dictionary of differences
Return type:RowDifference
Raises: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
Return type: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
Returns:true if workbooks are semantically equal
Return type:bool
__ne__(other)[source]

Compare two workbooks

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

Get difference with another workbook

Parameters:other (Workbook) – other workbook
Returns:dictionary of differences, grouped by worksheet
Return type:dict
Raises: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
Return type: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
Returns:True if worksheets are semantically equal
Return type: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
Returns:row or range of rows
Return type:Row or Worksheet
__ne__(other)[source]

Compare two worksheets

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

Get difference with another worksheet

Parameters:other (Worksheet) – other worksheet
Returns:dictionary of differences, grouped by row
Return type:WorksheeDifference
Raises: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
Return type:str

2.1.1.5.3. wc_utils.workbook.io module

IO utilities

Author:Jonathan Karr <karr@mssm.edu>
Author:Arthur Goldberg <Arthur.Goldberg@mssm.edu>
Date:2016-11-28
Copyright:2016-2018, Karr Lab
License:MIT
class wc_utils.workbook.io.ExcelReader(path)[source]

Bases: wc_utils.workbook.io.Reader

Read data from Excel file

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
Return type:obj
initialize_workbook()[source]

Initialize workbook

Returns:data
Return type: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
Returns:

value of cell

Return type:

object

read_worksheet(sheet_name, ignore_empty_final_rows=True, ignore_empty_final_cols=True)[source]

Read data from Excel worksheet

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

data

Return type:

Worksheet

Raises:

ValueError

class wc_utils.workbook.io.ExcelWriter(path, title=None, description=None, keywords=None, version=None, language=None, creator=None)[source]

Bases: wc_utils.workbook.io.Writer

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

Type:FieldValidationType
criterion[source]

validation criterion

Type:FieldValidationCriterion
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

Hyperlink from a cell

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

Read data from file(s)

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
Return type:obj
initialize_workbook()[source]

Initialize workbook

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

Read data from file

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

data

Return type:

Worksheet

run()[source]

Read data from file(s)

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

Bases: wc_utils.workbook.io.Reader

Read data from csv/tsv file(s)

get_sheet_names()[source]

Get names of files contained within path glob

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

Initialize workbook

Returns:data
Return type:Workbook
read_cell(value)[source]

Read the value of a cell

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

Read data from file

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

data

Return type:

Worksheet

class wc_utils.workbook.io.SeparatedValuesWriter(path, title=None, description=None, keywords=None, version=None, language=None, creator=None)[source]

Bases: wc_utils.workbook.io.Writer

Write data to csv/tsv file(s)

finalize_workbook()[source]

Finalize workbook

initialize_workbook()[source]

Initialize workbook

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(head_rows=0, head_columns=0, head_row_font_bold=False, head_row_fill_pattern='solid', head_row_fill_fgcolor='', blank_head_fill_fgcolor='', merged_head_fill_fgcolor='', extra_rows=inf, extra_columns=inf, font_family='Arial', font_size=11.0, row_height=15.0, col_width=15.0, auto_filter=True, merge_ranges=None, hyperlinks=None)[source]

Bases: object

Worksheet style

head_rows[source]

number of head rows

Type:int
head_columns[source]

number of head columns

Type:int
head_row_font_bold[source]

head row bold

Type:bool
head_row_fill_pattern[source]

head row fill pattern

Type:str
head_row_fill_fgcolor[source]

head row background color

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

list of hyperlinks

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
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
Raises:

ValueError – if file extensions are not supported or file names are equal

wc_utils.workbook.io.get_reader(extension)[source]

Get reader

Parameters:extension (str) – extension
Returns:reader class
Return type:class
Raises: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
Returns:writer class
Return type:class
Raises: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)
Returns:python representation of data
Return type: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

2.1.1.5.4. Module contents