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)