Operation on Excel by Python

最新推荐文章于 2024-04-28 14:10:04 发布

原创最新推荐文章于 2024-04-28 14:10:04 发布 · 990 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #python #xlrd #xlwt #写入并保存

Python && SCM 专栏收录该内容

38 篇文章

订阅专栏

本文介绍了一个Python程序，用于批量计算两个文件夹中YUV视频文件的结构相似性(SSIM)和峰值信噪比(PSNR)，并记录结果到Excel表格中。程序读取指定路径下的YUV文件，比较每帧图像的质量差异，并输出统计结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

The whole integrated program just below:

#-*-coding:utf-8-*-

'''
Create on 2015/11/16 9:05:59
@author: Chen Yu in RockChip Internship 
'''

import os
import sys
import re
import string
import math
import win32api
import win32process
import win32event
import xlrd
import xlwt
from xlutils.copy import copy 
from subprocess import Popen,PIPE

# processing folder path
dirGood = r"E:\ErrorConcealment\Sequence\Sequence_Bat_Good"
dirCopy = r"E:\ErrorConcealment\Sequence\Sequence_Bat"
dirFirstBatch = r"E:\ErrorConcealment\Sequence\Sequence_Bat\SmoothFile"

global columnExcel
columnExcel = 0

def MyGetFileList(dir, filelist):
    if os.path.isdir(dir):
        for s in os.listdir(dir):
            if re.match(r"([\s\S]*)\.yuv", s):
                filelist.append(s)
    return filelist             


def CalculateForTwoFolder(fileList1, fileList2, dir1, dir2, directive, workbook):
    for f1 in fileList1:
        for f2 in fileList2:
            if f1 == f2:
                if directive == 1:
                    CalculateSSIMForTwoYUVs(f1, f2, dir1, dir2, workbook)
                elif directive == 0:
                    CalculatePSNRForTwoYUVs(f1, f2, dir1, dir2, workbook)
                else:
                    print "---------------------------Sorry, Ur directive is wrong------------------------------\n"
                    print "---------------------------0: Count PSNR for two YUV files---------------------------\n"
                    print "---------------------------1: Count SSIM for two YUV files---------------------------\n" 


def CalculateSSIMForTwoYUVs(f1, f2, dir1, dir2, newWb):
    d1 = dir1 + '\\' + f1
    d2 = dir2 + '\\' + f2
    result = re.findall("([\s\S]*)\_(\d+)x(\d+)\_([\s\S]*)",d1) 
    storagePath = re.findall("([\s\S]*)\.yuv", d2)
    global columnExcel
    rowExcel = 0
    sheetName = newWb.get_sheet(1)  
    ssim = 0
    ssimList = []
    AvaregeSsim = 0
    AverageSsimNum = 0
    frameNum = 0
    for line in result:
        width = string.atoi(line[1])
        height = string.atoi(line[2])   
    chunkSize = (width * height * 3) >> 1
    Area = width * height   
    frame0 = [range(height) for i in range(width)] 
    frame1 = [range(height) for i in range(width)] 
    if (os.path.isfile(d1) and os.path.isfile(d2)):
        file1Object = open(d1, 'rb')
        file2Object = open(d2, 'rb')    
        print d1
        print d2
        sheetName.write(rowExcel, columnExcel, d1)
        rowExcel += 1
        sheetName.write(rowExcel, columnExcel, d2)
        rowExcel += 1   
        SumOfDev = 0
        stdDev = 0
        while True:
            chunk0 = file1Object.read(chunkSize)
            chunk1 = file2Object.read(chunkSize)
            if not chunk0:  
                break
            if not chunk1:  
                break       
            l0 = list(chunk0)
            l1 = list(chunk1)
            for i in range (Area):      
                if i:
                    ck0 = ord(l0[i])
                    ck1 = ord(l1[i])
                    frame0[i % width][i / width] = ck0      
                    frame1[i % width][i / width] = ck1      
            ssim = Ssim(frame0, frame1, width, height)
            if ssim < 1:
                AvaregeSsim += ssim
                ssimList.append(ssim)               
                AverageSsimNum += 1
            sheetName.write(rowExcel, columnExcel, ssim)
            newWb.save("PSNR&&SSIM.xls")
            rowExcel += 1       
            print ("------------------------The %-3d------------------- frame's SSIM is | %f \n" % (frameNum, ssim))
            frameNum += 1  
        if AverageSsimNum != 0:
            AvaregeSsim /= AverageSsimNum
            for itemSSIM in ssimList:
                SumOfDev += ((itemSSIM - AvaregeSsim) ** 2)
            stdDev = math.sqrt(SumOfDev*1.0/AverageSsimNum)             
            sheetName.write(rowExcel, columnExcel, AverageSsimNum)
            newWb.save("PSNR&&SSIM.xls")
            rowExcel += 1
            sheetName.write(rowExcel, columnExcel, stdDev)
            newWb.save("PSNR&&SSIM.xls")
            rowExcel += 1      
            newWb.save("PSNR&&SSIM.xls")        
            print ("\n------------------------The Average SSIM for %-3d frames is %f------------------- \n" % (AverageSsimNum, AvaregeSsim))
            print ("\n------------------------The Standard Deviation is %f------------------- \n\n\n" % (stdDev))
        else:
            print ("\n---------------------------------------There is NO error in this YUV-------------------------\n\n\n")     
        newWb.save("PSNR&&SSIM.xls")
        columnExcel += 1
        file1Object.close()
        file2Object.close() 


