How to Manipulate XLS Files With Python

Python users are not given a single, unified interface for interacting with Excel spreadsheet files. Instead, there are two different libraries: xlrd and xlwt, or "Excel Reader" and "Excel Writer." The data structures used by the two libraries are not directly compatible, so Python developers must use a third library, xlutils, to move data from one library to the other.

• 1

  Download and install the libraries by typing the following into a terminal:

  easy_install xlwt xlrd xlutils

• 2

  Open a text editor and paste the following to import the libraries:

  import xlwt

  import xlrd

  import xlutils

• 3

  Paste the following afterward to open an existing XLS file, add a new worksheet with some data and a formula, and write the new data out to the disk again:

  nativeBook = open_workbook('example.xls')

  myBook = copy(nativeBook)

  sheet = myBook.add_sheet('Test Sheet')

  ws.write(0,0, 1234)

  ws.write(0,1, 4321)

  ws.write(0,2, xlwt.Formula("A1+B1"))

  wb.save('example.xls')

  This uses the "open_workbook" function from xlrd, the "copy" function from xlutils to translate the xlrd workbook into an xlwt workbook, and "add_sheet," "write," and "save" functions from xlwt.