最近和读者群里的学员交流，发现国内的网工同行们在平常办公中使用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 Workbookwb = Workbook()ws = wb.activews.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 Workbookwb = 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.activews.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.activews.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='https://www.itzhengshu.com/excel/Gi1/0/1')ws1.cell(row=3, column=1, value='https://www.itzhengshu.com/excel/Gi1/0/2')ws1.cell(row=2, column=2, value='https://www.itzhengshu.com/excel/PC1')ws1.cell(row=3, column=2, value='https://www.itzhengshu.com/excel/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='https://www.itzhengshu.com/excel/Gi1/0/1')ws1.cell(row=3, column=1, value='https://www.itzhengshu.com/excel/Gi1/0/2')ws1.cell(row=2, column=2, value='https://www.itzhengshu.com/excel/PC1')ws1.cell(row=3, column=2, value='https://www.itzhengshu.com/excel/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.activews.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='https://www.itzhengshu.com/excel/Gi1/0/1')ws1.cell(row=3, column=1, value='https://www.itzhengshu.com/excel/Gi1/0/2')ws1.cell(row=2, column=2, value='https://www.itzhengshu.com/excel/PC1')ws1.cell(row=3, column=2, value='https://www.itzhengshu.com/excel/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 = 'https://www.itzhengshu.com/excel/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 = 'https://www.itzhengshu.com/excel/PC3'print (b3.value)wb.save('test_openpyxl.xlsx')

实验五

实验目的：为单元格添加背景颜色（黄色），为A列和B列所有有内容的单元格（A1,A2,A3,B1,B2,B3）设置边框，以及根据每列里宽度最长的单元格来调整列的宽度 。
开始实验五之前 ， 我们通过下列代码把B3的内容宽度改长一点：
b3 = ws1['B3']b3.value = 'https://www.itzhengshu.com/excel/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.activews.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='https://www.itzhengshu.com/excel/Gi1/0/1')ws1.cell(row=3, column=1, value='https://www.itzhengshu.com/excel/Gi1/0/2')ws1.cell(row=2, column=2, value='https://www.itzhengshu.com/excel/PC1')ws1.cell(row=3, column=2, value='https://www.itzhengshu.com/excel/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 = 'https://www.itzhengshu.com/excel/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=yellowFillws1['B1'].fill=yellowFilldims = {}for row in ws1.rows:for cell in row:cell.border=thin_borderif 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 = value1wb.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=yellowFillws1['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 = value1wb.save('test_openpyxl.xlsx')
最后保存执行代码后看效果：

实战篇

最后附上一个使用Openpyxl的实战案例的脚本 。该脚本的内容为在现网一台24口的思科IOS交换机（真机）上配合Napalm模块抓取交换机的hostname（将hostname用作工作表的名字）以及其他的端口信息，包括端口号（Interfaces） ， 端口描述(Descriptions)，端口状态(Status)等三个信息，然后将抓取到的这些信息分别作为工作表里的A、B、C列，然后用Openpyxl写入并保存进excel文件，并且完成为单元格添加背景颜色，设置边框 ， 以及根据每列里宽度最长的单元格来调整列的宽度等操作 。
from napalm import get_network_driverimport jsonfrom openpyxl import Workbookfrom 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.activews.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=https://www.itzhengshu.com/excel/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=yellowFillws['B1'].fill=yellowFillws['C1'].fill=yellowFilldims = {}for row in ws.rows: for cell in row:cell.border=thin_borderif 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 = value1wb.save('switch.xlsx')
【用Excel办公的网工的福音：Openpyxl模块】代码具体内容就不讲了，相信读过我书的读者朋友们都有能力看懂，这里只放出实际的效果图：

相关经验推荐

• excel如何删除多余的行列 excel怎么快速删除行
• excel表格中怎么对指定的几列进行筛选 excel如何筛选出自己想要的部分
• 如何给excel文档设置打开权限密码 excel如何设置权限
• Excel条件格式比较数据技巧，自动变色提示，清爽不烧心
• 输入公式后，Excel却没有自动计算结果，你一定遇到了这些坑
• excel怎么设置条件格式公式 excel里如何设置条件公式并且如何设置
• 如何将excel表格拆分成若干表格 excel中怎么把表格拆分单元格
• excel怎么插入表格 word表格怎么转换成excel
• excel表格怎么查找 excel如何参照对标值列