python通过xlrd读取Excel表格和Template批量生成配置

发表于 | 分类于 | 阅读次数： | 字数： 5,773 | 阅读时长： 33

当需要配置的网络设备很多的时候，不可能都通过手动去配置。此时则需要利用脚本来批量生成配置。从而既保证了配置的准确性，也提高了配置的效率。

可以利用python的xrld库来读取execl表格，利用String库的Template模板来配合批量生成配置。

Template模板替换：

主要利用Template的substitute(config)方法进行替换。

substitute(mapping[, kws])

执行模板替换，返回一个新字符串。映射是任何类似于字典的对象，其键与模板中的占位符匹配。或者，可以提供关键字参数，其中关键字是占位符。当映射和kws都给定且存在重复时，kws中的占位符优先。

safe_substitute(mapping[, kws])

与substitute()类似，但如果映射和kws中缺少占位符，则不会引发KeyError异常，而原始占位符将完整地出现在结果字符串中。而且，与substitute()不同的是，\$的任何其他外观都将简单地返回\$，而不是引发ValueError。
当其他异常仍然可能发生时，这个方法被称为“安全”，因为它总是试图返回一个可用的字符串，而不是引发异常。在另一种意义上，safe_substitute()可能不是安全的，因为它将无声地忽略包含悬空的畸形模板

substitute()和safe_substitute()的主要区别：

  • substitute()：

    在没有匹配成功的参数是会引发异常。

  • safe_substitute()

    在没有匹配的时候会返回$，不会引发异常。

使用示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
>>> from string import Template
>>> s = Template('$who likes $what')
>>> s.substitute(who='tim', what='kung pao')
'tim likes kung pao'
>>> d = dict(who='tim')
>>> Template('Give $who $100').substitute(d)
Traceback (most recent call last):
...
ValueError: Invalid placeholder in string: line 1, col 11
>>> Template('$who likes $what').substitute(d)
Traceback (most recent call last):
...
KeyError: 'what'
>>> Template('$who likes $what').safe_substitute(d)
'tim likes $what'

示例程序：

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
# -*- coding: utf-8 -*-
from string import Formatter
from datetime import datetime
import time 

#时间格式化
d = datetime(2019, 12, 4, 21, 45, 58)
time = time.time()
print('{:%Y-%m-%d %H:%M:%S}'.format(d))

from string import Template

# 替换示例
s = Template('$who likes $what')
ss = s.substitute( who= 'cao', what = 'hui')
print(ss)

d = dict(who = 'cao')
s = Template('Give $who $$100').substitute(d)
print(s)

# 定义配置模板，使用$name， 标识出要替换的变量
tmpl = '''
vlan $vlanID
undo  stp global  enable  
description dto_$SwitchName
#
interface Vlan $vlanID
description dto_$SwitchName
ip address  $ServerGWIP   255.255.255.0
#
#
#
interface   $SwitchPort
description $SwitchName
port link-mode bridge
port link-type trunk
undo port trunk permit vlan 1
port trunk permit vlan $vlanID 4002 to 4003
#
'''

templ = Template(tmpl)
# 要替换的变量配置
config = {
        'vlanID' : 3101,
        'SwitchName' : 'sw1',
        'ServerGWIP' : '10.1.1.254',
        'SwitchPort' : 'Ethernet1/2'}
try:
    cfg = templ.substitute(config)#实现替换。
except  KeyError as err:
    print('请检查要替换的关键字: %s'  % err)
print(cfg)

程序执行结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2019-12-04 21:45:58
cao likes hui
Give cao $100

vlan 3101
undo  stp global  enable  
description dto_sw1
#
interface Vlan 3101
description dto_sw1
ip address  10.1.1.254   255.255.255.0
#
#
#
interface   Ethernet1/2
description sw1
port link-mode bridge
port link-type trunk
undo port trunk permit vlan 1
port trunk permit vlan 3101 4002 to 4003
#

xlrd库读取Execl表格：

利用如下简单操作，基本可以利用循环读取所需数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# -*- coding: utf-8 -*-

import xlrd

xlwt.Workbook()

excel_file = r'test.xlsx'

try:
    file = xlrd.open_workbook(excel_file)
