None
Blog / 2010 / January / 14

Reading XLS files in Python

January 14, 2010

The xlrd library, available at http://www.python-excel.org/ allows you to read xls files from python code.

Download and install the library, knock up a suitable test file in Excel, then try the following code in a python shell:

>>> from mmap import mmap,ACCESS_READ
>>> from xlrd import open_workbook

>>> lBook = open_workbook('/test.xls')
>>> print lBook
<xlrd.Book object at 0x00C94330>

>>> lSheets = lBook.sheets()
>>> lSheet = lSheets[0]
>>> print lSheet.name
test

>>> lRows = lSheet.nrows
>>> lCols = lSheet.ncols

>>> lSheet.cell(0,0)
test:u'First Cell Text'

There are also libraries here for manipulating spreadsheets too.

Converting XLS to CSV

This code will convert a spreadsheet into a CSV structure in a string.

def _XlsToCsv(pFilePath):
  lCsvFile = ""
  lWorkbook = open_workbook(pFilePath)
  lSheets = lWorkbook.sheets()
  lSheet = lSheets[0]
  lRows = lSheet.nrows
  lCols = lSheet.ncols

  for row in range(0, lRows):
      lRow = ""
      for col in range(0, lCols):
          lCell = lSheet.cell(row, col)
          lValue = str(lCell.value)
          if len(lRow) > 0:
              lRow += ','
          lRow += '"' + lValue + '"'
      lCsvFile += lRow
      lCsvFile += '\n'
  return lCsvFile

Tags: python xls