你提出的需求是一个较为复杂的桌面应用程序，涉及文件读取、多线程网络探测（ping）、GUI 界面设计、排序逻辑、颜色标记、导出功能等。我们可以使用 **Python + PyQt5/PySide2** 实现这个窗口软件，并结合 `openpyxl` 处理 Excel 文件，`ping3` 或系统 `subprocess` 做 ping 操作。
---
### ✅ 功能概览
- 读取 D:\1.xlsx 的所有 sheet
- 提取 IP、车站、设备名称等字段
- 多线程持续 ping 所有 IP
- GUI 显示监控表格（含序号、时间戳、线路、IP、车站、设备名、断开次数、结果）
- 支持“开始监控”、“设置”、“导出”
- 设置项：线程数、ping间隔、超时、Excel路径、关键设备类型、同站TVM/AGM数量
- 自动排序规则：
- 断网的关键设备 → 黄色
- 断网且同站TVM/AGM ≥ 设置值 → 浅蓝
- 其他断网设备按断开次数降序
- 正常设备最后
- 导出当前断网设备为 Excel / TXT / PNG 图片（表格图）
---
## 🧩 完整实现代码（Python + PyQt5）
```python
import sys
import os
import threading
import time
import subprocess
from datetime import datetime
from collections import defaultdict
import openpyxl
import pandas as pd
from PyQt5.QtWidgets import (
QApplication, QMainWindow, QTableWidget, QTableWidgetItem,
QPushButton, QVBoxLayout, QHBoxLayout, QWidget, QFileDialog,
QDialog, QFormLayout, QLineEdit, QLabel, QMessageBox, QHeaderView,
QAbstractItemView, QComboBox
)
from PyQt5.QtCore import Qt, QTimer, QThread, pyqtSignal
from PyQt5.QtGui import QColor, QPixmap, QPainter, QFont
import matplotlib.pyplot as plt
from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
# =================== 配置类 ===================
class Settings:
def __init__(self):
self.thread_count = 10
self.ping_interval = 5
self.ping_timeout = 2
self.excel_path = "D:/1.xlsx"
self.critical_types = ["二层交换机", "三层交换机", "UPS"]
self.tvm_count_threshold = 2
self.agm_count_threshold = 2
settings = Settings()
# =================== Ping 工具函数 ===================
def ping_ip(ip, timeout=2):
try:
result = subprocess.run(
['ping', '-n', '1', '-w', str(int(timeout * 1000)), ip],
stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True
)
return 'Reply' in result.stdout or 'TTL' in result.stdout
except Exception:
return False
# =================== 数据加载 ===================
def load_excel_data(filepath):
if not os.path.exists(filepath):
return []
try:
wb = openpyxl.load_workbook(filepath, read_only=True)
all_data = []
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
headers = [cell.value for cell in ws[1]]
ip_col_idx = None
station_col_idx = None
device_col_idx = None
for i, h in enumerate(headers):
if h and "IP" in str(h):
ip_col_idx = i
elif h and ("车站" in str(h) or "站名" in str(h)):
station_col_idx = i
elif h and "设备名称" in str(h):
device_col_idx = i
if ip_col_idx is None:
continue
for row in ws.iter_rows(min_row=2, values_only=True):
ip_val = row[ip_col_idx]
if not ip_val:
continue
ip_str = str(ip_val).strip()
if not ip_str or ip_str.lower() == 'none':
continue
station = row[station_col_idx] if station_col_idx is not None else ""
device_name = row[device_col_idx] if device_col_idx is not None else ""
all_data.append({
"sheet": sheet_name,
"ip": ip_str,
"station": str(station) if station else "",
"device_name": str(device_name) if device_name else ""
})
wb.close()
return all_data
except Exception as e:
print(f"读取Excel失败: {e}")
return []
# =================== 主监控线程 ===================
class MonitorWorker(QThread):
update_signal = pyqtSignal(list)
def __init__(self):
super().__init__()
self.running = False
self.device_stats = {}
self.lock = threading.Lock()
def run(self):
self.running = True
data = load_excel_data(settings.excel_path)
if not data:
QMessageBox.warning(None, "警告", "无法加载Excel数据")
return
# 初始化统计信息
with self.lock:
for d in data:
key = d['ip']
if key not in self.device_stats:
self.device_stats[key] = {
"data": d,
"fail_count": 0,
"last_result": True,
"timestamp": datetime.now()
}
while self.running:
batch = list(self.device_stats.items())
# 分批并发执行
threads = []
step = settings.thread_count
for i in range(0, len(batch), step):
batch_items = batch[i:i + step]
thread_batch = []
def worker(items):
for key, stat in items:
ip = key
success = ping_ip(ip, settings.ping_timeout)
now = datetime.now()
with self.lock:
old_success = stat["last_result"]
stat["last_result"] = success
stat["timestamp"] = now
if not success:
stat["fail_count"] += 1
else:
stat["fail_count"] = 0 # 只要通一次就重置
time.sleep(0.1) # 防止系统资源占用过高
t = threading.Thread(target=worker, args=(batch_items,))
t.start()
threads.append(t)
for t in threads:
t.join()
# 发送更新信号
with self.lock:
emit_data = [v for k, v in self.device_stats.items()]
self.update_signal.emit(emit_data)
time.sleep(settings.ping_interval)
def stop(self):
self.running = False
# =================== 设置对话框 ===================
class SettingsDialog(QDialog):
def __init__(self, parent=None):
super().__init__(parent)
self.setWindowTitle("设置")
self.resize(400, 300)
layout = QFormLayout()
self.path_edit = QLineEdit(settings.excel_path)
self.browse_btn = QPushButton("浏览")
self.browse_btn.clicked.connect(self.browse_file)
path_layout = QHBoxLayout()
path_layout.addWidget(self.path_edit)
path_layout.addWidget(self.browse_btn)
layout.addRow("Excel路径:", path_layout)
self.thread_edit = QLineEdit(str(settings.thread_count))
self.interval_edit = QLineEdit(str(settings.ping_interval))
self.timeout_edit = QLineEdit(str(settings.ping_timeout))
self.tvm_edit = QLineEdit(str(settings.tvm_count_threshold))
self.agm_edit = QLineEdit(str(settings.agm_count_threshold))
self.critical_edit = QLineEdit(",".join(settings.critical_types))
layout.addRow("并发线程数:", self.thread_edit)
layout.addRow("Ping间隔(秒):", self.interval_edit)
layout.addRow("Ping超时(秒):", self.timeout_edit)
layout.addRow("关键设备类型:", self.critical_edit)
layout.addRow("同站TVM个数阈值:", self.tvm_edit)
layout.addRow("同站AGM个数阈值:", self.agm_edit)
buttons = QHBoxLayout()
self.ok_btn = QPushButton("确定")
self.cancel_btn = QPushButton("取消")
self.ok_btn.clicked.connect(self.accept)
self.cancel_btn.clicked.connect(self.reject)
buttons.addStretch()
buttons.addWidget(self.ok_btn)
buttons.addWidget(self.cancel_btn)
main_layout = QVBoxLayout()
main_layout.addLayout(layout)
main_layout.addLayout(buttons)
self.setLayout(main_layout)
def accept(self):
try:
settings.excel_path = self.path_edit.text().strip()
settings.thread_count = int(self.thread_edit.text())
settings.ping_interval = float(self.interval_edit.text())
settings.ping_timeout = float(self.timeout_edit.text())
settings.tvm_count_threshold = int(self.tvm_edit.text())
settings.agm_count_threshold = int(self.agm_edit.text())
types_text = self.critical_edit.text().strip()
settings.critical_types = [t.strip() for t in types_text.split(",") if t.strip()]
if settings.thread_count < 1:
raise ValueError("线程数至少为1")
if settings.ping_interval <= 0:
raise ValueError("间隔必须大于0")
if not os.path.exists(settings.excel_path):
raise FileNotFoundError("Excel文件不存在")
super().accept()
except Exception as e:
QMessageBox.critical(self, "错误", f"设置保存失败：{e}")
def browse_file(self):
file, _ = QFileDialog.getOpenFileName(self, "选择Excel文件", "", "Excel Files (*.xlsx)")
if file:
self.path_edit.setText(file)
# =================== 主窗口 ===================
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("网络设备Ping监控系统")
self.resize(1200, 600)
# 表格控件
self.table = QTableWidget()
self.table.setColumnCount(8)
self.table.setHorizontalHeaderLabels([
"序号", "时间戳", "线路", "IP", "车站", "设备名称", "断开次数", "结果"
])
self.table.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.table.setSelectionBehavior(QAbstractItemView.SelectRows)
self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 按钮布局
self.start_btn = QPushButton("开始监控")
self.settings_btn = QPushButton("设置")
self.export_btn = QPushButton("导出断网设备")
btn_layout = QHBoxLayout()
btn_layout.addWidget(self.start_btn)
btn_layout.addWidget(self.settings_btn)
btn_layout.addWidget(self.export_btn)
btn_layout.addStretch()
layout = QVBoxLayout()
layout.addLayout(btn_layout)
layout.addWidget(self.table)
container = QWidget()
container.setLayout(layout)
self.setCentralWidget(container)
# 连接按钮
self.start_btn.clicked.connect(self.toggle_monitor)
self.settings_btn.clicked.connect(self.open_settings)
self.export_btn.clicked.connect(self.export_failure_devices)
# 定时器刷新表格
self.timer = QTimer()
self.timer.timeout.connect(self.refresh_table)
self.monitor_worker = None
# 加载初始数据
self.all_device_data = []
def toggle_monitor(self):
if self.monitor_worker and self.monitor_worker.isRunning():
self.monitor_worker.stop()
self.monitor_worker.wait()
self.monitor_worker = None
self.start_btn.setText("开始监控")
self.timer.stop()
else:
self.monitor_worker = MonitorWorker()
self.monitor_worker.update_signal.connect(self.on_update_received)
self.monitor_worker.start()
self.timer.start(1000) # 每秒刷新UI
self.start_btn.setText("停止监控")
def on_update_received(self, data):
self.all_device_data = data
def refresh_table(self):
if not self.all_device_data:
return
# 统计每站的TVM和AGM数量
station_counter = defaultdict(lambda: {"TVM": 0, "AGM": 0})
for stat in self.all_device_data:
station = stat["data"]["station"]
name = stat["data"]["device_name"]
if "TVM" in name or "自动售票机" in name:
station_counter[station]["TVM"] += 1
elif "AGM" in name or "闸机" in name:
station_counter[station]["AGM"] += 1
# 排序：先分组
critical_failures = [] # 关键设备断网
tvm_agm_failures = [] # 同站TVM/AGM过多断网
other_failures = [] # 其他断网
normal = []
for stat in self.all_device_data:
is_critical = any(t in stat["data"]["device_name"] for t in settings.critical_types)
is_down = not stat["last_result"]
station = stat["data"]["station"]
tvm_count = station_counter[station]["TVM"]
agm_count = station_counter[station]["AGM"]
item = (stat, is_critical, tvm_count, agm_count)
if is_down:
if is_critical:
critical_failures.append(item)
elif tvm_count >= settings.tvm_count_threshold or agm_count >= settings.agm_count_threshold:
tvm_agm_failures.append(item)
else:
other_failures.append(item)
else:
normal.append(item)
# 排序：断开次数从高到低
def sort_key(x):
return -x[0]["fail_count"]
critical_failures.sort(key=sort_key)
tvm_agm_failures.sort(key=sort_key)
other_failures.sort(key=sort_key)
sorted_data = (
critical_failures +
tvm_agm_failures +
other_failures +
sorted(normal, key=sort_key)
)
# 填充表格
self.table.setRowCount(len(sorted_data))
for row_idx, (stat, is_critical, tvm_cnt, agm_cnt) in enumerate(sorted_data):
d = stat["data"]
fail_count = stat["fail_count"]
is_up = stat["last_result"]
timestamp = stat["timestamp"].strftime("%H:%M:%S")
def set_item(col, text, color=None):
item = QTableWidgetItem(text)
item.setTextAlignment(Qt.AlignCenter)
if color:
item.setBackground(QColor(*color))
self.table.setItem(row_idx, col, item)
set_item(0, str(row_idx + 1))
set_item(1, timestamp)
set_item(2, d["sheet"])
set_item(3, d["ip"])
set_item(4, d["station"])
set_item(5, d["device_name"])
set_item(6, str(fail_count))
result_text = "✅ 正常" if is_up else "🔴 断开"
bg_color = None
if not is_up:
if is_critical:
bg_color = (255, 255, 0) # 黄色
elif tvm_cnt >= settings.tvm_count_threshold or agm_cnt >= settings.agm_count_threshold:
bg_color = (173, 216, 230) # 浅蓝
else:
bg_color = (255, 0, 0) # 红色
set_item(7, result_text, bg_color)
def open_settings(self):
dialog = SettingsDialog(self)
if dialog.exec_() == QDialog.Accepted:
QMessageBox.information(self, "提示", "设置已保存")
def export_failure_devices(self):
if not self.all_device_data:
QMessageBox.warning(self, "警告", "暂无数据可导出")
return
failures = [s for s in self.all_device_data if not s["last_result"]]
if not failures:
QMessageBox.information(self, "提示", "当前无断网设备")
return
filename, _ = QFileDialog.getSaveFileName(
self, "导出文件", "", "Excel文件 (*.xlsx);;文本文件 (*.txt);;图片文件 (*.png)"
)
if not filename:
return
if filename.endswith(".xlsx"):
self.export_to_excel(failures, filename)
elif filename.endswith(".txt"):
self.export_to_txt(failures, filename)
elif filename.endswith(".png"):
self.export_to_image(failures, filename)
else:
QMessageBox.warning(self, "错误", "不支持的文件格式")
def export_to_excel(self, failures, filepath):
rows = []
for stat in failures:
d = stat["data"]
rows.append([
d["sheet"], d["ip"], d["station"], d["device_name"],
stat["fail_count"], stat["timestamp"].strftime("%Y-%m-%d %H:%M:%S")
])
df = pd.DataFrame(rows, columns=["线路", "IP", "车站", "设备名称", "断开次数", "最后断开时间"])
df.to_excel(filepath, index=False)
QMessageBox.information(self, "成功", f"已导出Excel至：\n{filepath}")
def export_to_txt(self, failures, filepath):
with open(filepath, 'w', encoding='utf-8') as f:
f.write("=== 断网设备列表 ===\n")
f.write(f"导出时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n")
for stat in failures:
d = stat["data"]
line = f"[{d['sheet']}] {d['ip']} | {d['station']} | {d['device_name']} | 断开次数: {stat['fail_count']}\n"
f.write(line)
QMessageBox.information(self, "成功", f"已导出TXT至：\n{filepath}")
def export_to_image(self, failures, filepath):
fig, ax = plt.subplots(figsize=(10, len(failures) * 0.4 + 2))
ax.axis('tight')
ax.axis('off')
data = [[
d["data"]["sheet"],
d["data"]["ip"],
d["data"]["station"],
d["data"]["device_name"],
d["fail_count"],
d["timestamp"].strftime("%H:%M:%S")
] for d in failures]
col_labels = ["线路", "IP", "车站", "设备名称", "断开次数", "最后时间"]
table = ax.table(cellText=data, colLabels=col_labels, cellLoc='center', loc='center')
table.auto_set_font_size(False)
table.set_fontsize(9)
table.scale(1, 2)
# 上色
colors = {
"critical": "#FFFF00",
"tvm_agm": "#ADD8E6",
"normal": "#FF0000"
}
for i, stat in enumerate(failures):
is_critical = any(t in stat["data"]["device_name"] for t in settings.critical_types)
station = stat["data"]["station"]
tvm_cnt = sum(1 for s in failures if s["data"]["station"] == station and ("TVM" in s["data"]["device_name"]))
agm_cnt = sum(1 for s in failures if s["data"]["station"] == station and ("AGM" in s["data"]["device_name"]))
row_color = "white"
if is_critical:
row_color = colors["critical"]
elif tvm_cnt >= settings.tvm_count_threshold or agm_cnt >= settings.agm_count_threshold:
row_color = colors["tvm_agm"]
else:
row_color = colors["normal"]
for j in range(len(col_labels)):
table[(i + 1, j)].set_facecolor(row_color)
plt.title("断网设备监控快照", fontsize=14, pad=40)
plt.savefig(filepath, bbox_inches='tight', dpi=150)
plt.close()
QMessageBox.information(self, "成功", f"已导出图片至：\n{filepath}")
# =================== 启动应用 ===================
if __name__ == "__main__":
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec_())
```
---
### 🔍 代码说明
| 模块 | 作用 |
|------|------|
| `Settings` | 存储全局配置参数 |
| `load_excel_data()` | 读取 Excel 所有 sheet，提取 IP、车站、设备名等 |
| `ping_ip()` | 使用系统 `ping` 命令检测连通性 |
| `MonitorWorker` | 多线程后台持续 ping，更新状态 |
| `SettingsDialog` | 设置界面，修改参数 |
| `MainWindow` | 主界面，包含表格、按钮、排序、导出等功能 |
| `export_to_xxx()` | 支持导出 Excel、TXT、PNG 图片 |
---
### 🎨 颜色策略
| 条件 | 背景色 | 含义 |
|------|--------|------|
| 断开 + 关键设备 | 黄色 | 高优先级告警 |
| 断开 + 同站 TVM/AGM ≥ 阈值 | 浅蓝色 | 影响较大站点 |
| 断开 + 其他 | 红色 | 普通断网 |
| 正常 | 白色 | 正常 |
---
### ⚠️ 注意事项
1. **权限问题**：确保程序有权限执行 `ping` 命令（Windows 一般默认允许）。
2. **Excel 格式**：确保 `1.xlsx` 第一行是表头，且包含 “IP”、“车站”、“设备名称” 字段。
3. **依赖安装**：
```bash
pip install pyqt5 openpyxl pandas matplotlib
```
4. **图标优化**：可添加 `.setWindowIcon()` 增强 UI。
5. **日志记录**：可扩展写入日志文件功能。
---
###
本文探讨了N-Up类型数据窗口中序号列的不同布局方法，包括从左到右由上及下、由上及下从左到右以及考虑页面连续性的布局规则，并提供了具体的计算表达式。
扫一扫
1138
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
