python 解析execl文件并写入新文件-手有余香-51CTO博客

#!/usr/bin/env python#coding:utf-8import osimport xlrd,xlwtimport requests

NatExeclFile=os.getcwd() + '/all_nat.xlsx'

def ReadAllNat(filename):natExecl = xlrd.open_workbook('%s' % NatExeclFile)sheetNames = natExecl.sheets()[1:]sheetNames.pop(2)f = xlwt.Workbook() # 创建一个新文件写入

for se in sheetNames:
    new_sheet = f.add_sheet(se.name,cell_overwrite_ok=True)
    for i in range(3):
        new_sheet.col(i).width = 256 * 60
    cols = se.col_values(1)
    for i,v in enumerate(cols):
        mip = v.split()[2]
        port = v.split()[4]
        try:
            firstline = se.col_values(0)[i]
            modulename = getModule(mip,port)
        except Exception ,e:
            print "error : " , mip,port,modulename,' not found'
            modulename = 'vip & port not found '

        row_list = [firstline,v,modulename]
        for c, v in enumerate(row_list):
            print i, c, v
            new_sheet.write(i, c, v)
    f.save(filename)

'''
web 防火墙
'''
web_sheet = natExecl.sheets()[3]
cols = web_sheet.col_values(3)
set_mip = set()
for v in cols:
    set_mip.add(v)
#print web_sheet.name ,len(set_mip),set_mip

'''

'''
oneDay = natExecl.sheets()[0]
mip_set = set()
cols = oneDay.col_values(oneDay.ncols -1)

for i,v in enumerate(cols ):
    if v == u'目的:':
        mip = cols[i + 1]
        mip_set.add(mip)

def web_xml(filename):natExecl = xlrd.open_workbook('%s' % NatExeclFile)f = xlwt.Workbook() # 创建一个新文件写入new_sheet = f.add_sheet('web-120.246')

for i in range(5):
    new_sheet.col(i).width = 256 * 30
'''
web 防火墙
'''
web_sheet = natExecl.sheets()[3]
cols = web_sheet.col_values(3)
for i,v in enumerate(cols):
    mip = v
    firsline = web_sheet.col_values(0)[i]
    secondline = web_sheet.col_values(1)[i]
    thirdline = web_sheet.col_values(2)[i]
    port = thirdline.split('/')[0]
    try:
        modulename = getModule(mip,port)
    except Exception , e:
        print 'error ', mip,port
        modulename = 'vip & port not fount'
    print firsline,secondline,port,mip,modulename
    row_list = [firsline,secondline,thirdline,port,mip,modulename]
    for c,v in enumerate(row_list):
        new_sheet.write(i,c,v)
        print i,c,v

f.save(filename)

def dayleeter(filename):natExecl = xlrd.open_workbook('%s' % NatExeclFile)f = xlwt.Workbook() # 创建一个新文件写入new_sheet = f.add_sheet(u'一期')for i in range(3):new_sheet.col(i).width = 256 * 30

oneDay = natExecl.sheets()[0]
cols = oneDay.col_values(oneDay.ncols - 1)
mip_list = []
modulename_list = []
for i, v in enumerate(cols):
    if v == u'目的:':
        mip = cols[i + 1]
        modulename = getModule(mip)
        mip_list.append(mip)
        modulename_list.append(modulename)
        print mip,modulename
for i in range(len(mip_list)):
    mip = mip_list[i]
    modulename = modulename_list[i]
    row_list = [mip,modulename]
    print mip,modulename
    for c,v in enumerate(row_list):

        new_sheet.write(i,c,v)
f.save(filename)

def getModule(ip,port=None):if port:url = "http://10.97.200.114:8080/lvs/v1/vs/?vip=%s" % ipres = requests.get(url)if res.content != '[]':url = "http://10.97.200.114:8080/lvs/v1/vs/?vip=%s&service_port=%s" % (ip,port)res = requests.get(url)try:modulename = res.json()[0]['modulename']except Exception ,e:modulename = 'error vip & port not fetch 'else:url = "http://10.97.200.8/api/resource/findModuleAllInfo?moduleip=" + ipres = requests.get(url)module_info = res.json()['ModuleAllInfo']if module_info :modulename = module_info[0]['moduleInfo']['modulename']else:modulename = module_inforeturn modulename

if name == 'main':

dayleeter('oneDayLetter.xls')