Python has multiple 3rd party libraries for reading and writing Microsoft Excel and Apache OpenOffice files.
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.
For working with .ods files, there is pyexcel-ezodf for reading and writing.
xlrd[edit | edit source]
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'
- xlrd, pypi.python.org
- xlrd documentation, readthedocs.io
- xlrd API documentation, readthedocs.io
- Python: xlrd discerning dates from floats, stackoverflow.com
xlwt[edit | edit source]
Supports writing .xls files. License: BSD.
openpyxl[edit | edit source]
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
XlsxWriter[edit | edit source]
Supports writing of .xlsx files. License: BSD.
PyExcelerate[edit | edit source]
Supports writing .xlsx files. License: BSD.
- PyExcelerate, pypi.org
xlutils[edit | edit source]
Supports various operations and queries on .xls files; depends on xlrd and xlwt. License: MIT.
- xlutils, pypi.org
pywin32[edit | edit source]
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.
- 3.4.1. PyWin32, docs.python.org
- pywin32, pypi.org
- Python for Windows Extensions files, sourceforge.net
- Python Excel Mini Cookbook, pythonexcels.com
[edit | edit source]
- Working with Excel Files in Python, python-excel.org