python向excel中添加数据
很多大的工程项目会将自己的结果甚至日志写入不同的txt文件,往往在最后汇总的时候,手动填写时一项容易出错并且繁琐的事情。
python从txt文件中读取
def read_from_txt(self, txt_filename):
file_txt = open(txt_filename)
lines = file_txt.readlines() //不是很大的文件可以先全部读出来
count = len(lines)
# read Bitrate and YUV info
strlist = lines[-4].split(" ") //读取倒数第3行的文本内容,并根据项目结果的存储格式取结果
self.Bitrate = (strlist[4])
self.Y = (strlist[5])
self.U = (strlist[6])
self.V = (strlist[7])
strlist = lines[-1].split(" ") //读取倒数第一行的文本内容
self.Time = (strlist[6])
python向excel文件中写数据
考虑到汇总结果文件需要被重复读写,因此不能每次仅仅以xlwt.write()的方式进行覆盖。
def write_to_excel(self, excel_filename):
rb = xlrd.open_workbook(excel_filename) //xlrd将已经存在的excel文件打开
#r_sheet = rb.sheet_by_index(0) //根据索引号读取sheet table
wb = copy(rb) //先将读取的信息copy一份再写,再保存在同个文件名下,即仅修改对应数据
sheet = wb.get_sheet(0) //写操作获取sheet table
sheet.write(self.row, self.col, self.Bitrate) //通过col和row控制,在对应位置写数据
self.col += 1
sheet.write(self.row, self.col, self.Y)
self.col += 1
sheet.write(self.row, self.col, self.U)
self.col += 1
sheet.write(self.row, self.col, self.V)
self.col += 1
sheet.write(self.row, self.col, self.Time)
self.row += 1
self.col = 0
wb.save(excel_filename) //最后保存为同原文件相同名称的文件下
用类控制以上功能
为了更方便的对多个结果进行合并,用类的功能可以同意同个结果文件的行/列等信息。最终完整代码如下:
# -*- coding:utf8-*-
import numpy as np
import xlrd
import xlwt
from xlutils.copy import copy
import os
#从txt文件中读取内容,放到ftext1中
class Solution:
row = 0
col = 0
def Solution(self):
self.row = 0
self.col = 0
def read_from_txt(self, txt_filename):
file_txt = open(txt_filename)
lines = file_txt.readlines()
count = len(lines)
# read Bitrate and YUV info
strlist = lines[-4].split(" ")
self.Bitrate = (strlist[4])
self.Y = (strlist[5])
self.U = (strlist[6])
self.V = (strlist[7])
strlist = lines[-1].split(" ")
self.Time = (strlist[6])
def write_to_excel(self, excel_filename):
rb = xlrd.open_workbook(excel_filename)
#r_sheet = rb.sheet_by_index(0) # 通过名称获取
wb = copy(rb)
sheet = wb.get_sheet(0)
sheet.write(self.row, self.col, self.Bitrate)
self.col += 1
sheet.write(self.row, self.col, self.Y)
self.col += 1
sheet.write(self.row, self.col, self.U)
self.col += 1
sheet.write(self.row, self.col, self.V)
self.col += 1
sheet.write(self.row, self.col, self.Time)
self.row += 1
self.col = 0
wb.save(excel_filename)
if __name__ == '__main__':
dir = "groud_SlideShow_qp"
qp = [22, 27, 32, 37] //所有可能的qp信息,对于视频序列也可以通过建立数组进行遍历
s = Solution()
for i in qp:
txt_filename = dir + str(i) + '.txt'
excel_filename = "temp.xls"
s.read_from_txt(txt_filename)
s.write_to_excel(excel_filename)