Python - Working With Excel Files Using xlrd and xlwt

To work with excel files in Python, you can use xlrd and xlwt libraries - xlrd is used for reading or extracting data from an existing excel file and xlwt is used for writing or creating an excel file.

Downloading xlrd and xlwt:

xlrd:

    1. Go to https://pypi.python.org/pypi/xlrd

    2. Select the desired package (preferably the latest package)

    3. Download the desired package(click xlrd-v.v.v.tar.gz or click on the download xlrd-v.v.v.tar.gz button, where v.v.v is the version)

xlwt:

    1. Go to https://pypi.python.org/pypi/xlwt

    2. Select the desired package (preferably the latest package)

    3. Download the desired package(click xlwt-v.v.v.tar.gz or click on the download xlwt-v.v.v.tar.gz button, where v.v.v is the version)

Installing xlrd and xlwt in Windows Operating System:

To install xlrd in Windows, follow the following steps:

    1. Extract the downloaded xlrd package

    2. Open Windows command prompt (Windows key + R, then type "cmd" the Enter key)

    3. Go to the directory where the extracted xlrd package was stored. (For example, we stored the package in c:\xlrd-v.v.v, in the command promt we will enter cd c:\xlrd-v.v.v then press Enter key)

    4. Enter  \Python26\python setup.py install then press Enter key. (Make sure to use the Python version that you intend to work for your project)

To install xlwt in Windows, follow the following steps:

    1. Extract the downloaded xlwt package

    2. Open Windows command prompt (Windows key + R, then type "cmd" the Enter key)

    3. Go to the directory where the extracted xlwt package was stored. (For example, we stored the package in c:\xlwt-v.v.v, in the command promt we will enter cd c:\xlwt-v.v.v then press Enter key)

    4. Enter  \Python26\python setup.py install then press Enter key. (Make sure to use the Python version that you intend to work for your project)

This is a sample Python code to read the contents of an excel file:

import xlrd #to read excel(.xls) files

def readFromExcel():

    #read excel file (filename at the moment is Sample.xls)

    workbook = xlrd.open_workbook("Sample.xls")

    #read content of Sheet1

    sheet = workbook.sheet_by_name("Sheet1")

    allInfo = [] #nested list for row and column data

    for rows in range(sheet.nrows):    

            current = [] #list for row data

            for cols in range(sheet.ncols):

                #add information to the list

                current.append( sheet.cell_value(rows,cols) )

            #add list to the nested list

            allInfo.append(current)

This is a sample Python code to write to an excel file:

import xlwt #to write excel(.xls) files

#Create a new workbook object
workbook = xlwt.Workbook()

#Add an excel sheet
worksheet = workbook.add_sheet('Coordinates')

#Add coordinate on each cell
for x in range(0, 5):
    for y in range(0, 5):
        value = str(x) + "," + str(y)
        worksheet.write(x, y, value)

#Save and create new excel file 
workbook.save('OutputFile.xls')