3.3. database¶

3.3.1. Database Class¶

class pylightxl.pylightxl.Database¶

add_nr(name, ws, address)¶

Add a NamedRange to the database. There can not be duplicate name or addresses. A named range that overlaps either the name or address will overwrite the database’s existing NamedRange

Parameters:	name (str) – NamedRange name ws (str) – worksheet name address (str) – range of address (single cell ex: “A1”, range ex: “A1:B4”)
Returns:	None

add_ws(ws, data=None)¶

Logs worksheet name and its data in the database

Parameters:	ws (str) – worksheet name data – dictionary of worksheet cell values (ex: {‘A1’: {‘v’:10,’f’:’’,’s’:’’, ‘c’: ‘’}, ‘A2’: {‘v’:20,’f’:’’,’s’:’’, ‘c’: ‘’}})
Returns:	None

nr(name, formula=False, output='v')¶

Returns the contents of a name range in a nest list form [row][col]

Parameters:	name (str) – NamedRange name formula (bool) – flag to return the formula of this cell output (str) – output request “v” for value, “f” for formula, “c” for comment
Return list:	nest list form [row][col]

nr_names¶

Returns the dictionary of named ranges ex: {unique_name: unique_address, …}

Return dict:	{unique_name: unique_address, …}

remove_nr(name)¶

Removes a Named Range from the database

Parameters:	name (str) – NamedRange name
Returns:	None

remove_ws(ws)¶

Removes a worksheet and its data from the database

Parameters:	ws (str) – worksheet name
Returns:	None

rename_ws(old, new)¶

Renames an existing worksheet. Caution, renaming to an existing new worksheet name will overwrite

Parameters:	old (str) – old name new (str) – new name
Returns:	None

set_emptycell(val)¶

Custom definition for how pylightxl returns an empty cell

Parameters:	val – (default=’’) empty cell value
Returns:	None

ws(ws)¶

Indexes worksheets within the database

Parameters:	ws (str) – worksheet name
Returns:	pylightxl.Database.Worksheet class object

ws_names¶

Returns a list of database stored worksheet names

Returns:	list of worksheet names

3.3.2. Worksheet Class¶

class pylightxl.pylightxl.Worksheet(data=None)¶

address(address, formula=False, output='v')¶

Takes an excel address and returns the worksheet stored value

Parameters:	address (str) – Excel address (ex: “A1”) formula (bool) – flag to return the formula of this cell output (str) – output request “v” for value, “f” for formula, “c” for comment
Returns:	cell value

col(col, formula=False, output='v')¶

Takes a col index input and returns a list of cell data

Parameters:	col (int) – col index (start at 1 that corresponds to column “A”) formula (bool) – flag to return the formula of this cell output (str) – output request “v” for value, “f” for formula, “c” for comment
Returns:	list of cell data

cols¶

Returns a list of cols that can be iterated through

Returns:	list of cols-lists (ex: [[11,21],[12,22],[13,23]] for 2 rows with 3 columns of data

index(row, col, formula=False, output='v')¶

Takes an excel row and col starting at index 1 and returns the worksheet stored value

Parameters:	row (int) – row index (starting at 1) col (int) – col index (start at 1 that corresponds to column “A”) formula (bool) – flag to return the formula of this cell output (str) – output request “v” for value, “f” for formula, “c” for comment
Returns:	cell value

keycol(key, keyindex=1)¶

Takes a column key value (value of any cell within keyindex row) and returns the entire column, no match returns an empty list

Parameters:	key (str/int/float) – any cell value within keyindex row (type sensitive) keyindex (int) – option keyrow override. Must be >0 and smaller than worksheet size
Return list:	list of the entire matched key column data (only first match is returned)

keyrow(key, keyindex=1)¶

Takes a row key value (value of any cell within keyindex col) and returns the entire row, no match returns an empty list

Parameters:	key (str/int/float) – any cell value within keyindex col (type sensitive) keyrow (int) – option keyrow override. Must be >0 and smaller than worksheet size
Return list:	list of the entire matched key row data (only first match is returned)

range(address, formula=False, output='v')¶

Takes an range (ex: “A1:A2”) and returns a nested list [row][col]

Parameters:	address (str) – cell range (ex: “A1:A2”, or “A1”) formula (bool) – returns the values if false, or formulas if true of cells output (str) – output request “v” for value, “f” for formula, “c” for comment
Return list:	nested list [row][col] regardless if range is a single cell or a range

row(row, formula=False, output='v')¶

Takes a row index input and returns a list of cell data

Parameters:	row (int) – row index (starting at 1) formula (bool) – flag to return the formula of this cell output (str) – output request “v” for value, “f” for formula, “c” for comment
Returns:	list of cell data

rows¶

Returns a list of rows that can be iterated through

Returns:	list of rows-lists (ex: [[11,12,13],[21,22,23]] for 2 rows with 3 columns of data

set_emptycell(val)¶

Custom definition for how pylightxl returns an empty cell

Parameters:	val – (default=’’) empty cell value
Returns:	None

size¶

Returns the size of the worksheet (row/col)

Returns:	list of [maxrow, maxcol]

ssd(keyrows='KEYROWS', keycols='KEYCOLS')¶

Runs through the worksheet and looks for “KEYROWS” and “KEYCOLS” flags in each cell to identify the start of a semi-structured data. A data table is read until an empty header is found by row or column. The search supports multiple tables.

Parameters:	keyrows (str) – (default=’KEYROWS’) a flag to indicate the start of keyrow’s cells below are read until an empty cell is reached keycols (str) – (default=’KEYCOLS’) a flag to indicate the start of keycol’s cells to the right are read until an empty cell is reached
Return list:	list of data dict in the form of [{‘keyrows’: [], ‘keycols’: [], ‘data’: [[], …]}, {…},]

update_address(address, val)¶

Update worksheet data via address

Parameters:	address (str) – excel address (ex: “A1”) val (int/float/str) – cell value; equations are strings and must begin with “=”
Returns:	None

update_index(row, col, val)¶

Update worksheet data via index

Parameters:	row (int) – row index col (int) – column index val (int/float/str) – cell value; equations are strings and must begin with “=”
Returns:	None

3.3.3. Support Functions¶

pylightxl.pylightxl.utility_address2index(address)¶

Convert excel address to row/col index

Parameters:	address (str) – Excel address (ex: “A1”)
Returns:	list of [row, col]

pylightxl.pylightxl.utility_index2address(row, col)¶

Converts index row/col to excel address

Parameters:	row (int) – row index (starting at 1) col (int) – col index (start at 1 that corresponds to column “A”)
Returns:	str excel address

pylightxl.pylightxl.utility_columnletter2num(text)¶

Takes excel column header string and returns the equivalent column count

Parameters:	text (str) – excel column (ex: ‘AAA’ will return 703)
Returns:	int of column count

pylightxl.pylightxl.utility_num2columnletters(num)¶

Takes a column number and converts it to the equivalent excel column letters

Parameters:	num (int) – column number
Return str:	excel column letters