pylightxl

https://pylightxl.readthedocs.io/en/latest/
Admin
pylightxl is a lightweight, zero-dependency Python library for reading and writing Microsoft Excel...
Tokens:6,459
Snippets:55
Trust Score:9.6
Update:1 month ago
Show doc for...
Tokens:
Raw
### Create pylightxl folder structure

Source: https://pylightxl.readthedocs.io/en/latest/installation.html

Create a folder named pylightxl and add the required files __init__.py and pylightxl.py inside it, as part of the manual source file setup.

```text
pylightxl
    1- __init__.py
    2- pylightxl.py
```

--------------------------------

### Install pylightxl via pip from PyPI

Source: https://pylightxl.readthedocs.io/en/latest/installation.html

Use this command to install the latest official release of pylightxl from PyPI using pip.

```bash
pip install pylightxl
```

--------------------------------

### Install pylightxl via pip from GitHub

Source: https://pylightxl.readthedocs.io/en/latest/installation.html

Use this command to install the latest master branch release of pylightxl directly from GitHub using pip.

```bash
pip install git+https://github.com/PydPiper/pylightxl.git
```

--------------------------------

### Read semi-structured data with pylightxl ssd()

Source: https://pylightxl.readthedocs.io/en/latest/solutions.html

Use db.ws('Sheet1').ssd(keycols='KEYCOLS', keyrows='KEYROWS') to read groups of 2D data from a sheet. The method accepts any keyword arguments as flags for key rows/columns. Output is a list of dictionaries, each with 'keyrows', 'keycols', and 'data' keys. The example shows two data groups read from the same sheet.

```python
import pylightxl
db = pylightxl.readxl('Book1.xlsx')

# request a semi-structured data (ssd) output
ssd = db.ws('Sheet1').ssd(keycols="KEYCOLS", keyrows="KEYROWS")

ssd[0]
>>> {'keyrows': ['r1', 'r2', 'r3'], 'keycols': ['c1', 'c2', 'c3'], 'data': [[1, 2, 3], [4, '', 6], [7, 8, 9]]}
ssd[1]
>>> {'keyrows': ['rr1', 'rr2', 'rr3', 'rr4'], 'keycols': ['cc1', 'cc2', 'cc3'], 'data': [[10, 20, 30], [40, 50, 60], [70, 80, 90], [100, 110, 120]]}
```

--------------------------------

### Get row or column by key-value with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Find a column or row based on a key value in a specified index. keycol returns the column where the key matches in the given row, and keyrow returns the row where the key matches in the given column. The key is type sensitive.

```python
# lets say we would like to return the column that has a cell value = 20 in row=1
db.ws(ws='Sheet1').keycol(key=20, keyindex=1)
>>> [20,30]

# we can also specify a custom keyindex (not just row=1), note that we now are matched based on row=2
db.ws(ws='Sheet1').keycol(key=30, keyindex=2)
>>> [20,30]

# similarly done for keyrow with keyindex=1 (look fora match in col=1)
db.ws(ws='Sheet1').keyrow(key='', keyindex=1)
>>> ['',30,40]

```

--------------------------------

### Get entire row or column with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Retrieve an entire row or column from a worksheet. row() returns a list of cell values for the given row, and col() returns a list for the given column.

```python
db.ws(ws='Sheet1').row(row=1)
>>> [10,20,'']

db.ws(ws='Sheet1').col(col=1)
>>> [10,'']

```

--------------------------------

### Get and manage named ranges with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Manage named ranges: add, retrieve contents, find location, update value, list all, and remove. The nr_names property returns a dict mapping names to 'Sheet!Address' strings.

```python
# define a named range
db.add_nr(name='Table1', ws='Sheet1', address='A1:B2')
# get the contents of a named ranges
db.nr(name='Table1')
>>> [[10, 20], ['', 30]]
# find the location of a named range
db.nr_loc(name='Table1')
>>> ['Sheet1','A1:B2']
# update the value of a named range
db.update_nr(name='Table1', val=10)
# see all existing named ranges
db.nr_names
>>> {'Table1': 'Sheet1!A1:B2'}
# remove a named range
db.remove_nr(name='Table1')

```

--------------------------------

### Create and write a new Excel file with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Creates a blank database, adds a worksheet, populates column A with a list of values, and writes the result to output.xlsx. The loop uses update_index with row and column coordinates.

