How to Manipulate XLS Files With Python

Written by kevin walker
  • Share
  • Tweet
  • Share
  • Email

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.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

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

    easy_install xlwt xlrd xlutils

  2. 2

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

    import xlwt

    import xlrd

    import xlutils

  3. 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:

    native Book = open_workbook('example.xls')

    my Book = 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.

Tips and warnings

  • The procedure for opening a terminal varies depending on the operating system. On Mac OS X, click the "Spotlight" icon and type "Terminal." On Windows, click "Start," then "Run," and type "cmd." On Unix or Linux systems, there is normally a terminal icon on the desktop.

Don't Miss

References

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.