python脚本实现xlsx文件解析，供大家参考，具体内容如下
环境配置：
1.系统环境：Windows 7 64bit
2.编译环境：Python3.4.3
3.依赖库： os sys xlrd re
4.其他工具：none
5.前置条件：待处理的xlsx文件
脚本由来
最近的工作是做测试，而有一项任务呢，就是分析每天机器人巡检时采集的数据，包括各种传感器，CO2、O2、噪声等等，每天的数据也有上千条，通过站控的导出数据功能，会把数据库里面导出成xlsx文件，而这项任务要分析一下当天采集的数据是否在正常范围，要计算摄像头的识别率和识别准确率，自己傻呵呵的每天都在手动操作，突然觉得很浪费时间，索性写个python脚本吧，这样每天一条命令，就能得到自己想看的数据结果。每天至少节省10分钟！
这是要解析的xlsx文件：
一般手动就得筛选、排序、打开计算器计算 - - 繁琐枯燥乏味
还是python大法好
代码浅析
流程图
脚本demo
|
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
#-*- coding:utf-8 -*-import xlrdimport osimport sysimport loggingimport re#logging.basicConfig(level=logging.DEBUG)xfile = sys.argv[1]dateList = []InspectionType = []InspectionRresult = []def load_data(): CO2Type = [] O2Type = [] NoiseType = [] SupwareType = [] TowareType = [] TemperatureType = [] HumidityType = [] InfraredType = [] CO2Result = [] O2Result = [] NoiseResult = [] SupwareResult = [] TowareResult = [] TemperatureResult = [] HumidityResult = [] InfraredResult = [] logging.debug(InspectionType) logging.debug(InspectionRresult) for index, value in enumerate(InspectionType): if value == "二氧化碳": #CO2Type CO2Type.extend(value) logging.debug(index) logging.debug("CO2 RESULT: "+InspectionRresult[index]) CO2Result.append(InspectionRresult[index]) if value == "氧气传感器": #O2Type O2Type.extend(value) O2Result.append(InspectionRresult[index]) if value == "噪声传感器": #NoiseType NoiseType.extend(value) NoiseResult.append(InspectionRresult[index]) if value == "局放(超声波测量)": #SupwareType SupwareType.extend(value) SupwareResult.append(InspectionRresult[index]) if value == "局放(地电波测量)": #SupwareType TowareType.extend(value) TowareResult.append(InspectionRresult[index]) if value == "温度传感器": #TemperatureType TemperatureType.extend(value) TemperatureResult.append(InspectionRresult[index]) if value == "湿度传感器": #TemperatureType HumidityType.extend(value) HumidityResult.append(InspectionRresult[index]) if value == "温度(红外测量)": #TemperatureType InfraredType.extend(value) InfraredResult.append(InspectionRresult[index]) logging.debug(CO2Result) logging.debug(O2Result) logging.debug(NoiseResult) logging.debug(SupwareResult) logging.debug(TowareResult) logging.debug(TemperatureResult) logging.debug(HumidityResult) logging.debug(InfraredResult) return CO2Result,O2Result,NoiseResult,SupwareResult,TowareResult,TemperatureResult,HumidityResult,InfraredResultdef get_data_print(co2,o2,noise,supware,toware,temperature,humidity,infrared): co2 = list(map(eval,co2)) o2 = list(map(eval,o2)) noise = list(map(eval,noise)) supware = list(map(eval,supware)) toware = list(map(eval,toware)) temperature = list(map(eval,temperature)) humidity = list(map(eval,humidity)) infrared = list(map(eval,infrared)) co2Min = min(co2) co2Max = max(co2) logging.debug("CO2 min value :~~"+str(co2Min)) logging.debug("CO2 max value :~~"+str(co2Max)) o2Min = min(o2) o2Max = max(o2) noiseMin = min(noise) noiseMax = max(noise) supwareMin = min(supware) supwareMax = max(supware) towareMin = min(toware) towareMax = max(toware) temperatureMin = min(temperature) temperatureMax = max(temperature) humidityMin = min(humidity) humidityMax = max(humidity) infraredMin = min(infrared) infraredMax = max(infrared) print("CO2 values :",co2Min,'~~~~~~~',co2Max) print("o2 values :",o2Min,'~~~~~~~',o2Max) print("noise values :",noiseMin,'~~~~~~~',noiseMax) print("supware values :",supwareMin,'~~~~~~~',supwareMax) print("toware values :",towareMin,'~~~~~~~',towareMax) print("temperature values :",temperatureMin,'~~~~~~~',temperatureMax) print("humidity values :",humidityMin,'~~~~~~~',humidityMax) print("infrared values :",infraredMin,'~~~~~~~',infraredMax)def cal_picture(): result7to19List = [] result19to7List = [] count7to19List = [] count19to7List = [] count7to19Dict = {} count19to7Dict = {} failfind7to19cnt = 0 failfind19to7cnt = 0 photoType = [] photoDateList = [] allPhotoResult = [] for index,value in enumerate(InspectionType): #按照巡检类型筛选出视觉类，通过索引值同步时间、巡检结果 if value == "开关(视觉识别)" or value == "旋钮(视觉识别)" or \ value == "电流表(视觉识别)" or value == "电压表(视觉识别)": photoType.extend(value) photoDateList.append(dateList[index]) allPhotoResult.append(InspectionRresult[index]) for index,value in enumerate(photoDateList): if value[-8:] > '07:00:00' and value[-8:] < '19:00:00': result7to19List.append(allPhotoResult[index]) if value[-8:] > '19:00:00' or value[-8:] < '7:00:00': result19to7List.append(allPhotoResult[index]) logging.debug(result7to19List[-20:]) logging.debug(result19to7List[:20]) noduplicate7to19Set=set(result7to19List) #里面无重复项 for item in noduplicate7to19Set: count7to19List.append(result7to19List.count(item)) logging.debug(count7to19List) count7to19Dict= dict(zip(list(noduplicate7to19Set),count7to19List)) noduplicate19to7Set=set(result19to7List) for item in noduplicate19to7Set: count19to7List.append(result19to7List.count(item)) count19to7Dict= dict(zip(list(noduplicate19to7Set),count19to7List)) logging.debug(count7to19Dict) None7to19cnt = count7to19Dict[''] all7to19cnt = len(result7to19List) None19to7cnt = count19to7Dict[''] all19to7cnt = len(result19to7List) logging.debug(None7to19cnt) for key in count7to19Dict: if count7to19Dict[key] == 1 : failfind7to19cnt = failfind7to19cnt+1 if re.match('识别失败:*',key): failfind7to19cnt = failfind7to19cnt+ count7to19Dict[key] for key in count19to7Dict: if count19to7Dict[key] == 1 : failfind19to7cnt = failfind19to7cnt+1 if re.match('识别失败:*',key): failfind19to7cnt = failfind19to7cnt+count19to7Dict[key] logging.debug(all19to7cnt) print("7:00 ~~~ 19:00 识别率：",(all7to19cnt-None7to19cnt)/all7to19cnt) print("7:00 ~~~ 19:00 识别准确率：",(all7to19cnt-None7to19cnt-failfind7to19cnt)/(all7to19cnt-None7to19cnt)) print("19:00 ~~~ 7:00 识别率：",(all19to7cnt-None19to7cnt)/all19to7cnt) print("19:00 ~~~ 7:00 识别准确率：",(all19to7cnt-None19to7cnt-failfind19to7cnt)/(all19to7cnt-None19to7cnt))#读取xlsx文件xlsxdata=xlrd.open_workbook(xfile)tablepage=xlsxdata.sheets()[0]dateList.extend(tablepage.col_values(5))InspectionType.extend(tablepage.col_values(3))InspectionRresult.extend(tablepage.col_values(6))cal_picture()co2,o2,noise,supware,toware,temperature,humidity,infrared=load_data()get_data_print(co2,o2,noise,supware,toware,temperature,humidity,infrared) |
结果图
回顾与总结
渐渐体会到python脚本的优势所在。
python在代码保密上可能是解释性语言共有的小小缺陷，做项目还是C/C++，当然是指传统项目
写python很开心啊
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：http://blog.csdn.net/qq_30650153/article/details/78935666
