Excel Libraries in Python
There are so many libraries available in Python language that could help to handle the spreadsheets and let you edit, modify, run formulas and help in data analysis.
Here we are going to see some of the best excel libraries in Python that can really make your life easier, If you are dealing with excel, csv and spreadsheets regularly.
Pandas
Pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language.
Pandas is a software library written for the Python programming language for data manipulation and analysis. In particular, it offers data structures and operations for manipulating numerical tables and time series
https://github.com/pandas-dev/pandas
xlwt
It is a library for writing data and formatting information to older Excel files (ie: .xls) compatible with Microsoft Excel versions 95 to 2003, The package itself is pure Python with no dependencies on modules or packages outside the standard Python distribution.
https://github.com/python-excel/xlwt
pycel
It is a small python library that can translate an Excel spreadsheet into executable python code which can be run independently of Excel.
The python code is based on a graph and uses caching & lazy evaluation to ensure (relatively) fast execution. The graph can be exported and analyzed using tools like Gephi
https://github.com/dgorissen/pycel
Xlsxwriter
It is a Python module for writing files in the Excel 2007+ XLSX file format.
XlsxWriter can be used to write text, numbers, formulas and hyperlinks to multiple worksheets and it supports features such as formatting and many more, It supports Python 3.4+ and PyPy3 and uses standard libraries only.
https://github.com/jmcnamara/XlsxWriter
xlwings
It is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa:
- Scripting: Automate/interact with Excel from Python using a syntax that is close to VBA.
- Macros: Replace your messy VBA macros with clean and powerful Python code.
- UDFs: Write User Defined Functions (UDFs) in Python (Windows only).
Numpy arrays and Pandas Series/DataFrames are fully supported. xlwings-powered workbooks are easy to distribute and work on Windows and macOS.
xlwings includes all files in the xlwings package except the pro
folder, i.e., the xlwings.pro
subpackage.
https://github.com/xlwings/xlwings
PyExcelerate
It is a Python for writing Excel-compatible XLSX spreadsheet files, with an emphasis on speed.
https://github.com/kz26/PyExcelerate
xlutils
This package provides a collection of utilities for working with Excel files. Since these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.
https://github.com/python-excel/xlutils
formulas
formulas implements an interpreter for Excel formulas, which parses and compile Excel formulas expressions.
Moreover, it compiles Excel workbooks to python and executes without using the Excel COM server. Hence, Excel is not needed.
https://github.com/vinci1it2000/formulas
pylightxl
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
https://github.com/PydPiper/pylightxl
nb2xls
It converts Jupyter notebook to Excel Spreadsheets (xlsx), through a new ‘Download As’ option or via nbconvert on the command line.
Respects tables such as Pandas DataFrames. Also exports image data such as matplotlib output.
Markdown is supported where possible (some elements still need work).
Input (code) cells are not included in the spreadsheet.
This allows you to share your results with non-programmers such that they can still easily play with the data.
https://github.com/ideonate/nb2xls
openpyxl
A Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.
It was born from lack of existing library to read/write natively from Python the Office Open XML format.
All kudos to the PHPExcel team as openpyxl is based on PHPExcel http://www.phpexcel.net/
https://github.com/theorchard/openpyxl
Koala
It converts any Excel workbook into a python object that enables on the fly calculation without the need of Excel.
Koala parses an Excel workbook and creates a network of all the cells with their dependencies. It is then possible to change any value of a node and recompute all the depending cells.
You can read more on the origins of Koala here. If you are looking for ways to contribute, you can get started here.
https://github.com/vallettea/koala
xlsxpandasformatter
Deals with the limitations of formatting when using Pandas dataframe and xlsxwriter to export to Excel format.
Provides a helper class that wraps the worksheet, workbook and dataframe objects written by pandas to_excel method using the xlsxwriter engine to allow consistent formatting of cells. A FormatedWorksheet is a helper class that wraps the worksheet, workbook and dataframe objects written by pandas to_excel method using the xlsxwriter engine. It takes care of keeping record of cells format and allows user to apply successively formats to columns, rows and cells. It also provides methods to format group of columns based on column name pattern, and apply special separations between groups of rows.
This class is a quick and dirty workaround to the limitations of formatting in xlsxwriter. It was inspired by the other package XlsxFormatter. The latter, however, cannot be used in the case of creating xlsxwriter Worksheet through the Pandas to_excel
method.
https://github.com/webermarcolivier/xlsxpandasformatter
xls2slsx
- Convert
.xls
files to.xlsx
using xlrd and openpyxl. - Convert
.htm
and.mht
files containing tables or excel contents to.xlsx
using beautifulsoup4 and openpyxl.
https://github.com/snoopyjc/xls2xlsx
xltable
It is an API for writing tabular data and charts to Excel. It is not a replacement for other Excel writing packages such as xlsxwriter, xlwt or pywin32. Instead it uses those packages as a back end to write the Excel files (or to write to Excel directly in the case of pywin32) and provides a higer level abstraction that allows the programmer to deal with tables of data rather than worry about writing individual cells.
The main feature that makes xltable more useful than just writing the Excel files directly is that it can handle tables with formulas that relate to cells in the workbook without having to know in advance where those tables will be placed on a worksheet
https://github.com/fkarb/xltable
pyexcel
It provides one application programming interface to read, manipulate and write data in various excel formats.