Python API for Excel XLSX Spreadsheets

Getting Started with pylightxl

The library can smoothly run on both Python3 and Python2.7.18. The recommended way to install is via pip: Please use the following command.

 pip install pylightx

Access and Read Excel Spreadsheet via Python

The pylightxl library gives software programmers the capability to read Excel files inside their own applications without any external dependencies. The library allows software developers to read XLSX and XLSM files with just a couple of lines of Java code. The library also provides support for reading only selected worksheet. It also supports accessing and reading a specific column or row from a given 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

Create an Excel XLSX File via Python

The open source pylightxl library has provided the capability for creating Excel XLSX files with just a couple of lines of Python code inside their own applications without installing Microsoft Excel file. The library only supports cell data writing and at the moment does not support graphs, formatting, images, macros, etc. It allows users to write a new excel file from python data and supports features like adding an empty worksheet, adding rows, columns, and many more.

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

Reading Semi-Structured Data via Python API

The open source pylightxl library has included the capability for reading semi-structured data inside their own Python applications. Sometimes it’s required to read data from a sheet that can begin at any row or column and has any number of rows or columns per data group. The library searches out and finds the columnIDs where data groups start and read out multiple tables the same way as you would read a book.

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