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

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:

importxlrd
workbook = xlrd.open_workbook("MySpreadsheet.xls")
#for sheet in workbook.sheets(): # Loads all the sheets, unlike workbook.sheet_names()
for sheetName in workbook.sheet_names(): # Sheet iteration by name
 print("Sheet name:", sheetName)
 sheet = workbook.sheet_by_name(sheetName)
 for rowno in range(sheet.nrows):
 for colno in range(sheet.ncols):
 cell = sheet.cell(rowno, colno)
 print(str(cell.value)) # Output as a string
 if cell.ctype == xlrd.XL_CELL_DATE:
 dateTuple = xlrd.xldate_as_tuple(cell.value, workbook.datemode)
 print(dateTuple) # E.g. (2017, 1, 1, 0, 0, 0)
 mydate = xlrd.xldate.xldate_as_datetime(cell.value, workbook.datemode)
 print(mydate) # In xlrd 0.9.3
 print()
 
for sheetno in range(workbook.nsheets): # Sheet iteration by index
 sheet = workbook.sheet_by_index(sheetno)
 print("Sheet name:", sheet.name)
 for notekey in sheet.cell_note_map: # In xlrd 0.7.2
 print("Note AKA comment text:", sheet.cell_note_map[notekey].text)
 
print(xlrd.formula.colname(1)) # Column name such as A or AD, here 'B'

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

Supports writing .xls files. License: BSD.

Links:

• xlwt, pypi.python.org
• xlwt documentation, readthedocs.io

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

Reading a workbook:

from openpyxl import load_workbook
workbook = load_workbook("MyNewWorkbook.xlsx")
for worksheet in workbook.worksheets:
 print("==%s==" % worksheet.title)
 for row in worksheet: # For each cell in each row
 for cell in row:
 print(cell.row, cell.column, cell.value) # E.g. 1 A Value
 for cell in worksheet["A"]: # For each cell in column A
 print(cell.value)
 print(worksheet["A1"].value) # A single cell
 print(worksheet.cell(column=1, row=1).value) # A1 value as well

Creating a new workbook:

fromopenpyxlimport Workbook
workbook = Workbook()
worksheet = workbook.worksheets[0]
worksheet['A1'] = 'String value'
worksheet['A2'] = 42 # Numerical value
worksheet.cell(row=3, column=1).value = "New A3 Value"
workbook.save("MyNewWorkbook.xlsx") # Overrides if it exists

Changing an existing workbook:

fromopenpyxlimport load_workbook
workbook_name = 'MyWorkbook.xlsx'
workbook = load_workbook(workbook_name)
worksheet = workbook.worksheets[0]
worksheet['A1'] = "String value"
workbook.save(workbook_name)

Links:

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

Supports writing of .xlsx files. License: BSD.

Links:

• XlsxWriter, pypi.org
• Creating Excel files with Python and XlsxWriter, readthedocs.io

Supports writing .xlsx files. License: BSD.

Links:

• PyExcelerate, pypi.org

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

Links:

• xlutils, pypi.org

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:

importdatetimeasdt
@xloil.func
defpyTestDate(x: dt.datetime) -> dt.datetime:
    return x + dt.timedelta(days=1)

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

@xloil.func
async def pyTestAsyncGen(secs):
 while True:
 await asyncio.sleep(secs)
 yield datetime.datetime.now()

Links:

• xlOil, pypi.org
• xloil, readthedocs.io

Professional solution for creating high performance, fully featured, Excel add-ins entirely in Python. Supports all major Python releases from 2.3 to 3.14 and later, and all versions of Excel for Windows (both 64 and 32 bit) including Office 365 from Office 2003.

Widely used in the finance industry to expose quant and risk models to Excel users, as well as many other scientific industries. Originally released in 2010, PyXLL was the first mainstream Python based Excel add-in.

Features include: fast worksheet functions and macros (UDFs) with a flexible type system; built in support for pandas, polars and numpy types; real time data (RTD) functions, using threads or asyncio; easy development with automatic module reloading; interactive and static charts using matplotlib, seaborn, plotly, bokeh and altair (others can be added via extensions); integrated Jupyter notebooks; ribbon customization; custom task panes using wxPython, PySide, PyQt and tkinter; custom deployment options.

frompyxllimport xl_func
# Adding '@xl_func' to a Python function makes it callable from Excel.
@xl_func
defhello(name: str) -> str:
 return f"Hello, {name}"

Links:

• www.pyxll.com

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

• Working with Excel Files in Python, python-excel.org -- has links to source code repositories (GitHub, etc.) for the packages

• Book:Python Programming