python写多个数据到excel表格的一个单元格中
程序员文章站
2022-07-13 21:52:13
...
python写多个数据到excel表格的一个单元格中
往excel单元格中写一个数字很简单，但是如何写进多个有格式的数据呢，假如我们想写入一个点坐标：（56,25）
解决思路
可以先将数据强制格式format 转换为特定格式的字符串，然后以写字符串的方式写入excel中。
关键核心代码
tel_net = '({},{})'.format(56,25)
table.write(1, 3, str(tel_net)) # 中点
效果图
全部代码
# -*- coding: utf-8 -*-
'''**************************************************************************
这是通用的图像识别的qt界面，具有读入图片，读入视频，显示图片显示视频的功能。
版本：1.0
内容：创建pyqt界面，用于图像识别，有基本打开图像，显示图像功能
时间：2020.3.20
作者：狄云
版本：2.0
内容：新增视频可以实时显示并检测，修改图片检测缓存路径，改为相对路劲
时间：2020.7.20
作者：狄云
版本：3.0
内容：新增空间间距设置，新增打开视频功能，（与之前打开图片功能相同）
时间：2020.8.16
作者：狄云
版本：4.0
内容：新增表格显示，使用方法参考：https://www.cnblogs.com/linyfeng/p/11832237.html
时间：2020.8.30
作者：狄云
版本：5.0
内容：新增登录界面，需要调用login_main.py
时间：2020.9.8
作者：狄云
***************************************************************************'''
from __future__ import division, print_function, absolute_import
from xlwt import *
import sys
import cv2 as cv
import argparse
#from yolo import YOLO, detect_video
from PIL import Image
import numpy as np
import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
import os
import datetime
from timeit import time
import warnings
import cv2
import numpy as np
import argparse
from PIL import Image
from yolo import YOLO
from collections import deque
from keras import backend
from moviepy.editor import VideoFileClip #获取视频时间长度
import os
import sys
import xlwt
###========全局变量 定义开始=======###
#打开的是摄像头还是本地视频
camera_or_local_flag = 0
#识别进行中还是暂停识别中，1为摄像头，2位本地摄像头
Start_or_pause_flag = 0
result_image=...
file_times=0 #打开的视频文件总时长
pupil_counter = 0 #
zhayan_sum_zhen=0 #闭眼总帧数
class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
# 1、总界面框大小 MainWindow
MainWindow.resize(1850, 820) # 总界面框
#左边界面区域：verticalLayoutWidget QWidget类
self.verticalLayoutWidget = QtWidgets.QWidget(MainWindow)
self.verticalLayoutWidget.setGeometry(QtCore.QRect(30, 25, 1280, 720))#左边图片框。左上角坐标及宽度高度
self.verticalLayoutWidget.setStyleSheet('background-color:rgb(55,55,55)') # 设置做左边框的颜色
self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget) #QVBoxLayout类 垂直地摆放小部件
self.verticalLayout.setContentsMargins(0, 0, 0, 0)#设置左侧、顶部、右侧和底部边距，以便在布局周围使用。
self.verticalLayout.setObjectName("verticalLayout")
self.label_ShowPicture = QtWidgets.QLabel(self.verticalLayoutWidget)
self.label_ShowPicture.setObjectName("label_ShowPicture")
self.verticalLayout.addWidget(self.label_ShowPicture)
# 右边按钮及显示结果字符的一块区域：verticalLayoutWidget_2 QWidget类
self.verticalLayoutWidget_2 = QtWidgets.QWidget(MainWindow)
self.verticalLayoutWidget_2.setGeometry(QtCore.QRect(1350, 50, 450, 700)) #右边按钮及显示结果字符的大小
#self.verticalLayoutWidget_2.setStyleSheet('background-color:rgb(155,155,155)') # 设置做左边框的颜色
self.verticalLayoutWidget_2.setObjectName("verticalLayoutWidget_2")
self.verticalLayout_2 = QtWidgets.QVBoxLayout(self.verticalLayoutWidget_2) #QVBoxLayout类 垂直地摆放小部件
self.verticalLayout_2.setContentsMargins(0, 0, 0, 0)
self.verticalLayout_2.setObjectName("verticalLayout_2")
# 设置控件间的间距
self.verticalLayout_2.setSpacing(30)
#按钮1 选择图片按钮：pushButton_select_pcture
self.pushButton_open_camera = QtWidgets.QPushButton(self.verticalLayoutWidget_2)
self.pushButton_open_camera.setObjectName("pushButton_open_camera")
self.verticalLayout_2.addWidget(self.pushButton_open_camera)#将按钮1增加到
#按钮2 选择视频按钮：pushButton_select_pcture
self.pushButton_open_Local_video = QtWidgets.QPushButton(self.verticalLayoutWidget_2)
self.pushButton_open_Local_video.setObjectName("pushButton_open_Local_video")
self.verticalLayout_2.addWidget(self.pushButton_open_Local_video)#将按钮1增加到
#按钮3 开始识别按钮：pushButton_shibie
self.pushButton_Start_or_pause = QtWidgets.QPushButton(self.verticalLayoutWidget_2)
self.pushButton_Start_or_pause.setObjectName("pushButton_Start_or_pause")
self.verticalLayout_2.addWidget(self.pushButton_Start_or_pause)
#按钮4 选择模型：pushButton_shibie
self.pushButton_save = QtWidgets.QPushButton(self.verticalLayoutWidget_2)
self.pushButton_save.setObjectName("pushButton_save")
self.verticalLayout_2.addWidget(self.pushButton_save)
# 放“图像识别结果为”这一句话
self.label = QtWidgets.QLabel(self.verticalLayoutWidget_2)
font = QtGui.QFont()
font.setPointSize(15)
self.label.setFont(font)
self.label.setObjectName("label")
self.verticalLayout_2.addWidget(self.label)
# #lable_2放显示结果1
# self.label_2 = QtWidgets.QLabel(self.verticalLayoutWidget_2)
# font = QtGui.QFont()
# font.setPointSize(15)
# self.label_2.setFont(font)
# self.label_2.setText("")
# self.label_2.setObjectName("label_2")
# self.verticalLayout_2.addWidget(self.label_2)
# #png = QtGui.QPixmap('E:/testpicture/animal_pic.png')
#
# #lable_3放显示结果2
# self.lable_3 = QtWidgets.QLabel(self.verticalLayoutWidget_2)
# font = QtGui.QFont()
# font.setPointSize(15)
# self.lable_3.setFont(font)
# self.lable_3.setObjectName("label_3")
# self.verticalLayout_2.addWidget(self.lable_3)
#
# #lable_4放显示结果3
# self.label_4 = QtWidgets.QLabel(self.verticalLayoutWidget_2)
# font = QtGui.QFont()
# font.setPointSize(15)
# self.label_4.setFont(font)
# self.label_4.setObjectName("label_4")
# self.verticalLayout_2.addWidget(self.label_4)
#表格
self.tableWidget = QtWidgets.QTableWidget(self.verticalLayoutWidget_2)
self.tableWidget.setObjectName("tableWidget")
self.tableWidget.setColumnCount(6)#列数
self.tableWidget.setRowCount(100)#行数
#设置表格的行、列标题，如下：
self.tableWidget.setHorizontalHeaderLabels(['ID', '类别', '坐标0', '坐标1', '坐标2', '坐标3'])
#self.tableWidget.setVerticalHeaderLabels(['1', '2'])
self.verticalLayout_2.addWidget(self.tableWidget)#将表格加入到verticalLayout_2空间中，依次罗列
#指定列宽
self.tableWidget.horizontalHeader().resizeSection(0, 40)
self.tableWidget.horizontalHeader().resizeSection(1, 90)
self.tableWidget.horizontalHeader().resizeSection(2, 70)
self.tableWidget.horizontalHeader().resizeSection(3, 70)
self.tableWidget.horizontalHeader().resizeSection(4, 70)
self.tableWidget.horizontalHeader().resizeSection(5, 70)
#self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)#禁止用户编辑，即只读
#行、列标题的显示与隐藏。
#self.tableWidget.horizontalHeader().setVisible(False)#列标题不显示
self.tableWidget.verticalHeader().setVisible(False)#行标题不显示
self.timer_camera = QtCore.QTimer() # 初始化定时器
self.cap = cv.VideoCapture() # 初始化摄像头
self.CAM_NUM = 0
self.__flag_work = 0
self.x = 0
self.count = 0
# 视频显示与计时器，以及关闭识别
# 建立通信连接
self.pushButton_open_camera.clicked.connect(self.button_open_camera_click)
self.timer_camera.timeout.connect(self.deep_sort)
self.pushButton_open_Local_video.clicked.connect(self.pushButton_open_Local_video_click)#
self.pushButton_Start_or_pause.clicked.connect(self.pushButton_Start_or_pause_click)
self.pushButton_save.clicked.connect(self.pushButton_save_click)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
name_picture = 0
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "行人检测追踪系统"))
# self.label_ShowPicture.setText(_translate("MainWindow", "图片展示区"))
self.pushButton_open_camera.setText(_translate("MainWindow", "打开摄像头识别"))
self.pushButton_open_Local_video.setText(_translate("MainWindow", "选择本地视频识别"))
self.pushButton_Start_or_pause.setText(_translate("MainWindow", "开始识别"))
self.pushButton_save.setText(_translate("MainWindow", "保存结果"))
self.label.setText(_translate("MainWindow", "图像识别结果为："))
# self.label_2.setText("你点击了按钮")
image=None
#打开摄像头
def button_open_camera_click(self):
global camera_or_local_flag
if self.timer_camera.isActive() == False:
flag = self.cap.open(self.CAM_NUM)
if flag == False:
msg = QtWidgets.QMessageBox.Warning(self, u'Warning', u'请检测相机与电脑是否连接正确',buttons=QtWidgets.QMessageBox.Ok,defaultButton=QtWidgets.QMessageBox.Ok)
# if msg==QtGui.QMessageBox.Cancel:
# pass
else:
self.timer_camera.start(30)
camera_or_local_flag = 1 #打开了摄像头
self.pushButton_open_camera.setText(u'关闭相机')
else:
self.timer_camera.stop()
self.cap.release()
camera_or_local_flag = 0 # 关闭了摄像头
self.label_ShowPicture.clear()
self.pushButton_open_camera.setText(u'打开相机')
#选择图片
def pushButton_select_pcture_click(self):
filename = QFileDialog.getOpenFileName(None, 'Open file', 'C:/Users/Desktop/testpicture/')
# 设置标签的图片
src0 = cv.imread(filename[0])
resized0 = cv.resize(src0, (1280, 720), interpolation=cv.INTER_AREA)
cv.imwrite("./temp/temp0.jpg", resized0)
self.label_ShowPicture.setPixmap(QPixmap("./temp/temp0.jpg"))
print("filename[0]=",filename[0])
self.image = Image.open(filename[0])
#img1 = yolo.detect_image(image)
#img1.show()
def getTotalTime(file_path):
total = 0
cap = cv2.VideoCapture(file_path)
if cap.isOpened():
rate = cap.get(5) # 获取帧率
fraNum = cap.get(7) # 获取帧数
duration = fraNum / rate / 60
total += duration
return total
#选择视频
def pushButton_open_Local_video_click(self):
global camera_or_local_flag
global file_times
#打开本地视频，则关闭摄像头的视频
self.timer_camera.stop()
self.cap.release()
camera_or_local_flag = 0 # 关闭了摄像头
self.label_ShowPicture.clear()
self.pushButton_open_camera.setText(u'打开相机')
if self.timer_camera.isActive() == False:
print("打开本地视频")
self.fileName, self.fileType = QFileDialog.getOpenFileName(None, 'Choose file', '', '*.mp4')
self.cap_Local_video = cv.VideoCapture(self.fileName)
# file_path是文件的绝对路径，防止路径中含有中文时报错，需要解码
if self.cap_Local_video.isOpened(): # 当成功打开视频时cap.isOpened()返回True,否则返回False
# get方法参数按顺序对应下表（从0开始编号)
rate = self.cap_Local_video.get(5) # 帧速率
FrameNumber = self.cap_Local_video.get(7) # 视频文件的帧数
duration = FrameNumber / rate # 帧速率/视频总帧数 是时间，除以60之后单位是分钟
print(duration)#总时长
#self.timer_camera.start(30)
camera_or_local_flag = 2 #打开了本地视频
self.pushButton_open_Local_video.setText(u'关闭本地视频')
#打开录像后，不进行播放，等点击开始识别后开始播放
ret, self.frame = self.cap_Local_video.read()
img = cv.resize(self.frame, (1280, 720), interpolation=cv.INTER_AREA)
height, width, bytesPerComponent = img.shape # 取彩色图片的长、宽、通道
bytesPerLine = 3 * width
cv.cvtColor(img, cv.COLOR_BGR2RGB, img)
QImg = QImage(img.data, width, height, bytesPerLine, QImage.Format_RGB888)
pixmap = QPixmap.fromImage(QImg)
self.label_ShowPicture.setPixmap(QPixmap(pixmap))
else:
self.timer_camera.stop()
self.cap_Local_video.release()
camera_or_local_flag = 0 # 关闭了摄像头
self.label_ShowPicture.clear()
self.pushButton_open_Local_video.setText(u'打开本地视频')
#暂停开启按钮
def pushButton_Start_or_pause_click(self):
global camera_or_local_flag
global Start_or_pause_flag
print(camera_or_local_flag,Start_or_pause_flag)
print(254)
if camera_or_local_flag == 1:
if Start_or_pause_flag == 0:
Start_or_pause_flag = 1 # 代表开始识别
print(258)
self.timer_camera.start(30)
self.pushButton_Start_or_pause.setText(u'暂停识别')
#self.deep_sort() # 调用函数
else:
Start_or_pause_flag = 0 # 3代表开始识别
self.timer_camera.stop(30)
self.pushButton_Start_or_pause.setText(u'开始识别')
elif camera_or_local_flag == 2:
if Start_or_pause_flag == 0:
Start_or_pause_flag = 1 # 代表开始识别
self.timer_camera.start(30)
print(269)
print( camera_or_local_flag, Start_or_pause_flag)
self.pushButton_Start_or_pause.setText(u'暂停识别')
#self.deep_sort() # 调用函数
else:
Start_or_pause_flag = 0 # 3代表开始识别
self.timer_camera.stop(30)
self.pushButton_Start_or_pause.setText(u'开始识别')
print(" 2 camera_or_local_flag =%d Start_or_pause_flag =%d",camera_or_local_flag,Start_or_pause_flag)
def pushButton_save_click(self):
print("保存识别结果")
#事件函数
def select_shipin(self):
global camera_or_local_flag
global Start_or_pause_flag
global result_image
print("选择视频中")
def deep_sort(self):
global camera_or_local_flag
global Start_or_pause_flag
global result_image
global counter_zhenhao
global counter_jueduizhenhao
global pupil_counter#眨眼保持帧数
global zhayan_sum_zhen#闭眼总帧数
zhayan_flag=0 #是否眨眼
Center_point=[] #眼球位置
for i in range(2):
Center_point.append(0)
pupil_size=[] #眼球大小
for i in range(2):
pupil_size.append(0)
ret=False
if camera_or_local_flag == 1 and Start_or_pause_flag ==0:
ret, self.frame = self.cap.read()
elif camera_or_local_flag == 2 and Start_or_pause_flag == 0: # 打开本地文件，且只显示首帧
ret, self.frame = self.cap_Local_video.read()
if camera_or_local_flag == 1 and Start_or_pause_flag == 1:
ret, self.frame = self.cap.read()
elif camera_or_local_flag == 2 and Start_or_pause_flag == 1: # 打开本地文件，且只显示首帧
ret, self.frame = self.cap_Local_video.read()
#print(ret, camera_or_local_flag, Start_or_pause_flag)
if ret != True:
print("打开图像失败 or 识别结束")
return
if Start_or_pause_flag == 1:
counter_jueduizhenhao+=1
frame0 = cv.resize(self.frame, (1280, 720), interpolation=cv.INTER_AREA)
frame = cv.cvtColor(frame0, cv.COLOR_BGR2RGB)
# 转变成Image
frame = Image.fromarray(np.uint8(frame))
#img1, out_boxes, out_classes = np.array(yolo.detect_image(frame))
img_get, out_boxes, out_classes = yolo.detect_image(frame)
img1 = np.array(img_get)
boxes_num=len(out_boxes)#输出目标框数量
#print(boxes_num)
if boxes_num > 0: #有检测到瞳孔
#print("有检测到瞳孔")
print("zhayan_num:", pupil_counter)
pupil_counter = 0
zhayan_flag=0 #没有眨眼
#print(pupil_counter)
elif boxes_num == 0:
#print("没有检测到瞳孔")
pupil_counter += 1 # 累加
if pupil_counter>=3:
zhayan_flag = 1 # 说明闭眼状态
zhayan_sum_zhen += 1 # 用于统计所有的闭眼帧占当前帧的比值
biyan_shijian_bi = zhayan_sum_zhen / counter_jueduizhenhao # 闭眼时间比
print("shijianbi:", biyan_shijian_bi)
#当前帧，眨眼是否，眨眼保持帧数，眼球位置，眼球大小
#['帧号', '是否眨眼', '眨眼保持帧数', '眼球位置', '眼球大小', '坐标3']
table.write(counter_jueduizhenhao, 0, counter_jueduizhenhao)
table.write(counter_jueduizhenhao, 1, zhayan_flag) # 是否眨眼
table.write(counter_jueduizhenhao, 2, pupil_counter) # 眨眼保持帧数
if len(out_classes) > 0:
for i, c in list(enumerate(out_classes)):
box = out_boxes[i]
top, left, bottom, right = box
print(top, left, bottom, right )
Center_point[0] = (int(left) + int(right)) / 2 # 中点x坐标
Center_point[1] = (top + bottom) / 2 # 中点y坐标
pupil_size[0] = (right-left) / 2 # 中点x坐标
pupil_size[1] = (bottom-top) / 2 # 中点y坐标
tel_net = '({},{})'.format(56,25)
table.write(counter_jueduizhenhao, 3, str(tel_net)) # 中点
#table.write(counter_jueduizhenhao, 4, int(pupil_size[0])) #瞳孔大小：长宽
# for j in range(0, len(Center_point)):
# table.write(counter_jueduizhenhao, 3, int(Center_point[j])) # 中点
# cv2.namedWindow('dianyun')
# cv2.circle(img111, (int(Center_point[0]), int(Center_point[1])), 3, (10, 201, 10), 1)
# cv2.imshow('dianyun', img111)
frame0 = img1.copy()
else:
frame0 = self.frame.copy()
#在界面实时显示结果
img = cv.resize(frame0, (1280, 720), interpolation=cv.INTER_AREA)
height, width, bytesPerComponent = img.shape # 取彩色图片的长、宽、通道
bytesPerLine = 3 * width
#cv.cvtColor(img, cv.COLOR_BGR2RGB, img)
QImg = QImage(img.data, width, height, bytesPerLine, QImage.Format_RGB888)
pixmap = QPixmap.fromImage(QImg)
self.label_ShowPicture.setPixmap(QPixmap(pixmap))
file.save('data.xls')
if __name__ == "__main__":
#main.main(YOLO())
yolo = YOLO()
# deep_sort
max_cosine_distance = 0.5 # 余弦距离的控制阈值
nn_budget = None
nms_max_overlap = 0.3 # 非极大抑制的阈值
counter = []#总目标数，得放总循环外面
fps = 0.0
detections=[]
boxs=[]
#画多目标跟踪的轨迹
pts = [deque(maxlen=30) for _ in range(9999)]
warnings.filterwarnings('ignore')
np.random.seed(100)
COLORS = np.random.randint(0, 255, size=(200, 3),
dtype="uint8")
img111 = np.zeros([720, 1280, 3], np.uint8)
img111.fill(250)
#excel表格相关
file = Workbook(encoding='utf-8') # 指定file以utf-8的格式打开
table = file.add_sheet('data') # 指定打开的文件名，若没有，则新建一个
list_header = ['帧号', '是否眨眼', '眨眼保持帧数', '眼球位置', '眼球大小', '坐标3']
for j in range(len(list_header)):
table.write(0, j, list_header[j])
counter_zhenhao = 3
counter_jueduizhenhao=0
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = Ui_MainWindow()
ui.setupUi(MainWindow)
MainWindow.show()
sys.exit(app.exec_())
上一篇： opencv 学习笔记（二）
下一篇： OpenCV小白学习笔记(二)