• 售前

  • 售后

热门帖子
入门百科

Python对excel的根本操纵方法

[复制链接]
乐态思 显示全部楼层 发表于 2021-10-25 19:02:18 |阅读模式 打印 上一主题 下一主题
目次


  • 1. 前言
  • 2. 实行环境
  • 3. 基本操纵

    • 3.1 安装openpyxl第三方库
    • 3.2 新建工作簿
    • 3.3 加载工作簿


1. 前言

本文是通过Python的第三方库
  1. openpyxl
复制代码
, 该库根据官方介绍是支持Excel 2010 的 xlsx/xlsm/xltx/xltm格式文件,哪个版本的这些格式应该都可以支持。
作为网络攻城狮的我们,使用python对excel的基本操纵技能就可以了,固然能够精通更好了。
那我们使用
  1. openpyxl
复制代码
有何作用?我是想背面跟各人分享一篇
  1. 批量备份网络设备配置
复制代码
的文章,里面会涉及到对excel的操纵,就提前给各人分享下怎样操纵基本的excel,趁便巩固下本身的知识。
来来来,先看下如下图所示,这是存放一张所有网络设备的管理地址表,通过python的
  1. openpyxl
复制代码
库就可以读取ip地址信息、巡检命令等信息,就可以批量去备份网络设备设置了,之前我都是用python团结txt文本的,觉得不太方便,就改成python团结excel的方式,excel编辑起来就非常方便了。


2. 实行环境

      
  • windown 10  
  • Python3.69  
  • Pycharm  
  • Python第三方库
    1. openpyxl
    复制代码

  • excel 2013
阐明:各位实行环境请随意组合,python版本是3.x以上。

3. 基本操纵

接下来就开始一步一步教各人怎样操纵...

3.1 安装openpyxl第三方库

C:\>pip install openpyxl

3.2 新建工作簿

3.2.1 新创建工作簿
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # 保存并生成文件
  5. wb.save('simple_excel.xlsx')
复制代码
​阐明:运行该代码后,会天生一份excel文件:
  1. simple_excel.xlsx
复制代码
,临时没内容。
3.2.2 缺省工作表
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # 激活并缺省创建第一个工作表:sheet
  5. ws1 = wb.active
  6. # 第一个工作表命名:1_sheet
  7. ws1.title = '1_sheet'
  8. # 保存并生成文件
  9. wb.save('simple_excel.xlsx')
复制代码
​效果如下所示:

3.2.3 创建工作表
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # 激活并缺省创建第一个工作表
  5. ws1 = wb.active
  6. # 第一个工作表命名
  7. ws1.title = '1_sheet'
  8. # 创建工作表3
  9. ws3 = wb.create_sheet(title='3_sheet', index=2)
  10. # 创建工作表2
  11. ws2 = wb.create_sheet('2_sheet', 1)
  12. # 创建工作表4
  13. ws4 = wb.copy_worksheet(ws3)
  14. # 保存并生成文件
  15. wb.save('simple_excel.xlsx')
复制代码
​参数阐明:
      
  • 属性title:为工作表定名;  
  • 方法create_sheet:创建新的工作表,此中index为工作表的顺序索引,如0表示第一张表...;  
  • 方法copy_worksheet:复制工作表;  
  • 方法save:保存并天生文件,每次运行都会覆盖同名文件;
3.2.4 删除工作表
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # 激活并缺省创建第一个工作表
  5. # ...省略中间代码...
  6. ws4 = wb.copy_worksheet(ws3)
  7. # 删除工作表
  8. wb.remove(ws4)
  9. # 保存并生成文件
  10. wb.save('simple_excel.xlsx')
复制代码
​阐明:此步调我就不运行了。
3.2.5 设置工作表颜色
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # ...省略中间代码...
  5. # 设置工作表背景色
  6. ws1.sheet_properties.tabColor = '90EE90'
  7. ws2.sheet_properties.tabColor = '1E90FF'
  8. ws3.sheet_properties.tabColor = '90EE90'
  9. ws4.sheet_properties.tabColor = '1E90FF'
  10. # 保存并生成文件
  11. wb.save('simple_excel.xlsx')
复制代码
​参数阐明:
属性tabColor:设置工作表配景色,可以使用
  1. RGB
