0

I have multiple router/switches. I want to read router Ip address from csv file and write the outputs into an excel. In excel I want to create sheets per device.

I can connect and get the outputs with the code below but couldnt create excel and multiple dynamic sheets in it. I tried xlsxwriter and xlwt, what is your suggestion?

router = {}
output_dict = {}

with open('Devices.csv', mode='r') as devicesFile:
    devicesDict = csv.DictReader(devicesFile, dialect = 'excel')
    for row in devicesDict:
        devicetype = row['device_type']
        hostname = row['hostname']
        ipaddress = row['ip']
        username = row['username']
        password = row['password']
        router = {'host':hostname,'device_type':devicetype,'ip':ipaddress,'username':username,'password':password, }
        net_connect = ConnectHandler(**router)
        output = net_connect.send_command('display clock')
        print('\n\n>>> Hostname {0} <<<'.format(row['hostname']))
        print(output)
        print('>>>>>>>>> End <<<<<<<<<')


def net_connect(row, output_q):
       ipaddress = row['ip']
       output_dict[ipaddress] = output
       output_q.put(output_dict)
apaderno
  • 28,547
  • 16
  • 75
  • 90
frd
  • 27
  • 5
  • hi @ferdi, why don't you use `xlwt`? – Andy K Sep 09 '17 at 15:12
  • I tried but couldnt create the sheets with hostnames. Do you have similar code? – frd Sep 09 '17 at 17:32
  • https://stackoverflow.com/a/13437772/2572645 @ferdi – Andy K Sep 09 '17 at 17:45
  • thanks for the links. I can write the outputs per excel sheet and excel sheets are device hostnames. My other problem is all output is being written into one cell, for ex A1 with the "sh.write(0, 0, output)" command. How can I write the outputs in excel like I did copy paste into excel sheet? using multiple rows for output. – frd Sep 11 '17 at 17:42
  • I could do this with "enumerate(output.splitlines())".. Thanks – frd Sep 12 '17 at 19:42

1 Answers1

0

My working code is as below. This splits the output lines in excel.

for y, line in enumerate(output.splitlines()):
    rowx = y + 3       
    for x, value in enumerate(line.splitlines()):
        colx = x + 2
        if value.isdigit():
            value = int(value)
        sh.write(rowx, colx, value)
frd
  • 27
  • 5