Python利用xlrd模板读取excel

 1 #!/usr/bin/env python
#coding:utf-8
import xlrd,os,datetime

data=xlrd.open_workbook(u"\\\\10.25.1.9\\Dpt_IT\\基础架构部\\服务器_系统\\3.配置信息\\IDC设备配置信息列表.xlsx") #打开excel文件,Windows下
table=data.sheet_by_index(0) #通过索引选择第一个sheet
nrows=table.nrows #行数
ncols=table.ncols #列数

item=["Item","HostName","IP","外部IP","域名","环境","描述","类型","基础运维负责人","应用管理员","位置","设备类型","硬件厂商","型号","SN","CPU","DISK","MEM","OS","APP","开放端口","备注","日期"]
key=[chr(i) for i in range(65,88)] #生产列名A,B,C,D,E...W
choice=dict(zip(key,item))
ncols_dir=dict(zip(key,range(len(choice))))
data_list=[]
for i in range(5,nrows):
    data_list.append(table.row_values(i)) #生成每行数据组成的列表

def show(chosen):
    chosen=chosen.split(',')
    chosen=[i.upper() for i in chosen]
    print "*"*len(chosen)*50
    for i in range(4,nrows):
        for j in chosen:
            ctype=table.cell(i,ncols_dir[j]).ctype
            if ctype == 3:
                date_value=xlrd.xldate_as_tuple(table.cell(i,ncols_dir[j]).value,data.datemode)
                date_value=datetime.date(*date_value[:3]).strftime("%Y/%m/%d")
                print "%-50s" % date_value,
            elif ctype == 0:
                print "%-50s" % "NULL",
            else:
                print "%-50s" % table.cell(i,ncols_dir[j]).value, #打印每行指定列的元素
        print

while True:
    print '''
    ******************************************
                    1.query
                    2.see all
                    3.exit
    '''
    print
    chosen_num=raw_input("Pls enter ur choice:")
    #类型: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
    if chosen_num == '1':
        for i,j in sorted(choice.items()):
            print i+"--->"+j
        chosen=raw_input("Pls enter ur choice(以,分隔,不区分大小写):")
        show(chosen)
    elif chosen_num == '2':
        chosen=','.join(key)
        show(chosen)
    elif chosen_num == '3':
        exit(0)
    elif chosen_num == '':
        pass
    else:
        pass