复制代码
颜色。
​效果如下:

3.2.6 单位格写入数据
#### 写入单个数据
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # ...省略中间代码...
  5. # 单元格写入数据
  6. # 方法1:
  7. ws1['A1'] = '示例:'
  8. # 方法2:
  9. ws1.cell(row=1, column=1, value='示例:')
  10. # 保存并生成文件
  11. wb.save('simple_excel.xlsx')
复制代码
批量写入数据
  1. from openpyxl import Workbook
  2. # 类实例化
  3. wb = Workbook()
  4. # ...省略中间代码...
  5. # 单元格写入数据
  6. data = [
  7. ["device_name", "device_ip", "vendor", "model", "sn", "os", "version", "update_time"],
  8. ['switch-01', "192.168.1.1", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ],
  9. ['switch-02', "192.168.1.2", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ],
  10. ['switch-03', "192.168.1.3", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ],
  11. ]
  12. for row in data:
  13. ws1.append(row)
  14. # 保存并生成文件
  15. wb.save('simple_excel.xlsx')
复制代码
​参数阐明:
append:传入可迭代对象(字符串、列表、元组...),迭代写入单位格;
​效果如下:

3.2.7 设置单位格配景色
  1. from openpyxl import Workbook
  2. from openpyxl.styles import PatternFill, Border, Side, Font, colors
  3. # 类实例化
  4. wb = Workbook()
  5. # ...省略中间代码...
  6. # 单元格填充背景色
  7. background_color = PatternFill(start_color='00BFFF', fill_type='solid')
  8. # 设置边框
  9. border = Border(left=Side(style='thin'),
  10.   right=Side(style='thin'),
  11.   top=Side(style='thin'),
  12.   bottom=Side(style='thin'))
  13. font_type = Font(color=colors.WHITE,
  14.    size=12,
  15.    b=True,
  16.    bold=True)
  17. # 设置字体居中
  18. Align = Alignment(horizontal='center', vertical='center')
  19. # 循环迭代cell并设置样式
  20. for row in ws1.iter_rows(min_row=2,max_row=2):
  21. for cell in row:
  22. cell.fill, cell.font, cell.alignment = background_color, font_type, Align
复制代码
​参数阐明:
      
  • 类PatternFill:start_color、end_color为配景色、图案颜色、图案样式;  
  • 类Border:设置边框线条样式,如线条宽度样式、对角线等;  
  • 类Font:设置字体颜色、巨细、下划线等;  
  • 类Alignment:设置文本对齐方式,水平对齐、垂直对齐;
​效果如下:

3.2.8 合并单位格
  1. # ...省略代码...
  2. # 合并单元格
  3. ws1.merge_cells('A1:H1')
  4. ws1.unmerge_cells('A1:H1')
  5. # ...省略代码...
复制代码
​参数阐明:
      
  • merge_cells:合并单位格;  
  • unmerge_cells:取消合并单位格;
效果如下:
3.2.9 主动调解列单位格宽度
  1. from openpyxl import Workbook
  2. from openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignment
  3. from openpyxl.utils import get_column_letter
  4. # 类实例化
  5. wb = Workbook()
  6. # ...省略中间代码...
  7. # 自动调整单元格宽度
  8. # 筛选出每一列中cell的最大长度,并作为调整列宽度的值。
  9. all_ws = wb.sheetnames
  10. for ws in all_ws:
  11. dims = {}
  12. ws = wb[ws]
  13. for row in ws.rows:
  14. for cell in row:
  15.   if cell.value:
  16.   dims[cell.column] = max(dims.get(cell.column, 0), len(str(cell.value)))
  17.   
  18. for col, value in dims.items():
  19. ws.column_dimensions[get_column_letter(col)].width = value + 3
  20. 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 图表
  1. from openpyxl.chart import BarChart3D, Reference
  2. # ...省略中间代码...
  3. # 单元格先写入数据
  4. data = [
  5. ["Fruit", "2017", "2018", "2019", "2020"],
  6. ['Apples', 10000, 5000, 8000, 6000],
  7. ['Pears', 2000, 3000, 4000, 5000],
  8. ['Bananas', 6000, 6000, 6500, 6000],
  9. ['Oranges', 500, 300, 200, 700],
  10. ]
  11. for row in data:
  12. ws2.append(row)
  13. # 开始绘3D柱状图
  14. chart = BarChart3D()
  15. chart.type = 'col'
  16. chart.style = 10
  17. chart.title = '销量柱状图'
  18. chart.x_axis.title = '水果'
  19. chart.y_axis.title = '销量'
  20. # set_categories() X轴设置数据, add_data() y轴设置数据
  21. data = Reference(ws2, min_col=2, min_row=1, max_col=5, max_row=5)
  22. series = Reference(ws2, min_col=1, min_row=2, max_row=5)
  23. chart.add_data(data=data, titles_from_data=True)
  24. chart.set_categories(series)
  25. 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 加载工作簿

