DDR爱好者之家

Python对excel的基本操作方法
站长资源 2024/11/24 佚名
23 1538
DDR爱好者之家 Design By 杰米

1. 前言

本文是通过Python的第三方库openpyxl, 该库根据官方介绍是支持Excel 2010 的 xlsx/xlsm/xltx/xltm格式文件，哪个版本的这些格式应该都可以支持。

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

那我们使用openpyxl有何作用？我是想后面跟大家分享一篇批量备份网络设备配置的文章，里面会涉及到对excel的操作，就提前给大家分享下如何操作基本的excel，顺便巩固下自己的知识。

来来来，先看下如下图所示，这是存放一张所有网络设备的管理地址表，通过python的openpyxl库就可以读取ip地址信息、巡检命令等信息，就可以批量去备份网络设备配置了，之前我都是用python结合txt文本的，觉得不太方便，就改成python结合excel的方式，excel编辑起来就非常方便了。

Python对excel的基本操作方法

2. 实验环境

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

说明：各位实验环境请随意组合，python版本是3.x以上。

3. 基本操作

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

3.1 安装openpyxl第三方库

C:\>pip install openpyxl

3.2 新建工作簿

3.2.1 新创建工作簿

from openpyxl import Workbook

# 类实例化
wb = Workbook()

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

"htmlcode"> 

from openpyxl import Workbook

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

"text-align: center">Python对excel的基本操作方法

3.2.3 创建工作表

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')

"htmlcode"> 

from openpyxl import Workbook

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

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

ws4 = wb.copy_worksheet(ws3)

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

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

"htmlcode"> 

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')

"text-align: center">Python对excel的基本操作方法

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')

"text-align: center">Python对excel的基本操作方法

3.2.7 设置单元格背景色

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

"text-align: center">Python对excel的基本操作方法

3.2.8 合并单元格

# ...省略代码...

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

# ...省略代码...

"" loading="lazy" src="/UploadFiles/2021-04-08/2021021809232236.jpg">

3.2.9 自动调整列单元格宽度

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()

"text-align: center">Python对excel的基本操作方法

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 = 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')

"text-align: center">"" loading="lazy" src="/UploadFiles/2021-04-08/2021021809232238.jpg">

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']

"htmlcode"> 

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

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_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

方法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

"htmlcode"> 

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

方法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颜色参考

DDR爱好者之家 Design By 杰米
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
DDR爱好者之家 Design By 杰米
ddrfans.com DDR爱好者之家
8,675无损音乐
1,324高清电影
213破解软件
70,141收录资讯

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网
DDR爱好者之家 Design By 杰米