def CalculatePSNRForTwoYUVs(f1, f2, dir1, dir2, newWb):             
    d1 = dir1 + '\\' + f1
    d2 = dir2 + '\\' + f2
    global columnExcel
    rowExcel = 0
    sheetName = newWb.get_sheet(0)  
    result = re.findall("([\s\S]*)\_(\d+)x(\d+)\_([\s\S]*)",d1)
    storagePath = re.findall("([\s\S]*)\.yuv", d2) 
    for line in result:
        width = string.atoi(line[1])
        height = string.atoi(line[2])
    Area = width * height   
    chunkSize = (Area * 3) >> 1  # 420
    frameNum = 0
    MSE = 0
    MAXDivMSE = 0
    PSNR = 0
    AveragePSNR = 0
    CountAveragePSNRFrame = 0
    ListPSNR = []
    if (os.path.isfile(d1) and os.path.isfile(d2)):
        file1Object = open(d1, 'rb')
        file2Object = open(d2, 'rb')
        print d1
        print d2
        sheetName.write(rowExcel, columnExcel, d1)
        rowExcel += 1
        sheetName.write(rowExcel, columnExcel, d2)
        rowExcel += 1
        while True:
            chunk1 = file1Object.read(chunkSize)
            chunk2 = file2Object.read(chunkSize)
            if not chunk1:  
                break
            if not chunk2:  
                break     
            sumOfMSE = 0    
            l1 = list(chunk1)
            l2 = list(chunk2)
            for i in range (Area):      
                ck1 = ord(l1[i])
                ck2 = ord(l2[i])    
                sumOfMSE += (abs(ck1 - ck2)**2)
            MSE = math.sqrt(sumOfMSE*1.0/Area)
            if MSE != 0:
                MAXDivMSE = 255 / MSE   
            else:
                MAXDivMSE = 0
            if MAXDivMSE > 0:
                PSNR = 20 * math.log10(MAXDivMSE)
            else:
                PSNR = 0        
            print ("------------------------The %-3d------------------- frame's PSNR is | %f \n" % (frameNum, PSNR))  
            sheetName.write(rowExcel, columnExcel, PSNR)
            newWb.save("PSNR&&SSIM.xls")
            rowExcel += 1       
            frameNum += 1       
            if PSNR != 0:
                CountAveragePSNRFrame += 1
                AveragePSNR += PSNR
                ListPSNR.append(PSNR)
        AveragePSNR = 0 if CountAveragePSNRFrame == 0 else (AveragePSNR*1.0/CountAveragePSNRFrame)  
        SumOfDev = 0    
        stdDev = 0
        if CountAveragePSNRFrame != 0:  
            for itemPSNR in ListPSNR:
                SumOfDev += ((itemPSNR - AveragePSNR) ** 2)
            stdDev = math.sqrt(SumOfDev*1.0/CountAveragePSNRFrame)    
        print ("------------------------The Average PSNR is %f-------------------\n" % (AveragePSNR))  
        print("----------------------The Standard Deviation is %f----------------------\n" % (stdDev))   
        sheetName.write(rowExcel, columnExcel, AveragePSNR)
        newWb.save("PSNR&&SSIM.xls")
        rowExcel += 1
        sheetName.write(rowExcel, columnExcel, stdDev)
        newWb.save("PSNR&&SSIM.xls")
        rowExcel += 1     
        file1Object.close()
        file2Object.close() 
        newWb.save("PSNR&&SSIM.xls")
        print "--------------------------------------------------------------------\n\n"        
        columnExcel += 1    


