python实现xlsx文件分析详解

概述python脚本实现xlsx文件解析，供大家参考，具体内容如下环境配置：1.系统环境：Windows764bit

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,infrared=load_data()get_data_print(co2,infrared)

结果图

回顾与总结

渐渐体会到python脚本的优势所在。
python在代码保密上可能是解释性语言共有的小小缺陷，做项目还是C/C++，当然是指传统项目
写python很开心啊

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

总结

以上是内存溢出为你收集整理的python实现xlsx文件分析详解全部内容，希望文章能够帮你解决python实现xlsx文件分析详解所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。