python实现xlsx文件分析详解

#-*- 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)