Python handles Excel (iv): Custom classes handle Excel data

Complete the code below and have time to summarize the details:

#coding =utf-8import xlrdimport xlwtfrom xlutils.copy import copyfrom copy import Deepcopyclass dataoutput_helper: "'   It ' s a data structure about scenario. There is many methods provioded to make output the Excel data easily ' Def __init__ (self,project,gvalue,lvalue,ip,mode,pa Raname,inputp,outputp,formula,scen): self.__project=projectself.__gvalue=gvalueself.__lvalue=lvalueself.__ip= Ipself.__mode=modeself.__paraname=paranameself.__inputp=inputpself.__outputp=outputpself.__formula=formulaself . __scen=scenprint self.__scendef 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=0for N in range (len (self._ _scen[scenname][' param ')): if self.__scen[scenname][' param'][n]!= ' or self.__scen[scenname][' period '][n]!= ': M+=1return mdef get_project_name (self): return self.__projectdef Get_data_by_index (Self,scenname,index): If self.__scen[scenname][' param '][index]== ' and self.__scen[scenname][' Period '][index]== ': Return noneelif 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 (', ') :p aralist.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 :d EF __init__ (self,filename= ' Scenario.xls '): Self.__filename__=filenameself.__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): # # #-----------read the config parameter---------------------------#-------read the variable locator project, #key, #value, #formx X (0-~) if Sheet.cell_type (Row,col) ==xlrd. Xl_cell_text and Sheet.cell_value (row,col). lower () = = ' Project ':p rojectname=sheet.cell_value (row,col+1) elif Sheet.cell_type (Row,col) ==xlrd. Xl_cell_text and Sheet.cell_value (row,col). lower () = = ' #key ': #--------Read global parameters Key-value to 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 ': #--------Read table Parameters formx Auto-judgmentWidth and height formx_width=1formx_height=1for formx_col in range (col,sheet.ncols): If Sheet.cell_value (row+2,formx_col) = = ': Formx_width=formx_col-colbreakelif Formx_col==sheet.ncols-1:formx_width=formx_col-col+1#print ' W ', formx_widthfor Formx_row in Range (row+1,sheet.nrows): If Sheet.cell_value (formx_row,col) = = ": Formx_height=formx_row-rowbreakelif Formx_row==sheet.nrows-1:formx_height=formx_row-row#print ' h ', formx_height#----------reading tabular data into __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)]) # # #-----------Read the BW parameter---------------------------elif sheet.cell_type (row,col) ==xlrd. Xl_cell_text and Sheet.cell_value (row,col). lower () = = ' IP ': ip=[]i=0for 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+=1elif sheet.cell_type (row,col) = = Xlrd. Xl_cell_text and Sheet.cell_value (row,col). lower () = = ' mode ': Mode=[]i=0for 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+=1elif 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 ':p aramname=[]i= 0for r in range (Row+1,sheet.nrows): If Sheet.cell_value (r,col) = = ":p aramname.append (' N/A ') else:paramName.append ( Sheet.cell_value (R,col). Split (', ')) I+=1elif Sheet.cell_type (Row,col) ==xlrd. Xl_cell_text and Sheet.cell_value (row,col). lower () = = ' Input ': inputp=[]i=0for 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+=1elif Sheet.cell_ Type (Row,col) ==xlrd. Xl_cell_text and Sheet.cell_value (row,col). lower () = = ' Output ': Outputp=[]i=0for 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=0for 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 Structure of ##***********************************scen data structure ***************************************************#### storage IP: 1.                                        Scen Name 2. Parameter 3. Time {' 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__[:]#************************* Draw Table ************************************************************** #self. __wbook__=copy (self.__book__) # SELF.__WBOOK__=XLWT. Workbook () xlwt.add_palette_colour (' Style1 ', ') Self.__wbook__.set_colour_rgb (22,189,183,107) Xlwt.add_palette_ Colour (' style2 ', ') Self.__wbook__.set_colour_rgb (23,25,25,112) xlwt.add_palette_colour (' Style3 ') 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 (): #scensheet =self.__wbook __.add_sheet (n,cell_overwrite_ok=true) #构建表格, build fixed-position content sheet.write (0,0, ' scenario\n ' +n,style1) sheet.row (0). height= 550sheet.col (0). Width=4000sheet.col (4). Width=4000sheet.write_merge (0,0,1,6, ' BW requirement ', style1) for K in range ( Bw.get_data_num (n)): Sheet.row (2+k). Height=800sheet.write (the "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 ntotal=0for m in range (bw.get_ Data_num (n)): #ip number#print mmaster,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+resultsheet.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) #填写Totalfor 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 formulaself.__wbook__.save (filename) n=readexcel ()

Python handles Excel (iv): Custom classes handle Excel data