python实现xlsx文件分析详解

Python  /  管理员 发布于 7年前   160

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

#-*- 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很开心啊

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。