Python Programming/Excel

Python has multiple 3rd party libraries for reading and writing Microsoft Excel spreadsheet files, including .xls and .xlsx.

For working with .xls files, there is xlrd for reading and xlwt for writing.

For working with .xlsx files, there is xlrd for reading, openpyxl for reading and writing, and XlsxWriter and PyExcelerate for writing.

To interact with the Excel application and create Python-based add-ins: xlwings, xlOil, PyXLL (commercial).

xlrd
Supports reading .xls Excel files. Support for .xlsx files was removed in xlrd version 2.0.0 from Dec 2020 due to security concerns, but is still available in xlrd version 1.2.0 from Dec 2018. License: BSD.

Example:

Links:
 * xlrd, pypi.python.org
 * xlrd 1.2.0, pypi.python.org
 * xlrd documentation, readthedocs.io
 * xlrd API documentation, readthedocs.io
 * Python: xlrd discerning dates from floats, stackoverflow.com
 * xlrd.biffh.XLRDError: Excel xlsx file; not supported, 11 Dec 2020, stackoverflow.com
 * xlrd 2 released, 11 Dec 2020, groups.google.com

xlwt
Supports writing .xls files. License: BSD.

Links:
 * xlwt, pypi.python.org
 * xlwt documentation, readthedocs.io

openpyxl
Supports reading and writing .xlsx Excel files. Does not support .xls files. License: MIT.

Reading a workbook:

Creating a new workbook:

Changing an existing workbook:

Links:
 * openpyxl, pypi.org
 * openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files, readthedocs.io

XlsxWriter
Supports writing of .xlsx files. License: BSD.

Links:
 * XlsxWriter, pypi.org
 * Creating Excel files with Python and XlsxWriter, readthedocs.io

PyExcelerate
Supports writing .xlsx files. License: BSD.

Links:
 * PyExcelerate, pypi.org

xlutils
Supports various operations and queries on .xls files; depends on xlrd and xlwt. License: MIT.

Links:
 * xlutils, pypi.org

xlOil
Supports creation of Python-based Excel add-ins. Requires Python 3.6 or later; requires Excel 2010 or later installed. Supports: global and local scope worksheet functions, ribbon customisation, custom task panes, RTD/async functions, numpy, matplotlib, pandas, jupyter. Low overhead function calls due to use of the Excel's C-API and embedded in-process Python

Examples:

Create a function to add one day to a date:

Create a function which give a live ticking clock in an cell (uses RTD):

Links:
 * xlOil, pypi.org
 * xloil, readthedocs.io

pywin32
Supports access to Windows applications via Windows Component Object Model (COM). Thus, on Windows, if Excel is installed, PyWin32 lets you call it from Python and let it do various things. You can install PyWin32 by downloading a .exe installer from SourceForge, where it is currently hosted.

Links:
 * 3.4.1. PyWin32, docs.python.org
 * pywin32, pypi.org
 * Python for Windows Extensions files, sourceforge.net
 * Python Excel Mini Cookbook, pythonexcels.com