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