except Exception as err:
    print(err)
#获取表格sheet名字
sheet = file.sheet_names()
print(sheet)

#获取指定sheet表
table = file.sheet_by_name('sheet1')

print(table.row(3)) #第三行
print(table.col(5, 3, 9)) #第五列
table.nrows #行数
table.ncols #列数

xlwt库写入Execl表格：

参考自：https://www.cnblogs.com/python-robot/p/9958352.html

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
# -*- coding: utf-8 -*-
#https://www.cnblogs.com/python-robot/p/9958352.html

import xlwt
# 创建一个workbook 设置编码
workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个worksheet
worksheet = workbook.add_sheet('My Worksheet')

# 写入excel
# 参数对应 行, 列, 值
worksheet.write(1,0, label = 'this is test')
# 保存
workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet')
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = 'Times New Roman' 
font.bold = True # 黑体
font.underline = True # 下划线
font.italic = True # 斜体字
style.font = font # 设定样式
worksheet.write(0, 0, 'Unformatted value') # 不带样式的写入

worksheet.write(1, 0, 'Formatted value', style) # 带样式的写入

workbook.save('formatting.xls') # 保存文件

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0,'My Cell Contents')

# 设置单元格宽度
worksheet.col(0).width = 33333
workbook.save('cell_width.xls')

#输入一个日期到单元格:
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls')

#向单元格添加一个公式:
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])
workbook.save('Excel_Workbook.xls')
    

#向单元格添加一个超链接:
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')) # Outputs the text "Google" linking to http://www.google.com
workbook.save('Excel_Workbook.xls')


#和并行和列
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.
workbook.save('Excel_Workbook.xls')

#设置单元格内容的对其方式:

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER 
alignment.vert = xlwt.Alignment.VERT_CENTER FIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')

#为单元格议添加边框:
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED 
  #  DASHED虚线
   # NO_LINE没有
    #THIN实线


#为单元格设置背景色:

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xlsx')

批量生成CRT或者Xshell会话（Session）文件：

当需要管理的设备很多的时候，如果一个个网CRT或者Xshell中添加的话，很费时间。所以可以利用小程序批量生成session文件，在生成中，可以将账号，密码也填进去，从而生成的session登录设备的时候不需要输入用户名，密码。

注意：该程序中调用的session模板，需要从CRT或者Xshell的会话路径中复制一个.ini或者.xsh文件，主要改变三行：

1
2
3
4
5
6
7
8
9
10
> CRT .ini文件中需要改变
> S:"Username"=$USERNAME
> S:"Password V2"=$PASSWORD
> S:"Hostname"=$DEV_IP
>     
> Xshell .xsh文件需要改变
> UserName=$USERNAME
> Password=$PASSWORD
> Host=$DEV_IP
>

替换示例小程序：

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
# -*- coding: utf-8 -*-
"""
Created on Wed Dec 25 09:33:05 2019

@author: cao
"""

import requests
from bs4 import BeautifulSoup
from string import Template
import re
import os
        
# 批量生成Xshell软件使用的session文件。
def gen_xshll_session(tmpl_path, all_dev, user, password):
    tmpl = ''
    with open(tmpl_path, 'r',  encoding='utf-16-le') as f:
        for line in f.readlines():
            tmpl += line
        f.close() 
    tmpl = Template(tmpl)
    if not os.path.exists('./Xshell/Session/'):
        os.makedirs('./Xshell/Session/')
    for dev_name in all_dev:
        dev_ip = all_dev[dev_name]
        config = {
            'DEV_IP' : dev_ip,
            'USERNAME' : user,
            'PASSWORD' : password}
        cfg = tmpl.safe_substitute(config)
        cfg_path = './Xshell/Session/' + dev_name + '.xsh'
        with open(cfg_path, 'w+', encoding='utf-16-le') as f:
            f.write(cfg)
            f.close()
            print('生成%s' % dev_name + '.xsh文件成功!')
            
