Python has multiple 3rd party libraries for reading and writing Microsoft Excel 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.
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
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 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. PyWin32 is bundled with ActivePython distribution. If you do not have ActivePython, 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
- Working with Excel Files in Python, python-excel.org