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_loc(name)

Returns the worksheet and address loction of a named range

Parameters:name (str) – NamedRange name
Return list(str,str):
 [worksheet, address]
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
update_nr(name, val)

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

Parameters:
  • name (str) – NamedRange name
  • value (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
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 a 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 “=”
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 “=”
update_range(address, val)

Update worksheet data via address range with a single value

Parameters:
  • address (str) – excel address (ex: “A1:B3”)
  • val (int/float/str) – 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]
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