1. Products
  2.   Spreadsheet
  3.   Python
  4.   pylightxl  



Python API for Excel XLSX Spreadsheets

Generate, Modify,Manipulate and Read Microsoft Excel XLSX and XLSM File Format via open-source Python library.

pylightxl is an open source lightweight Python library that gives software developers the capability to work with Microsoft Excel Files inside their own application with zero dependencies. The library has provided support for reading and writing Excel spreadsheets using Python commands.

The library has included Reading support XLSX and XLSM spreadsheet file formats and writing support for XLSX file formats. The library is very easy to use and install as there is only a single source file that can need to be copied directly into a project. The library has included support for several important features, such as reading excel files, reading all or selected sheets, better memory management and speed, reading a particular row or column, updating row data, adding or removing columns from a sheet, and many more.

Previous Next

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.

Install pylightxl via PIP 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. 

Read Excel Spreadsheet via Python API

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

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.

Create Excel XLSX File via Python API

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

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

Read Semi-Structured Data via Python API

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

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