finalist

python⼀些⽅便excel⾏操作的函数（⼀）

importcollections

classheadhandler():

def__init__(lf,mylist):

age={}

=mylist

defdelempty(lf):

'''

去除重复

:return:

'''

while"":

("")

defformatmydata(lf,i):

try:

i=e("：","")

exceptException:

i=i

returni

deffillempty(lf):

'''

只⽤于处理表头信息

:return:

'''

#对于不规则列表的处理办法，如果元素的下⼀个元素仍是字符串类型，或者不存在

#就插⼊或者⽤0填充

ty()

:

myindex=(i)

ifmyindex==0or(myindex%2==0):

try:

nextelement=[myindex+1]

ifisinstance([myindex+1],str):

(myindex+1,0)

exceptIndexError:

(0)

=list(map(mydata,))

print()

defturntodict(lf):

pty()

[::2]:

age[i]=[(i)+1]

age

deffinalchart(lf):

ty()

=list(map(mydata,))

#print()

finalchart=dict()

#print(finalchart)

returnfinalchart

classrowhandler(headhandler):

def__init__(lf,mylist):

super(rowhandler,lf).__init__(mylist)

deffillempty(lf):

ty()

staticdict={}

formyindex,myelementinenumerate():

ifmyelementinstaticdict:

staticdict[myelement].append(myindex)

el:

staticdict[myelement]=[]

staticdict[myelement].append(myindex)

foriinlist(()):

iflen(staticdict[i])==1:

delstaticdict[i]

el:

[staticdict[i][0]]=[staticdict[i][0]]+'重量'

[staticdict[i][1]]=[staticdict[i][1]]+'含量'

[staticdict[i][2]]=[staticdict[i][2]]+'价格'

defturntodict(lf):

pty()

[::2]:

age[i]=[(i)+1]

age

#mylist=['采购⽇期：','',43495.0,'','','','','','','','','','','','','','','索赔⾦额：','','','','','',0.0,'','']

mydict={'a':[1,2],'b':[2,3,4]}

foriinlist(()):

print(mydict[i])

iflen(mydict[i])>2:

delmydict[i]

print(mydict)

#print(('每吨⼈⼯：'))

输出结果:

[1,2]

[2,3,4]

{'a':[1,2]}

fromanewclassimport*

classdocgen:

def__init__(lf,mylist):

=mylist[1::]

line=mylist[0]

t=[]#⽤于承载⾮规则⾏信息

=[]

defaddstring(lf):

mycounter=dict(r(line))

keypos=[]

finalist=[]

():

ifmycounter[i]>1:

formyindex,myelementsinenumerate(line):

ifmyelements==i:

(myindex)

ifmyindex==len(line)-1:

(keypos)

keypos=[]

foriinfinalist:

line[i[0]]=line[i[0]]+"重量"

line[i[1]]=line[i[1]]+"含量"

line[i[2]]=line[i[2]]+"价格"

line

defgenmx(lf):

ing()

:

ifi[0]=="":

myhandler=rowhandler(i)

(dict())

el:

myrow=rowhandler(line)

line=pty()

(dict(zip(line,i)))

defreturnall(lf):

()

return{'mx':,'others':t}

wuwa=[

['品名','采购价','每吨成本','重量','货品总成本','铜重量','铝重量','⽚重量','⽆限长','锄头马','铁重量','铜含量','铝含量','⽚含量','⽆限长',

'锄头马','铁含量','铜价格','铝价格','⽚价格','⽆限长','锄头马','铁价格','产值','每吨⽑利','货品赢利'],

['铜芯',0.72,11956.0,19.617,234540.852,4.665,0.068,4.706,0.506,1.386,1.63,0.23788,0.9796094,

0.23989396951623593,0.377682,0.64321,0.27592,39200.0,7000.0,5050.0,4500.0,2750.0,1800.0,

11791.65009940358,-164.3499005964204,-3224.],

['','','','','','','23尖⾓',1.157,'35尖⾓',1.766,'','','23尖⾓',0.770706,'35尖⾓',0.23515,'','',

'23尖⾓',5000.0,'35尖⾓',3500.0,'','','',''],

['','','','','','','35平⾓',1.073,'','','','','35平⾓',0.91354,'','','','','35平⾓',3000.0,'','','','',

'','']

]

saiwa=docgen(wuwa)

print("===============mx===================")

all()['mx']:

print(i)

print("===============others===================")

all()['others']:

print(i)

输出结果：

[1,2]

[2,3,4]

{'a':[1,2]}

===============mx===================

{'品名':'铜芯','采购价':0.72,'每吨成本':11956.0,'重量':19.617,'货品总成本':234540.852,'铜重量':4.665,'铝重量':0.068,'⽚重量':4.706,

'⽆限长重量':0.506,'锄头马重量':1.386,'铁重量':1.63,'铜含量':0.23788,'铝含量':0.9796094,'⽚含量':

0.23989396951623593,'⽆限长含量':0.377682,'锄头马含量':0.64321,'铁含量':0.27592,'铜价格':39200.0,

'铝价格':7000.0,'⽚价格':5050.0,'⽆限长价格':4500.0,'锄头马价格':2750.0,'铁价格':1800.0,'产值':11791.65009940358,'每吨⽑利':

-164.3499005964204,'货品赢利':-3224.}

===============others===================

{'23尖⾓重量':1.157,'35尖⾓重量':1.766,'23尖⾓含量':0.770706,'35尖⾓含量':0.23515,'23尖⾓价格':5000.0,

'35尖⾓价格':3500.0}

{'35平⾓重量':1.073,'35平⾓含量':0.91354,'35平⾓价格':3000.0}

defreadexcel(path):

datablock=_excel(path,sheet_name=0)

print(len(datablock))

wenwa=(2)

print(type())

print(.__dict__)

print("columns",s[0])

print("columns",(2).columns)

defloadexcel(path):

mysheet=_workbook(path)

mybook=_by_index(0)

#print(_values(0))

colnamelist=_values(2)

row3=_values(3)

#print(dict(zip(colnamelist,row3)))

allrets=[]

foriinrange():

#print(_values(i))

(_values(i))

print()

foriinallrets:

#print(i)

pass

returnallrets

defmergerows(mylist):

splitline=0

doc={}

foriinmylist:

print(i)

k='每吨⼈⼯：'

ifkini:

print('in:',(i))

splitline=(i)

doc["mx"]=mylist[2:splitline-1]

doc["header"]=mylist[splitline:]

returndoc

duwa=loadexcel('⽕烧⽚3272441铜芯.csv')

doc=mergerows(duwa)

foriindoc['header']:

print(i)

print("==================mx=============================")

foriindoc['mx']:

print(i)

defdealmx(mylist):

ifmylist[0]=='':

pass

mylist1=['品名','采购价','每吨成本','重量','货品总成本','铜重量','铝重量','⽚重量','⽆限长','锄头马','铁重量','铜含量','铝含量','⽚含量',

'⽆限长','锄头马','铁含量','铜价格','铝价格','⽚价格','⽆限长','锄头马','铁价格','产值','每吨⽑利','货品赢利']

mylist2=['铜芯',0.72,11956.0,19.617,234540.852,4.665,0.068,4.706,0.506,1.386,1.63,0.23788,0.9796094,

0.23989396951623593,0.377682,0.64321,0.27592,39200.0,7000.0,5050.0,4500.0,2750.0,1800.0,

11791.65009940358,-164.3499005964204,-3224.]

print(dict(zip(mylist1,mylist2)))

print(r(mylist1))

print(('⽆限长'))

defaddstring(mylist):

mycounter=r(mylist)

keypos=[]

finalist=[]

():

ifmycounter[i]>1:

formyindex,myelementsinenumerate(mylist):

ifmyelements==i:

(myindex)

ifmyindex==len(mylist)-1:

(keypos)

keypos=[]

foriinfinalist:

mylist[i[0]]=mylist[i[0]]+"重量"

mylist[i[1]]=mylist[i[1]]+"含量"

mylist[i[2]]=mylist[i[2]]+"价格"

returnmylist

print(addstring(mylist1))

mycounter=r(mylist1)

print(dict(mycounter))

输出结果：

12

['⽕烧⽚','','','','','','','','','','','','','','','','','','','','','','','','','']

['','','','','','','','','','','','','','','','','','','','','','','','','','']

['品名','采购价','每吨成本','重量','货品总成本','铜重量','铝重量','⽚重量','⽆限长','锄头马','铁重量','铜含量','铝含量','⽚含量','⽆限长',

'锄头马','铁含量','铜价格','铝价格','⽚价格','⽆限长','锄头马','铁价格','产值','每吨⽑利','货品赢利']

['铜芯',0.72,11956.0,19.617,234540.852,4.665,0.068,4.706,0.506,1.386,1.63,0.23788,0.9796094,

0.23989396951623593,0.377682,0.64321,0.27592,39200.0,7000.0,5050.0,4500.0,2750.0,1800.0,

11791.65009940358,-164.3499005964204,-3224.]

['','','','','','','23尖⾓',1.157,'35尖⾓',1.766,'','','23尖⾓',0.770706,'35尖⾓',0.23515,'','',

'23尖⾓',5000.0,'35尖⾓',3500.0,'','','','']

