Python has multiple 3rd party libraries for reading and writing Microsoft Excel.
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 and .xlsx Excel files. License: BSD.
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'
Supports writing .xls files. License: BSD.
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:
from openpyxl import Workbook workbook = Workbook() worksheet = workbook.worksheets 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:
from openpyxl import load_workbook workbook_name = 'MyWorkbook.xlsx' workbook = load_workbook(workbook_name) worksheet = workbook.worksheets worksheet['A1'] = "String value" workbook.save(workbook_name)
- openpyxl, pypi.org
- openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files, readthedocs.io
Supports writing of .xlsx files. License: BSD.
Supports writing .xlsx files. License: BSD.
- PyExcelerate, pypi.org
Supports various operations and queries on .xls files; depends on xlrd and xlwt. License: MIT.
- xlutils, pypi.org
Supports creation of python-based Excel add-ins. 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
- Documentation: xloil.readthedocs.io
- PyPI: pypi.org/project/xlOil
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.
- Working with Excel Files in Python, python-excel.org