利用python脚本对excel表格目标数据进行自动筛选计数

发表于 更新于

工作要求，需要对几十个格式相同的设备台账excel表格中电源数据、光端机起终点数据分别进行筛选计数，由于表格数据量极大，人工效率低，因此利用python脚本对这几十个表格进行自动筛选计数。

在写脚本的过程中，学到了很多python处理excel表格的相关技巧，写下此文帮助记忆。

xlrd/xlwt模块介绍

python操作excel主要用到xlrd和xlwt两个库。xlrd是读excel，xlwt是写excel。

xlrd/xlwt的安装

cmd窗口下：

1
2
pip install xlrd
pip install xlwt

xlrd/xlwt的使用

xlrd读excel：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# -*- coding=utf-8 -*-

import xlrd  #导入读excel的模块

#打开excel
filename='test.xlsx'
data=xlrd.open_workbook(filename,"rb")

#获取excel工作表
mysheets=data.sheets() #获取工作表list

#通过索引获取第一个sheet
mysheet=mysheets[0]

#通过索引顺序获取
#mysheet=data.sheet_by_index(0)

#通过名称获取
#mysheet=data.sheet_by_name(u'Sheet1')


#获取行数和列数
nrows=mysheet.nrows
print nrows
ncols=mysheet.ncols
print ncols

#获取一行和一列
#myRowValues=mysheet.row_values(0)
#print myRowValues
#myColValues=mysheet.col_values(0)
#print myColValues

#读取单元格数据
for i in range(ncols):
    for j in range(nrows):
        myCell=mysheet.cell(j,i)
        myCellValue=myCell.value
        print myCellValue

xlwt写excel：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# -*- coding=utf-8 -*-
#导入模块
import  xlwt

#创建excel工作薄
myWorkbook=xlwt.Workbook()

#添加Excel工作表
mySheet=myWorkbook.add_sheet("a Test Sheet")

#写入数据
myStyle=xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00') #数据格式
mySheet.write(1,1,1234.56,myStyle)
mySheet.write(2,0,1)
mySheet.write(2,1,1)
mySheet.write(2,2,xlwt.Formula("A3+B3"))

myWorkbook.save("excelFile.xlsx")

自动筛选脚本编写

分析表格数据，目标表格存在合并单元格，因此我需要先将合并单元格拆分，并将数据复制到每一个拆分的单元格，这里从百度搜到了现成代码，经测试可以正常使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
def read_excel(file_path):
	data = xlrd.open_workbook(file_path) #打开excel
	sheet_names = data.sheet_names()#读sheet

	table = data.sheet_by_name(sheet_name) #按名字获取工作表
	nrows = table.nrows #获得行数
	ncols = table.ncols #获得列数

	name = []

	colspan = {}
	if table.merged_cells :
		for item in table.merged_cells:
			for row in range(item[0], item[1]):
				for col in range(item[2], item[3]):
					if (row, col) != (item[0], item[2]):
						colspan.update({(row, col): (item[0], item[2])})

	for i in range(1, nrows):
		row = []
		for j in range(ncols):
			if colspan.get((i, j)):
				row.append(table.cell_value(*colspan.get((i, j))))
			else:
				row.append(table.cell_value(i, j))

分析目标表格，需要筛选的电源数据格式如下：

电源数据格式

需要筛选的光端机起终点数据格式如下：

光端机数据格式

根据数据格式，编写脚本：

扫描目录下所有目标excel表格

1
2
3
4
5
6
7
8
9
filelist=[]

for root, dirs, files in os.walk(".", topdown=False):
	for name in files:
		str=os.path.join(root, name)
		if str.split('.')[-1]=='xlsx':
			filelist.append(str)

print(filelist)

分别读数据，筛选计数，并将统计结果写入新建excel表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for file in filelist :

	result = []
	result.append(["屏序号", "屏名称", "屏内设备", "厂家型号", "起点", "终点", "备注"])
	result2 = []
	result2.append(["屏序号", "屏名称", "屏内设备", "厂家型号", "数量", "备注"])
	read_excel(file)


	tmp = file.split(".xlsx")[0].split("\\")[-1]

	print(tmp)

	write_excel(result, tmp+" 光端机.xls")
	write_excel(result2, tmp+" 电源.xls")

将各excel统计结果进行汇总，得到总的统计结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
filelist=[]

for root, dirs, files in os.walk(".", topdown=False):
	for name in files:
		str=os.path.join(root, name)
		if str.split('.')[-1]=='xls':
			filelist.append(str)

print(filelist)

result = []
result.append(["屏序号", "屏名称", "屏内设备", "厂家型号", "起点", "终点", "备注", "来源"])
result2 = []
result2.append(["屏序号", "屏名称", "屏内设备", "厂家型号", "数量", "备注", "来源"])

sum(filelist)

read_excel的编写

在拆分数据格后，按行扫描，扫描到关键词后将本段内容统计计数，需要注意的是拆分后导致的重复计数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
def read_excel(file_path):
	data = xlrd.open_workbook(file_path) #打开excel
	sheet_names = data.sheet_names()#读sheet

	table = data.sheet_by_name("设备屏")
	nrows = table.nrows #获得行数
	ncols = table.ncols #获得列数

	name = []

	colspan = {}
	if table.merged_cells :
		for item in table.merged_cells:
			for row in range(item[0], item[1]):
				for col in range(item[2], item[3]):
					if (row, col) != (item[0], item[2]):
						colspan.update({(row, col): (item[0], item[2])})

	for i in range(1, nrows):
		row = []
		for j in range(ncols):
			if colspan.get((i, j)):
				row.append(table.cell_value(*colspan.get((i, j))))
			else:
				row.append(table.cell_value(i, j))

		if row[4].find("起点") :
			name.append(row[4])
		else :
			result.append([row[0], row[1], row[3], name[-1], row[4], row[5], row[6]])

		if row[1].find("电源") > -1 or row[1].find("直流") > -1 or row[1].find("交流") > -1:
			if row[6] :
				result2.append([row[0], row[1], row[3], row[4], row[5], row[6]])

write_excel的编写

写入的话可以进行一些参数的设定，这里这设置了固定列宽

1
2
3
4
5
6
7
8
9
10
11
12
13
def write_excel(data, file_path):
	xls = xlwt.Workbook()
	sheet = xls.add_sheet('sheet', cell_overwrite_ok=True)

	for row in range(len(data)):

		sheet.col(row).width = 7500  #固定列宽

		for col in range(len(data[0])):
		
			sheet.write(row, col, data[row][col])

	xls.save(file_path)

sum的编写

sum函数只是简单的excel表格拼接，汇总各excel表格统计结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def sum(filelist):
	
	for file in filelist :

		tmp = file.split(".xlsx")[0].split("\\")[-1]
		print(tmp)
		
		if file.find("汇总") > -1 :
			print("233")
		elif file.find("光端机") > -1 :
			sum_excel(file, result, tmp)
		elif file.find("电源") > -1 :
			sum_excel(file, result2, tmp)

	write_excel(result, "汇总 - 光端机.xls")
	write_excel(result2, "汇总 - 电源.xls")

效果测试

首先将需要统计的excel表格放到同一目录下，执行脚本，自动生成各excel表格统计结果表与汇总表。这里使用了三个excel表格进行测试。

原目录文件

执行脚本后目录文件