python读取xls文件详解_python实现xlsx文件分析详解

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 xlrd

import os

import sys

import logging

import 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,InfraredResult

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

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