# 批量生成CRT使用的session文件
def gen_crt_session(tmpl_path, all_dev, user, password):
    tmpl = ''
    with open(tmpl_path, 'r',  encoding='UTF-8-sig') as f:
        for line in f.readlines():
            tmpl += line
        f.close()  
    tmpl = Template(tmpl)
    if not os.path.exists('./secureCRT/Session/'):
        os.makedirs('./secureCRT/Session/')
    for dev_name in all_dev:
        dev_ip = all_dev[dev_name]
        config = {
            'DEV_IP' : dev_ip,
            'USERNAME' : user,
            'PASSWORD' : password}
        cfg = tmpl.safe_substitute(config)
        cfg_path = './secureCRT/Session/' + dev_name + '.ini'
        with open(cfg_path, 'w+', encoding='utf-8-sig') as f:
            f.write(cfg)
            f.close()
            print('生成%s' % dev_name + '.xsh文件成功!')
    
if __name__ == '__main__':
    #需要制定模板路径，ini或者xsh
    tmpl_path = 'crt_tmp.ini'#
    # 设备名和IP数据来源地址。可以在/后面添加过滤，例如添加p3,生成的只有p3机房的seesion
    url ='' #设备名和IP来源。可以是Excel表格或者其他形式
    user = 'user'
    # 密码是对应软件生成的密文。
    # 如果是CRT，需要去ini文件中拷贝秘文， Xshell需要去xsh文件中拷贝秘文。
    password = 'password' #密码需要不是明文密码，需要在对应的软件生成的session文件中复制对应的密文密码。
    all_dev = get_all_dev(url)
    #gen_xshll_session(tmpl_path, all_dev， user, password)
    gen_crt_session(tmpl_path, all_dev, user, password)

通过上面的程序会生成衣设备名为文件名的.ini 或者.xsh文件。将这些文件复制到CRT的默认会话管理文件夹或者Xshell默认的会话管理路径。从新打开软件即可。从而可以利用软件的会话管理器，输入关键字进行搜索指定会话。快速定位需要管理的设备。

crt_tmp.ini模板文件示例：

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
338
339
S:"Username"=$USERNAME
S:"Password"=
S:"Password V2"=$PASSWORD
S:"Login Script V2"=
S:"Login Script V3"=
D:"Session Password Saved"=00000001
S:"Local Shell Command Pre-connect V2"=
S:"Monitor Username"=
S:"Monitor Password"=
S:"Monitor Password V2"=
B:"Normal Font v2"=00000060
 eb ff ff ff 00 00 00 00 00 00 00 00 00 00 00 00 90 01 00 00 00 00 00 00 03 02 01 31 4c 00 75 00
 63 00 69 00 64 00 61 00 20 00 43 00 6f 00 6e 00 73 00 6f 00 6c 00 65 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 a0 00 00 00
B:"Narrow Font v2"=00000060
 f1 ff ff ff 00 00 00 00 00 00 00 00 00 00 00 00 90 01 00 00 00 00 00 01 00 00 00 01 4d 00 65 00
 6e 00 6c 00 6f 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 70 00 00 00
