完整代码如下，有时间再总结细节：
#coding=utf-8
import xlrd
import xlwt
from xlutils.copy import copy
from copy import deepcopy
class DataOutput_Helper:
'''It's a data structure about scenario.
there are many methods provioded to make output the excel data easily'''
def __init__(self,project,gvalue,lvalue,ip,mode,paraName,inputP,outputP,formula,scen):
self.__project=project
self.__gvalue=gvalue
self.__lvalue=lvalue
self.__ip=ip
self.__mode=mode
self.__paraName=paraName
self.__inputP=inputP
self.__outputP=outputP
self.__formula=formula
self.__scen=scen
print self.__scen
def get_scen_num(self):
return len(self.__scen.keys())
def get_scen_name(self):
return self.__scen.keys()
def get_rawdata_by_index(self,scenName,index):
return [self.__ip[index],self.__mode[index],self.__paraName[index],self.__inputP[index],self.__outputP[index],self.__formula[index],\
self.__scen[scenName]['param'][index],self.__scen[scenName]['period'][index]]
def get_data_num(self,scenName):
m=0
for n in range(len(self.__scen[scenName]['param'])):
if self.__scen[scenName]['param'][n]!='' or self.__scen[scenName]['period'][n]!='':
m+=1
return m
def get_project_name(self):
return self.__project
def get_data_by_index(self,scenName,index):
if self.__scen[scenName]['param'][index]=='' and self.__scen[scenName]['period'][index]=='':
return None
elif self.__mode[index]>1:
master=self.__ip[index]+str(self.__mode[index])
elif self.__mode[index]==1:
master=self.__ip[index]
paralist=[]
for n in self.__scen[scenName]['param'][index].split(','):
paralist.append(n.split('='))
formula_re=self.__formula[index]
for n in range(len(paralist)):
formula_re=formula_re.replace(paralist[n][0],str(paralist[n][1]))
for n in range(len(self.__gvalue)):
formula_re=formula_re.replace(self.__gvalue[n][0],str(self.__gvalue[n][1]))
for n in range(len(self.__lvalue)):
formula_re=formula_re.replace(self.__lvalue[n][0],str(self.__lvalue[n][1]))
return [master,formula_re,round(eval(formula_re),2)]
class readexcel:
def __init__(self,filename='scenario.xls'):
self.__filename__=filename
self.__book__=xlrd.open_workbook(\
filename,formatting_info=True)
self.__sheetnames__=[]#sheet名字
self.__VPparam__=[]
self.__VPperiod__=[]
self.__VRparam__=[]
self.__VRperiod__=[]
self.__gvalue=[]
self.__lvalue=[]
for i in self.__book__.sheet_names():
if i.lower()=='config'or i.lower()=='bw':
sheet=self.__book__.sheet_by_name(i)
for row in range(0,sheet.nrows):
for col in range(0,sheet.ncols):
###-----------读取config参数---------------------------
#-------读取变量定位符project，#key，#value，#formx x(0-~)
if sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='project':
projectName=sheet.cell_value(row,col+1)
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='#key':
#--------读取全局参数key-value对
for key_row in range(row+1,sheet.nrows):
if sheet.cell_value(key_row,col)!='':
self.__gvalue.append([sheet.cell_value(key_row,col),sheet.cell_value(key_row,col+1)])
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='#form':
#--------读取表格参数formx 自动判定宽度和高度
formx_width=1
formx_height=1
for formx_col in range(col,sheet.ncols):
if sheet.cell_value(row+2,formx_col)=='':
formx_width=formx_col-col
break
elif formx_col==sheet.ncols-1:
formx_width=formx_col-col+1
#print 'w',formx_width
for formx_row in range(row+1,sheet.nrows):
if sheet.cell_value(formx_row,col)=='':
formx_height=formx_row-row
break
elif formx_row==sheet.nrows-1:
formx_height=formx_row-row
#print 'h',formx_height
#----------将表格数据读入__formx__
if sheet.cell_value(formx_row,col+2)!='':
self.__lvalue.append([sheet.cell_value(formx_row,col+2),sheet.cell_value(formx_row,col+1)])
###-----------读取BW参数---------------------------
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='ip':
ip=[]
i=0
for r in range(row+1,sheet.nrows):
if sheet.cell_value(r,col)=='':
ip.append(ip[i-1])
else:
ip.append(sheet.cell_value(r,col))
i+=1
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='mode':
mode=[]
i=0
for r in range(row+1,sheet.nrows):
if sheet.cell_value(r,col)=='':
mode.append('N/A')
else:
mode.append(sheet.cell_value(r,col))
i+=1
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='param'and sheet.cell_value(row,col-1)=='Mode':
paramName=[]
i=0
for r in range(row+1,sheet.nrows):
if sheet.cell_value(r,col)=='':
paramName.append('N/A')
else:
paramName.append(sheet.cell_value(r,col).split(','))
i+=1
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='input':
inputP=[]
i=0
for r in range(row+1,sheet.nrows):
if sheet.cell_value(r,col)=='':
inputP.append('N/A')
else:
inputP.append(sheet.cell_value(r,col))
i+=1
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='output':
outputP=[]
i=0
for r in range(row+1,sheet.nrows):
if sheet.cell_value(r,col)=='':
outputP.append('N/A')
else:
outputP.append(sheet.cell_value(r,col))
i+=1
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='bw formula':
formula=[]
i=0
for r in range(row+1,sheet.nrows):
if sheet.cell_value(r,col)=='':
formula.append('N/A')
else:
formula.append(sheet.cell_value(r,col))
i+=1
##***********************************Scen数据结构***************************************************##
## 存储IP的结构： 1.Scen名字 2.参数 3.时间 {'name':{'param':[xx,xx,xx],'period':[xx,xx,xx]},.....}
##Exp:{u'Video Recoding': {'period': [33.0, ], 'param': [u'arg1=3', ]}, }
##
##*************************************************************************************************##
elif sheet.cell_type(row,col)==xlrd.XL_CELL_TEXT and sheet.cell_value(row,col).lower()=='#sce':
scen={}
for c in range(col+1,sheet.ncols):
if sheet.cell_value(row,c)!='':
for c1 in range(c,c+2):
for r in range(row+2,sheet.nrows):
if c1==c:
self.__VPparam__.append(sheet.cell_value(r,c1))
elif c1==c+1:
self.__VPperiod__.append(sheet.cell_value(r,c1))
scen.setdefault(sheet.cell_value(row,c),{}).__setitem__('param',self.__VPparam__[:])
scen.setdefault(sheet.cell_value(row,c),{}).__setitem__('period',self.__VPperiod__[:])
del self.__VPparam__[:]
del self.__VPperiod__[:]
#******************************绘制表格**************************************************************#
self.__Wbook__=copy(self.__book__)
#self.__Wbook__=xlwt.Workbook()
xlwt.add_palette_colour('style1',22)
self.__Wbook__.set_colour_RGB(22,189,183,107)
xlwt.add_palette_colour('style2',23)
self.__Wbook__.set_colour_RGB(23,25,25,112)
xlwt.add_palette_colour('style3',24)
self.__Wbook__.set_colour_RGB(24,240,230,140)
style1 = xlwt.easyxf('pattern: pattern solid, fore_colour style1,back_colour black;'
'font: colour style2, bold True;'
'borders: left 0x0d , right 0x0d, top 0x0d, bottom 0x0d;'
'alignment: horz center,vert center')
style2 = xlwt.easyxf(
'font: colour style2, bold False;'
'borders: left 0x0d,left_colour black , right 0x0d, top 0x0d, bottom 0x0d;'
'alignment: horz center,vert center')
style3 = xlwt.easyxf('pattern: pattern solid, fore_colour style3,back_colour black;'
'font: colour style2, bold True;'
'borders: left 0x0d , right 0x0d, top 0x0d, bottom 0x0d;'
'alignment: horz center,vert center')
bw=DataOutput_Helper(projectName,self.__gvalue,self.__lvalue,ip,mode,paramName,inputP,outputP,formula,scen)
#print bw.get_scen_name()
for n in bw.get_scen_name():#scen
sheet=self.__Wbook__.add_sheet(n,cell_overwrite_ok=True)
#构建表格，构建固定位置内容
sheet.write(0,0,'Scenario\n'+n,style1)
sheet.row(0).height=550
sheet.col(0).width=4000
sheet.col(4).width=4000
sheet.write_merge(0,0,1,6,'BW Requirement',style1)
for k in range(bw.get_data_num(n)):
sheet.row(2+k).height=800
sheet.write(1,1,'Master',style1)
sheet.write_merge(1,1,2,3,'BW(MB/s)',style1)
sheet.write(1,5,'Master',style1)
sheet.write(1,6,'BW(MB/s)',style1)
#print n
total=0
for m in range(bw.get_data_num(n)):#ip number
#print m
master,BW,Result=bw.get_data_by_index(n,m)
print bw.get_data_by_index(n,m)
for l in [1,5]:
sheet.write(2+m,l,master,style3)
sheet.write(2+m,2,BW,style3)
sheet.write(2+m,6,'',style3)
sheet.write(2+m,3,Result,style3)
total=total+Result
sheet.write_merge(1,bw.get_data_num(n)+2,0,0,bw.get_project_name()+'\n estimate \nBW(MB/s)',style2)
sheet.write_merge(1,bw.get_data_num(n)+2,4,4,bw.get_project_name()+'\n simulation \nBW(MB/s)',style2)
#填写Total
for t in [1,5]:
sheet.write(bw.get_data_num(n)+2,t,'Total',style3)
sheet.write(bw.get_data_num(n)+2,t+1,'',style3)
sheet.write(bw.get_data_num(n)+2,3,total,style3)#计算总和
sheet.col(2).width=17000 #BW formula
self.__Wbook__.save(filename)
n=readexcel()