# 获取 html 数据并写入目标文件 html_list = os.listdir(html_path) finished_doc = 1 for i in html_list: a, b = os.path.splitext(i) if b == '.html': parseHtml(html_path + i, a) print('[' + a + '] 文件已解析,当前已有' + str(finished_doc) + '个完成') finished_doc += 1
print('处理完毕!')
if __name__ == '__main__': main()
解析 html
解析 html 函数 parseHtml 使用 beautifulSoup 来将 html 的目标 dom 节点给提取出来; 使用正则匹配目标信息; 其中 table 里的数据采用 二维数组 来存储, 方便之后添加进 excel, xlwings 可以以 数组 的形式向xlsx 文档添加行, 或列 最后查看当前处理的 html 名字是否和 excel 文档匹配的, 如是则进入到数据写入 xlsx 环节
# Mains Mains = data['Mains'] # 定义一个二维数组存储数据 Mains_handle = [[0for y in range(3)] for x in range(3)] Mains_index = 0 # 对数据进行处理 for k, l in Mains.items(): for i, v in enumerate(l): if v[-2:] == 'Hz': Mains_handle[Mains_index][i] = v[:-3].strip() elif v[-1:] == 'V': Mains_handle[Mains_index][i] = round(float(v[:-1]) * math.sqrt(3), 2) else: Mains_handle[Mains_index][i] = v[:-1].strip() Mains_index += 1 # 将数据渲染至目标位置 - 纵向 sheet2.range('G167').options(transpose=True).value = Mains_handle[0] sheet2.range('K167').options(transpose=True).value = Mains_handle[1] sheet2.range('O167').options(transpose=True).value = Mains_handle[2] # sheet2.range('G167:R169').api.HorizontalAlignment = -4131
# Reserve Reserve = data['Reserve'] Reserve_handle = [[0for y in range(3)] for x in range(3)] Reserve_index = 0 for k, l in Reserve.items(): # 清空二维数组初始化元素 Reserve_handle[Reserve_index].clear() for i, v in enumerate(l): if v[-1] == 'V': Reserve_handle[Reserve_index].append(v[:-1].strip()) elif v[-2:] == 'Hz': Reserve_handle[Reserve_index].append(v[:-2].strip()) Reserve_index += 1 sheet2.range('G178').options(transpose=True).value = Reserve_handle[0] sheet2.range('K178').options(transpose=True).value = Reserve_handle[1] sheet2.range('O178').options(transpose=True).value = Reserve_handle[2] # sheet2.range('G178:R179').api.HorizontalAlignment = -4131
# output Output = data['Output'] Output_handle = [[0for y in range(9)] for x in range(3)] Output_index = 0 for k, l in Output.items(): for i, v in enumerate(l): # 两个行被清空了 ... Output_handle[Output_index][2] = '' Output_handle[Output_index][5] = '' if v[-1] == 'V': Output_handle[Output_index][0] = round(float(v[:-1].strip()) * math.sqrt(3), 2) Output_handle[Output_index][1] = v[:-1].strip() elif v[-2:] == 'Hz': Output_handle[Output_index][3] = v[:-2].strip() elif v[-1] == 'A': if v[-3:] == 'kVA': Output_handle[Output_index][6] = v[:-3].strip() else: Output_handle[Output_index][4] = v[:-1].strip() elif v[-2:] == 'PF': Output_handle[Output_index][7] = abs(float('0 PF'[:-2].strip())) else: Output_handle[Output_index][8] = float(l[3][:-3].strip()) * 3 / powerclass Output_index += 1 sheet2.range('G263').options(transpose=True).value = Output_handle[0] sheet2.range('K263').options(transpose=True).value = Output_handle[1] sheet2.range('O263').options(transpose=True).value = Output_handle[2] # sheet2.range('G263:R269').api.HorizontalAlignment = -4131
defrun(self): while self.is_running: try: time.sleep(self._interval) self._close_msgbox() except Exception as e: print(e, flush=True)
def_close_msgbox(self): ''' Click any button ("OK", "Yes" or "Confirm") to close message box. ''' # get handles of all top windows h_windows = [] win32gui.EnumWindows(lambda hWnd, param: param.append(hWnd), h_windows) # check each window for h_window in h_windows: # get child button with text OK, Yes or Confirm of given window h_btn = win32gui.FindWindowEx(h_window, None,'Button', None) ifnot h_btn: continue # check button text text = win32gui.GetWindowText(h_btn) ifnot text.strip().lower() in ('确定', 'ok', 'yes', 'confirm'): continue # click button win32gui.PostMessage(h_btn, win32con.WM_LBUTTONDOWN, None, None) time.sleep(0.2) win32gui.PostMessage(h_btn, win32con.WM_LBUTTONUP, None, None) time.sleep(0.2)
if __name__ == '__main__': t = MsgBoxListener(2) t.start() time.sleep(10) t.stop()
# Mains Mains = data['Mains'] # 定义一个二维数组存储数据 Mains_handle = [[0for y in range(3)] for x in range(3)] Mains_index = 0 # 对数据进行处理 for k, l in Mains.items(): for i, v in enumerate(l): if v[-2:] == 'Hz': Mains_handle[Mains_index][i] = v[:-3].strip() elif v[-1:] == 'V': Mains_handle[Mains_index][i] = round(float(v[:-1]) * math.sqrt(3), 2) else: Mains_handle[Mains_index][i] = v[:-1].strip() Mains_index += 1 # 将数据渲染至目标位置 - 纵向 sheet2.range('G167').options(transpose=True).value = Mains_handle[0] sheet2.range('K167').options(transpose=True).value = Mains_handle[1] sheet2.range('O167').options(transpose=True).value = Mains_handle[2] # sheet2.range('G167:R169').api.HorizontalAlignment = -4131
# Reserve Reserve = data['Reserve'] Reserve_handle = [[0for y in range(3)] for x in range(3)] Reserve_index = 0 for k, l in Reserve.items(): # 清空二维数组初始化元素 Reserve_handle[Reserve_index].clear() for i, v in enumerate(l): if v[-1] == 'V': Reserve_handle[Reserve_index].append(v[:-1].strip()) elif v[-2:] == 'Hz': Reserve_handle[Reserve_index].append(v[:-2].strip()) Reserve_index += 1 sheet2.range('G178').options(transpose=True).value = Reserve_handle[0] sheet2.range('K178').options(transpose=True).value = Reserve_handle[1] sheet2.range('O178').options(transpose=True).value = Reserve_handle[2] # sheet2.range('G178:R179').api.HorizontalAlignment = -4131
# output Output = data['Output'] Output_handle = [[0for y in range(9)] for x in range(3)] Output_index = 0 for k, l in Output.items(): for i, v in enumerate(l): # 两个行被清空了 ... Output_handle[Output_index][2] = '' Output_handle[Output_index][5] = '' if v[-1] == 'V': Output_handle[Output_index][0] = round(float(v[:-1].strip()) * math.sqrt(3), 2) Output_handle[Output_index][1] = v[:-1].strip() elif v[-2:] == 'Hz': Output_handle[Output_index][3] = v[:-2].strip() elif v[-1] == 'A': if v[-3:] == 'kVA': Output_handle[Output_index][6] = v[:-3].strip() else: Output_handle[Output_index][4] = v[:-1].strip() elif v[-2:] == 'PF': Output_handle[Output_index][7] = abs(float('0 PF'[:-2].strip())) else: Output_handle[Output_index][8] = float(l[3][:-3].strip()) * 3 / powerclass Output_index += 1 sheet2.range('G263').options(transpose=True).value = Output_handle[0] sheet2.range('K263').options(transpose=True).value = Output_handle[1] sheet2.range('O263').options(transpose=True).value = Output_handle[2] # sheet2.range('G263:R269').api.HorizontalAlignment = -4131
# 获取代编辑文件,收集名字为数组,写入后从数组删除 xlsb_files_list = os.listdir(xlsb_path) for i, v in enumerate(xlsb_files_list): a, b = os.path.splitext(v) if b == ('.xlsb'or'.xlsx'): xlsb_list.append(a)
# 抓取 html 数据并写入目标文件 html_list = os.listdir(html_path) finished_doc = 1 for i in html_list: a, b = os.path.splitext(i) if b == '.html': parseHtml(html_path + i, a) print('[' + a + '] 文件已解析,当前已有' + str(finished_doc) + '个完成') finished_doc += 1