Python: Read Excel file

14 07 2014

read data in excel by python using xlrd plugin

import xlrd
xlsName = "data.xls"
book = xlrd.open_workbook(xlsName)
print "The Number of worksheets is ", book.nsheets
print "Worksheet name(s):", book.sheet_names()
sht = len(book.sheet_names())
for i in range(sht):
   sh = book.sheet_by_index(i)
   print "%s have %d rows %d columns"%(, sh.nrows, sh.ncols)
worksheet = book.sheet_by_index(0)
header_row = 0
headers = [worksheet.cell_value(header_row,i) for i in xrange(worksheet.ncols)]
print headers
def getVal(raw,row,col):
   if raw == xlrd.XL_CELL_EMPTY:
     value = None
   elif raw == xlrd.XL_CELL_TEXT:
     value = worksheet.cell_value(row, col)
   elif raw == xlrd.XL_CELL_NUMBER:
     value = float(worksheet.cell_value(row,col))
   elif raw == xlrd.XL_CELL_DATE:
     value = xlrd.xldate_as_tuple(worksheet.cell_value(row, col), workbook.datemode)
   elif raw == xlrd.XL_CELL_BOOLEAN:
     value = bool(worksheet.cell_value(row, col))
     value = worksheet.cell_value(row, col)
row_dict_list = []
for row in xrange(header_row+1, worksheet.nrows):
   rowData = [worksheet.cell_value(row,i) for i in xrange(worksheet.ncols) ]

the result show:

The Number of worksheets is 2
Worksheet name(s): [u’Sheet4′, u’Sheet5′]
Sheet4 have 121 rows 6 columns
Sheet5 have 0 rows 0 columns
[u’No.’, u’NAME’, u’DISTANCE (M)’, u’P&L’, u’TRANSACTION’, u’DOWNTME’]




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: