Python对excel的基本操作

[TOC]

1. 前言

本文是通过Python的第三方库openpyxl, 该库依据官网介绍是反对Excel 2010 的 xlsx/xlsm/xltx/xltm格式文件，哪个版本的这些格局应该都能够反对。

作为网络攻城狮的咱们，应用python对excel的根本操作技能就能够了，当然可能精通更好了。

那咱们应用openpyxl有何作用？我是想前面跟大家分享一篇批量备份网络设备配置的文章，外面会波及到对excel的操作，就提前给大家分享下如何操作根本的excel，顺便坚固下本人的常识。

来来来，先看下如下图所示，这是寄存一张所有网络设备的治理地址表，通过python的openpyxl库就能够读取ip地址信息、巡检命令等信息，就能够批量去备份网络设备配置了，之前我都是用python联合txt文本的，感觉不太不便，就改成python联合excel的形式，excel编辑起来就十分不便了。

2. 试验环境

• windown 10
• Python3.69
• Pycharm
• Python第三方库openpyxl
• excel 2013

阐明：各位试验环境请随便组合，python版本是3.x以上。

3. 基本操作

接下来就开始一步一步教大家如何操作...

3.1 装置openpyxl第三方库

 首先，咱们得先装置一下第三方库`openpyxl`，应用如下命令装置即可。

C:\>pip install openpyxl

3.2 新建工作簿

3.2.1 新创建工作簿

from openpyxl import Workbook# 类实例化wb = Workbook()# 保留并生成文件wb.save('simple_excel.xlsx')

阐明：运行该代码后，会生成一份excel文件：simple_excel.xlsx，临时没内容。

3.2.2 缺省工作表

from openpyxl import Workbook# 类实例化wb = Workbook()# 激活并缺省创立第一个工作表：sheetws1 = wb.active# 第一个工作表命名:1_sheetws1.title = '1_sheet'# 保留并生成文件wb.save('simple_excel.xlsx')

成果如下所示：

3.2.3 创立工作表

from openpyxl import Workbook# 类实例化wb = Workbook()# 激活并缺省创立第一个工作表ws1 = wb.active# 第一个工作表命名ws1.title = '1_sheet'# 创立工作表3ws3 = wb.create_sheet(title='3_sheet', index=2)# 创立工作表2ws2 = wb.create_sheet('2_sheet', 1)# 创立工作表4ws4 = wb.copy_worksheet(ws3)# 保留并生成文件wb.save('simple_excel.xlsx')

参数阐明:

• 属性title：为工作表命名；
• 办法create_sheet：创立新的工作表，其中index为工作表的顺序索引，如0示意第一张表...；
• 办法copy_worksheet：复制工作表；
• 办法save：保留并生成文件，每次运行都会笼罩同名文件；

3.2.4 删除工作表

from openpyxl import Workbook# 类实例化wb = Workbook()# 激活并缺省创立第一个工作表# ...省略中间代码...ws4 = wb.copy_worksheet(ws3)# 删除工作表wb.remove(ws4)# 保留并生成文件wb.save('simple_excel.xlsx')

阐明：此步骤我就不运行了。

3.2.5 设置工作表色彩

from openpyxl import Workbook# 类实例化wb = Workbook()# ...省略中间代码...# 设置工作表背景色ws1.sheet_properties.tabColor = '90EE90'ws2.sheet_properties.tabColor = '1E90FF'ws3.sheet_properties.tabColor = '90EE90'ws4.sheet_properties.tabColor = '1E90FF'# 保留并生成文件wb.save('simple_excel.xlsx')

参数阐明：

• 属性tabColor：设置工作表背景色，能够应用RGB色彩。

成果如下：

3.2.6 单元格写入数据

#### 写入单个数据

from openpyxl import Workbook# 类实例化wb = Workbook()# ...省略中间代码...# 单元格写入数据# 办法1：ws1['A1'] = '示例:'# 办法2：ws1.cell(row=1, column=1, value='示例:')# 保留并生成文件wb.save('simple_excel.xlsx')    

批量写入数据

from openpyxl import Workbook# 类实例化wb = Workbook()# ...省略中间代码...# 单元格写入数据data = [    ["device_name", "device_ip", "vendor", "model", "sn", "os", "version", "update_time"],    ['switch-01', "192.168.1.1", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ],    ['switch-02', "192.168.1.2", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ],    ['switch-03', "192.168.1.3", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ],        ]for row in data:    ws1.append(row)# 保留并生成文件wb.save('simple_excel.xlsx')    

参数阐明：

• append：传入可迭代对象(字符串、列表、元组...)，迭代写入单元格；

成果如下：

3.2.7 设置单元格背景色

