在我的工作中，有时候要处理大批量的数据的excel表格，通常是几万行的，如果手工做的话，大概需要半天时间，而且还不一定能保证不出错，如果是筛选数据的话，还不能对筛选好的数据直接进行求取平均值的需求，必须拷贝到另一张表里进行平均值操作。下面我针对具体的需求，来说一下python是如何处理的。
需求：对OPER、STATENAME两列进行筛选，得到CPU、ELAPSE两列的数据，然后求出它们的平均值，最好有几个OPER生成几张表
代码：
1 import xlrd,sys
2 from pyExcelerator import *
3 OPER = 5
4 CPU = 6
5 ELAPSE = 8
6 STATENAME = 15
7 FILENAME = "WUPHG1HTC.xls"
8 class CpuTestTime:
9 StateTable = {}
10 OperList = []
11 def __init__(self,filename):
12 self.fname = filename
13
14 def openExcelFile(self,index):
15 self.bk = xlrd.open_workbook(self.fname)
16 self.sh = self.bk.sheets()[index]
17 self.nrows = self.sh.nrows
18 self.dic3 = {}
19
20 def cpuTestTime(self):
21 for i in range(1,self.nrows):
22 if not str(self.sh.row(i)[OPER])[7:-1] in CpuTestTime.OperList: #oper
23 CpuTestTime.OperList.append(str(self.sh.row(i)[OPER])[7:-1])
24 CpuTestTime.StateTable[str(self.sh.row(i)[OPER])[7:-1]]=[]
25 for i in range(1,self.nrows):
26 if not str(self.sh.row(i)[STATENAME])[7:-1] in CpuTestTime.StateTable[str(self.sh.row(i)[OPER])[7:-1]]:#statename
27 CpuTestTime.StateTable[str(self.sh.row(i)[OPER])[7:-1]].append(str(self.sh.row(i)[STATENAME])[7:-1])
28
29 dic1 ={}
30 dic2 ={}
31 for key,values in CpuTestTime.StateTable.items():
32 for value in values:
33 for tt in ['ELAPSED_TIME','CPU_ELAPSED_TIME','COUNT']:
34 dic1.update({tt:0})
35 dic2.update({value:dic1.copy()})
36 self.dic3.update({key:dic2.copy()})
37
38 for i in xrange(1,self.nrows):
39 self.dic3[str(self.sh.row(i)[OPER])[7:-1]][str(self.sh.row(i)[STATENAME])[7:-1]]['ELAPSED_TIME'] += self.sh.row(i)[ELAPSE].value
40 self.dic3[str(self.sh.row(i)[OPER])[7:-1]][str(self.sh.row(i)[STATENAME])[7:-1]]['CPU_ELAPSED_TIME'] += self.sh.row(i)[CPU].value
41 self.dic3[str(self.sh.row(i)[OPER])[7:-1]][str(self.sh.row(i)[STATENAME])[7:-1]]['COUNT'] += 1
42
43 for key,values in CpuTestTime.StateTable.items():
44 for value in values:
45 for tt in ['ELAPSED_TIME','CPU_ELAPSED_TIME']:
46 self.dic3[key][value][tt]=self.dic3[key][value][tt]/self.dic3[key][value]['COUNT']
47
48 def saveResult(self):
49 wb = Workbook()
50 for key,values in CpuTestTime.StateTable.items():
51 loop = 1
52 ws = wb.add_sheet(key)
53 ws.write(0,0,'STATE')
54 ws.write(0,1,'ELAPSED_TIME')
55 ws.write(0,2,'CPU_ELAPSED_TIME')
56 ws.write(0,3,'COUNT')
57 for value in values:
58 ws.write(loop,0,value)
59 ws.write(loop,1,round(self.dic3[key][value]['ELAPSED_TIME'],2))
60 ws.write(loop,2,round(self.dic3[key][value]['CPU_ELAPSED_TIME'],2))
61 ws.write(loop,3,self.dic3[key][value]['COUNT'])
62 loop = loop+1
63 wb.save('result'+FILENAME)
64
65 if "__main__" == __name__:
66 excelFile = CpuTestTime(FILENAME)
67 excelFile.openExcelFile(0)
68 excelFile.cpuTestTime()
69 excelFile.saveResult()
首先讲一下两个库：xlrd 和 pyExcelerator，xlrd从名字上来看就是读excel比较方便的，而pyExcelerator对于生成excel并写excel很方便。让我们分析一下上述代码的两个函数：
1 def openExcelFile(self,index):
2 self.bk = xlrd.open_workbook(self.fname)
3 self.sh = self.bk.sheets()[index]
4 self.nrows = self.sh.nrows
5 self.dic3 = {}
行2是打开一个文件名为self.fname的excel工作薄，行3是获取工作薄中的第（index+1）张表，因为一个工作薄里可以新建好几张表，它们的索引从0开始，行4记录了这张表里数据的行数。下面读取每个表格的值就很容易了,sh.row(i)[3]这个就代表第i行的第4列.特别注意sh.row(i)[3][7:-1]才能取到正确的字符串值，和excel的编码有关。
1 def saveResult(self):
2 wb = Workbook()
3 for key,values in CpuTestTime.StateTable.items():
4 loop = 1
5 ws = wb.add_sheet(key)
6 ws.write(0,0,'STATE')
7 ws.write(0,1,'ELAPSED_TIME')
8 ws.write(0,2,'CPU_ELAPSED_TIME')
9 ws.write(0,3,'COUNT')
10 for value in values:
11 ws.write(loop,0,value)
12 ws.write(loop,1,round(self.dic3[key][value]['ELAPSED_TIME'],2))
13 ws.write(loop,2,round(self.dic3[key][value]['CPU_ELAPSED_TIME'],2))
14 ws.write(loop,3,self.dic3[key][value]['COUNT'])
15 loop = loop+1
16 wb.save('result'+FILENAME)
上面这段代码，行2创建一个excel工作薄，行5增加一个表，行6写cell(0,0)单元格，值为最后一个参数'STATE'。行16保存到文件。
这种方式处理excel表比较好懂，也比较简单，当然，也可以用windows的COM来处理，不过就没有这么简单了~
联系客服