def BatchCalculate(directive):
    if(os.path.isfile("PSNR&&SSIM.xls") == False):
        DataFile = xlwt.Workbook()
        NewSheetForPSNR = DataFile.add_sheet("PSNR", cell_overwrite_ok = True)      
        NewSheetForSSIM = DataFile.add_sheet("SSIM", cell_overwrite_ok = True)  
        DataFile.save("PSNR&&SSIM.xls") 
    book = xlrd.open_workbook('PSNR&&SSIM.xls', formatting_info = True)
    newWb = copy(book) 

    FileListOfGood = MyGetFileList(dirGood, [])
#   FileListOfCopy = MyGetFileList(dirCopy, [])
    FileListOfFirstBatch = MyGetFileList(dirFirstBatch, [])
#   FileListOfSecondBatch = MyGetFileList(dirSecondBatch, [])

#   CalculateForTwoFolder(FileListOfGood, FileListOfCopy, dirGood, dirCopy, directive)
    CalculateForTwoFolder(FileListOfGood, FileListOfFirstBatch, dirGood, dirFirstBatch, directive, newWb)   
#   CalculateForTwoFolder(FileListOfGood, FileListOfSecondBatch, dirGood, dirSecondBatch, directive)    


def SsimEnd1(s1, s2, ss, s12):
    ssim_c1 = (int)(.01*.01*255*255*64 + .5)
    ssim_c2 = (int)(.03*.03*255*255*64*63 + .5)    
    vars = ss*64 - s1*s1 - s2*s2
    covar = s12*64 - s1*s2
    return (float)(2*s1*s2 + ssim_c1) * (float)(2*covar + ssim_c2)\
       / ((float)(s1*s1 + s2*s2 + ssim_c1) * (float)(vars + ssim_c2));


# Here I make the decision that for a list list[x][y], x stands column(like a picture)
def SsimEnd4(l1, l2, width):
    ssim = 0
    for i in range(width):
        ssim += SsimEnd1(l1[i][0] + l1[i + 1][0] + l2[i][0] + l2[i + 1][0], 
                                         l1[i][1] + l1[i + 1][1] + l2[i][1] + l2[i + 1][1],
                                         l1[i][2] + l1[i + 1][2] + l2[i][2] + l2[i + 1][2],
                                         l1[i][3] + l1[i + 1][3] + l2[i][3] + l2[i + 1][3])
    return ssim


