创建模板，对比Excel，生成对比结果

testExcle.py

from ContrastExcelFile import ContrastExcelFile as cef

file1 = 'C:\\Users\\hlj\\Desktop\\test\\1\\'
file2 = 'C:\\Users\\hlj\\Desktop\\test\\2\\'
file3 = 'C:\\Users\\hlj\\Desktop\\test\\3\\'



'''获得当前目录下所有文件夹、以及文加下的文件名'''
name = cef.file_name(file1)
'''创建模板'''
cef.createExcel(name,file3)
'''生成对比文件'''
cef.WriteExcel(name,file2,file3)

ContrastExcelFile.py

'''文件1下不能有其他的文件夹'''
import openpyxl
import pandas
import os
from openpyxl import Workbook
# from ExcelTemplate import ExcelTemplate as et

class ContrastExcelFile:
    @classmethod
    def file_name(cal,file_dir):
        ListFeilsNames = []
        listName = []
        ListFeil = []
        for root, dirs, files in os.walk(file_dir):
            # print(root) #当前目录路径
            # print(dirs) #当前路径下所有子目录
            # print('文件夹：'.format(1),root)  # 当前路径下所有非目录子文件
            # print('文件夹所有子目录：'.format(1),dirs) #当前路径下所有子目录
            # print('有如下文件名：'.format(1), files)  # 当前路径下所有非目录子文件
            # print()
            ListFeil.append(root)
            listName.append(files)
        ListFeilsNames.append(ListFeil)
        ListFeilsNames.append(listName)
        # print(listName)
        # print(ListFeil)
        # print(ListFeilsNames)
        return ListFeilsNames

    '''创建模板'''
    @classmethod
    def createExcel(cal,files, files3):
        for i in range(len(files[1])):
            fileName = files[0][i]
            print('文件夹：' + fileName)
            for j in files[1][i]:
                if 'xls' in j:
                    # 获得某个文件下的所有shell
                    shellName = cal.ExcelFile(fileName + '\\', j)
                    print('文件名：' + j)
                    print('shell名：'.format(1), shellName)
                    # 创建工作簿
                    workbook = Workbook()
                    active_sheet = workbook.active
                    for o in range(len(shellName)):
                        # print(shellName[o])
                        salary_sheet = workbook.create_sheet(shellName[o], i)
                    print('文件：' + j + '创建模板完成')
                    workbook.save(files3 + 'TEST' + j)
            print()

    '''写入文件，对比文件中的值'''
    @classmethod
    def WriteExcel(cal,files, files2, files3):
        for i in range(len(files[1])):
            fileName = files[0][i]
            print('文件夹：' + fileName)
            for j in files[1][i]:
                if 'xls' in j:
                    # 获得某个文件下的所有shell
                    shellName = cal.ExcelFile(fileName + '\\', j)
                    print('文件名：' + j)
                    # print('shell名：'.format(1), shellName)
                    for o in range(len(shellName)):
                        '''将内容写入模板文件中'''
                        basedir = os.path.dirname(__file__)
                        upload_path = os.path.join(basedir, fileName + '\\' + j)
                        upload_path2 = os.path.join(basedir, files2 + j)
                        upload_path3 = os.path.join(basedir, files3 + 'TEST' + j)
                        print('对比文件1:' + fileName + j + ',shell名：' + shellName[o])
                        print('对比文件2:' + files2 + j + ',shell名：' + shellName[o])
                        print('生成模板3:' + files3 + 'TEST' + j + ',shell名：' + shellName[o])
                        wb1 = openpyxl.load_workbook(upload_path, data_only=True)
                        wb2 = openpyxl.load_workbook(upload_path2, data_only=True)
                        wb3 = openpyxl.load_workbook(upload_path3, data_only=True)
                        sh1 = wb1[shellName[o]]
                        sh2 = wb2[shellName[o]]
                        sh3 = wb3[shellName[o]]
                        a = 1
                        sh3.cell(1, 1).value = 'JPMCHistorybook'
                        sh3.cell(1, 2).value = 'JPMCHistorysheet'
                        sh3.cell(1, 3).value = 'Row'
                        sh3.cell(1, 4).value = 'Col'
                        sh3.cell(1, 5).value = 'JPMCReportValue'
                        sh3.cell(1, 6).value = 'YSSReportValue'
                        sh3.cell(1, 7).value = 'Test Results'
                        for x in range(sh1.max_row):
                            for y in range(sh1.max_column):
                                a += 1
                                sh3.cell(a, 1).value = j
                                sh3.cell(a, 2).value = shellName[o]
                                sh3.cell(a, 3).value = x + 1
                                sh3.cell(a, 4).value = y + 1
                                sh3.cell(a, 5).value = sh1.cell(x + 1, y + 1).value
                                sh3.cell(a, 6).value = sh2.cell(x + 1, y + 1).value
                                if sh1.cell(x + 1, y + 1).value != sh2.cell(x + 1, y + 1).value:
                                    sh3.cell(a, 7).value = 'FAIL'
                                else:
                                    sh3.cell(a, 7).value = 'PASS'
                        wb3.save(files3 + 'TEST' + j)
                    print('文件：' + j + '对比完成')
            print()

    '''返回文件下的shell页名字'''
    @classmethod
    def ExcelFile(cal,files,fileName):
        xls = pandas.ExcelFile(files+fileName)
        # 获得Excel的所有shell名称
        sheet_names = xls.sheet_names
        return sheet_names