最近和读者群里的学员交流，发现国内的网工同行们在平常办公中使用Excel的比较多，比如用Excel来做配置命令的模板，做inventory，做IPAM，以及用来保存设备的一些日志、基本信息或端口信息等等。有学员问我能不能讲下怎么通过Python自动实现抓取设备中一些想要的信息然后自动生成一个Excel文件，将这些信息自动写入Excel文件中，并自动完成创建工作表（Worksheet）、为工作表改名，自动调整单元格背景颜色、为单元格设置边框，自动调整每一列的宽度等操作。答案是肯定的，今天就带大家从一个网工的角度来讲下怎么使用openpyxl这个Python第三方模块完成上述需求。


首先想要说的是目前和Excel相关的Python第三方模块多如牛毛，xlwt, xlrd, xlutils，openpyxl以及大名鼎鼎的pandas都能支持和Excel相关的操作，笔者用过xlwt，它最大的缺点是只支持xls格式的Excel文件（也就是俗称的97-2003格式），扩展名为xlsx的Excel文件它是不支持的，考虑到这马上都要2021年了，笔者就弃用xlwt了。而xlrd呢，它在读取Excel后文件是不能对其进行操作的：xlrd.open_workbook()方法返回的xlrd.Book类型是只读的，不能对其进行操作，需要配合xlutils（依赖于xlrd和xlwt）来提供复制excel文件内容和修改文件的功能。xlutils其实际也只是在xlrd.Book和xlwt.Workbook之间建立了一个管道而已。所以综合起来比较，笔者最后还是决定深耕Openpyxl就够（不过Openpyxl只支持xlsx格式，这点需要注意）了。因为虽然工具不一样，但做的事是一样的，我肯定会选取一个学习成本相对较低的工具来用，下面进入正题。

本篇将分为五个小实验循序渐进地讲解Openpyxl的使用方法，最后再附上一个在现网真机交换机上配合Napalm模块抓取端口信息，然后将信息写入保存进excel文件的实战案例的脚本，保证手把手教会网工怎么使用Openpyxl来满足工作中自动化使用Excel的部分需求。

Openpyxl的安装很简单，直接pip install openyxl就行了，这里就不讲了。

实验一

实验目的：用Openpyxl创建workbook和工作表（worksheet） ，并为工作表改名。

实验前先确定当前脚本所在的文件夹下没有任何Excel文件：

实验代码：

from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = 'Test' wb.save('test_openpyxl.xlsx')

代码分段讲解：

  • 调用Openpyxl创建Workbook对象，用来创建一个workbook，并将它赋值给变量wb 。注意除了Workbook外，Openpyxl还要很多很多诸如load_book, Color, PatternFill, Font, Border, Side等等之类的对象，它们的用法各不相同，一般我们在导入的时候只需要用from...import...来导入我们需要用到的对象就行了。

from openpyxl import Workbook wb = Workbook()

  • 用wb.active生成第一个工作表(worksheet)，赋值给变量ws，该工作表默认名为'Sheet'。

ws = wb.active

如果这时你使用wb.save('test_openpyxl.xlsx')将workbook保存，会看到当前目录下现在多出了一个名为的test_openpyxl的xlsx文件：

打开后会看到该workbook下的工作表名即为刚才通过wb.active创建时默认的'Sheet'：



  • 修改工作表名也很简单，比如我们要将该工作表名从Sheet改成Test，使用ws.title = 'Test'然后wb.save('test_openpyxl.xlsx')再次保存workbook即可。

ws.title = 'Test' wb.save('test_openpyxl.xlsx')

再次打开excel文件后会看到该workbook下的工作表名已经变成了'Test'：


实验二

实验目的：在已有Test工作表的基础上，用Openpyxl为workbook另外添加两个工作表，并为它们分别取名Switch和Router，将Switch工作表排在所有工作表的最前面，将Router工作表排在最后面。

实验代码：

from openpyxl import Workbook #实验一代码部分 wb = Workbook() ws = wb.active ws.title = 'Test' #实验二代码部分 ws1 = wb.create_sheet("Switch", 0) ws2 = wb.create_sheet("Router") wb.save('test_openpyxl.xlsx')

代码分段讲解：

  • 通过wb.create_sheet()来创建名为Switch的工作表，将其赋值给另外一个变量ws1，后面的参数0表示将该工作表放在所有工作表中的第一位。