D:"Use Narrow Font"=00000000
S:"SCP Shell Password V2"=
S:"PGP Upload Command V2"=
S:"PGP Download Command V2"=
D:"Is Session"=00000001
S:"Protocol Name"=SSH2
D:"Request pty"=00000001
S:"Shell Command"=
D:"Use Shell Command"=00000000
D:"Force Close On Exit"=00000000
D:"Forward X11"=00000000
S:"XAuthority File"=
S:"XServer Host"=127.0.0.1
D:"XServer Port"=00001770
D:"XServer Screen Number"=00000000
D:"Enforce X11 Authentication"=00000001
D:"Request Shell"=00000001
D:"Max Packet Size"=00001000
D:"Pad Password Packets"=00000001
S:"Sftp Tab Local Directory V2"=C:\Users
S:"Sftp Tab Remote Directory"=
S:"Hostname"=$DEV_IP
S:"Firewall Name"=None
D:"Allow Connection Sharing"=00000000
D:"Disable Initial SFTP Extensions"=00000000
D:"[SSH2] Port"=00000016
S:"Key Exchange Algorithms"=ecdh-sha2-nistp521,ecdh-sha2-nistp384,ecdh-sha2-nistp256,diffie-hellman-group14-sha1,diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,gss-group1-sha1-toWM5Slw5Ew8Mqkay+al2g==,gss-gex-sha1-toWM5Slw5Ew8Mqkay+al2g==
D:"Use Global Host Key Algorithms"=00000001
S:"Host Key Algorithms"=ssh-rsa,ssh-ed25519,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,null,ssh-dss
S:"Cipher List"=aes256-ctr,aes192-ctr,aes128-ctr,aes256-cbc,aes192-cbc,aes128-cbc,twofish-cbc,blowfish-cbc,3des-cbc,arcfour
S:"MAC List"=hmac-sha2-512,hmac-sha2-256,hmac-sha1,hmac-sha1-96,hmac-md5,hmac-md5-96,umac-64@openssh.com
S:"SSH2 Authentications V2"=password,publickey,keyboard-interactive,gssapi
S:"Compression List"=none
D:"Compression Level"=00000005
D:"GEX Minimum Size"=00000800
D:"GEX Preferred Size"=00000800
D:"Use Global Public Key"=00000001
S:"Identity Filename V2"=
D:"Public Key Type"=00000000
D:"Public Key Certificate Store"=00000000
S:"PKCS11 Provider Dll"=
S:"Public Key Certificate Serial Number"=
S:"Public Key Certificate Issuer"=
D:"Use Certificate As Raw Key"=00000001
S:"GSSAPI Method"=auto-detect
S:"GSSAPI Delegation"=full
S:"GSSAPI SPN"=host@$(HOST)
D:"SSH2 Common Config Version"=00000006
D:"Enable Agent Forwarding"=00000002
D:"Transport Write Buffer Size"=00000000
D:"Transport Write Buffer Count"=00000000
D:"Transport Receive Buffer Size"=00000000
D:"Transport Receive Buffer Count"=00000000
D:"Sftp Receive Window"=00000000
D:"Sftp Maximum Packet"=00000000
D:"Sftp Parallel Read Count"=00000000
D:"Preferred SFTP Version"=00000000
S:"Port Forward Filter"=allow,127.0.0.0/255.0.0.0,0 deny,0.0.0.0/0.0.0.0,0
S:"Reverse Forward Filter"=allow,127.0.0.1,0 deny,0.0.0.0/0.0.0.0,0
D:"Port Forward Receive Window"=00000000
D:"Port Forward Max Packet"=00000000
D:"Port Forward Buffer Count"=00000000
D:"Port Forward Buffer Size"=00000000
D:"Auth Prompts in Window"=00000000
S:"Transfer Protocol Name"=None
D:"ANSI Color"=00000000
D:"Color Scheme Overrides Ansi Color"=00000000
S:"Emulation"=VT100
S:"Default SCS"=B
D:"Use Global ANSI Colors"=00000001
B:"ANSI Color RGB"=00000040
 00 00 00 00 a0 00 00 00 00 a0 00 00 a0 a0 00 00 00 00 a0 00 a0 00 a0 00 00 a0 a0 00 c0 c0 c0 00
 80 80 80 00 ff 00 00 00 00 ff 00 00 ff ff 00 00 00 00 ff 00 ff 00 ff 00 00 ff ff 00 ff ff ff 00
D:"Keypad Mode"=00000000
D:"Line Wrap"=00000001
D:"Cursor Key Mode"=00000000
D:"Newline Mode"=00000000
D:"Enable 80-132 Column Switching"=00000001
D:"Enable Cursor Key Mode Switching"=00000001
D:"Enable Keypad Mode Switching"=00000001
D:"Enable Line Wrap Mode Switching"=00000001
D:"Enable Alternate Screen Switching"=00000001
D:"WaitForStrings Ignores Color"=00000000
D:"SGR Zero Resets ANSI Color"=00000001
D:"SCO Line Wrap"=00000000
D:"Display Tab"=00000000
S:"Display Tab String"=
B:"Window Placement"=0000002c
 2c 00 00 00 02 00 00 00 03 00 00 00 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff f8 ff ff ff
 f8 ff ff ff 76 05 00 00 41 03 00 00