# Size is 32 for pix1 and pix2, and sums is [2][4]
def ssim_4x4x2_core( pix1, pix2, sums):
    for z in range(2):
        s1 = 0
        s2 = 0
        ss = 0
        s12 = 0
        for x in range(16):
                    a = pix1[x + (z << 4)]
                    b = pix2[x + (z << 4)]
                    s1  += a
                    s2  += b
                    ss  += a*a
                    ss  += b*b
                    s12 += a*b
        sums[z][0] = s1
        sums[z][1] = s2
        sums[z][2] = ss
        sums[z][3] = s12


# Calculate Two Frame's SSIM
def Ssim(pixel0, pixel1, width, height):
    Sums0 = [range(4) for i in range((width >> 2) + 3)] # width x 4( column x row = width x height )
    Sums1 = [range(4) for i in range((width >> 2) + 3)]
    width = (width >> 2)
    height = (height >> 2)
    z = 0   
    ssim = 0.0  
    pix0Ck = range(32)
    pix1Ck = range(32)
    sums0 = [range(4) for i in range(2)]
    sums1 = [range(4) for i in range(2)]

    for y in range(1,height):
        while(z <= y):
            for x in range(0, width, 2):
                for i in range(4):
                    for j in range(8):
                        if j >= 4:
                            pix0Ck[(i << 2) + j + 12] = pixel0[(x << 2) + j][(z << 2) + i] 
                            pix1Ck[(i << 2) + j + 12] = pixel1[(x << 2) + j][(z << 2) + i]
                        else:
                            pix0Ck[(i << 2) + j] = pixel0[(x << 2) + j][(z << 2) + i] 
                            pix1Ck[(i << 2) + j] = pixel1[(x << 2) + j][(z << 2) + i]                                
                if z % 2 == 0:
                    ssim_4x4x2_core(pix0Ck, pix1Ck, sums0)
                    Sums0[x] = sums0[0]
                    Sums0[x + 1] = sums0[1]
                else:
                    ssim_4x4x2_core(pix0Ck, pix1Ck, sums1)  
                    Sums1[x] = sums1[0]
                    Sums1[x + 1] = sums1[1]     
            z += 1                              
        for x in range(0, width - 1, 4):
            ssim += SsimEnd4(Sums0[x : x + 5], Sums1[x : x + 5], min(4, width - 1 - x))

    return 0 if (width - 1 == 0) or (height - 1 == 0) else ssim * 1.0/((width - 1.0)*(height - 1.0)) 


if __name__ == "__main__":
    BatchCalculate(0)       
    BatchCalculate(1)

Reference:

Ref 1：

http://www.crifan.com/python_append_new_data_into_existing_excel_xls_file/
http://blog.chinaunix.net/uid-21222282-id-3420444.html
http://www.xuebuyuan.com/1436412.html
http://www.jb51.net/article/60510.htm
http://www.tuicool.com/articles/BFFbUvu

Ref 2：

http://blog.youkuaiyun.com/a491057947/article/details/47614263
http://www.cnblogs.com/lhj588/archive/2012/01/06/2314181.html
http://huaxia524151.iteye.com/blog/1173828
http://blog.youkuaiyun.com/hongqiang200/article/details/38171517
http://blog.youkuaiyun.com/five3/article/details/7034826

Ref 3：

http://www.cnblogs.com/snake-hand/p/3153158.html
http://download.youkuaiyun.com/download/u013634684/9074603
http://zhaojing366.blog.163.com/blog/static/952384020124311071392/
http://biotopiblog.sinaapp.com/2014/06/python%E8%AF%BB%E5%86%99excel%E5%A6%82%E4%BD%95%E4%BF%9D%E7%95%99%E5%8E%9F%E6%9C%89%E6%A0%BC%E5%BC%8F/
http://blog.youkuaiyun.com/mr__fang/article/details/7089581

Ref 4：
Global Variable definition：

global a
a = 3

def Fuc():
    global a
    print a
    a = a + 1
if __name__ == "__main__":
　　global a
    for i in range(10):
        Fuc()print 'hello'
    print a