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

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, protected=False, include_help_comments=False)[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

  • protected (bool, optional) – if True, protect the worksheet

  • include_help_comments (bool, optional) – if True, include help comments

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

abstract get_sheet_names()[source]

Get names of sheets contained within path

Returns

list of str: list of sheet names

Return type

obj

abstract initialize_workbook()[source]

Initialize workbook

Returns

data

Return type

Workbook

abstract 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

run(data, style=None, validation=None, protected=False)[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

  • protected (bool, optional) – if True, protect the worksheet

write_worksheet(sheet_name, data, style=None, validation=None, protected=False)[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

  • protected (bool, optional) – if True, protect the worksheet

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]

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, include_help_comments=False)[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

  • include_help_comments (bool, optional) – if True, include help comments

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

abstract finalize_workbook()[source]

Finalize workbook

abstract initialize_workbook()[source]

Initialize workbook

run(data, style=None, validation=None, protected=False)[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

  • protected (bool, optional) – if True, protect the worksheet

abstract write_worksheet(sheet_name, data, style=None, validation=None, protected=False)[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

  • protected (bool, optional) – if True, protect the worksheet

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