D:"Is Full Screen"=00000000
D:"Rows"=00000018
D:"Cols"=00000050
D:"Scrollback"=0000c350
D:"Resize Mode"=00000000
D:"Sync View Rows"=00000001
D:"Sync View Cols"=00000001
D:"Horizontal Scrollbar"=00000002
D:"Vertical Scrollbar"=00000002
S:"Color Scheme"=绿/黑
S:"Output Transformer Name"=UTF-8
D:"Use Unicode Line Drawing"=00000001
D:"Draw Lines Graphically"=00000001
D:"Blinking Cursor"=00000001
D:"Cursor Style"=00000000
D:"Use Cursor Color"=00000000
D:"Cursor Color"=00000000
D:"Foreground"=00000000
D:"Background"=00ffffff
D:"Bold"=00000000
D:"Map Delete"=00000000
D:"Map Backspace"=00000000
S:"Keymap Name"=Default
S:"Keymap Filename V2"=
D:"Use Alternate Keyboard"=00000000
D:"Emacs Mode"=00000000
D:"Emacs Mode 8 Bit"=00000000
D:"Preserve Alt-Gr"=00000000
D:"Jump Scroll"=00000001
D:"Minimize Drawing While Jump Scrolling"=00000000
D:"Audio Bell"=00000001
D:"Visual Bell"=00000000
D:"Scroll To Clear"=00000001
D:"Close On Disconnect"=00000000
D:"Clear On Disconnect"=00000000
D:"Scroll To Bottom On Output"=00000001
D:"Scroll To Bottom On Keypress"=00000001
D:"CUA Copy Paste"=00000000
D:"Use Terminal Type"=00000000
S:"Terminal Type"=
D:"Use Answerback"=00000000
S:"Answerback"=
D:"Use Position"=00000000
D:"X Position"=00000000
D:"X Position Relative Left"=00000001
D:"Y Position"=00000000
D:"Y Position Relative Top"=00000001
D:"Local Echo"=00000000
D:"Strip 8th Bit"=00000000
D:"Shift Forces Local Mouse Operations"=00000001
D:"Ignore Window Title Change Requests"=00000000
D:"Copy Translates ANSI Line Drawing Characters"=00000000
D:"Copy to clipboard as RTF and plain text"=00000000
D:"Translate Incoming CR To CRLF"=00000000
D:"Dumb Terminal Ignores CRLF"=00000000
D:"Use Symbolic Names For Non-Printable Characters"=00000000
D:"Show Chat Window"=00000002
D:"User Button Bar"=00000002
S:"User Button Bar Name"=Default
S:"User Font Map V2"=
S:"User Line Drawing Map V2"=
D:"Hard Reset on ESC c"=00000000
D:"Ignore Shift Out Sequence"=00000000
D:"Use Title Bar"=00000000
S:"Title Bar"=
D:"Show Wyse Label Line"=00000000
D:"Send Initial Carriage Return"=00000001
D:"Use Login Script"=00000000
D:"Use Script File"=00000000
S:"Script Filename V2"=
S:"Script Arguments"=
S:"Upload Directory V2"=${VDS_USER_DATA_PATH}
S:"Download Directory V2"=${VDS_USER_DATA_PATH}
D:"XModem Send Packet Size"=00000000
S:"ZModem Receive Command"=rz\r
D:"Disable ZModem"=00000000
D:"ZModem Uses 32 Bit CRC"=00000000
D:"Force 1024 for ZModem"=00000000
D:"ZModem Encodes DEL"=00000001
D:"ZModem Force All Caps Filenames to Lower Case on Upload"=00000001
D:"Send Zmodem Init When Upload Starts"=00000000
S:"Log Filename V2"=
S:"Custom Log Message Connect"=
S:"Custom Log Message Disconnect"=
S:"Custom Log Message Each Line"=
D:"Log Only Custom"=00000000
D:"Generate Unique Log File Name When File In Use"=00000001
D:"Log Prompt"=00000000
D:"Log Mode"=00000000
D:"Start Log Upon Connect"=00000000
D:"Raw Log"=00000000
D:"Log Multiple Sessions"=00000000
D:"New Log File At Midnight"=00000000
D:"Trace Level"=00000000
D:"Keyboard Char Send Delay"=00000000
D:"Use Word Delimiter Chars"=00000000
S:"Word Delimiter Chars"=
D:"Idle Check"=00000001
D:"Idle Timeout"=0000012c
S:"Idle String"= 
D:"Idle NO-OP Check"=00000000
D:"Idle NO-OP Timeout"=0000003c
D:"AlwaysOnTop"=00000000
D:"Line Send Delay"=00000005
D:"Character Send Delay"=00000000
D:"Wait For Prompt"=00000000
S:"Wait For Prompt Text"=
D:"Wait For Prompt Timeout"=00000000
D:"Send Scroll Wheel Events To Remote"=00000000
D:"Position Cursor on Left Click"=00000000
D:"Highlight Reverse Video"=00000001
D:"Highlight Bold"=00000000
D:"Highlight Color"=00000000
S:"Keyword Set"=<无>
S:"Ident String"=
D:"Eject Page Interval"=00000000
S:"Monitor Listen Address"=0.0.0.0:22
D:"Monitor Allow Remote Input"=00000000
D:"Disable Resize"=00000002
D:"Auto Reconnect"=00000002
B:"Page Margins"=00000020
 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f
B:"Printer Font v2"=00000060
 f3 ff ff ff 00 00 00 00 00 00 00 00 00 00 00 00 90 01 00 00 00 00 00 00 03 02 01 31 43 00 6f 00
 75 00 72 00 69 00 65 00 72 00 20 00 4e 00 65 00 77 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 64 00 00 00
D:"Page Orientation"=00000001
D:"Paper Size"=00000001
D:"Paper Source"=00000007
S:"Printer Name"=
D:"Disable Pass Through Printing"=00000000
D:"Buffer Pass Through Printing"=00000000
D:"Force Black On White"=00000000
D:"Use Raw Mode"=00000000
D:"Printer Baud Rate"=00009600
D:"Printer Parity"=00000000
D:"Printer Stop Bits"=00000000
D:"Printer Data Bits"=00000008
D:"Printer DSR Flow"=00000000
D:"Printer DTR Flow Control"=00000001
D:"Printer CTS Flow"=00000000
D:"Printer RTS Flow Control"=00000001
D:"Printer XON Flow"=00000000
S:"Printer Port"=
S:"Printer Name Of Pipe"=
D:"Use Printer Port"=00000000
D:"Use Global Print Settings"=00000001
D:"Operating System"=00000000
S:"Time Zone"=
S:"Last Directory"=
S:"Initial Local Directory V2"=
S:"Default Download Directory V2"=
D:"File System Case"=00000000
S:"File Creation Mask"=
D:"Disable Directory Tree Detection"=00000002
D:"Verify Retrieve File Status"=00000002
D:"Resolve Symbolic Links"=00000002
B:"RemoteFrame Window Placement"=0000002c
 2c 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00 fc ff ff ff fc ff ff ff 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00
