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_listHere 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!