ws1 = wb.create_sheet("Switch", 0)

  • 再次通过wb.create_sheet()来创建名为Router的工作表，将其赋值给另外一个变量ws2，后面不带参数表示将该工作表放在所有工作表中的最后一位，然后将workbook保存退出。

ws2 = wb.create_sheet("Router") wb.save('test_openpyxl.xlsx')

最后打开excel文件验证，效果如下：

  • 另外也可以通过打印wb.sheetnames来查看所有工作表的名字

print (wb.sheetnames)

  • 也可以通过for循环遍历wb下面的sheet.title来查看所有工作表的名字

for sheet in wb: print (sheet.title)


实验三

实验目的：为工作表Switch的单元格A1和B1添加内容，分别将其命名为Interfaces和Description。然后为单元格A2和A3分别添加Gi1/0/1, Gi1/0/2，为B2和B3分别添加PC1, PC2。最后将A列下所有的单元格赋值给变量column_A，然后通过for循环遍历column_A，查看所有A列下的单元格的内容。

实验代码：

from openpyxl import Workbook #实验一代码部分 wb = Workbook() ws = wb.active ws.title = 'Test' #实验二代码部分 ws1 = wb.create_sheet("Switch", 0) ws2 = wb.create_sheet("Router") #实验三代码部分 ws1['A1'] = 'Interfaces' ws1['B1'] = 'Description' ws1.cell(row=2, column=1, value='Gi1/0/1') ws1.cell(row=3, column=1, value='Gi1/0/2') ws1.cell(row=2, column=2, value='PC1') ws1.cell(row=3, column=2, value='PC2') wb.save('test_openpyxl.xlsx')

代码分段讲解：

  • 这里我们可以直接通过ws['单元格号']的形式来为指定的单元格添加内容。注意因为我们是要对Switch这个工作表添加内容，所以这里要用ws1['A1'], ws1['B1']（记住ws代表的是Test这个工作表，ws2代表的是Router这个工作表）。

ws1['A1'] = 'Interfaces' ws1['B1'] = 'Description'

这时如果用wb.save('test_openpyxl.xlsx')将workbook保存，然后再次将其打开，效果如下：

注意：如果你现在把ws1['A1'] = 'Interfaces'替换成ws['A1'] = 'Interfaces'，然后再运行一次脚本，那么之前Switch工作表下的A1单元格里的Interfaces将会“消失”，你会在Test工作表下的A1单元格里看到Interfaces，原因是Openpyxl将会覆盖之前excel文件里的内容，而不是添加。

  • 另外还可以使用ws.cell()函数来修改单元格的内容，这里我们在Switch工作表里的A2添加'Gi1/0/1', 在A3添加'Gi1/0/2', 在B2添加'PC1', 在B3添加'PC2'。其中参数row代表排数，column代表列数，value代表要写入单元格的内容，做完之后保存退出。

ws1.cell(row=2, column=1, value='Gi1/0/1') ws1.cell(row=3, column=1, value='Gi1/0/2') ws1.cell(row=2, column=2, value='PC1') ws1.cell(row=3, column=2, value='PC2') wb.save('test_openpyxl.xlsx')

运行脚本后效果如下：


  • 另外我们也可以通过将ws1['A']（A后面不带数字的话，表示整个A列）赋值给一个变量，然后对该变量做遍历，即能在不打开Excel文件的情况下，从Python里看到A列下的单元格里有些什么内容：

column_A = ws1['A'] for i in column_A: print (i.value)

执行上面代码后效果如下：


实验四

实验目的：继续学习Openpyxl中的一些常用的属性和函数来在不打开Excel文件的前提下，查看指定单元格和指定列、指定排下的所有单元格里的内容，以及如何替换已有单元格里的内容。

实验代码：

from openpyxl import Workbook #实验一代码部分 wb = Workbook() ws = wb.active ws.title = 'Test' #实验二代码部分 ws1 = wb.create_sheet("Switch", 0) ws2 = wb.create_sheet("Router") #实验三代码部分 ws1['A1'] = 'Interfaces' ws1['B1'] = 'Description' ws1.cell(row=2, column=1, value='Gi1/0/1') ws1.cell(row=3, column=1, value='Gi1/0/2') ws1.cell(row=2, column=2, value='PC1') ws1.cell(row=3, column=2, value='PC2') #实验四代码部分 a3 = ws1['A3'] print (a3.value) column_A = ws1['A'] for i in column_A: print (i.value) row_3 = ws1[3] for i in row_3: print (i.value) for row in ws1.iter_rows(min_row=1, max_col=2, max_row=3): for cell in row: print (cell.value) for row in ws1.values: for value in row: print (value) b3 = ws1['B3'] b3.value = 'PC3' print (b3.value) wb.save('test_openpyxl.xlsx')

