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”)
add_ws(ws, data=None)

Logs worksheet name and its data in the database

Parameters:
  • ws (str) – worksheet name
  • data (dict, optional) – dictionary of worksheet cell values (ex: {‘A1’: {‘v’:10,’f’:’’,’s’:’’, ‘c’: ‘’}, ‘A2’: {‘v’:20,’f’:’’,’s’:’’, ‘c’: ‘’}}), defaults to 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, optional) – flag to return the formula of this cell, defaults to False
  • output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:

nest list form [row][col]
Return type:

List[list]
nr_loc(name)

Returns the worksheet and address loction of a named range

Parameters:name (str) – NamedRange name
Returns:[worksheet, address]
Return type:List[str]
nr_names

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

Returns:{unique_name: unique_address, …}
Return type:Dict[str, str]
remove_nr(name)

Removes a Named Range from the database

Parameters:name (str) – NamedRange name
remove_ws(ws)

Removes a worksheet and its data from the database

Parameters:ws (str) – worksheet name
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
set_emptycell(val)

Custom definition for how pylightxl returns an empty cell

Parameters:val (Union[str,int,float]) – (default=’’) empty cell value
update_nr(name, val)

Updates a NamedRange with a single value. Raises UserWarning if name not in workbook.

Parameters:
  • name (str) – NamedRange name
  • val (Union[int,float,str]) – cell value; equations are string and must being with “=”
ws(ws)

Indexes worksheets within the database

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

Returns a list of database stored worksheet names

Returns:list of worksheet names
Return type:List[str]

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, optional) – flag to return the formula of this cell, defaults to False
  • output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:

cell value
Return type:

Union[int, float, str, bool]
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, optional) – flag to return the formula of this cell, defaults to False
  • output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:

list of cell data
Return type:

List[Union[int, float, str, bool]]
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
Return type:Iterable[List[Union[int, float, str, bool]]]
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, optional) – flag to return the formula of this cell, defaults to False
  • output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:

cell value
Return type:

Union[int, float, str, bool]
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 (Union[str,int,float,bool]) – any cell value within keyindex row (type sensitive)
  • keyindex (int, optional) – option keyrow override. Must be >0 and smaller than worksheet size, defaults to 1
Returns:

list of the entire matched key column data (only first match is returned)
Return type:

List[Union[str,int,float,bool]]
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 (Union[str,int,float,bool]) – any cell value within keyindex col (type sensitive)
  • keyindex (int, optional) – option keyrow override. Must be >0 and smaller than worksheet size, defaults to 1
Returns:

list of the entire matched key row data (only first match is returned)
Return type:

List[Union[str,int,float,bool]]
range(address, formula=False, output='v')

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

Parameters:
  • address (str) – cell range (ex: “A1:A2”, or “A1”)
  • formula (bool, optional) – returns the values if false, or formulas if true of cells, defaults to False
  • output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:

nested list [row][col] regardless if range is a single cell or a range
Return type:

_type_
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, optional) – flag to return the formula of this cell, defaults to False
  • output (str, optional) – output request “v” for value, “f” for formula, “c” for comment, defaults to ‘v’
Returns:

list of cell data
Return type:

List[Union[int, float, str, bool]]
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
Return type:Iterable[List[Union[int, float, str, bool]]]
set_emptycell(val)

Custom definition for how pylightxl returns an empty cell

Parameters:val (Union[int, float, str]) – (default=’’) empty cell value
size

Returns the size of the worksheet (row/col)

Returns:list of [maxrow, maxcol]
Return type:List[int]
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, optional) – a flag to indicate the start of keyrow’s cells below are read until an empty cell is reached, defaults to ‘KEYROWS’
  • keycols (str, optional) – a flag to indicate the start of keycol’s cells to the right are read until an empty cell is reached, defaults to ‘KEYCOLS’
Returns:

list of data dict in the form of [{‘keyrows’: [], ‘keycols’: [], ‘data’: [[], …]}, {…},]
Return type:

List[Dict[str,list]]
update_address(address, val)

Update worksheet data via address

Parameters:
  • address (str) – excel address (ex: “A1”)
  • val (Union[int, float, str, bool]) – cell value; equations are strings and must begin with “=”
update_index(row, col, val)

Update worksheet data via index

Parameters:
  • row (int) – row index
  • col (int) – column index
  • val (Union[int, float, str, bool]) – cell value; equations are strings and must begin with “=”
update_range(address, val)

Update worksheet data via address range with a single value

Parameters:
  • address (str) – excel address (ex: “A1:B3”)
  • val (Union[int, float, str, bool]) – cell value; equations are strings and must begin with “=”

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]
Return type:List[int]
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
Return type:

str
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
Return type:int
pylightxl.pylightxl.utility_num2columnletters(num)

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

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