Python学习-Excel表格操作

一、安装xlrd模块
   到python官网下载http://pypi.python.org/pypi/xlrd模块安装，前提是已经安装了python 环境。
   安装命令： python setup.py install

二、使用介绍
  1、导入模块
      import xlrd

  2、打开Excel文件读取数据
       data = xlrd.open_workbook('excelFile.xls')

  3、使用技巧
        获取一个工作表
        table = data.sheets()[0]                #通过索引顺序获取
        table = data.sheet_by_index(0) #通过索引顺序获取
        table = data.sheet_by_name(u'Sheet1') #通过名称获取
 
        获取整行和整列的值（数组）　
        table.row_values(i)
        table.col_values(i)
 
        获取行数和列数
        nrows = table.nrows
        ncols = table.ncols
       
        循环行列表数据
        for i in range(nrows):
          print table.row_values(i)
 
获取单元格的值
cell_A1 = table.cell(0,0).value
cell_C4 = table.cell(2,3).value
 
使用行列索引获取单元格值
cell_A1 = table.row(0)[0].value

cell_A2 = table.col(1)[0].value

简单例子：

# -*- coding: utf-8 -*- import xlrdfrom pyExcelerator import *#创建一个新的spreedsheetdef createExcel(excelName):    workbook = Workbook()    workbook.add_sheet('Sheet01')    workbook.save(excelName)    return workbookif __name__ == '__main__':    workbook = createExcel('test.xlsx')    newSheet = workbook.add_sheet('Sheet02')    #在单元格中插入数据    newSheet.write(1, 1, 'tests')    workbook.save('test.xlsx')    #    打开Excel文件读取数据    data = xlrd.open_workbook('test01.xlsx')#    三种获取工作表的方法    table = data.sheets()[0]                #通过索引顺序获取    table = data.sheet_by_index(0)          #通过索引顺序获取    table = data.sheet_by_name(u'Sheet01')   #通过名称获取#   获取整行和整列的值（数组）    print table.row_values(0)    print table.col_values(0)    #    获取行数和列数    nrows=table.nrows    ncols=table.ncols    print nrows, ncols       #   循环行列表数据    for i in range(nrows):        print table.row_values(i) #   获取单元格的值方法一    cell_A1 = table.cell(0,0).value    cell_B2 = table.cell(1,1).value    print cell_A1,cell_B2 #   获取单元格的值方法二： 使用行列索引    cell_A1 = table.row(0)[0].value    cell_A2 = table.col(1)[0].value    print cell_A1,cell_A2    

两个excel表格的例子：

import xlrdfrom pyExcelerator import *fname_base = "C:/android/tools/Mars2/eclipse/ws/TcAssignment/src/com/moto/www/Base.xlsx"fname_rounds = "C:/android/tools/Mars2/eclipse/ws/TcAssignment/src/com/moto/www/Rounds.xlsx"bk_base = xlrd.open_workbook(fname_base)bk_rounds = xlrd.open_workbook(fname_rounds)#shxrange = range(bk_base.nsheets)try:    sh_base = bk_base.sheet_by_name("Sheet1")    sh_rounds = bk_rounds.sheet_by_name("Sheet1")except:    print "no sheet in %s named Sheet1" % fname_base#get row number nrow_base = sh_base.nrowsnrow_rounds = sh_rounds.nrows#get column number ncols_base = sh_base.ncolsncols_rounds = sh_rounds.ncolsprint "nrows %d, ncols %d" % (nrow_base, ncols_base)print "nrows %d, ncols %d" % (nrow_rounds, ncols_rounds)#get first row and first column data#cell_value = sh.cell_value(0,1)#print cell_value  row_list = []w = Workbook()  #create a workbookws = w.add_sheet('TcAssigment')  #create a spreedsheetfor j in range(0, nrow_rounds):#get data of each row    tc_rounds = sh_rounds.cell_value(j, 0)    print tc_rounds    for i in range(0, nrow_base):        base_tcs = sh_base.cell_value(i, 0)        base_name = sh_base.cell_value(i, 1)        print base_tcs == tc_rounds, '========'        if base_tcs == tc_rounds:            ws.write(j, 0, tc_rounds)            ws.write(j, 1, base_name)            break        else:            ws.write(j, 0, tc_rounds)w.save('TcsAssignmentResult.xls')  #save