['','','','','','','35平⾓',1.073,'','','','','35平⾓',0.91354,'','','','','35平⾓',3000.0,'','','','','',

'']

['','','','','','','','','','','','','','','','','','','','','','','','','','']

['','','','','','','','','','','','','','','','','','每吨⼈⼯：','','','','','总⼈⼯',0.0,'','']

in:7

['采购⽇期：','',43495.0,'','','','','','','','','','','','','','','索赔⾦额：','','','','','',0.0,'','']

['计算⽇期：','',43594.0,'','','','','','','','','','','','','','','总成本：','','','','','',234540.852,'','']

['','','','','','','','','','','','','','','','','','总利润：','','','','','',-3224.,'','']

['','','','','','','','','','','','','','','','','','利润百分⽐：','','','','','',-0.281063,'','']

['','','','','','','','','','','','','','','','','','每吨⼈⼯：','','','','','总⼈⼯',0.0,'','']

['采购⽇期：','',43495.0,'','','','','','','','','','','','','','','索赔⾦额：','','','','','',0.0,'','']

['计算⽇期：','',43594.0,'','','','','','','','','','','','','','','总成本：','','','','','',234540.852,'','']

['','','','','','','','','','','','','','','','','','总利润：','','','','','',-3224.,'','']

['','','','','','','','','','','','','','','','','','利润百分⽐：','','','','','',-0.281063,'','']

==================mx=============================

['品名','采购价','每吨成本','重量','货品总成本','铜重量','铝重量','⽚重量','⽆限长','锄头马','铁重量','铜含量','铝含量','⽚含量','⽆限长',

'锄头马','铁含量','铜价格','铝价格','⽚价格','⽆限长','锄头马','铁价格','产值','每吨⽑利','货品赢利']

['铜芯',0.72,11956.0,19.617,234540.852,4.665,0.068,4.706,0.506,1.386,1.63,0.23788,0.9796094,

0.23989396951623593,0.377682,0.64321,0.27592,39200.0,7000.0,5050.0,4500.0,2750.0,1800.0,

11791.65009940358,-164.3499005964204,-3224.]

['','','','','','','23尖⾓',1.157,'35尖⾓',1.766,'','','23尖⾓',0.770706,'35尖⾓',0.23515,'','',

'23尖⾓',5000.0,'35尖⾓',3500.0,'','','','']

['','','','','','','35平⾓',1.073,'','','','','35平⾓',0.91354,'','','','','35平⾓',3000.0,'','','','','',

'']

{'品名':'铜芯','采购价':0.72,'每吨成本':11956.0,'重量':19.617,'货品总成本':234540.852,'铜重量':4.665,'铝重量':0.068,'⽚重量':4.706,

'⽆限长':4500.0,'锄头马':2750.0,'铁重量':1.63,'铜含量':0.23788,'铝含量':0.9796094,'⽚含量':0.23989396951623593,

'铁含量':0.27592,

'铜价格':39200.0,'铝价格':7000.0,'⽚价格':5050.0,'铁价格':1800.0,'产值':11791.65009940358,'每吨⽑利':-164.3499005964204,'货品赢利':

-3224.}

Counter({'⽆限长':3,'锄头马':3,'品名':1,'采购价':1,'每吨成本':1,'重量':1,'货品总成本':1,'铜重量':1,'铝重量':1,'⽚重量':1,

'铁重量':1,'铜含量':1,'铝含量':1,'⽚含量':1,'铁含量':1,'铜价格':1,'铝价格':1,'⽚价格':1,'铁价格':1,'产值':1,'每吨⽑利':1,

'货品赢利':1})

8

['品名','采购价','每吨成本','重量','货品总成本','铜重量','铝重量','⽚重量','⽆限长重量','锄头马重量','铁重量','铜含量','铝含量','⽚含量',

'⽆限长含量','锄头马含量','铁含量','铜价格','铝价格','⽚价格','⽆限长价格','锄头马价格','铁价格','产值','每吨⽑利','货品赢利']

{'品名':1,'采购价':1,'每吨成本':1,'重量':1,'货品总成本':1,'铜重量':1,'铝重量':1,'⽚重量':1,'⽆限长重量':1,'锄头马重量':1,

'铁重量':1,'铜含量':1,'铝含量':1,'⽚含量':1,'⽆限长含量':1,'锄头马含量':1,'铁含量':1,'铜价格':1,'铝价格':1,'⽚价格':1,

'⽆限长价格':1,'锄头马价格':1,'铁价格':1,'产值':1,'每吨⽑利':1,'货品赢利':1}