通过
  1. load_workbook
复制代码
方法加载已存在的excel文件,并以
  1. read_only
复制代码
只读方式读取内容,不能举行编辑。
  1. load_workbook
复制代码
方法参数:
      
  • filename:文件路径或文件对象;  
  • read_only:是否为只读,针对阅读做了优化,不能编辑内容;  
  • keep_vba:是否保存vba内(并不意味可以用它),缺省保存;  
  • data_only:单位格是否保存公式或效果;  
  • keep_links:是否保存外部链接,缺省保存;
3.3.1 获取工作表
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx', read_only=True)
  4. # 获取所有工作表
  5. print('所有工作表: ', wb.sheetnames)
  6. # 关闭工作簿
  7. wb.close()
  8. # 回显结果如下:
  9. 所有工作表: ['1_sheet', '2_sheet', '3_sheet', '3_sheet Copy']
复制代码
​参数阐明:
      
  • 参数read_only=True:表示以
    1. 只读
    复制代码
    模式打开工作簿;  
  • 方法sheetnames:返回的是一个列表形式的工作表名称;  
  • 方法close():仅在
    1. read-only
    复制代码
    1. write-only
    复制代码
    模式使用即可,下同,故不做多次表明;
3.3.2 遍历工作表
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx', read_only=True)
  4. # 获取单个工作表
  5. print('第1个工作表:', wb.sheetnames[0])
  6. print('第2个工作表:', wb.sheetnames[1])
  7. print('第3个工作表:', wb.sheetnames[2])
  8. print('第4个工作表:', wb.sheetnames[3])
  9. # 循环遍历工作表
  10. for ws in wb.sheetnames:
  11. print(ws)
  12. # 关闭工作簿
  13. wb.close()
  14. # 回显结果如下:
  15. 第1个工作表: 1_sheet
  16. 第2个工作表: 2_sheet
  17. 第3个工作表: 3_sheet
  18. 第4个工作表: 3_sheet Copy
  19. 1_sheet
  20. 2_sheet
  21. 3_sheet
  22. 3_sheet Copy
复制代码
3.3.3 获取单位格数据
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx', read_only=True)
  4. # 第一个工作表对象
  5. ws1 = wb[wb.sheetnames[0]]
  6. # 或者
  7. # ws1 = wb['1_sheet']
  8. # 获取某个单元格
  9. print(f"获取单元格数据: {ws1['A3'].value}")
  10. # 选取范围获取单元格数据
  11. for row in ws1['A3:H3']:
  12. for cell in row:
  13. print(f"按范围获取单元格数据: {cell.value}")
  14. # 关闭工作簿
  15. wb.close()
  16. # 回显结果如下:
  17. 获取单元格数据: switch-01
  18. 按范围获取单元格数据: switch-01
  19. 按范围获取单元格数据: 192.168.1.1
  20. 按范围获取单元格数据: cisco
  21. 按范围获取单元格数据: WS-C3560G-24TS
  22. 按范围获取单元格数据: FOC00000000
  23. 按范围获取单元格数据: cisco_ios
  24. 按范围获取单元格数据: 12.2(50)SE5
  25. 按范围获取单元格数据: 1 weeks, 1 minutes
复制代码
3.3.4 遍历行
指定行
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx', read_only=True)
  4. # 第一个工作表对象
  5. ws1 = wb[wb.sheetnames[0]]
  6. # 指定第二行
  7. for cell in ws1['2']:
  8. print(cell.value)
  9. # 关闭工作簿
  10. wb.close()
  11. # 回显结果如下:
  12. device_name
  13. device_ip
  14. vendor
  15. model
  16. sn
  17. os
  18. version
  19. update_time
复制代码
指定行范围
  1. # ... 省略代码...
  2. # 指定行范围
  3. for row in ws1['2:3']:
  4. for cell in row:
  5. print(cell.value)
  6. # ... 省略代码...
  7. # 回显结果如下:
  8. device_name
  9. device_ip
  10. vendor
  11. model
  12. sn
  13. os
  14. version
  15. update_time
  16. switch-01
  17. 192.168.1.1
  18. cisco
  19. WS-C3560G-24TS
  20. FOC00000000
  21. cisco_ios
  22. 12.2(50)SE5
  23. 1 weeks, 1 minutes
复制代码
方法iter_rows,遍历行
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx', read_only=True)
  4. # 第一个工作表对象
  5. ws1 = wb[wb.sheetnames[0]]
  6. # 循环遍历行
  7. for row in ws1.iter_rows(min_row=2, max_row=2, min_col=1, max_col=8):
  8. for cell in row:
  9. print(f"单元格数据:{cell.value}")
  10. # 关闭工作簿
  11. wb.close()
  12. # 回显结果如下:
  13. 单元格数据:device_name
  14. 单元格数据:device_ip
  15. 单元格数据:vendor
  16. 单元格数据:model
  17. 单元格数据:sn
  18. 单元格数据:os
  19. 单元格数据:version
  20. 单元格数据:update_time
复制代码
​参数阐明:
方法iter_rows:通过该方法可以遍历每行数据,是一个
  1. tuple
复制代码
,可再次循环通过
  1. .value
复制代码
获取单位格数据;
3.3.5 遍历列
指定列
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx', read_only=True)
  4. # 第一个工作表对象
  5. ws1 = wb[wb.sheetnames[0]]
  6. # 指定第一列
  7. for cell in ws1['A']:
  8. print(cell.value)
  9. # 关闭工作簿
  10. wb.close()
  11. # 回显结果如下:
  12. 示例:
  13. device_name
  14. switch-01
  15. switch-02
  16. switch-03
复制代码
指定列范围
  1. # ... 省略代码...
  2. # 指定列范围
  3. for col in ws1['A:B']:
  4. for cell in col:
  5. print(cell.value)
  6. # ... 省略代码...
  7. # 回显结果如下:
  8. 示例:
  9. device_name
  10. switch-01
  11. switch-02
  12. switch-03
  13. None
  14. device_ip
  15. 192.168.1.1
  16. 192.168.1.2
  17. 192.168.1.3
复制代码
方法iter_cols,遍历列
  1. from openpyxl import load_workbook as open
  2. # 类示例化
  3. wb = open('simple_excel.xlsx')
  4. # 第一个工作表对象
  5. ws1 = wb[wb.sheetnames[0]]
  6. # 循环遍历列
  7. for col in ws1.iter_cols(min_row=3, max_row=5, min_col=1, max_col=1):
  8. for cell in col:
  9. print(f"单元格数据:{cell.value}")
  10. # 关闭工作簿
  11. wb.close()
  12. # 回显结果如下:
  13. 单元格数据:switch-01
  14. 单元格数据:switch-02
  15. 单元格数据:switch-03
复制代码
参数阐明:
方法iter_cols:通过该方法可以遍历每列数据,是一个
  1. tuple
复制代码
,可再次循环通过
  1. .value
复制代码
获取单位格数据,别的和
  1. iter_rows
复制代码
不一样的就是load_workbook 不能使用
  1. read_only=True
复制代码

附录openpyxl官方文档
openpyxl
RGB颜色参考
RGB颜色参考
到此这篇关于Python对excel的基本操纵的文章就介绍到这了,更多相干Python excel操纵内容请搜索脚本之家以前的文章或继续浏览下面的相干文章希望各人以后多多支持脚本之家!
到此这篇关于Python对excel的基本操纵方法的文章就介绍到这了,更多相干Python excel操纵内容请搜索脚本之家以前的文章或继续浏览下面的相干文章希望各人以后多多支持脚本之家!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作