def get_all_excel_files(path):
'''
获取指定目录下所有的Excel文件
Params:
ppath: String 存放Excel的根目录
Return:
指定目录下所有的Excel文件
'''
_files = [] # 存放返回的文件列表
_dir_cnt = 0 # 记录包含文件的文件夹数量
# 遍历所有文件夹，获取相应的信息
for _dirpath, _dirnames, _filenames in os.walk(path):
# 遍历文件夹中的文件
_dir_cnt += 1
for _filename in _filenames:
# 将所有的Excel文件（剔除了临时文件）加载到文件列表中
if (os.path.splitext(_filename)[1] in [
'.xlsx', '.xls'
]) and (not _filename.startswith('~')):
_files.append(os.path.join(_dirpath, _filename))
# print('包含Excel文件的目录共【{}】个'.format(_dir_cnt))
if len(_files) < 1:
# 文件夹内无Excel文件
LogTool.error("文件夹内无Excel：{0}".format(path))
return _files
def _show_ret(self, content):
if not self.ov():
LogTool.error("程序奔溃啦！ 快点联系开发者！")
self.run_text.insert(INSERT, "程序奔溃啦！ 快点联系开发者！")
return
self.ret_text.insert(INSERT, content)
self._show_run(content)
def change_to_int(numb):
"""
转化为int
:param numb:
:return:
"""
_result = None
if isinstance(numb, int):
return numb
# isinstance(numb, numpy.int64) or isinstance(numb, numpy.float64)
# if isinstance(numb, str) or isinstance(numb, float):
# try:
# _result = int(numb)
# except Exception as e:
# LogTool.error("{0}转int失败：{1}".format(type(numb), numb))
# finally:
# return _result
try:
_result = int(numb)
except Exception as e:
LogTool.error("{0}转int失败：{1}".format(type(numb), numb))
finally:
return _result
def get_time_by_str(str):
try:
ret = datetime.strptime(str, "%Y-%m-%d %H:%M:%S")
LogTool.info(f'格式化后的时间为：{ret}')
return ret
except:
return None
def _next_btn_back(self):
"""
切换下一个
:return:
"""
LogTool.info("开始下个报告")
assessmentUI = AssessmentUI()
assessmentUI.choose_next()
self._handle()
def wrapper(*args, **kwargs):
try:
_ret = func(*args, **kwargs)
return _ret
except Exception as err:
LogTool.error(traceback.format_exc())
LogTool.info(
f'函数异常：【{str(func)}】, args:【{args}】， kwargs:【{kwargs}】')
return None
def wrapper(*args, **kwargs):
LogTool.info('开始函数：【{0}】'.format(func.__name__))
# LogTool.info(f'参数：【{args}】【{kwargs}】')
_stime = dt.datetime.now()
_ret = func(*args, **kwargs)
_etime = dt.datetime.now()
LogTool.info('结束函数：【{0}】, 执行时间：【{1}】'.format(func.__name__,
_etime - _stime))
return _ret
def zip_file_path(cls, input_path, output_name):
# output_name = input_path + '.zip' # 压缩后文件夹的名字
z = zipfile.ZipFile(output_name, 'w', zipfile.ZIP_DEFLATED) # 参数一：文件夹名
for dirpath, dirnames, filenames in os.walk(input_path):
fpath = dirpath.replace(input_path,
'') # 这一句很重要，不replace的话，就从根目录开始复制
fpath = fpath and fpath + os.sep or '' # 这句话理解我也点郁闷，实现当前文件夹以及包含的所有文件的压缩
for filename in filenames:
z.write(os.path.join(dirpath, filename), fpath + filename)
LogTool.info("压缩成功")
z.close()
def _pos(self, x, y):
"""
点击
:param x:
:param y:
:return:
"""
LogTool.info(f"点击鼠标【{x}】【{y}】")
win32api.SetCursorPos([int(x), int(y)])
win32api.mouse_event(
win32con.MOUSEEVENTF_LEFTUP | win32con.MOUSEEVENTF_LEFTDOWN, 0, 0,
0, 0)
def _show_run(self, content):
"""
显示
:param content:
:return:
"""
if not self.ov():
LogTool.error("程序奔溃啦！ 快点联系开发者！")
self.run_text.insert(INSERT, "程序奔溃啦！ 快点联系开发者！")
return
LogTool.info(content)
self.run_text.insert(INSERT, content)
def write_file(file_path):
"""
写文件
:param file_path:
:return:
"""
w = None
try:
w = open(file_path, 'w+')
return w
except Exception:
LogTool.error("打开文件【%s】出错" % file_path)
w.close() if w else None
return None
def _open_file(self):
"""
打开文件操作
:param file_path: 文件路径
:return:
"""
ShowTool.show("打开文件")
f = None
try:
with open(os.path.abspath(self.file_path), 'r+',
encoding='gbk') as f:
self.content = f.read()
except Exception as e:
LogTool.error(f"打开文件出错;【{str(e)}】")
finally:
f.close() if f else None
def open_file(file_path):
"""
打开文件操作
:param file_path: 文件路径
:return:
"""
f = None
content = None
try:
with open(file_path, 'r+', encoding='utf8') as f:
content = f.read()
except Exception:
LogTool.error("打开文件出错")
finally:
f.close() if f else None
return content
def save_to_excel(pheader,
pvals,
pfname,
header_style=None,
val_style=None,
psname='Sheet1'):
'''
保存数据至Excel
:param pheader: List 标题
:param pvals: List 内容
:param pfname: String 路径
:param header_style: 标题格式
:param val_style: 内容格式
:param psname: sheet名称
:return:
'''
if not os.path.isdir(os.path.dirname(pfname)):
os.makedirs(os.path.dirname(pfname))
_workbook = xlwt.Workbook(encoding='utf8') # 创建工作簿对象
_xlssheet = _workbook.add_sheet(psname)
# 填充标题
for _i, _v in enumerate(pheader):
if header_style is None:
_xlssheet.write(0, _i, _v)
else:
_xlssheet.write(0, _i, _v, header_style)
# 填充内容
for _r, _rv in enumerate(pvals):
for _c, cv in enumerate(_rv):
cv = str(cv)
# 一个单元格最长为32766
if len(cv) > 32766:
LogTool.error('写入Excel字段超长！长度{0}'.format(len(cv)))
cv = cv[:32766]
if val_style is None:
_xlssheet.write(_r + 1, _c, cv)
else:
_xlssheet.write(_r + 1, _c, cv, val_style)
# 保存excel文件
_workbook.save(pfname)
def change_to_str(val):
"""
转化str
:param val:
:return:
"""
_result = None
if val is None:
return None
if isinstance(val, str):
return val
if isinstance(val, int):
_result = str(val)
else:
try:
_result = str(val)
except Exception as e:
LogTool.error("{0}转str失败：{1}".format(type(val), val))
return _result
def is_file(file_path):
"""
判断文件和文件夹是否存在，不存在自动创建文件夹
:param file_path:
:return:
"""
if os.path.exists(file_path):
LogTool.info("文件路径【%s】存在" % file_path)
else:
dir_name = os.path.dirname(file_path)
if not os.path.exists(dir_name):
LogTool.info("文件路径【%s】不存在，将自动创建" % file_path)
os.makedirs(dir_name)
LogTool.info("路径创建【%s】完成" % dir_name)
def get_excel_file(path):
'''
获取Excel文件
Params:
path: String Excel路径
Return:
Excel文件
'''
try:
_excel = xlrd.open_workbook(path)
if not _excel:
LogTool.error('无此Excel！！！{0}'.format(path))
return _excel
except Exception as err:
LogTool.error(traceback.format_exc())
LogTool.print(f'Excel文件无法读取')
return None
def _handle(self):
"""
处理当前报告
:return:
"""
LogTool.info("开始生成报告")
assessmentUI = AssessmentUI()
assessmentUI.set_fore()
assessmentUI.open_report()
sleep(2)
LogTool.info("获取地址")
url = assessmentUI.get_url()
self._show_run("---------\n")
self._show_run(f"URL:{url}\n")
LogTool.info("解析参数")
# 聚焦
self._set_focus()
handleFile = HandleFile(url)
content = handleFile.run()
self.result_list.append(content)
content = "\t".join(content)
self._show_run(f"解析结果:\n")
self._show_ret(f"{content}\n")
def main(self):
if not self.ov():
LogTool.error("程序奔溃啦！ 快点联系开发者！")
return
title = "参数解析工具v3.0"
self.windowUI = Tk(className=title)
self.windowUI.geometry('320x560+0+0')
padx = 3
pady = 2
fm1 = Frame(self.windowUI)
# 打开程序
open_btn = Button(fm1, text="打开程序", command=self._open_btn_back)
open_btn.grid(row=0, column=0, padx=padx, pady=pady)
# 下个项目
next_pro_btn = Button(fm1,
text="下个项目",
command=self._next_pro_btn_back)
next_pro_btn.grid(row=0, column=1, padx=padx, pady=pady)
# 清空结果
clear_btn = Button(fm1, text="清空结果", command=self._clear_btn_back)
clear_btn.grid(row=0, column=2, padx=padx, pady=pady)
# 保存结果
save_btn = Button(fm1, text="保存结果", command=self._save_btn_back)
save_btn.grid(row=0, column=3, padx=padx, pady=pady)
fm1.grid(row=0)
fm2 = Frame(self.windowUI)
label = Label(fm2, text="输入报告数量：")
label.grid(row=0, column=0, padx=padx, pady=pady)
self.num_entry = Entry(fm2)
self.num_entry.insert("insert", 1)
self.num_entry.grid(row=0, column=1, padx=padx, pady=pady)
# 开始按钮
start_btn = Button(fm2, text="开始处理", command=self._start_btn_back)
start_btn.grid(row=0, column=2, padx=padx, pady=pady)
fm2.grid(row=1)
fm3 = Frame(self.windowUI)
label = Label(fm3, text="运行日志：")
label.grid(row=0, column=0, padx=padx, pady=pady)
# 结果展示
self.run_text = Text(fm3, width=20, height=30)
self.run_text.grid(row=1, column=0, padx=padx, pady=pady)
ShowTool.init(self.run_text)
label = Label(fm3, text="结果列表：")
label.grid(row=0, column=1, padx=padx, pady=pady)
# 结果展示
self.ret_text = Text(fm3, width=20, height=30)
self.ret_text.grid(row=1, column=1, padx=padx, pady=pady)
fm3.grid(row=2)
mainloop()
sleep(1)
self._get_hwnd()
def __init__(self, file_path):
self.file_path = file_path.replace("file:///", "") # 文件路径
LogTool.info(f"路径为：{self.file_path}")
self.content = None # 文本内容
pass
def wrapper(*args, **kwargs):
LogTool.info(f"开启多线程")
thr = Thread(target=f, args=args, kwargs=kwargs)
thr.start()
from project.plugs.show_tool import ShowTool
from project.plugs.type_tool import TypeTool
from project.toprice.assessment_UI import AssessmentUI
from project.toprice.handle_file import HandleFile
from project.toprice.project_UI import ProjectUI
try:
mainroot = os.path.dirname(os.path.abspath(__file__))
except NameError:
mainroot = os.path.dirname(os.path.abspath(sys.argv[0]))
ConfigTool.set_path(mainroot)
log_path = os.path.abspath(
os.path.join(ConfigTool.get_path(), ConfigTool.get_str("logging", "path")))
from project.plugs.log_tool import LogTool
LogTool.init(log_path)
LogTool.info("------------------启动项目-----------------------------")
LogTool.info(f"平台信息： 【{platform.platform()}】")
LogTool.info(f"当前路径： 【{os.getcwd()}】")
LogTool.info(f"系统变量： 【{sys.path}】")
LogTool.info(f"日志路径： 【{log_path}】")
LogTool.info(f"main路径： 【{ConfigTool.get_path()}】")
LogTool.info(f"python路径： 【{sys.executable}】")
class MainUI(object):
def __init__(self):
self.windowUI = None
self.ret_text = None
self.run_text = None
self.num_entry = None
def show(cls, content):
LogTool.info(content)
cls.ui.insert(INSERT, f"{content}\n")
def get_url(self):
"""
获取生成报告地址
:return:
"""
processId = None
content = None
cur_client = None
# 获取浏览器
for i in range(3):
cur_client = win32gui.GetForegroundWindow()
name = win32gui.GetClassName(cur_client)
thread, processId = win32process.GetWindowThreadProcessId(
cur_client)
LogTool.info(f"当前应用名【{name}】")
if "WidgetWin" in name:
LogTool.info("找到了浏览器！")
break
sleep(1)
win32api.keybd_event(13, 0, 0, 0) # 回车
win32api.keybd_event(13, 0, win32con.KEYEVENTF_KEYUP, 0) # 释放按键
# 杀死进程
# os.kill(processId, signal.CTRL_C_EVENT)
# 点击一下
left, top, right, bottom = win32gui.GetWindowRect(cur_client)
x = right - left
y = bottom - top
self._pos(left + x / 2, top + y / 2)
sleep(0.1)
# 通过tab方式获取url
for i in range(5):
win32gui.SetActiveWindow(cur_client)
win32gui.SetForegroundWindow(cur_client)
win32api.keybd_event(9, 0, 0, 0) # tab
win32api.keybd_event(9, 0, win32con.KEYEVENTF_KEYUP, 0) # 释放按键
sleep(0.5)
# 清空 剪切板
self.clipboard_set("")
sleep(0.1)
# 全选
win32api.keybd_event(17, 0, 0, 0) # Ctrl
win32api.keybd_event(65, 0, 0, 0) # A
win32api.keybd_event(17, 0, win32con.KEYEVENTF_KEYUP, 0) # 释放按键
win32api.keybd_event(65, 0, win32con.KEYEVENTF_KEYUP, 0) # 释放按键
sleep(0.1)
# 复制
win32api.keybd_event(17, 0, 0, 0) # Ctrl
win32api.keybd_event(67, 0, 0, 0) # C
win32api.keybd_event(17, 0, win32con.KEYEVENTF_KEYUP, 0) # 释放按键
win32api.keybd_event(67, 0, win32con.KEYEVENTF_KEYUP, 0) # 释放按键
sleep(0.1)
temp = self.clipboard_get()
LogTool.info(f"剪切板内容【{temp}】")
if "htm" in temp:
content = temp
LogTool.info(f"找到了网址{content}")
break
return content
def templet_excel(pvals,
pfname,
psindex,
ptitle_rnt=0,
ptitle_cnt=0,
is_by_row=True,
val_style=None,
is_auto_col=False,
is_new_sheet=False):
"""
按模板输出Excel
:param pvals: 数据
:param pfname: 模板路径
:param psindex: sheet序号
:param ptitle_rnt: 标题行数
:param ptitle_cnt: 标题列数
:param is_by_row: 是否按行写入
:param is_auto_col：
:param is_new_sheet: 是否是新添的sheet
:return:
"""
_workbook = xlrd.open_workbook(pfname, formatting_info=True)
_wb = copy(_workbook)
if is_new_sheet:
_sheet = _wb.add_sheet(psindex)
else:
_sheet = _wb.get_sheet(psindex)
max_length_dict = {}
for _r, _rv in enumerate(pvals):
for _c, cv in enumerate(_rv):
# 写入内容
if is_by_row:
# 按行写入
_r_result = _r + ptitle_rnt
_c_result = _c + ptitle_cnt
else:
# 按列写入
_r_result = _c + ptitle_rnt
_c_result = _r + ptitle_cnt
# 一个sheet 最多可以写65535*255个单元格
if _r_result > 65535 or _c_result > 255:
LogTool.error('写入文件过大，超过行列数据被舍弃：{0} - {1}'.format(
_r_result, _c_result))
continue
cv = str(cv)
# 一个单元格最长为32766
if len(cv) > 32766:
LogTool.error('写入Excel字段超长！长度{0}'.format(len(cv)))
cv = cv[:32766]
if val_style is None or _c_result >= len(val_style):
_sheet.write(_r_result, _c_result, cv)
# if re.match('file://', cv):
# # 文本内容为本地文件
# formula = xlwt.Formula('HYPERLINK("{0}";"{1}")'.format(cv[7:], cv[7:]))
# _sheet.write(_r_result, _c_result, formula)
else:
_sheet.write(_r_result, _c_result, cv,
val_style[_c_result])
# if re.match('file://', cv):
# # 文本内容为本地文件
# formula = xlwt.Formula('HYPERLINK("{0}";"{1}")'.format(cv[7:], cv[7:]))
# _sheet.write(_r_result, _c_result, formula)
# 更新最长字符数
if max_length_dict.__contains__(_c_result):
if max_length_dict[_c_result] < len(cv):
max_length_dict[_c_result] = len(cv)
else:
max_length_dict[_c_result] = len(cv)
# 更新列宽
if is_auto_col:
for key, value in max_length_dict.items():
_val = value * 256
_val = _val if _val < 65536 else 65535
_sheet.col(key).width = _val
_wb.save(pfname)