from openpyxl import Workbookfrom openpyxl.styles import PatternFill, Border, Side, Font, colors# 类实例化wb = Workbook()# ...省略中间代码...# 单元格填充背景色background_color = PatternFill(start_color='00BFFF', fill_type='solid')# 设置边框border = Border(left=Side(style='thin'),                right=Side(style='thin'),                top=Side(style='thin'),                bottom=Side(style='thin'))font_type = Font(color=colors.WHITE,                 size=12,                 b=True,                 bold=True) # 设置字体居中Align = Alignment(horizontal='center', vertical='center') # 循环迭代cell并设置款式for row in ws1.iter_rows(min_row=2,max_row=2):    for cell in row:        cell.fill, cell.font, cell.alignment = background_color, font_type, Align

参数阐明：

• 类PatternFill：start_color、end_color为背景色、图案色彩、图案款式；
• 类Border：设置边框线条款式，如线条宽度款式、对角线等；
• 类Font：设置字体色彩、大小、下划线等；
• 类Alignment：设置文本对齐形式，程度对齐、垂直对齐；

成果如下：

3.2.8 合并单元格

# ...省略代码...# 合并单元格ws1.merge_cells('A1:H1')ws1.unmerge_cells('A1:H1')# ...省略代码...

参数阐明：

• merge_cells：合并单元格；
• unmerge_cells：勾销合并单元格；

成果如下：

3.2.9 主动调整列单元格宽度

from openpyxl import Workbookfrom openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignmentfrom openpyxl.utils import get_column_letter# 类实例化wb = Workbook()# ...省略中间代码...# 主动调整单元格宽度# 筛选出每一列中cell的最大长度，并作为调整列宽度的值。all_ws = wb.sheetnamesfor ws in all_ws:    dims = {}    ws = wb[ws]    for row in ws.rows:        for cell in row:            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[get_column_letter(col)].width = value + 3    dims.clear()

思路解读：
先找出列所有数据的最大长度，而后依照这个长度主动调整单元格的宽度。

• 先定义一个空字典dims，用来寄存键值对，column(列):value(单元格cell长度)；
• 每一列的单元格value长度一一比拟获得最大值，最初获得最最最大值，作为每列的宽度值width；
• 办法get_column_letter()：是将cell.column整数值1、2、3...转换为列字符串'A'、'B'、 'C'...;
• 办法column_dimensions：通过width设置列的宽度，倡议再加大一点；

成果如下：

3.2.10 图表

from openpyxl.chart import BarChart3D, Reference# ...省略中间代码...# 单元格先写入数据data = [    ["Fruit", "2017", "2018", "2019", "2020"],    ['Apples', 10000, 5000, 8000, 6000],    ['Pears', 2000, 3000, 4000, 5000],    ['Bananas', 6000, 6000, 6500, 6000],    ['Oranges', 500, 300, 200, 700],]for row in data:    ws2.append(row)    # 开始绘3D柱状图chart = BarChart3D()chart.type = 'col'chart.style = 10chart.title = '销量柱状图'chart.x_axis.title = '水果'chart.y_axis.title = '销量'# set_categories() X轴设置数据, add_data() y轴设置数据data = Reference(ws2, min_col=2, min_row=1, max_col=5, max_row=5)series = Reference(ws2, min_col=1, min_row=2, max_row=5)chart.add_data(data=data, titles_from_data=True)chart.set_categories(series)ws2.add_chart(chart, 'A7')

参数阐明：

• 属性type：能够设置列col 和程度bar两种形式；
• 属性style：设置款式，为整数值1~48之间；
• 属性title：设置题目；
• 属性x_axis.title：x轴的题目；
• 属性y_axis.title：y轴的题目；
• 类Reference：援用单元格范畴的数据;
• 办法add_data：设置Y轴数据；
• 办法set_categories：设置X轴数据；

成果如下：

3.3 加载工作簿

通过load_workbook办法加载已存在的excel文件，并以read_only只读形式读取内容，不能进行编辑。

load_workbook办法参数：

• filename：文件门路或文件对象；
• read_only：是否为只读，针对浏览做了优化，不能编辑内容；
• keep_vba：是否保留vba内(并不象征能够用它)，缺省保留；
• data_only：单元格是否保留公式或后果；
• keep_links：是否保留内部链接，缺省保留；

3.3.1 获取工作表

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx', read_only=True)# 获取所有工作表print('所有工作表: ', wb.sheetnames)# 敞开工作簿wb.close()# 回显后果如下:所有工作表:  ['1_sheet', '2_sheet', '3_sheet', '3_sheet Copy']

参数阐明：

