1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
| import sys
from contextlib import redirect_stdout
import pandas as pd
from netmiko import ConnectHandler
import re
import openpyxl
from openpyxl.styles import Font, Alignment
def output_txt():
device_info = pd.read_excel('devices_h3c.xls')
connection_params = {
'device_type': 'hp_comware',
'username': 'test',
'password': '123',
}
results = []
for index, row in device_info.iterrows():
dname = row['dname']
ip = row['ip']
connection_params['host'] = ip
try:
connection = ConnectHandler(**connection_params)
command = 'dis transceiver interface | exclude ab'
output = connection.send_command_timing(command, delay_factor=2)
print(output)
file = open('output_h3c.txt', 'a', encoding='utf-8')
with redirect_stdout(file):
print(f"Output from {dname} ({ip}):\n{output}\n")
file.close()
except Exception as e:
print(f"连接到 {dname} ({ip}) 失败: {e}")
finally:
try:
connection.disconnect()
except:
pass
def parse_transceiver_info(output):
results = []
device_name = ""
ip_address = ""
for line in output.split('\n'):
if line.startswith("Output from"):
match = re.search(r"Output from (\S+) \((\S+)\):", line)
if match:
device_name = match.group(1)
ip_address = match.group(2)
elif "transceiver information:" in line:
port = line.split()[0]
info = {"设备名称": device_name, "IP地址": ip_address, "端口": port}
results.append(info)
elif ":" in line:
key, value = line.split(":", 1)
key = key.strip()
value = value.strip()
if key == "Transceiver Type":
results[-1]["类型"] = value
elif key == "Wavelength(nm)":
results[-1]["波长(nm)"] = value
elif key == "Transfer Distance(m)":
results[-1]["传输距离(m)"] = value
elif key == "Manu. Serial Number":
results[-1]["序列号"] = value
elif key == "Manufacturing Date":
results[-1]["生产日期"] = value
elif key == "Vendor Name":
results[-1]["厂商"] = value
return results
def export_to_excel(data, filename):
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "光模块信息"
headers = ["设备名称", "IP地址", "端口", "类型", "波长(nm)", "传输距离(m)", "序列号", "生产日期", "厂商"]
for col, header in enumerate(headers, start=1):
cell = ws.cell(row=1, column=col, value=header)
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='center')
for row, item in enumerate(data, start=2):
for col, key in enumerate(headers, start=1):
ws.cell(row=row, column=col, value=item.get(key, ""))
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
try:
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = (max_length + 2)
ws.column_dimensions[column_letter].width = adjusted_width
wb.save(filename)
if __name__ == "__main__":
output_txt()
with open("output_h3c.txt", "r", encoding="utf-8") as file:
output = file.read()
transceiver_info = parse_transceiver_info(output)
export_to_excel(transceiver_info, "光模块信息_h3c.xlsx")
print("光模块信息已成功导出到光模块信息_h3c.xlsx")
|