代码分段讲解：

  • 查看指定单元格里的内容，需要用到value这个属性, 这里我们查看Switch工作表里A3的内容：

a3 = ws1['A3'] print (a3.value)

  • 要查看Switch工作表里整个A列下已有的内容，可以这么写：

column_A = ws1['A'] for i in column_A: print (i.value)

  • 要查看Switch工作表里整个第3排已有的内容，可以这么写

row_3 = ws1[3] for i in row_3: print (i.value)

  • 要同时查看Switch工作表里单元格A1-A3，B1-B3里的内容，可以用iter_rows()函数，其显示的顺序为A1,B1,A2,B2,A3,B3

for row in ws1.iter_rows(min_row=1, max_col=2, max_row=3): for cell in row: print (cell.value)

  • 要一次性查看excel文件里所有单元格的内容，可以用到ws.values:

for row in ws1.values: for value in row: print (value)

  • 要改变已有的单元格里的内容并查看可以这么写：

b3 = ws1['B3'] b3.value = 'PC3' print (b3.value) wb.save('test_openpyxl.xlsx')


实验五

实验目的：为单元格添加背景颜色（黄色），为A列和B列所有有内容的单元格（A1,A2,A3,B1,B2,B3）设置边框，以及根据每列里宽度最长的单元格来调整列的宽度。

开始实验五之前，我们通过下列代码把B3的内容宽度改长一点：

b3 = ws1['B3'] b3.value = 'PC31234123412342134123421341234' print (b3.value) wb.save('test_openpyxl.xlsx')

打开Excel文件，你可以看到这时B列的宽度(width)和B3的宽度完全不成正比，你还必须手动将B列宽度拉长。

如何让Openpyxl帮我们将列的宽度自动拉长直至匹配到该列下面宽度最长的单元格，将是实验五的重点内容。

实验代码：

from openpyxl import Workbook #实验五代码部分 from openpyxl.styles import PatternFill, Border, Side #实验一代码部分 wb = Workbook() ws = wb.active ws.title = 'Test' #实验二代码部分 ws1 = wb.create_sheet("Switch", 0) ws2 = wb.create_sheet("Router") #实验三代码部分 ws1['A1'] = 'Interfaces' ws1['B1'] = 'Description' ws1.cell(row=2, column=1, value='Gi1/0/1') ws1.cell(row=3, column=1, value='Gi1/0/2') ws1.cell(row=2, column=2, value='PC1') ws1.cell(row=3, column=2, value='PC2') #实验四代码部分 a3 = ws1['A3'] print (a3.value) column_A = ws1['A'] for i in column_A: print (i.value) row_3 = ws1[3] for i in row_3: print (i.value) for row in ws1.iter_rows(min_row=1, max_col=2, max_row=3): for cell in row: print (cell.value) for row in ws1.values: for value in row: print (value) b3 = ws1['B3'] b3.value = 'PC3' print (b3.value) #实验五代码部分 yellowFill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) ws1['A1'].fill=yellowFill ws1['B1'].fill=yellowFill dims = {} for row in ws1.rows: for cell in row: cell.border=thin_border if cell.value: dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value)))) for col, value in dims.items(): ws1.column_dimensions[col].width = value + 1 wb.save('test_openpyxl.xlsx')

代码分段讲解：

  • 要想实现为单元格添加背景颜色，设置边框，以及根据每列里宽度最长的单元格来调整列的宽度实验目的，必须从Openpyxl里导入PatternFill, Border, Side等几个对象。

from openpyxl.styles import PatternFill, Border, Side

  • 用PatternFill() 指定单元格的背景颜色模板（黄色）：

yellowFill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

这里用到RBG颜色码（https://www.rapidtables.com/web/color/RGB_Color.html），只要在参数start_color和end_color里输入自己想要颜色对应的16进制的代码即可，比如黑色对应的HEX值为000000, 这里我用的颜色为黄色，即为FFFF00

  • 调用openpyxl的Border()为单元格设置边框模板，左、右、上、下四个方向的边框类型均为thin：

thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

  • 为A1, B1添加刚才设置的单元格背景色（黄色）

ws1['A1'].fill=yellowFill ws1['B1'].fill=yellowFill

  • 调整列的宽度，思路是首先创建一个空字典，然后通过ws1.rows来遍历工作表中每一排和每一列有交集的所有单元格，在字典里为每一列添加一个键值对，键名通过cell.column返回的值生成，键名为'A'代表A列，键名为'B'代表B列。配合for循环和max()函数找出每一列下宽度最长的单元格的长度（比如遍历找出A1,A2,A3的宽度，看它们谁最长），然后将最长的宽度用作键名'A'和'B'各自对应的值。最后对该值加1然后赋值给ws1.column_dimensions来分别调整A列和B类的宽度。

#首先创建一个名为dims的空字典 dims = {} #ws1.rows返回值的类型为生成器generator，其中包含每一排和每一列有交集的所有单元格 #（每一排中至少有一个单元格为非空），比如(A1,B1), (A2,B2), (A3,B3) for row in ws1.rows: #遍历每一排元组里的每一个元素（即单元格A1,B1,A2,B2,A3,B3） for cell in row: #为每一个单元添加边框 cell.border=thin_border #如果单元格内容为非空，则用max()比较每一列下最长的字符，比如从A1和A2,A3相比较，B1和B2,B3相比较 if cell.value: #cell.column返回的值是单元格所在的列的名称，其数据类型为字符串，比如A1,A2,A3返回'A'，B1,B2,B3则返回'B'。 #第一次故意用dims.get(cell.column, 0)返回一个0，因为此时dims字典下还没有cell.column这个键名， #字典的的get()函数在键名缺失的情况下会返回第二个我们给定的参数，即这里的0。 dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value)))) #遍历字典里的键值对，以每一排宽度最长的单元格作为自动调整单元格长度的标准， #长度+1以确保列的宽度超过最长单元格的宽度 for col, value in dims.items(): ws1.column_dimensions[col].width = value + 1 wb.save('test_openpyxl.xlsx')

最后保存执行代码后看效果：


实战篇

最后附上一个使用Openpyxl的实战案例的脚本。该脚本的内容为在现网一台24口的思科IOS交换机（真机）上配合Napalm模块抓取交换机的hostname（将hostname用作工作表的名字）以及其他的端口信息，包括端口号（Interfaces），端口描述(Descriptions)，端口状态(Status)等三个信息，然后将抓取到的这些信息分别作为工作表里的A、B、C列，然后用Openpyxl写入并保存进excel文件，并且完成为单元格添加背景颜色，设置边框，以及根据每列里宽度最长的单元格来调整列的宽度等操作。

from napalm import get_network_driver import json from openpyxl import Workbook from openpyxl.styles import Color, PatternFill, Font, Border, Side driver = get_network_driver('ios') host = driver('xxx.xxx.xxx.xxx','xxx','xxx') host.open() facts = host.get_facts() facts_json = json.dumps(facts, indent=2) #print(facts_json) get_interfaces = host.get_interfaces() get_interfaces_json = json.dumps(get_interfaces, indent=2) #print(get_interfaces_json) interfaces = [] descriptions = [] status = [] for key,value in get_interfaces.items(): interfaces.append(key) descriptions.append(value['description']) status.append(value['is_up']) for n,i in enumerate(status): if i == False: status[n] = 'Inactive' else: status[n] = 'Active' #print (interfaces) #print (descriptions) #print (status) row_numbers = [n+2 for n in range(len(interfaces))] wb = Workbook() ws = wb.active ws.title = facts['hostname'] ws['A1'] = 'Interfaces' ws['B1'] = 'Description' ws['C1'] = 'Status' for interface, row in zip(interfaces, row_numbers): ws.cell(row=row, column=1, value=interface) for description, row in zip(descriptions, row_numbers): ws.cell(row=row, column=2, value=description) for stat, row in zip(status, row_numbers): ws.cell(row=row, column=3, value=stat) yellowFill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) ws['A1'].fill=yellowFill ws['B1'].fill=yellowFill ws['C1'].fill=yellowFill dims = {} for row in ws.rows: for cell in row: cell.border=thin_border if cell.value: dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value)))) for col, value in dims.items(): ws.column_dimensions[col].width = value + 1 wb.save('switch.xlsx')

代码具体内容就不讲了，相信读过我书的读者朋友们都有能力看懂，这里只放出实际的效果图：