S:"Remote ExplorerFrame State"=1,-1,-1
S:"Remote ListView State"=1,1,1,0,0
S:"SecureFX Remote Tab State"=1,-1,-1
D:"Restart Data Size"=00000000
S:"Restart Datafile Path"=
D:"Max Transfer Buffers"=00000004
D:"Filenames Always Use UTF8"=00000000
D:"Use Multiple SFTP Channels"=00000000
D:"Suppress Stat On CWD"=00000000
D:"Disable MLSX"=00000000
D:"SecureFX Trace Level"=00000001
D:"SecureFX Use Control Address For Data Connections"=00000001
D:"Use PGP For All Transfers"=00000000
D:"Disable Remote File System Watches"=00000000
S:"Sftp Tab Local Directory"=C:\Users\cao\Documents
D:"[SSH2] 端口"=00000016
S:"Compatibility Mode V2"=Auto Detect
S:"Identity Filename"=
D:"Disable SFTP Extended Commands"=00000000
S:"Keymap Filename"=
S:"User Font Map"=
S:"User Line Drawing Map"=
S:"Script Filename"=
S:"Upload Directory"=C:\Users\cao
S:"Download Directory"=C:\Users\cao\Downloads
D:"Start Tftp Server"=00000000
S:"Log Filename"=
D:"Highlighting Style"=00000000
S:"Initial Local Directory"=
S:"Default Download Directory"=
S:"主机名"=
S:"Keyboard Interactive Prompt"=assword
S:"Public Key Certificate Username"=
D:"Use Username From Certificate"=00000000
D:"Certificate Username Location"=00000000
D:"Packet Strings Always Use UTF8"=00000000
D:"Enable Xterm-256color"=00000000
D:"Ignore 80-132 Column Switching When Maximized or Full Screen"=00000000
D:"Enable TN3270 Base Colors"=00000000
D:"Raw EOL Mode"=00000000
D:"Printer Quality"=fffffffd
D:"Printer Color"=00000001
D:"Printer Duplex"=00000001
D:"Printer Media Type"=00000001
D:"Use A Separate Transport For Every Connection"=00000000
D:"Disable STAT For SFTP Directory Validation"=00000000
D:"Use STAT For SFTP Directory Validation"=00000000
D:"SecureFX Trace Level V2"=00000002
D:"Synchronize App Trace Level"=00000001
D:"Port"=00000017
D:"Send SGA"=00000001
D:"Send SGA All"=00000000
D:"Will LFLOW"=00000001
D:"Force Char Mode"=00000001
D:"Enable NAWS"=00000001
D:"Enable NTLM Authentication"=00000001
D:"Telnet Disable SSL Certificate Verification"=00000000
D:"Server Requires Bare CR"=00000000
Z:"Port Forward Table V2"=00000000
Z:"Reverse Forward Table V2"=00000000
Z:"Keymap v3"=00000000
Z:"MAC Log File Tags"=00000000
Z:"Description"=00000000
Z:"SecureFX Post Login User Commands"=00000000
Z:"SecureFX Bookmarks"=00000000
Z:"SCP Shell Prompts"=00000001
 "? ",0,"\n"
Z:"Sftp Drive Mappings"=00000001
 
Z:"Sftp User Mappings"=00000000
Z:"Keymap v4"=00000000

