topic Re: python export lldp to XLS new sheet per switch in Controllers

python export lldp to XLS new sheet per switch

Steven King — Mon, 12 Oct 2020 14:21:37 GMT

here is code that I have working to export 1 switch at a time. I am looking to export the lldp neighbor for over 60 switches to one excel file with multiple sheets with. I am wanting the sheet names to match the switch names. Is this possible and would anyone have any examples?

thank you

SW1 = {

'device_type': 'cisco_ios',

'host': '192.168.1.2',

'username':'cisco',

'password': 'cisco',

}

SW2 = {

'device_type': 'cisco_ios',

'host': '192.168.1.3',

'username':'cisco',

'password': 'cisco',

}

def main():

os.environ["NET_TEXTFSM"] = "./ntc-templates/templates"

# list of all devices

all_devices = [SW1, SW2]

# commands

# start_time = datetime.now()

for a_device in all_devices:

net_connect = ConnectHandler(**a_device)

# output = net_connect.send_command("show run | inc hostname", delay_factor=3)

# the show run | inc hostname portion is not working so I commented it out for now

# print(output)

lldp_neighbor = net_connect.send_command("show lldp neighbor", use_textfsm=True)

# print(f"\n\n-----------------------Device {a_device['host']}----------------------------")

print(lldp_neighbor)

print(len(lldp_neighbor))

for line in lldp_neighbor:

print(json.dumps(line, indent=6))

lldp_data = {'NEIGHBOR': [entry['neighbor'] for entry in lldp_neighbor],

'LOCAL_INTERFACE': [entry['local_interface'] for entry in lldp_neighbor],

'CAPABILITIES': [entry['capabilities'] for entry in lldp_neighbor],

'NEIGHBOR_INTERFACE': [entry['neighbor_interface'] for entry in lldp_neighbor],

}

df = pd.DataFrame(lldp_data, columns=list(lldp_data.keys()))

print(df.head())

df.to_excel('LLDP_Neighbor.xlsx')

# Standard call to the main() function.

if __name__ == '__main__':

parser = argparse.ArgumentParser(description="Script Description",

epilog="Usage: ' python ccom' ")

arguments = parser.parse_args()

main()

Re: python export lldp to XLS new sheet per switch

Claudia de Luna — Wed, 14 Oct 2020 13:54:27 GMT

Hi @Steven King

Short Answer for your code. As usual Pandas has a really easy way to do this.

You need to get your hostname SW1, SW2 etc into a variable so you can use that variable as the tab name.

When you are done processing all your switches you give the save() method to save your Excel Writer object. That part is outside your "processing" loop if that makes sense. I've attached the resulting MAC_TABLES.xlsx as an example of what you get.

    df.to_excel('mac_table.xlsx'). <--Your code leaves off here

    # Saving to a specific Excel Sheet or Tab

    # First create a Pandas Excel Writer "object"
    xlwriter_object = pd.ExcelWriter('MAC_TABLES.xlsx')

    # Save the data frame to a specific tab or sheet in your writer object
    df.to_excel(xlwriter_object, hostname)

    # Save the object to a file
    xlwriter_object.save()

Longer more generic answer:

You've got the "get a response back from a network device" down so I'm focusing on the Excel part.

I'm using the openpyxl module.

I've saved the full script to a text file so I could attach it to this message. Just change the extension to .py.

I've put steps into functions so you can clearly see what is happening (also better code).

- create_xlwkbk

- create_xltab

- open_xlwkbk

- save2tab

- save_xlwkbk

- get_show_output <- this can be replaced with your existing code

Here is the main part of the script that uses all the above functions.

