简单的查表工具
由于项目内的导表没有做检查工具，目前简单的做了一个检查工具
完成的功能有：
1、查重复ID
2、检查是否存在异常数值
3、检查语言表是否有空白项
其余的功能再慢慢实现
__author__ = "KID" from openpyxl.reader.excel import load_workbook import os import time class XlsxCheck(): def __init__(self, excel_path:str, sFileName, no_list, language): '''打开一个excel''' self.excel_path = excel_path self.sFileName = sFileName self.no_list = no_list self.language = language #print(excel_path) if not os.path.exists(excel_path): print("路径错误，请检查当前路径") os.system('pause') def Get_FileName(self, excel_path): print("开始检查文件") sFileName = [] filename = os.listdir(excel_path) try: for Name in filename: if os.path.exists(excel_path + r"\\" + Name): suffix = Name.split(".")[1] test = Name.split(".", 2) if len(test) == 2: if suffix == "xlsx" or suffix == "xlsm" or suffix == "xltx" or suffix == "xltm": name = Name.split(".")[0] if "~$" not in name: sFileName.append(Name) else: print("发现冲突的文件，请检查文件名：{}".format(Name.split(".")[0])) continue print("检查完毕！") return sFileName except Exception as e: print(e) pass def run(self): sFileName = self.Get_FileName(excel_path) print("==============================开始查表==============================") self.check_id(sFileName) self.check_str(sFileName) self.check_Language(sFileName) os.system('pause') def get_needchecksheetname(self, excel_path, sFileName): needchecksheetname = [] if sFileName == None: return wb = load_workbook(excel_path + r"\\" + sFileName) if wb != None: sheetnames = wb.get_sheet_names() for sheet in sheetnames: if sheet not in no_list: #print("现在检查的sheetname:{}".format(sheet), flush=True) needchecksheetname.append(sheet) return needchecksheetname #此处仅检查第一列的ID是否存在重复的情况 def check_id(self, sFileName): print("检查是否存在ID重复的情况") #sFileName = self.Get_FileName(excel_path) try: for filenames in sFileName: wb = load_workbook(excel_path + r"\\" + filenames) sheetnames = self.get_needchecksheetname(excel_path, filenames) for a in sheetnames: id_list = [] self.ws = wb.get_sheet_by_name(a) for i in range(1, self.ws.max_row + 1): if type(self.ws.cell(i, 1).value) == int: if self.ws.cell(i, 1).value not in id_list: id_list.append(self.ws.cell(i, 1).value) # print(id_list) else: print("重复！！表名:{} Sheet:{} 行数:{} 列数:{} 内容: {}".format(filenames, a, i, 1, self.ws.cell(i, 1).value), flush=True) print("==============================ID全部检查完毕！==================================\r\r") except Exception as e: print(e) os.system('pause') exit() #此处检查是否存在ERROR等异常数值 def check_str(self, sFileName): print("检查表格内是否存在###ERROR！！！等错误值") #sFileName = self.Get_FileName(excel_path) try: for filenames in sFileName: wb = load_workbook(excel_path + r"\\" + filenames) sheetnames = self.get_needchecksheetname(excel_path, filenames) for a in sheetnames: name_list = [] self.ws = wb.get_sheet_by_name(a) for i in range(2, self.ws.max_row + 1): for y in range(2, self.ws.max_column + 1): if type(self.ws.cell(i, y).value) == str: if str(self.ws.cell(i, y).value) not in name_list: if str(self.ws.cell(i, y).value) == "#ERROR!": print("发现##ERROR！！！表名:{} Sheet:{} 行数:{} 列数:{}".format(filenames, a, i, y)) name_list.append(self.ws.cell(i, y).value) print("==============================内容等异常数值检查完毕！==============================\r\r") except Exception as e: print(e) os.system('pause') exit() def check_Language(self, sFileName): print("专门检查语言表是否有漏翻译") logout = True#打印日志txt的总开关 log = [] #sFileName = self.Get_FileName(excel_path) try: for filenames in sFileName: if filenames == "语言总表_Language.xlsx": wb = load_workbook(excel_path + r"\\" + filenames) sheetnames = self.get_needchecksheetname(excel_path, filenames) for a in sheetnames: self.ws = wb.get_sheet_by_name(a) for y in range(2, self.ws.max_column + 1): if self.ws.cell(1, y).value in language: lan = self.ws.cell(1, y).value for i in range(6, self.ws.max_row + 1): # print("打印Sheet:{} 第{}行 第{}列的内容：{}".format(a, i, y, self.ws.cell(i, y).value)) if self.ws.cell(i, y).value == None and self.ws.cell(i, 1).value != None: print("打印Sheet:{} 语言：{} 第{}行 第{}列 为空，请检查！".format(a, lan, i, y)) log.append("打印Sheet:{} 语言：{} 第{}行 第{}列 为空，请检查！".format(a, lan, i, y)) if logout: print("日志文件输出已打开！") now_localtime = time.strftime("%y%m%d%H%M%S", time.localtime()) with open(now_localtime + ".txt", mode="w") as l: for w in log: l.write(str(w)) l.write("\n") l.close() print("输出完毕，请检查文件名:{}".format(now_localtime + ".txt")) print("==============================语言表检查完毕！==============================\r\r") except Exception as e: print(e) os.system('pause') exit() if __name__ == '__main__': excel_path = os.path.abspath("数据表") sFileName = "" no_list = [ "备注_buffer种类", "备注_buffer触发类型", "语言总表附录", "LanguageType", "备注相关", "价值", "母表", "存档", "奖励配置逻辑", "命名规则", "规则", "计算母表", "Sheet1", "Sheet2", "后坐力模板参考", "后坐力模板打印", "投掷物属性参照", "范围伤害衰减计算", "枪械伤害设计", "辅助瞄准", "骨骼挂点", "皮肤枪属性加成", "原档", "备注", "修正", "公式", "公式2", "设置表其他母表", "原件", "旧邮件", "语言参考" ]#这里是不需要检查的sheet language = [ "简体中文", "繁體", "英文", "泰文", "印尼文", "越南语", ]#这里是要检查的语言 s = XlsxCheck(excel_path, sFileName, no_list, language) s.run()