xshell_tmp.xsh模板文件示例：

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
[CONNECTION:PROXY]
Proxy=
StartUp=0
[CONNECTION:SERIAL]
BaudRate=6
StopBits=0
FlowCtrl=0
Parity=0
DataBits=3
ComPort=0
[SessionInfo]
Version=6.0
Description=Xshell session file
[TRACE]
SockConn=1
SshLogin=0
SshTunneling=0
SshPacket=0
TelnetOptNego=0
[CONNECTION:SSH]
LaunchAuthAgent=1
KeyExchange=
SSHCiphers=aes128-ctr:1,aes192-ctr:1,aes256-ctr:1,aes128-gcm@openssh.com:1,aes256-gcm@openssh.com:1,aes128-cbc:1,aes192-cbc:1,aes256-cbc:1,3des-cbc:1,blowfish-cbc:1,cast128-cbc:1,arcfour:1,rijndael128-cbc:1,rijndael192-cbc:1,rijndael256-cbc:1,rijndael-cbc@lysator.liu.se:1,arcfour128:1,arcfour256:1
ForwardToXmanager=1
Compression=0
NoTerminal=0
UseAuthAgent=0
MAC=
SSHMACs=hmac-sha2-256-etm@openssh.com:1,hmac-sha2-512-etm@openssh.com:1,hmac-sha1-etm@openssh.com:1,hmac-sha2-256:1,hmac-sha2-512:1,hmac-sha1:1,hmac-sha1-96:1,hmac-md5:1,hmac-md5-96:1,hmac-ripemd160:1,hmac-ripemd160@openssh.com:1,hmac-sha1-96-etm@openssh.com:1,hmac-md5-etm@openssh.com:1,hmac-md5-96-etm@openssh.com:1
InitRemoteDirectory=
ForwardX11=1
VexMode=0
Cipher=
Display=localhost:0.0
FwdReqCount=0
InitLocalDirectory=
SSHKeyExchanges=curve25519-sha256@libssh.org:1,ecdh-sha2-nistp256:1,ecdh-sha2-nistp384:1,ecdh-sha2-nistp521:1,diffie-hellman-group-exchange-sha256:1,diffie-hellman-group-exchange-sha1:1,diffie-hellman-group14-sha1:1,diffie-hellman-group1-sha1:1
SaveHostKey=0
[BELL]
FilePath=
RepeatTime=3
FlashWindow=0
BellMode=1
IgnoreTime=3
[USERINTERFACE]
NoQuickButton=0
QuickCommand=Default Quick Command Set
[CONNECTION:FTP]
Passive=1
InitRemoteDirectory=
InitLocalDirectory=
[TRANSFER]
FolderMethod=0
DropXferHandler=2
XmodemUploadCmd=rx
ZmodemUploadCmd=rz -E
FolderPath=
YmodemUploadCmd=rb -E
AutoZmodem=1
SendFolderPath=
DuplMethod=0
XYMODEM_1K=0
[CONNECTION]
Port=22
Host=$DEV_IP
Protocol=SSH
AutoReconnect=0
AutoReconnectLimit=0
Description=
AutoReconnectInterval=30
FtpPort=21
UseNaglesAlgorithm=0
IPV=0
[TERMINAL]
Rows=24
CtrlAltIsAltGr=1
InitOriginMode=0
InitReverseMode=0
DisableBlinkingText=0
CodePage=65001
InitAutoWrapMode=1
Cols=80
InitEchoMode=0
Type=xterm
DisableAlternateScreen=0
CJKAmbiAsWide=0
ScrollBottomOnKeyPress=0
DisableTitleChange=0
ForceEraseOnDEL=0
InitInsertMode=0
ShiftForcesLocalUseOfMouse=1
FontLineCharacter=1
ScrollbackSize=1024
InitCursorMode=0
FixedCols=0
BackspaceSends=2
UseInitSize=0
UseLAltAsMeta=0
UseRAltAsMeta=0
AltKeyMapPath=
DeleteSends=0
DisableTermPrinting=0
IgnoreResizeRequest=1
ScrollBottomOnTermOutput=1
FontPowerLine=1
ScrollErasedText=1
KeyMap=0
RecvLLAsCRLF=0
EraseWithBackgroundColor=1
InitNewlineMode=0
InitKeypadMode=0
TerminalNameForEcho=Xshell
[TERMINAL:WINDOW]
ColorScheme=James Stytle
FontQuality=0
LineSpace=0
CursorColor=65280
CursorBlinkInterval=600
TabColorType=0
CursorAppearance=0
TabColorOther=0
FontSize=16
AsianFontSize=16
CursorBlink=0
BGImageFile=
BoldMethod=2
CursorTextColor=0
BGImagePos=0
AsianFont=Consolas
FontFace=Consolas
CharSpace=0
MarginBottom=5
MarginLeft=5
MarginTop=5
MarginRight=5
[CONNECTION:TELNET]
XdispLoc=1
NegoMode=0
Display=$PCADDR:0.0
[HIGHLIGHT]
HighlightSet=None
[CONNECTION:AUTHENTICATION]
Pkcs11Pin=
Library=0
Passphrase=
Pkcs11Middleware=
Delegation=0
UseInitScript=0
TelnetLoginPrompt=ogin:
Password=$PASSWORD
RloginPasswordPrompt=assword:
UseExpectSend=0
TelnetPasswordPrompt=assword:
ExpectSend_Count=0
Method=0
ScriptPath=
UserKey=
UserName=$USERNAME
[LOGGING]
FilePath=%n_%Y-%m-%d_%t.log
Overwrite=1
WriteFileTimestamp=0
TimestampFormat=[%a] 
TermCode=0
AutoStart=0
Prompt=0
WriteTermTimestamp=0
[ADVANCED]
WaitPrompt=
PromptMax=0
SendLineDelayType=0
SendLineDelayInterval=0
SendCharDelayInterval=0
[CONNECTION:RLOGIN]
TermSpeed=38400
[CONNECTION:KEEPALIVE]
SendKeepAliveInterval=60
KeepAliveInterval=60
TCPKeepAlive=0
KeepAliveString=
SendKeepAlive=0
KeepAlive=1

参考文档：

https://github.com/python-excel/xlwt

https://docs.python.org/2/library/string.html#template-strings

https://www.cnblogs.com/python-robot/p/9958352.html

坚持原创技术分享，您的支持将鼓励我继续创作！