关于python:Python对excel的基本操作

34次阅读

共计 8198 个字符，预计需要花费 21 分钟才能阅读完成。

Python 对 excel 的基本操作

[TOC]

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

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

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

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

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

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

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

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

C:\>pip install openpyxl

from openpyxl import Workbook

# 类实例化
wb = Workbook()

# 保留并生成文件
wb.save('simple_excel.xlsx')

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

from openpyxl import Workbook

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

成果如下所示：

from openpyxl import Workbook

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

参数阐明:

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

from openpyxl import Workbook

# 类实例化
wb = Workbook()
# 激活并缺省创立第一个工作表

# ... 省略中间代码...

ws4 = wb.copy_worksheet(ws3)

# 删除工作表
wb.remove(ws4)

# 保留并生成文件
wb.save('simple_excel.xlsx')

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

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 色彩。

成果如下：

#### 写入单个数据

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：传入可迭代对象(字符串、列表、元组 …)，迭代写入单元格；

成果如下：

from openpyxl import Workbook
from 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：设置文本对齐形式，程度对齐、垂直对齐；

成果如下：

# ... 省略代码...

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

# ... 省略代码...

参数阐明：

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

成果如下：

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignment
from openpyxl.utils import get_column_letter

# 类实例化
wb = Workbook()

# ... 省略中间代码...

# 主动调整单元格宽度
# 筛选出每一列中 cell 的最大长度，并作为调整列宽度的值。all_ws = wb.sheetnames
for 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 设置列的宽度，倡议再加大一点；

成果如下：

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 = 10
chart.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 轴数据；

成果如下：

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

load_workbook办法参数：

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

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 Copy
1_sheet
2_sheet
3_sheet
3_sheet Copy

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

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_name
device_ip
vendor
model
sn
os
version
update_time

# ... 省略代码...

# 指定行范畴
for row in ws1['2:3']:
    for cell in row:
        print(cell.value)
    
# ... 省略代码...


# 回显后果如下:
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

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 获取单元格数据；

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_name
switch-01
switch-02
switch-03

# ... 省略代码...

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

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 色彩参考

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

正文完

python

发表至： python

2021-02-17

0

关于python:Python-连接-MySQL-的几种方法

Python快速爬取车标网图片以后不要说这什么车你不认识了

关于python:Django入门与项目实战

python常见错误和异常附带处理方法

关于alpine:如何上手学习-Alpinejs

关于python:Python对excel的基本操作

1. 前言

2. 试验环境

3. 基本操作

3.1 装置 openpyxl 第三方库

3.2 新建工作簿

3.2.1 新创建工作簿

3.2.2 缺省工作表

3.2.3 创立工作表

3.2.4 删除工作表

3.2.5 设置工作表色彩

3.2.6 单元格写入数据

批量写入数据

3.2.7 设置单元格背景色

3.2.8 合并单元格

3.2.9 主动调整列单元格宽度

3.2.10 图表

3.3 加载工作簿

3.3.1 获取工作表

3.3.2 遍历工作表

3.3.3 获取单元格数据

3.3.4 遍历行

指定行

指定行范畴

办法 iter_rows，遍历行

3.3.5 遍历列

指定列

指定列范畴

办法 iter_cols，遍历列

附录

Just My Socks（注册教程内含优惠码）

关于python:Python对excel的基本操作

1. 前言

2. 试验环境

3. 基本操作

3.1 装置 openpyxl 第三方库

3.2 新建工作簿

3.2.1 新创建工作簿

3.2.2 缺省工作表

3.2.3 创立工作表

3.2.4 删除工作表

3.2.5 设置工作表色彩

3.2.6 单元格写入数据

批量写入数据

3.2.7 设置单元格背景色

3.2.8 合并单元格

3.2.9 主动调整列单元格宽度

3.2.10 图表

3.3 加载工作簿

3.3.1 获取工作表

3.3.2 遍历工作表

3.3.3 获取单元格数据

3.3.4 遍历行

指定行

指定行范畴

办法 iter_rows，遍历行

3.3.5 遍历列

指定列

指定列范畴

办法 iter_cols，遍历列

附录

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）