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

``` Python
import xlrd
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

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

``` python
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:

``` python
from openpyxl import 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:

``` python
from openpyxl import 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

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

``` Python
import datetime as dt
@xloil.func
def pyTestDate(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):

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

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

## External links

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