• 参数read_only=True：示意以只读模式关上工作簿；
• 办法sheetnames：返回的是一个列表模式的工作表名称；

• 办法close()：仅在read-only 和 write-only 模式应用即可，下同，故不做屡次解释；

  3.3.2 遍历工作表

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx', read_only=True)# 获取单个工作表print('第1个工作表:', wb.sheetnames[0])print('第2个工作表:', wb.sheetnames[1])print('第3个工作表:', wb.sheetnames[2])print('第4个工作表:', wb.sheetnames[3])# 循环遍历工作表for ws in wb.sheetnames:    print(ws)# 敞开工作簿wb.close()# 回显后果如下:第1个工作表: 1_sheet第2个工作表: 2_sheet第3个工作表: 3_sheet第4个工作表: 3_sheet Copy1_sheet2_sheet3_sheet3_sheet Copy

3.3.3 获取单元格数据

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx', read_only=True)# 第一个工作表对象ws1 = wb[wb.sheetnames[0]]# 或者# ws1 = wb['1_sheet']# 获取某个单元格print(f"获取单元格数据: {ws1['A3'].value}")# 选取范畴获取单元格数据for row in ws1['A3:H3']:    for cell in row:        print(f"按范畴获取单元格数据: {cell.value}")# 敞开工作簿wb.close()# 回显后果如下:获取单元格数据: switch-01    按范畴获取单元格数据: switch-01按范畴获取单元格数据: 192.168.1.1按范畴获取单元格数据: cisco按范畴获取单元格数据: WS-C3560G-24TS按范畴获取单元格数据: FOC00000000按范畴获取单元格数据: cisco_ios按范畴获取单元格数据: 12.2(50)SE5按范畴获取单元格数据: 1 weeks, 1 minutes    

3.3.4 遍历行

指定行

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx', read_only=True)# 第一个工作表对象ws1 = wb[wb.sheetnames[0]]# 指定第二行for cell in ws1['2']:    print(cell.value)    # 敞开工作簿wb.close()# 回显后果如下:device_namedevice_ipvendormodelsnosversionupdate_time

指定行范畴

# ... 省略代码...# 指定行范畴for row in ws1['2:3']:    for cell in row:        print(cell.value)    # ... 省略代码...# 回显后果如下:device_namedevice_ipvendormodelsnosversionupdate_timeswitch-01192.168.1.1ciscoWS-C3560G-24TSFOC00000000cisco_ios12.2(50)SE51 weeks, 1 minutes

办法iter_rows，遍历行

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx', read_only=True)# 第一个工作表对象ws1 = wb[wb.sheetnames[0]]# 循环遍历行for row in ws1.iter_rows(min_row=2, max_row=2, min_col=1, max_col=8):    for cell in row:        print(f"单元格数据:{cell.value}")        # 敞开工作簿wb.close()# 回显后果如下:单元格数据:device_name单元格数据:device_ip单元格数据:vendor单元格数据:model单元格数据:sn单元格数据:os单元格数据:version单元格数据:update_time

参数阐明：

• 办法iter_rows：通过该办法能够遍历每行数据，是一个tuple，可再次循环通过.value获取单元格数据；

3.3.5 遍历列

指定列

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx', read_only=True)# 第一个工作表对象ws1 = wb[wb.sheetnames[0]]# 指定第一列for cell in ws1['A']:    print(cell.value)    # 敞开工作簿wb.close()# 回显后果如下:示例:device_nameswitch-01switch-02switch-03

指定列范畴

# ... 省略代码...# 指定列范畴for col in ws1['A:B']:    for cell in col:        print(cell.value)        # ... 省略代码...                # 回显后果如下:示例:device_nameswitch-01switch-02switch-03Nonedevice_ip192.168.1.1192.168.1.2192.168.1.3

办法iter_cols，遍历列

from openpyxl import load_workbook as open# 类示例化wb = open('simple_excel.xlsx')# 第一个工作表对象ws1 = wb[wb.sheetnames[0]]# 循环遍历列for col in ws1.iter_cols(min_row=3, max_row=5, min_col=1, max_col=1):    for cell in col:        print(f"单元格数据:{cell.value}")        # 敞开工作簿wb.close()# 回显后果如下:单元格数据:switch-01单元格数据:switch-02单元格数据:switch-03

参数阐明：

• 办法iter_cols：通过该办法能够遍历每列数据，是一个tuple，可再次循环通过.value获取单元格数据，另外和iter_rows不一样的就是load_workbook 不能应用read_only=True；

附录

• openpyxl官网文档
  openpyxl
• RGB色彩参考
  RGB色彩参考

如果喜爱的我的文章，欢送关注我的公众号：点滴技术，扫码关注，不定期分享