python库实战 - openpyxl

一、安装

pip install openpyxl

二、脚本背景

做游戏测试的都知道，很多功能的实现依赖策划的配置。然后在进行版本合并的时候，配置表的合并是挺困难的一件事。通常配置表合并使用两种方式：

1、直接覆盖（容易出现不需要外放的功能代码没进，但是配置进了的情况。导致客户端or服务器出现错误）
2、在新分支上重新修改一下再直接提交（手动操作改错的纪律很高）

不管哪种合并方式对于QA来说都是痛苦的。因为合并出错率很高。因此就需要策划测试完成后对配表进行检查。目前个人已知检查配表的两个工具

1、Spreadsheet Compare（功能强大，个人觉得看起来不够直观）
2、beyoundCompare（看起来非常直观）

结合实际个人的使用，我选择了使用beyoundCompare。但是他有一个致命的问题，就是如果一个excel中含有多张sheet，则只会载入第一张sheet。那么策划配表中的其他sheet将无法参与比对。

因此有了这个脚本

三、脚本思路及脚本

1、脚本思路

1）遍历获取一个目录下所有的.xls文件
2）依次载入每一个excel文件，并获取excel中sheet数量，有几个sheet则对这个excel执行几次copy操作。copy出来的表格用excel名+sheet名的命名方式进行命名
3）载入每一个excel+sheet命名的表格，删除掉这张表格中除了sheet名以外的所有sheet
4）删除原始excel
这样操作完毕后，所有多sheet的excel都将会变为单sheet的excel。然后就可以使用beyoundCompare愉快的进行比对测试了

2、脚本内容

import sys, os, copy
import openpyxl
import gc

dir1 = "E:\\myPython\\dealWithExcel\\devB_old"
dir2 = "E:\\myPython\\dealWithExcel\\trunk2"
toolPath = "D:\\beyondCompare4\\BeyondCompare\\BCompare.exe"

def getAllFile(filePtah):
    tmp = []
    for root, dirs, files in os.walk(filePtah):
        for name in files:
            tmp.append(os.path.join(root, name))
    return tmp

# 这里通过复制表，然后将表内多余的sheet删除的方式。来将一张多sheet的表分割成单sheet的多张表
# 经过实际测试决定放弃使用该方式，因为很多表存在引用关系，当存在引用关系时，则新的表会失效
# 这个函数的运行耗时： 1678.9s， 1743s
def dealWithExcel(fileList):
    for excel in fileList:
        excelPath = os.path.dirname(excel)      # 获取原始excel所在目录名
        excelName = os.path.basename(excel)     # 获取原始excel的名字

        if ".xlsx" in excel:
            excelData = openpyxl.load_workbook(excel, read_only=True, data_only=True)       # 加载原始excel,这边使用只读模式加载excel，可以有效节省内存资源，避免出现MemoryError的情况
            sheetNames = excelData.sheetnames               # 获取原始excel的所有sheet名            
            print(excel, sheetNames)

            if len(sheetNames) == 1: continue               # 如果excel中只有一个sheet则跳过此次循环
            for sheetName in sheetNames:
                if excelData[sheetName].sheet_state == "hidden":
                    print("存在隐藏sheet{}.{}".format(excel, sheetName))
                    continue    # 这里加一个判断，被隐藏的表格不做处理。有可能表格被隐藏报错

                newExcelName = excelName[:-5] + "_" + sheetName.replace(".", "") +".xlsx"   # 新表的名字
                newExcel = os.path.join(excelPath, newExcelName)    # 新表的完整路径
                print("--> newExcel = {}".format(newExcel))
                os.system('copy "{}" "{}" >null'.format(excel, newExcel))   # 复制表格,使用复制命令的时候要在路径上加双引号，避免可爱的策划命名的时候中间含有空格

                newExcelData = openpyxl.load_workbook(newExcel)
                tmpSheetNames = copy.deepcopy(sheetNames)
                tmpSheetNames.remove(sheetName)

                for delSheet in tmpSheetNames:              # 遍历删除
                    del newExcelData[delSheet]

                newExcelData._active_sheet_index = 0    # 这里必须要加一行这个，不然会报数组越界的错误
                newExcelData.save(newExcel)         

            excelData.close()   #只读excel必须要记得关闭
            os.system("del {}".format(excel)) # 删除之前的excel
            gc.collect()    # 这里加一个内存释放

        
        elif ".txt" in excel:
            continue
        else:
            print("未识别的文件名后缀: ", excel)

"""
# 弃用函数， 函数耗时太高，对于大文件，很容易卡死
def dealWithExcel2(fileList):
    for excel in fileList:
        excelPath = os.path.dirname(excel)      # 获取原始excel所在目录名
        excelName = os.path.basename(excel)     # 获取原始excel的名字

        if ".xlsx" in excel:
            # 加载原始excel,这边使用只读模式加载excel，可以有效节省内存资源，避免出现MemoryError的情况. 另外为了处理数据引用的问题，这里要加一个data_only属性
            excelData = openpyxl.load_workbook(excel, read_only=True, data_only=True)       
            sheetNames = excelData.sheetnames               # 获取原始excel的所有sheet名
            
            print(excel, sheetNames)

            if len(sheetNames) == 1: continue               # 如果excel中只有一个sheet则跳过此次循环
            for sheetName in sheetNames:
                if excelData[sheetName].sheet_state == "hidden":    continue    # 如果sheet是隐藏属性则直接跳过不处理

                newExcelName = excelName[:-5] + "_" + sheetName.replace(".", "") +".xlsx"   # 新表的名字
                newExcel = os.path.join(excelPath, newExcelName)    # 新表的完整路径
                print("--> newExcel = {}".format(newExcel))
                
                sheetData = excelData[sheetName]    # 获取原始表格sheet数据
                # print(sheetData.max_row, sheetData.max_column)    # 有效行和列

                wb = openpyxl.Workbook()    # 创建表格
                newSheet = wb.active # 获取默认sheet
                newSheet.title = sheetName  # 设置页签名与原始表格页签名相同

                for row_num in range (1, sheetData.max_row+1):
                    for col_num in range (1, sheetData.max_column+1):
                        cell_value = sheetData.cell(row=row_num, column=col_num).value      # 获取每一个单元格的值
                        newSheet.cell(row=row_num, column=col_num).value = cell_value           
                
                wb.save(newExcel)       # 保存新表
                gc.collect()    # 这里加一个内存释放

            
            excelData.close()   #只读excel必须要记得关闭
            os.system("del /f {}".format(excel)) # 删除之前的excel
        
        elif ".txt" in excel:
            continue
        else:
            print("未识别的文件名后缀: ", excel)     
"""
def main():
    excelList = getAllFile(dir1)
    dealWithExcel(excelList)


if __name__ == '__main__':
    main()