In the previous articles, we have seen how to leverage pandas and csv modules for reading and writing csv and excel files. This post will talk about how to read write excel using python openpyxl. Although pandas is a much more robust package to deal with excel files or tabular data, it has its own learning curve. I have covered the basics of pandas for network engineers and other posts where we see how to read write with pandas. Openpyxl on the other hand is reasonably versatile but that versatility is limited to only read and write the excel files while pandas can also effectively manipulate and analyze the data you read from excel.
Read from excel file using python’s openpyxl module for network engineers.
In [3]: import openpyxl
...: from rich import print
In [4]: book = openpyxl.load_workbook(filename='cisco_always_on_devnet_sanbox_lab.xlsx')
In [5]: book
Out[5]: <openpyxl.workbook.workbook.Workbook at 0x120ce41f0>View all sheets in the workbook
In [6]: book.sheetnames
Out[6]: ['cisco_always_on_devnet_sanbox_l']See the workbook currently selected
In [7]: book.active
Out[7]: <Worksheet "cisco_always_on_devnet_sanbox_l">In this case, since the workbook only had 1 sheet, so active and sheet names show the same sheet name. I added a new sheet to the workbook.
In [3]: book.sheetnames
Out[3]: ['cisco_always_on_devnet_sanbox_l', 'Openpyxl_demo_worksheet']Select the sheet you want to work with
In [5]: sheet=book['cisco_always_on_devnet_sanbox_l']
In [6]: sheet
Out[6]: <Worksheet "cisco_always_on_devnet_sanbox_l">Alternatively, you could have said sheet.active to select whichever sheet is active or there is just 1 sheet in the book.
In [8]: sheet=book.active
In [9]: sheet
Out[9]: <Worksheet "Openpyxl_demo_worksheet">You could also select the sheet you want using lists index
In [13]: book.sheetnames
Out[13]: ['cisco_always_on_devnet_sanbox_l', 'Openpyxl_demo_worksheet']
In [14]: sheet=book[book.sheetnames[0]]
In [15]: sheet
Out[15]: <Worksheet "cisco_always_on_devnet_sanbox_l">Find max rows/columns and min rows/columns
In [24]: sheet.min_row
Out[24]: 1
In [25]: sheet.min_column
Out[25]: 1
In [26]: sheet.max_row
Out[26]: 4
In [27]: sheet.max_column
Out[27]: 7The sheet has a max of 7 columns and the first column is at index 1 and max rows are 4 and the first row starts at index 1 of the excel sheet.
Read all values in all columns. Each column is read sequentially.
In [41]: for col in sheet.iter_cols(values_only=True):
...: print(col)
...:
('HOST', 'sandbox-iosxe-latest-1.cisco.com', 'sandbox-iosxr-1.cisco.com', 'sandbox-nxos-1.cisco.com')
('SSH PORT', 22, 22, 22)
('NETCONF PORT', 830, 830, 830)
('RESTCONF PORT', 443, None, 443)
('USERNAME', 'developer', 'admin', 'admin')
('PASSWORD', 'C1sco12345', 'C1sco12345', 'Admin_1234!')
('SECRET', 'C1sco12345', 'C1sco12345', 'Admin_1234!')Read all values in all rows. Each row is read sequentially
In [43]: for row in sheet.iter_rows(values_only=True):
...: print(row)
...:
('HOST', 'SSH PORT', 'NETCONF PORT', 'RESTCONF PORT', 'USERNAME', 'PASSWORD', 'SECRET')
('sandbox-iosxe-latest-1.cisco.com', 22, 830, 443, 'developer', 'C1sco12345', 'C1sco12345')
('sandbox-iosxr-1.cisco.com', 22, 830, None, 'admin', 'C1sco12345', 'C1sco12345')
('sandbox-nxos-1.cisco.com', 22, 830, 443, 'admin', 'Admin_1234!', 'Admin_1234!')You might want to fetch details only for a specific host
In [46]: for row in sheet.iter_rows(values_only=True):
...: if "sandbox-iosxr-1.cisco.com" in row:
...: print(row)
...:
('sandbox-iosxr-1.cisco.com', 22, 830, None, 'admin', 'C1sco12345', 'C1sco12345')Read a specific column
In [59]: for cell in sheet['A']:
...: print(cell.value)
...:
HOST
sandbox-iosxe-latest-1.cisco.com
sandbox-iosxr-1.cisco.com
sandbox-nxos-1.cisco.comAnother method is to iterate through values from 1 through max_row value
In [61]: for i in range(sheet.max_row):
...: print(sheet['A' + str(i+1)].value)
...:
...:
HOST
sandbox-iosxe-latest-1.cisco.com
sandbox-iosxr-1.cisco.com
sandbox-nxos-1.cisco.comLet’s take a little more complicated example of filtering. Suppose you want to get all the devices that have their username as admin
In [66]: for i in range(sheet.max_row):
...: if 'admin' in sheet['E' + str(i+1)].value:
...: print(sheet['A' + str(i + 1)].value)
...:
sandbox-iosxr-1.cisco.com
sandbox-nxos-1.cisco.comIf I have missed a use case, please feel free to comment below to ask.
Write to excel file using python’s openpyxl module for network engineers.
Writing excel using openpyxl is very use case-specific but I will discuss the two most common methods. Since you are mostly going to dump large amounts of data into excel, you will most likely have data in the form of dictionaries or lists.
Basic of preparing a sheet for write
In [1]: import openpyxl
In [2]: from rich import print
In [3]: book = openpyxl.Workbook()
In [4]: sheet = book.active
In [5]: sheet.title = 'First sheet'
In [6]: sheet
Out[6]: <Worksheet "First sheet">Write some data to individual cells.
In [9]: sheet['A1'].value = 'Device Name'
In [10]: sheet['B1'].value = 'Version'
In [11]: sheet['A2'].value = 'rtr-01'
In [12]: sheet['A3'].value = 'rtr-02'
In [13]: sheet['B2'].value = '12.2(55)SE8'
In [14]: sheet['B3'].value = '16.3.8'
In [15]: sheet
Out[15]: <Worksheet "First sheet">
In [16]: for row in sheet.iter_rows(values_only=True):
...: print(row)
...:
('Device Name', 'Version')
('rtr-01', '12.2(55)SE8')
('rtr-02', '16.3.8')But this of course is not a practical way. Let’s create a new sheet and select the second sheet to work.
In [17]: book.create_sheet('Second sheet')
Out[17]: <Worksheet "Second sheet">
In [18]: book.sheetnames
Out[18]: ['First sheet', 'Second sheet']
In [19]: sheet = book['Second sheet']
In [21]: sheet
Out[21]: <Worksheet "Second sheet">Suppose we have a list of lists where each internal list is the device info for each device.
In [28]: device_data = [
...: ('rtr-01', 'router', '16.3.8'),
...: ('rtr-02', 'router', '16.3.8'),
...: ('sw-01', 'switch', '12.2(55)SE8')
...: ]
In [46]: header
Out[46]: ['Hostname', 'Device Type', 'Version']Write each row of the device_data sequentially to each row of the empty sheet we created.
sheet.append(header)
In [49]: sheet['A1'].value
Out[49]: 'Hostname'
In [50]: sheet['B1'].value
Out[50]: 'Device Type'
In [51]: sheet['C1'].value
Out[51]: 'Version'After adding the header row, we can now add the rest of the rows to the sheet.
In [52]: for each_device in device_data:
...: sheet.append(each_device)
...:
In [53]: for row in sheet.iter_rows(values_only=True):
...: print(row)
...:
('Hostname', 'Device Type', 'Version')
('rtr-01', 'router', '16.3.8')
('rtr-02', 'router', '16.3.8')
('sw-01', 'switch', '12.2(55)SE8')Let’s create a third sheet to write data from a list of dictionaries to excel.
In [58]: book.create_sheet('third sheet')
Out[58]: <Worksheet "third sheet">
In [59]: sheet = book['third sheet']
In [60]: sheet
Out[60]: <Worksheet "third sheet">In [65]: device_data = [
...: {'Hostname' : 'rtr-01', 'Device Type' : 'router', 'Version' : '16.3.8'},
...: {'Hostname' : 'rtr-02', 'Device Type' : 'router', 'Version' : '16.3.8'},
...: {'Hostname' : 'sw-01', 'Device Type' : 'router', 'Version' : '12.2(55)SE8'},
...: ]
In [66]: device_data
Out[66]:
[{'Hostname': 'rtr-01', 'Device Type': 'router', 'Version': '16.3.8'},
{'Hostname': 'rtr-02', 'Device Type': 'router', 'Version': '16.3.8'},
{'Hostname': 'sw-01', 'Device Type': 'router', 'Version': '12.2(55)SE8'}]Write to excel
sheet.append(header)
In [71]: for each_device in device_data:
...: row_data = []
...: for k, v in each_device.items():
...: row_data.append(v)
...: print(row_data)
...:
['rtr-01', 'router', '16.3.8']
['rtr-02', 'router', '16.3.8']
['sw-01', 'router', '12.2(55)SE8']
## append row_data to each row of excel file
In [72]: for each_device in device_data:
...: row_data = []
...: for k, v in each_device.items():
...: row_data.append(v)
...: sheet.append(row_data)
...:
In [73]: book.write('output.xlsx)We can further reduce the code by using list comprehension at the cost of readability but it achieves the same end goal.
In [81]: for each_device in device_data:
...: sheet.append([v for v in each_device.values()])
In [82]: book.write('output.xlsx)Another method to write a list of dictionaries to excel with openpyxl could be below
In [88]: for index, each_device in enumerate(device_data):
...: print(index, each_device['Hostname'], each_device['Device Type'], each_device['Version'])
...: print('A' + str(index+2), 'B' + str(index+2), 'C' + str(index+2))
...: sheet['A' + str(index+2)].value = each_device['Hostname']
...: sheet['B' + str(index+2)].value = each_device['Device Type']
...: sheet['C' + str(index+2)].value = each_device['Version']
...:
...:
0 rtr-01 router 16.3.8
A2 B2 C2
1 rtr-02 router 16.3.8
A3 B3 C3
2 sw-01 router 12.2(55)SE8
A4 B4 C4Explanation:-
- We are iterating through each device in the device_data
- index value starts with 0 because enumerate returns the values as 0,1,2,3,4 so on. So we need to add +2 to index value because we need to store the values in A2 for first row, A3 for second row and A4 for third row since A1 is taken up the by the header.
- each_device is a dictionary and we are referencing the appropriate value from that dictionary using corresponding keys.
The last method is probably a little tricky, but sure, it’s versatile bcoz you can reference values based on the corresponding keys, giving you more flexibility.
A more advanced section of this tutorial could talk about the below points. If you feel that is required, please let me know and I will work on it.
- How to modify the column widths and row heights.
- How to fill in colors, put filters, add graphs
- Merge cells, draw borders
- Hide, unhide etc.
- Basically anything you do with excel GUI, you can do all that using python openpxyl too.
If you have any doubts/comments/suggestions. Please feel free to leave them below. I hope this was informative for you.
