Python Programming/Excel

From Wikibooks, open books for an open world
Jump to navigation Jump to search

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.

xlrd[edit]

Supports reading .xls and .xlsx Excel files. License: BSD.

Example:

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:

xlwt[edit]

Supports writing .xls files. License: BSD.

Links:

openpyxl[edit]

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

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:

XlsxWriter[edit]

Supports writing of .xlsx files. License: BSD.

Links:

PyExcelerate[edit]

Supports writing .xlsx files. License: BSD.

Links:

xlutils[edit]

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

Links:

pywin32[edit]

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.

Links:

External links[edit]