```python
import pylightxl as xl

# take this list for example as our input data that we want to put in column A
mydata = [10,20,30,40]

# create a blank db
db = xl.Database()

# add a blank worksheet to the db
db.add_ws(ws="Sheet1")

# loop to add our data to the worksheet
for row_id, data in enumerate(mydata, start=1)
    db.ws(ws="Sheet1").update_index(row=row_id, col=1, val=data)

# write out the db
xl.writexl(db=db, fn="output.xlsx")
```

--------------------------------

### Clone pylightxl repository via git

Source: https://pylightxl.readthedocs.io/en/latest/installation.html

Use this command to clone the pylightxl repository from GitHub.

```bash
git clone https://github.com/PydPiper/pylightxl.git
```

--------------------------------

### Read Excel file with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Shows how to read an Excel file with pylightxl using a file path, a pathlib.Path, or a file-like object. Also demonstrates reading only selected sheets and listing all worksheet names.

```python
import pylightxl as xl

# readxl returns a pylightxl database that holds all worksheets and its data
db = xl.readxl(fn='folder1/folder2/excelfile.xlsx')

# pylightxl also supports pathlib as well
my_pathlib = pathlib.Path('folder1/folder2/excelfile.xlsx')
db = xl.readxl(my_pathlib)

# pylightxl also supports file-like objects for django users
with open('excelfile.xlsx', 'rb') as f:
    db = xl.readxl(f)

# read only selective sheetnames
db = xl.readxl(fn='folder1/folder2/excelfile.xlsx', ws=('Sheet1','Sheet3'))

# return all sheetnames
db.ws_names
>>> ['Sheet1', 'Sheet3']

```

--------------------------------

### writexl(db, fn)

Source: https://pylightxl.readthedocs.io/en/latest/sourcecode/writexl.html

Writes an Excel file from a pylightxl Database object. The function takes a Database instance and a file output path, and writes the database contents to the specified file.

```APIDOC
## writexl(db, fn)

### Description
Writes an Excel file from a pylightxl Database object.

### Method
Function call

### Signature
`pylightxl.pylightxl.writexl(db, fn)`

### Parameters
- **db** (_Database_) - Required - Database containing sheetnames and their data.
- **fn** (_Union[str, pathlib.Path]_) - Required - File output path.

### Usage Example
```python
import pylightxl

db = pylightxl.readxl(fn='input.xlsx')
pylightxl.writexl(db=db, fn='output.xlsx')
```

### Returns
None
```

--------------------------------

### Read and write CSV with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Reads a CSV file with a custom delimiter and worksheet name, then writes it back as a CSV with a different delimiter. Use when you need to convert or modify CSV data with pylightxl.

```python
import pylightxl as xl

# set the delimiter of the CSV to be the value of your choosing
# set the default worksheet to write the read in CSV data to
db = xl.readcsv(fn='input.csv', delimiter='/', ws='sh2')

# make modifications to it then,
# now write it back out as a csv; or as an excel file, see xl.writexl()
xl.writecsv(db=db, fn='new.csv', ws=('sh2'), delimiter=',')

```

--------------------------------

### Iterate through rows and columns with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Iterate over all rows or columns of a worksheet. The rows and cols properties yield lists of cell values for each row or column.

```python
for row in db.ws(ws='Sheet1').rows:
    print(row)

>>> [10,20,'']
>>> ['',30,40]

for col in db.ws(ws='Sheet1').cols:
    print(col)

>>> [10,'']
>>> [20,30]
>>> ['',40]

```

--------------------------------

### utility_num2columnletters(num)

Source: https://pylightxl.readthedocs.io/en/latest/sourcecode/database.html

Takes a column number and converts it to the equivalent Excel column letters.

```APIDOC
## utility_num2columnletters(num)

### Description
Takes a column number and converts it to the equivalent Excel column letters.

### Parameters
- **num** (_int_) - Required - column number

### Returns
excel column letters

### Return Type
str
```

--------------------------------

### utility_address2index(address)

Source: https://pylightxl.readthedocs.io/en/latest/sourcecode/database.html

Converts an Excel address (e.g., 'A1') to a list of [row, col] indices.

