Operation on Excel by Python

The whole integrated program just below:

#-*-coding:utf-8-*-'''Create on 2015/11/16 9:05:59@author: Chen Yu in RockChip Internship '''import osimport sysimport reimport stringimport mathimport win32apiimport win32processimport win32eventimport xlrdimport xlwtfrom xlutils.copy import copy from subprocess import Popen,PIPE# processing folder pathdirGood = r"E:\ErrorConcealment\Sequence\Sequence_Bat_Good"dirCopy = r"E:\ErrorConcealment\Sequence\Sequence_Bat"dirFirstBatch = r"E:\ErrorConcealment\Sequence\Sequence_Bat\SmoothFile"global columnExcelcolumnExcel = 0def 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 SSIMdef 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.csdn.net/a491057947/article/details/47614263
http://www.cnblogs.com/lhj588/archive/2012/01/06/2314181.html
http://huaxia524151.iteye.com/blog/1173828
http://blog.csdn.net/hongqiang200/article/details/38171517
http://blog.csdn.net/five3/article/details/7034826

Ref 3：

http://www.cnblogs.com/snake-hand/p/3153158.html
http://download.csdn.net/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.csdn.net/mr__fang/article/details/7089581

Ref 4：
Global Variable definition：

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