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