-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathxlsx2html.py
executable file
·337 lines (294 loc) · 11.4 KB
/
xlsx2html.py
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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
#!/usr/bin/env python
from openpyxl import load_workbook
import json, datetime
from mac_vendor_lookup import MacLookup
maclookup = MacLookup()
#print('populating MAC vendor table')
#mactable.update_vendors() # <- This can take a few seconds for the download
class Ports():
'''A class for reading a spreadsheet generated by BNL ITD explaining
the behavior on the ports of each managed network switch and
writing a useful HTML file summarizing that data.
Example
=======
from xlsx2html import Ports
m = Ports()
m.spreadsheet = '06BM port info.xlsx'
m.html = 'test.html'
m.read_spreadsheet() # read data from spreadsheet
m.make_html() # write data to an html file
'''
def __init__(self):
self.spreadsheet = 'current.xlsx'
self.patchpanel = 'PatchPanel.xlsx'
self.switch_data = {}
self.patch_data = {}
self.html = 'test.html'
self.singlepage = False
self.cssfile = 'ports.css'
## this should work at any beamline by modding the VLAN number by 10
# 0 1 2 3 4 5 6 7 8 9
self.port_roles = ['SCI', 'CAM', 'INST', 'EPICS', '4', '5', '6', '7', '8', 'MGMT',]
def read_patchpanel(self, spreadsheet=None):
if spreadsheet is None:
spreadsheet = self.patchpanel
if spreadsheet is None:
print('You need to specify the path to the patch panel spreadsheet')
return()
print(f'Reading path panel data from {spreadsheet}')
workbook = load_workbook(spreadsheet, read_only=True);
worksheet = workbook.active
for row in worksheet.rows:
if row[0].value is None:
continue
if 'Cable' in str(row[0].value):
continue
this = {'Cable number' : row[0].value,
'Patch panel number' : row[1].value,
'Patch panel label' : row[2].value,
'Switch' : row[3].value,
'Port number' : row[4].value,
'dangling' : row[5].value,
}
if this["Switch"] is not None:
key = f'net-06bm-{this["Switch"]}:{this["Port number"]}'
self.patch_data[key] = this
def read_spreadsheet(self, spreadsheet=None):
'''Read spreadsheet data, storing its contents in a dict '''
if spreadsheet is None:
spreadsheet = self.spreadsheet
if spreadsheet is None:
print('You need to specify the path to a spreadsheet')
return()
print(f'Reading data from {spreadsheet}')
workbook = load_workbook(spreadsheet, read_only=True);
worksheet = workbook.active
for row in worksheet.rows:
if row[0].value is None:
continue
if row[0].value == 'Switch':
continue
try:
if '/' in row[1].value:
if row[1].value.split('/')[1] != '1': # skip Ports like 1/3/1 and such
continue
pnum = row[1].value.split('/')[2]
else:
pnum = row[1].value
this = {'Port number' : pnum,
'Link' : row[2].value,
'Duplex' : row[3].value,
'Speed' : row[4].value,
'Tag' : row[5].value,
'VLAN' : row[6].value,
'MAC address' : row[7].value,
'Name' : row[8].value,
'IP address' : row[9].value,
'notes' : row[10].value,
}
if row[0].value in self.switch_data:
self.switch_data[row[0].value].append(this)
else:
self.switch_data[row[0].value] = [this,]
except:
pass
def to_json(self, filename='ports.json'):
'''Save spreadsheet data to a JSON file'''
with open(filename, 'w') as fp:
json.dump(self.switch_data, fp, indent=4)
def make_html(self):
'''Write the contents of the spreadsheet to a pretty html file'''
if self.switch_data == {}:
print('It seems you have not yet read a spreadsheet')
return()
##########
# header #
##########
if self.singlepage is True:
with open(self.cssfile) as x: css = x.read()
page = f'''
<!DOCTYPE html>
<html>
<head>
<style>
{css}
</style>
</head>
'''
else:
page = f'''
<html>
<head>
<link rel="stylesheet" href="{self.cssfile}" />
</head>
'''
page += f''' <body>
<fieldset class="container">
<legend class="insetlabel">BMM Switch Map</legend>
<p>
</p>
<ul>
<li><a href="https://github.com/NSLS-II-BMM/switch-pretty-printer">Go to Github repository</a></li>
<li>Patch panel cables in RGC3 are numbered to match the patch numbering.</li>
<li>Cables should be moved on the switch, not on the patch panel.</li>
<li>Cable numbers in RGC3 count from 1 to 48.</li>
<li>Cable numbers in RGA1 count from 49 to 70.</li>
<li>Capture cable changes in the <span class=file>current.xlsx</span> and <span class=file>PatchPanel.xlsx</span> files.</li>
<li>Writing date: {datetime.date.today().strftime("%B %d, %Y")}</li>
</ul>
</fieldset>'''
for sw in self.switch_data.keys():
## h1 for this switch + grid wrapper div
page = page + f'''
<p>
</p>
<fieldset class="container">
<legend class="insetlabel">{sw}</legend>
<p>
</p>
<div class="wrapper">\n'''
## generate a div for the table explaining each port
for i,this in enumerate(self.switch_data[sw]):
if i == 0:
text = ' <div class="box box1">' + self.oneport(this,sw) + ' </div>\n'
#print(text, '\n')
elif i == 1:
text = ' <div class="box box2">' + self.oneport(this,sw) + ' </div>\n'
#print(text, '\n')
else:
text = ' <div class="box">' + self.oneport(this,sw) + ' </div>\n'
#print(text, '\n')
page = page + text
## close the wrapper div
page = page + ''' </div>
</fieldset>'''
##########
# footer #
##########
page = page + '''
</body>
</html>
'''
with open(self.html, 'w') as fh:
fh.write(page)
print(f'Wrote html to {self.html}')
def regularize_mac_address(self, mac):
#if ':' in mac:
# return mac.lower()
if mac.lower() == 'no mac':
return ''
mac = mac.replace('.','').replace(':','')
return '.'.join(mac[i:i+4] for i in range(0,12,4)).lower()
#return ':'.join(mac[i:i+2] for i in range(0,12,2)).lower()
def boxify(self, word):
'''Convert a word to be spelled by unicode points in the Enclosed
Alphanumeric Supplement section.
e.g. "Negative Squared Latin Capital Letter X"
See https://unicode-table.com/en/#1F173'''
boxedword = ''
for letter in word:
character = f'&#{127247+ord(letter)};'
boxedword = boxedword + character
return(boxedword)
def oneport(self, this, sw):
'''Generate a table that will write one div of the output html file.
This table contains the data from a single port. The div looks
something like this:
+--------------------------+
| Port VLAN: ### |
| number Tag: yes/no |
| Speed: speed |
| |
| IP address on this port |
| DNS name on this port |
| MAC address on this port |
| patch panel connected |
+--------------------------+
'''
form = '''
<table>
<tr>
<td rowspan=3 style="vertical-align: top;"><span class="port">{portn}</span></td>
<td><span class="minor">VLAN:</span></td>
<td><span class="vlan">{vlan_role}</span></td>
</tr>
<tr>
<td><span class="minor">Tag:</span></td>
<td><span class="minor">{tag}</span></td>
</tr>
<tr>
<td><span class="minor">Speed:</span></td>
<td><span class="minor">{speed}</span></td>
</tr>
<tr>
<td colspan=3 align=center><span class="major">{ip}</span></td>
</tr>
<tr>
<td colspan=3 align=center><span class="name">{name}</span></td>
</tr>
<tr>
<td colspan=3 align=center><span class="mac">{mac}</span></td>
</tr>
<tr>
<td colspan=3 align=center><span class="vendor">{vendor}</span></td>
</tr>
<tr>
<td colspan=3 align=center><span class="notes">{notes}</span></td>
</tr>
<tr>
<td colspan=3 align=center><span class="patchpanel">{patchpanel}</span></td>
</tr>
</table>
'''
if this['VLAN'] < 10 :
role = ''
else:
n = this['VLAN'] % 10
role = self.port_roles[n]
this_ip = this['IP address']
if this['IP address'].lower() == 'no ip':
#this_ip = '🆄🅽🆄🆂🅴🅳' # a schmancy way of saying "unused"
this_ip = self.boxify('unused')
this_name = this['Name']
if this['Name'] is None or this['Name'].lower() == 'none':
this_name = ' '
if this['notes'] is None:
this['notes'] = ''
mac_address = self.regularize_mac_address(this['MAC address'])
try:
vendor = maclookup.lookup(mac_address)
except:
vendor = ''
patchpanel = ''
key = f'{sw}:{this["Port number"]}'
if key in self.patch_data:
patchpanel = f'patch {self.patch_data[key]["Patch panel number"]} ({self.patch_data[key]["Patch panel label"]})'
return(form.format(duplex = this['Duplex'],
speed = this['Speed'],
tag = this['Tag'],
vlan_role = f'{this["VLAN"]}/{role}',
#vlan = this['VLAN'],
#role = role,
portn = this['Port number'],
ip = this_ip,
name = this_name,
mac = mac_address,
vendor = vendor,
notes = this['notes'],
patchpanel = patchpanel,
))
def main():
m=Ports()
m.spreadsheet = 'current.xlsx'
m.patchpanel = 'PatchPanel.xlsx'
m.html = 'example_output.html'
m.singlepage = True
m.read_patchpanel()
m.read_spreadsheet()
m.to_json()
m.make_html()
if __name__ == "__main__":
main()