python工具库实战_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()