def main():

    datestamp = datetime.date.today()
    print(f"===== Date is {datestamp} ====")

    # Define an Excel Workbook filename based on the name passed as an argument in the command line
    # python save2xl_tabs.py -f CLOD
    # the CLI command above creates a file CLOD_YYYY-MM-DD.xlsx
    # or using the default file name DEFAULT
    xlfilename = f"{arguments.filename}_{datestamp}.xlsx"

    # Note on the functions. Many have an optional parameter "debug" that just prints information when set to True
    # It is set to False by default but sometimes its handy to know where you are in the script

    # Create an Excl Workbook Object
    xobj = create_xlwkbk(debug=True)

    # Add a Tab to the Excel Workbook Ojbect
    create_xltab(xobj, "TEST", debug=True)

    # Save data to a Tab
    # Sample output string
    output = get_show_output()

    # Take the string response and split by lines
    list_of_output = output.splitlines()

    # Send the raw string to be saved to a tab called RAW Output
    save2tab(xobj,"RAW Output", output, debug=True)
    # Send the list of lines to be saved to a tab called LIST Output
    save2tab(xobj,"LIST Output", list_of_output, debug=True)

    # Save the Excel Workbook Object to Disk
    res, filepath = save_xlwkbk(xobj, xlfilename, debug=True)

    # Open the saved Workbook back up again and display the Tabs
    wkbk = open_xlwkbk(filepath)
    print(f"Saved Excel file {filepath} \n  has the following Tabs: \n\t{wkbk.sheetnames}\n\n")

The function you want to look at first is the save2tab:

Ive tried to put comments throughout to try to clarify what is going on.

def save2tab(xlobj, xltab_name, data_list, debug=False):
    """
    Function to save data to the provided tab
    Funciton checks to see if the tab already exists and if it does not it creates it.
    :param xl0bj:  Excel Workbook Object
    :param xltab_name:  Excel Workbook tab or sheet to save data to
    :param data_list:  Data to save to specified tab or sheet.  This should be a list of lists
    :return:
    """
    if debug: print(f"\t--- Saving Data to Tab {xltab_name}.\n")
    if xltab_name not in xlobj.sheetnames:
        create_xltab(xlobj, xltab_name)

    ws = xlobj[xltab_name]

    # The tricky part is manipulating the data into the cells
    # In this block first we check to see if "data_list" is actually a list
    if type(data_list) == list:
        # then we have to check to see if what is in each row is a list or a string
        for row in data_list:
            if type(row) == list:
                # the append method expects an iterable (list, tuple)
                ws.append(row)
            elif type(row) == str:
                # if its a string then you want to cast is as a list otherwise you will get a letter in each cell
                ws.append([row])
    # if data-list is actually a string we just put the string in the first cell.
    # This is not what you want so you will be in the business of manipulating your text as above
    elif type(data_list) == str:
        ws['A1'] = data_list

Here is an example with all the debugging print statements enabled (set to True):

(netmiko38) claudia@Claudias-iMac netmiko38 % python save2xl_tabs.py -f EXAMPLE4STEVEN
===== Date is 2020-10-14 ====
        --- Creating an Excel Workbook Object.

        --- Creating Tab TEST in Excel file. 

        --- Dummy function to load test show command data.

        --- Saving Data to Tab RAW Output.

        --- Saving Data to Tab LIST Output.

        --- Saving the Workbook Object as EXAMPLE4STEVEN_2020-10-14.xlsx to path /Users/claudia/Dropbox (Indigo Wire Networks)/scripts/python/2020/netmiko38.

Saved Excel file /Users/claudia/Dropbox (Indigo Wire Networks)/scripts/python/2020/netmiko38/EXAMPLE4STEVEN_2020-10-14.xlsx 
  has the following Tabs: 
        ['Sheet', 'TEST', 'RAW Output', 'LIST Output']

The tricky part is getting from the RAW Output which I don't think you want to the LIST Output which may be closer to what you are looking for.

I've also attached the resulting Excel file.

Hope this helps & reach out if you have any questions!

Re: python export lldp to XLS new sheet per switch

Steven King — Thu, 15 Oct 2020 13:14:31 GMT

thank you Claudia

I will give it a shot.

I dont see the txt document mentioned above.

Thank you again

Steven

Re: python export lldp to XLS new sheet per switch

Claudia de Luna — Thu, 15 Oct 2020 14:06:21 GMT

Shoot - looks like the system stripped it.

I should have known better and put it up on a repo to begin with. Sorry about that!
https://github.com/cldeluna/Cisco_Community_Solutions/blob/main/save2xl_tabs.py