_images/logo.png

pylightxl pypi | github

Welcome to pylightxl documentation

A light weight, zero dependency (only standard libs used), to the point (no bells and whistles) Microsoft Excel reader/writer python 2.7.18 - 3+ library. Although there are several excellent read/write options out there (python-excel.org or excelpython.org) pylightxl focused on the following key features:

  • Zero non-standard library dependencies!

    • No compatibility/version control issues.

  • Python2.7.18 to Python3+ support for life!

    • Don’t worry about which python version you are using, pylightxl will support it for life

  • Light-weight single source code file

    • Want your project remain truly dependent-less? Copy the single source file into your project without any extra dependency issues or setup.
    • Do you struggle with other libraries weighing your projects down due to their very large size? Pylightxl’s single source file size and zero dependency will not weight your project down (preferable for django apps)
    • Do you struggle with pyinstaller or other exe wrappers failing to build or building to very large packages? Pylightxl will not cause any build errors and will not add to your build size since it has zero dependencies and a small lib size.
    • Do you struggle with download restrictions at your company? Copy the entire pylightxl source from 1 single file and use it in your project.

  • 100% test-driven development for highest reliability/maintainability that aims for 100% coverage on all supported versions

    • Pylightxl aims to test all of its features, however unforeseen edge cases can occur when receiving excel files created by non-microsoft excel. We actively monitor issues to add support for these edge cases should they arise.

  • API aimed to be user friendly and intuitive and well documented. Structure: database > worksheet > indexing

    • db.ws('Sheet1').index(row=1,col=2) or db.ws('Sheet1').address(address='B1')
    • db.ws('Sheet1').row(1) or db.ws('Sheet1').col(1)

High-Level Feature Summary

  • Reader

    • supports Microsoft Excel 2004+ files (.xlsx, .xlsm) and .csv files
    • read files via str path, pathlib path or file objects
    • read all or selective sheets
    • read type converted cell value (string, int, float), formula, comments, and named ranges

  • Database

    • call cell value by row/col ID, excel address, or range
    • call an entire row/col or a semi-structured table based on user-defined headers

  • Writer

    • write to new excel file (write excel files without having excel on your machine)
    • write to existing excel files (see limitations below)

Limitations

Although every effort was made to support a variety of users, the following limitations should be read carefully:

  • Does not support .xls files (Microsoft Excel 2003 and older files)
  • Writer does not support anything other than cell data (no graphs, images, macros, formatting)
  • Does not support worksheet cell data more than 536,870,912 cells (32-bit list limitation), please use 64-bit if more data storage is required.

Installation

There are several ways to use pylightxl. The easiest download is through pip however we understand that certain workplaces may not allow externally downloaded content, therefore we made it easy to copy/paste the source code needed to get going as well. Please see License terms of agreement: License

pip install via pypi

Download via Python Package Installer the latest official release:

pip install pylightxl

pip install via github

Download via github the latest master branch release:

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

Git Clone

Download via github clone:

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

Download Source Files

Download via github: https://github.com/PydPiper/pylightxl/archive/master.zip

Copy Source Files

Create a copy of the entire library that the user can copy directly into a project, a virtual environment, or into the python/lib/site-packages folder for general use.

1.) Create a folder pylightxl

2.) Create the following files within the pylightxl folder:

pylightxl
    1- __init__.py
    2- pylightxl.py

3.) Populate the files with their respective source code contents:

3.1) File1: __init__.py

3.2) File2: pylightxl.py

Quick Start Guide

Get up and running in less than 5 minutes with pylightxl!

_images/readme_demo.gif

Read/Write CSV File

Read a csv file with contents:

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=',')

Read Excel File

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']

Access Worksheet and Cell Data

The following example assumes excelfile.xlsx contains a worksheet named Sheet1 and it has the following cell content:

  A B C
1 10 20  
2   30 40

Via Cell Address

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

Via Cell Index

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

Via Cell Range

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)

Get entire row or column

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

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

Iterate through rows/cols

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]

Update Cell Value

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

Update Cell Formula

Same as update cell value except the entry must begin with a equal sign “=”

Note

updating a cell formula will clear the previously read in cell value. Formulas will not calculate their cell value until the excel file is opened.

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

Get Named Ranges

# 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')

Get row/col based on key-value

Note: key is type sensitive

# 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]

Read Semi-Structured Data

_images/ex_readsemistrdata.png
  • note that ssd function takes any key-word argument as your KEYROWS/KEYCOLS flag
  • multiple tables are read the same way as you would read a book. Top left-to-right, then down
import pylightxl
db = pylightxl.readxl(fn='Book1.xlsx')

# request a semi-structured data (ssd) output
ssd = db.ws(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]]}

Write out a pylightxl.Database as an excel file

Pylightxl support excel writing without having excel installed on the machine. However it is not without its limitations. The writer only supports cell data writing (ie.: does not support graphs, formatting, images, macros, etc) simply just strings/numbers/equations in cells.