```APIDOC
## utility_address2index(address)

### Description
Converts an Excel address (e.g., 'A1') to a list of [row, col] indices.

### Parameters
- **address** (_str_) - Required - Excel address (ex: "A1")

### Returns
- list of [row, col]

### Return Type
List[int]
```

--------------------------------

### Update cell value with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Update a cell value by address or by index. Shows reading the value before and after the update to confirm the change.

```python
db.ws(ws='Sheet1').address(address='A1')
>>> 10
db.ws(ws='Sheet1').update_address(address='A1', val=100)
db.ws(ws='Sheet1').address(address='A1')
>>> 100

db.ws(ws='Sheet1').update_index(row=1, col=1, val=10)
db.ws(ws='Sheet1').index(row=1, col=1)
>>> 10

```

--------------------------------

### Access and update cell ranges with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Access a range of cells by address (e.g., 'A1:C2') and optionally retrieve formulas with output='f'. Also shows updating a range with a single value using update_range().

```python
db.ws(ws='Sheet1').range(address='A1')
>>> 10
db.ws(ws='Sheet1').range(address='A1:C2')
>>> [[10, 20, ''], ['', 30, 40]]
# get the range's formulas
db.ws(ws='Sheet1').range(address='A1:B1', output='f')
>>> [['=10', '=A1+10']]
# update a range with a single value
db.ws(ws='Sheet1').update_range(address='A1:B1', val=10)

```

--------------------------------

### utility_index2address(row, col)

Source: https://pylightxl.readthedocs.io/en/latest/sourcecode/database.html

Converts row and column indices to an Excel address string.

```APIDOC
## utility_index2address(row, col)

### Description
Converts row and column indices to an Excel address string.

### Parameters
- **row** (_int_) - Required - row index (starting at 1)
- **col** (_int_) - Required - col index (start at 1 that corresponds to column "A")

### Returns
str excel address

### Return Type
str
```

--------------------------------

### utility_columnletter2num(text)

Source: https://pylightxl.readthedocs.io/en/latest/sourcecode/database.html

Takes an Excel column header string and returns the equivalent column count.

```APIDOC
## utility_columnletter2num(text)

### Description
Takes an Excel column header string and returns the equivalent column count.

### Parameters
- **text** (_str_) - Required - excel column (ex: 'AAA' will return 703)

### Returns
int of column count

### Return Type
int
```

--------------------------------

### Access cell by address with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Access cell values, formulas, and comments by address (e.g., 'A1'). Empty cells return an empty string by default, but you can override this per worksheet with set_emptycell().

```python
db.ws(ws='Sheet1').address(address='A1')
>>> 10
# access the cell's formula (if there is one)
db.ws(ws='Sheet1').address(address='A1', output='f')
>>> ''
# access the cell's comment (if there is one)
db.ws(ws='Sheet1').address(address='A1', output='c')
>>> 'this is a comment on cell A1!'
# note index a empty cell will return an empty string
db.ws(ws='Sheet1').address(address='A100')
>>> ''
# however default empty value can be overwritten for each worksheet
db.ws(ws='Sheet1').set_emptycell(val=0)
db.ws(ws='Sheet1').address(address='A100')
>>> 0

```

--------------------------------

### Access cell by index with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Access cell values and formulas by row and column index. Empty cells return an empty string by default, but you can override this per worksheet with set_emptycell().

```python
db.ws(ws='Sheet1').index(row=1, col=2)
>>> 20
# access the cell's formula (if there is one)
db.ws(ws='Sheet1').index(row=1, col=2, output='f')
>>> '=A1+10'
# note index a empty cell will return an empty string
db.ws(ws='Sheet1').index(row=100, col=1)
>>> ''
# however default empty value can be overwritten for each worksheet
db.ws(ws='Sheet1').set_emptycell(val=0)
db.ws(ws='Sheet1').index(row=100, col=1)
>>> 0

```

--------------------------------

### Update cell formula with pylightxl

Source: https://pylightxl.readthedocs.io/en/latest/quickstart.html

Update a cell formula by address or index. The value must begin with '='. Note that updating a formula clears the previously read cell value, and formulas are not calculated until the Excel file is opened.

```python
db.ws(ws='Sheet1').update_address(address='A1', val='=B1+100')
db.ws(ws='Sheet1').update_index(row=1, col=1, val='=B1+100')

```
Tokens:
Raw