Note that equations typed by the user will not calculate for its value until the excel sheet is opened in excel.

import pylightxl as xl

# read in an existing worksheet and change values of its cells (same worksheet as above)
db = xl.readxl(fn='excelfile.xlsx')
# overwrite existing number value
db.ws(ws='Sheet1').index(row=1, col=1)
>>> 10
db.ws(ws='Sheet1').update_index(row=1, col=1, val=100)
db.ws(ws='Sheet1').index(row=1, col=1)
>>> 100
# write text
db.ws(ws='Sheet1').update_index(row=1, col=2, val='twenty')
# write equations
db.ws(ws='Sheet1').update_address(address='A3', val='=A1')

xl.writexl(db=db, fn='updated.xlsx')

Write a new excel file from python data

For new python data that did not come from an existing excel speadsheet.

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")

Source Code Documentation

readxl

pylightxl.pylightxl.readxl(fn, ws=None)

Reads an xlsx or xlsm file and returns a pylightxl database

Parameters:
  • fn (Union[str, pathlib.Path]) – Excel file path, also supports Pathlib.Path object, as well as file-like object from with/open
  • ws (Union[str,List[str]], optional) – sheetnames to read into the database, if not specified - all sheets are read entry support single ws name (ex: ws=’sh1’) or multi (ex: ws=[‘sh1’, ‘sh2’]), defaults to None
Returns:

pylightxl Database
Return type:

Database

writexl

pylightxl.pylightxl.writexl(db, fn)

Writes an excel file from pylightxl.Database

Parameters:
  • db (Database) – database contains sheetnames, and their data
  • fn (Union[str, pathlib.path]) – file output path

database

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]

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 “=”

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

Example Solutions

Reading Semi Structured data

  • Question posted on stackoverflow
  • Problem: read groups of 2D data from a single sheet that can begin at any row/col and has any number of rows/columns per data group, see figure below.
_images/ex_readsemistrdata.png
  • Solution: note that ssd function takes any key-word argument as your KEYROWS/KEYCOLS flag and multiple tables are read the same way as you would read a book. Top left-to-right, then down.
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]]}

Revision Log

pypi version 1.61

  • bug-fix: occasionally a <definedName> tag would case pylightxl to add duplicate of the same worksheet, see issue #75
  • update: updated date handling (code cleanup)
  • added feature: added python2 compatible typing to the library
  • added feature: added io.StringIO support to readcsv

pypi version 1.60

  • added feature: ability to update NamedRanges wb.update_nr(name, val), see issue #72
  • added feature: ability to find where a NamedRange is wb.nr_loc(name)
  • added feature: ability to fill a range with a single value: wb.ws(‘Sheet1’).update_range(address=’A1:B3’, val=10)
  • update: NamedRanges now add the worksheets if they are not already in the workbook. Note that using readxl with worksheet names specified will also ignore NamedRanges from being read in from the sheet that are not read in.
  • update: updated quickstart docs with the new feature demo scripts

pypi version 1.59

  • bug fix: error in printing formulas that were read in as None type, see issue #59
  • bug fix: added custom datetime and time style handling, see issue #36

pypi version 1.58

  • improvement: added support for non-standard sheet ids, see issue #55
  • improvement: added support for general IO file type inputs, see issue #57

pypi version 1.57

  • improvement: added support for non-standard sheet ids (created by 3rd party tools), see issue #53
  • improvement: added support for writing to existing sheets that contain excel customization, see issue #54

pypi version 1.56

  • imporvement: added support for non-standard excel file xml tags, see issue #44
  • bug fix: fixed keyrow bug, see issue #47
  • bug fix: addressed csv writing issue related to cells that contain ‘n’ that previous started a new row. New version replaces ‘n’ with ‘’, see issue #49
  • bug fix: newly written workbooks written by pylightxl could not create new worksheets within excel after opening. The fix was to removed sheetView xml tag, see issue #50
  • improvement: added encoding=’utf-8’ to write altworksheets to support chinese encoding error, see issue #51

pypi version 1.55

  • added comment parsing, see issue #41
  • DEPRECATION WARNING: all indexing method that use “formula” as an argument will be replaced with “output” in future version. Please update your codebase to use “output” instead of “formula”. This was done to simplify indexing the value (output='v'), the formula (output='f') or the comment (output='c').
  • added file stream reading for readxl that now supports with block for reading. See issue #25

pypi version 1.54

  • added handling for datetime parsing

pypi version 1.53

  • bug fix: writing to existing file previously would only write to the current working directory, it now can handle subdirs. In addition inadvertently discovered a bug in python source code ElementTree.iterparse where source passed as a string was not closing the file properly. We submitted a issue to python issue tracker.

pypi version 1.52

  • updated reading error’ed cells “#N/A”
  • updated workbook indexing bug from program generated workbooks that did not index from 1

pypi version 1.51

  • license update within setup.py

pypi version 1.50

  • hot-fix: added python2 support for encoding with cgi instead of html

pypi version 1.49

  • bug-fix: updated encoding for string cells that contained xml-like data (ex: cell A1 “<cell content>”)

pypi version 1.48

  • add feature to writecsv to be able to handle pathlib object and io.StreamIO object
  • refactored readxl to remove regex, now readxl is all cElementTree
  • refactored readxl/writexl to able to handle excel files written by openpyxl that is generated differently than how excel write files.

pypi version 1.47

  • added new function: db.nr('table1') returns the contents of named range “table1”
  • added new function: db.ws('Sheet1').range('A1:C3') that returns the contents of a range it also has the ability to return the formulas of the range
  • updated db.ws('Sheet1').row() and db.ws('Sheet1').col() to take in a new argument formual that returns the formulas of a row or col
  • bugfix: write to existing without named ranges was throwing a “repair” error. Fixed typo on xml for it and added unit tests to capture it
  • added new function: xl.readcsv(fn, delimiter, ws) to read csv files and create a pylightxl db out of it (type converted)
  • added new function: xl.writecsv(db, fn, ws, delimiter) to write out a pylightxl worksheet as a csv

pypi version 1.46

  • bug fix: added ability to input an empty string into the cell update functions (previously entering val=’’) threw and error

pypi version 1.45

  • added support for cell values that have multiple formats within a single cell. previous versions did not support this functionality since it is logged differently in sharedString.xml

  • added support for updating formulas and viewing them:

    • view formula: db.ws('Sheet1').address('A1', formula=True)
    • edit formula: db.ws('Sheet1').update_address('A1', val='=A1+10')

  • updated the following function arguments to drive commonality:

    • was: readxl(fn, sheetnames) new: readxl(fn, ws)
    • was: writexl(db, path) new: writexl(db, fn)
    • was: db.ws(sheetname) new: db.ws(ws)
    • was: db.add_ws(sheetname, data) new: db.add_ws(ws, data)

  • added new feature to be able to read-in NamedRanges, store it in the Database, update it, remove it, and write it. NamedRanges were integrated with existing function to handle semi-structured-data

    • db.add_nr(name'range1', ws='sheet1', address='A1:C2')
    • db.remove_nr(name='range1')
    • db.nr_names

  • add feature to remove worksheet: db.remove_ws(ws='Sheet1')

  • add feature to rename worksheet: db.rename_ws(old='sh1', new='sh2')

  • added a cleanup function upon writing to delete _pylightxl_ temp folder in case an error left them

  • added feature to write to file that is open by excel by appending a “new_” tag to the file name and a warning message that file is opened by excel so a file was saved as “new_” + filename

pypi version 1.44

  • bug fix: accounted for num2letter roll-over issue
  • new feature: added a pylightxl native function for handling semi-structured data

pypi version 1.43

  • bug fix: accounted for reading error’ed out cell “#N/A”
  • bug fix: accounted for bool TRUE/FALSE cell values not registering on readxl
  • bug fix: accounted for edge case that was prematurely splitting cell tags <c r /> by formula closing bracket <f />
  • bug fix: accounted for cell address roll-over

pypi version 1.42

  • added support for pathlib file reading
  • bug fix: previous version did not handle merged cells properly
  • bug fix: database updates did not update maxcol maxrow if new data addition was larger than the initial dataset
  • bug fix: writexl that use linefeeds did not read in properly into readxl (fixed regex)
  • bug fix: writexl filepath issues

pypi version 1.41

  • new-feature: write new excel file from pylightxl.Database
  • new-feature: write to existing excel file from pylightxl.Database
  • new-feature: db.update_index(row, col, val) for user defined cell values
  • new-feature: db.update_address(address, val) for user defined cell values
  • bug fix for reading user defined sheets
  • bug fix for mis-alignment of reading user defined sheets and xml files

pypi version 1.3

  • new-feature: add the ability to call rows/cols via key-value ex: db.ws('Sheet1').keycol('my column header') will return the entire column that has ‘my column header’ in row 1
  • fixed-bug: fixed leading/trailing spaced cell text values that are marked <t xml:space="preserve"> in the sharedString.xml

pypi version 1.2

  • fixed-bug: fixed Sheet number to custom Sheet name matching for 10+ sheets that were previously only sorting alphabetical which resulted with sorting: Sheet1, Sheet10, Sheet11, Sheet2… and so on.

pypi version 1.1

  • initial release

License

Copyright (c) 2019 Viktor Kis

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Contributor Code of Conduct

Pylightxl as with many other python open source projects practices an open and welcoming community for users and contributors. The following is our minimum expectation:

  • A diverse community is a great community, and everyone is welcomed here, no matter the age, race, gender or background.
  • Practice upmost respect for our users and maintainers. Maintainers answering issues on github or stackoverflow should always start by thanking the individual for considering using the tool, then begin to help them. Users, please be patient with our developers we try to test as much we can, but there will always be edge cases - just know that we are here to help you!

Support Content Creator

If you have enjoy using this library please consider supporting it by one or more of the following ways: