python 使用openpyxl读取excel数据

openpyxl介绍

​ openpyxl是一个开源项目,它是一个用于读取/写入Excel 2010文档(如xlsx 、xlsm 、xltx 、xltm文件 )的Python库,如果要处理更早格式的Excel文档(xls),需要用到其它库(如:xlrd、xlwt等),这是openpyxl比较其他模块的不足之处。openpyxl是一款比较综合的工具,不仅能够同时读取和修改Excel文档,而且可以对Excel文件内单元格进行详细设置,包括单元格样式等内容,甚至还支持图表插入、打印设置等内容。

​ python中与excel操作相关的模块:

  • 📔 xlrd库:从excel中读取数据,支持xls、xlsx格式
  • 📔 xlwt库:对excel进行修改操作,但不支持对xlsx格式的修改
  • 📔 xlutils库:在xlwt和xlrd中,对一个已存在文件进行修改
  • 📘 openpyxl库:主要针对xlsx格式的excel进行读取和编辑

openpyxl安装

pip install openpyxl

openpyxl基本概念

  • WorkBook:工作簿对象
  • Sheet:表单对象
  • Cell:表格对象

​ 也就是我们使用excel时可以看到的三个对象,我们操作excel的步骤是打开工作簿,选中表单,对单元格进行操作,而openpyxl的工作原理也是如此。

openpyxl对excel进行操作

新建excel

import openpyxl

# 创建一个工作簿
workbook = openpyxl.Workbook()
# 创建一个表单
sheet = workbook.create_sheet('表单1')
# 写入一个数据
sheet.cell(row=1, column=1, value="python")
# 保存
workbook.save('test.xlsx')

​ 🎈 注意点:如果保存的文件名已存在,将会覆盖原来的文件

打开已存在的文件

  • 打开已存在的文件:load_workbook()
  • 获取表单:workbook['表单名']
  • 读取指定表格:cell(row, column).value
import openpyxl

# 打开工作簿
workbook = openpyxl.load_workbook('test.xlsx')
# 获取表单
sheet = workbook['表单1']
# 读取指定的单元格数据
cell = sheet.cell(row=1, column=1).value
print(cell)

​ 运行结果:

C:\software\python\python.exe D:/learn/python24/python_base/test.py
python

Process finished with exit code 0

读取单个单元格

# 方式一:读取A6单元格的值
cell1 = sheet['A6'].value

# 方式二:读取第3行,第4列单元格的值
cell2 = sheet.cell(row=3, column=4).value

读取多个单元格

# 方式一:读取A6单元格的值
cell1 = sheet['A6'].value

# 方式二:读取第3行,第4列单元格的值
cell2 = sheet.cell(row=3, column=4).value

​ 读取多个单元格时,都是以元组的形式返回

其他相关操作

关键代码 相关描述
cell(row, column, value) 写入数据,三个参数分别是行,列,值
cell(row, column).value 获取指定单元格的值,两个参数分别是行,列
workbook.remove('表单名') 删除表单
del workbook['表单名'] 删除表单
workbook.save('文件名') 保存工作簿
sheet.max_row 获取表单数据的总行数
sheet.max_column 获取表单数据的总列数
sheet.rows 获取按行所有的数据
sheet.columns 获取按列所有的数据

​ 🎈 注意点:

  • openpyxl中列和行的起始标识都是1,不是从0开始
  • 从excel中读取出来的数据只有两种类型,即数值类型和字符串类型
  • 不要随便在表格中敲空格,会影响判断最大行数和最大列数
  • 运行操作excel的代码时,要先关闭在操作系统中打开的相关excel表,否则可能会无法读取/写入数据
  • 写入数据时要执行保存
import openpyxl

# 打开工作簿
wb = openpyxl.load_workbook('test.xlsx')
# 获取表单
sh = wb['表单1']
# 读取指定的单元格数据
res1 = sh.cell(row=1, column=1).value
print(res1)

# 获取最大行数
print(sh.max_row)
# 获取最大列数
print(sh.max_column)

# 按列读取所有数据,每一列的单元格放入一个元组中
print(sh.columns) # 直接打印,打印结果是一个可迭代对象,我们可以转换成列表来查看

# 按行读取所有数据,每一行的单元格放入一个元组中
rows = sh.rows
# print(list(rows)) # 转换成列表之后打印结果为具体的单元格,如下
# [(<Cell '表单1'.A1>, <Cell '表单1'.B1>, <Cell '表单1'.C1>, <Cell '表单1'.D1>, <Cell '表单1'.E1>, <Cell '表单1'.F1>, <Cell '表单1'.G1>),
# (<Cell '表单1'.A2>, <Cell '表单1'.B2>, <Cell '表单1'.C2>, <Cell '表单1'.D2>, <Cell '表单1'.E2>, <Cell '表单1'.F2>, <Cell '表单1'.G2>),
# (<Cell '表单1'.A3>, <Cell '表单1'.B3>, <Cell '表单1'.C3>, <Cell '表单1'.D3>, <Cell '表单1'.E3>, <Cell '表单1'.F3>, <Cell '表单1'.G3>)]

# 我们可以通过for循环以及value来查看单元格的值
for row in list(rows): # 遍历每行数据
 case = [] # 用于存放一行数据
 for c in row: # 把每行的每个单元格的值取出来,存放到case里
  case.append(c.value)
 print(case)

​ 运行结果:

C:\software\python\python.exe D:/learn/python24/python_base/test.py
case_id
3
7
<generator object Worksheet._cells_by_col at 0x0000012FEF90D8B8>
['case_id', 'interface', 'title', 'method', 'url', 'data', 'expected']
[1, 'login', '登录成功', 'post', '/member/login', '{"mobile_phone":"15692004245","pwd":"miki12345"}', "{'code': 0, 'msg': 'OK'}"]
[2, 'login', '登录失败-手机号为空', 'post', '/member/login', '{"mobile_phone":"","pwd":"miki12345"}', "{'code': 2, 'msg': '手机号码为空'}"]

​ excel表中的原数据如下图:

openpyxl封装

​ 在测试过程中,我们会经常需要访问excel读取测试数据,如果毎访问一次我们就调用一次openpyxl,进行打开excel,选取表单,读取数据等操作,将会增加我们的工作量和代码量。凡是需要重复使用的,我们都可以对它进行二次封装,写成我们自己的版本,还可以在封装过程中加入我们想要的内容,使用的时候直接调用即可。

​ 在封装openpyxl时,读取excel时我写了两种实现方法,第一种是读取数据后,把每条测试数据的键值保存到一个字典中,再把字典存到一个列表,这种方式比较容易理解,但是调用的时候需要多敲几个括号或者引号;第二种是把每条测试数据的键值存到一个实例对象的属性中,再把每个实例存到一个列表。实际上他们的作用是一样,只是第二种方式调用时更简单。

# read_excel.py
import openpyxl

class CasesData:
 """用于保存测试用例数据"""
 pass

class ReadExcel:

 def __init__(self, file_name, sheet_name):
  self.file_name = file_name
  self.sheet_name = sheet_name

 def open(self):
  self.wb = openpyxl.load_workbook(self.file_name)
  self.sh = self.wb[self.sheet_name]

 def close(self):
  self.wb.close()

 def read_data(self):
  """按行读取数据,最后返回一个存储字典的列表"""
  self.open()
  rows = list(self.sh.rows)
  titles = []
  for t in rows[0]:
   title = t.value
   titles.append(title)
  cases = []
  for row in rows[1:]:
   case = []
   for r in row:
    case.append(r.value)
   cases.append(dict(zip(titles, case))) # 通过zip聚合打包用例的标题和数据
  self.close()
  return cases

 def read_data_obj(self):
  """按行读取数据,最后返回的是一个存储实例对象的列表"""
  self.open()
  rows = list(self.sh.rows)
  titles = []
  for t in rows[0]:
   title = t.value
   titles.append(title)
  cases = []
  for row in rows[1:]:
   case_obj = CasesData() # 创建一个对象用来保存该行用例数据
   case = []
   for r in row:
    case.append(r.value)
   case_info = dict(zip(titles, case))
   for k, v in case_info.items():
    setattr(case_obj, k, v)
   cases.append(case_obj)
  self.close()
  return cases

 def write_data(self, row, column, msg):
  self.open()
  self.sh.cell(row=row, column=column, value=msg)
  self.wb.save(self.file_name)
  self.close()

if __name__ == '__main__':
 # 直接运行本文件时执行,下面是一个应用实例
 # 需要读取excel时直接调用ReadExcel类
 test = ReadExcel('cases.xlsx', 'login')
 res = test.read_data_obj()	# 最后返回的是一个存储实例对象的列表
 print(res[0].case_id)	# 打印第1个用例的case_id
 print(res[3].title)		# 打印第4个用例的title

 test2 = ReadExcel('cases.xlsx', 'login')
 res2 = test2.read_data()	# 最后返回一个存储字典的列表
 print(res2[0]['case_id'])	# 打印第1个用例的case_id
 print(res2[3]['title'])		# 打印第4个用例的title

​ 上面封装的openpyxl中,应用实例的excel表结构如下:

以上就是python 使用openpyxl读取excel数据的详细内容,更多关于python openpyxl读取excel数据的资料请关注我们其它相关文章!

(0)

相关推荐

  • Python使用OpenPyXL处理Excel表格

    官方文档: http://openpyxl.readthedocs.io/en/default/ OpenPyXL库 --单元格样式设置 单元格样式的控制,依赖openpyxl.style包,其中定义有样式需要的对象,引入样式相关: from openpyxl.styles import PatternFill, Font, Alignment, Border, SideBorder 边框 Side 边线PatternFill 填充Font 字体Aignment 对齐 以上基本可满足需要 基本用

  • python使用openpyxl操作excel的方法步骤

    一 前言 知识追寻者又要放大招了,学完这篇openpyxl第三方库,读者将会懂得如何灵活的读取excel数据,如何创建excel工作表:更新工作表,删除工作表:是不是感觉很强大,留下赞赞吧!! 二 openpyxl常用属性函数 常用函数或者属性 说明 openpyxl.load_workbook() 加载excel工作本 Workbook.active 获得默认sheet Workbook.create_sheet() 创建sheet Workbook.get_sheet_names() 已过时

  • python基于openpyxl生成excel文件

    项目需要,需要自动生成PDF测试报告.经过对比之后,选择使用了reportlab模块. 项目背景:开发一个测试平台,供测试维护测试用例,执行测试用例,并且生成测试报告(包含PDF和excel),将生成的测试报告以邮件的形式发送相关人. excel生成代码如下: from openpyxl import load_workbook from openpyxl.styles import Font, Alignment, Side, Border import shutil # 生成测试计划的exc

  • python 利用openpyxl读取Excel表格中指定的行或列教程

    Worksheet 对象的 rows 属性和 columns 属性得到的是一 Generator 对象,不能用中括号取索引. 可先用列表推导式生成包含每一列中所有单元格的元组的列表,在对列表取索引. Worksheet 的 rows 属性亦可用相同的方法处理. 补充:python之表格数据读取 python 操作excel主要用到xlrd,xlwt这两个库,xlrd,是读取excel表,xlwt是写入表格 1.打开表格 table = xlrd.open("path_to_your_excel&

  • python 的 openpyxl模块 读取 Excel文件的方法

    Python 的 openpyxl 模块可以让我们能读取和修改 Excel 文件. 首先让我们先理解一些 Excel 基础概念. 1 Excel 基础概念 Excel 文件也称做为工作簿.每个工作簿可以包含多个工作表(Sheet).用户当前查看的表或关闭 Excel 前最后查看的表,称为活动表. 每一张表都是由列和行构成的.列是以 A 开始的字母表示:而行是以 1 开始的数字表示的.由特定行和列所指定的方格称为单元格.每个单元格都可以包含一个数字或文本.这些单元格就构成了这张表. 2 安装 op

  • Python自动化办公Excel模块openpyxl原理及用法解析

    openpyxl 介绍 openpyxl 是一个直接可用于读写 xlsx .xlsm.xltx.xltm 文件的 Python 内置库,借助它可以利用 Python 语法对本地 xlsx 文件进行自动化批量操作 先说一下安装部分,如果小伙伴们用 Anaconda 作为 Python 环境的话,openpyxl 无需安装可直接使用:需要安装的话方法也非常简单 pip 工具一行命令即可 pip install openpyxl xlsx 文件属性 在对 Excel 表格处理之前,需要了解一下 xls

  • Python3利用openpyxl读写Excel文件的方法实例

    前言 Python中常用的操作Excel的三方包有xlrd,xlwt和openpyxl等,xlrd支持读取.xls和.xlsx格式的Excel文件,只支持读取,不支持写入.xlwt只支持写入.xls格式的文件,不支持读取. openpyxl不支持.xls格式,但是支持.xlsx格式的读取写入,并且支持写入公式等. 原始数据文件apis.xlsx内容: name method url data json result get接口 get https://httpbin.org/get?a=1&b=

  • python3.7 openpyxl 在excel单元格中写入数据实例

    本来我是想尝试,选中某个多个单元格复制到同一个sheet的其他位置,找了很多资料没有找到,目前只有这么一个办法,如果有大佬看到,欢迎补充请教. # encoding:utf-8 import pandas as pd import openpyxl xl = pd.read_excel(r"E:\55\CRM经营分析表-10001741-1570416265044.xls") xl.to_excel(r"E:\55\crms.xlsx") wk = openpyxl

  • python之openpyxl模块的安装和基本用法(excel管理)

    前言 openpyxl模块是一个读写Excel 2010文档的Python库,如果要处理更早格式的Excel文档,需要用到额外的库,openpyxl是一个比较综合的工具,能够同时读取和修改Excel文档.其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能. 要对excel进行操作,必须要有能打开excel的软件,这里以wps为例 一.获取wps的安装包并安装 二.excel文档的基本定义 1) 工作薄(workbook):一个EXCEL文件就称为一个工作薄,一个工作薄中可以包含

  • python 使用openpyxl读取excel数据

    openpyxl介绍 ​ openpyxl是一个开源项目,它是一个用于读取/写入Excel 2010文档(如xlsx .xlsm .xltx .xltm文件 )的Python库,如果要处理更早格式的Excel文档(xls),需要用到其它库(如:xlrd.xlwt等),这是openpyxl比较其他模块的不足之处.openpyxl是一款比较综合的工具,不仅能够同时读取和修改Excel文档,而且可以对Excel文件内单元格进行详细设置,包括单元格样式等内容,甚至还支持图表插入.打印设置等内容. ​ p

  • 配置python连接oracle读取excel数据写入数据库的操作流程

    前提条件:本地已经安装好oracle单实例,能使用plsql developer连接,或者能使用TNS连接串远程连接到oracle集群 读取excel写入数据库的方式有多种,这里介绍的是使用pandas写入,相对来说比较简便,不需要在读取excel后再去整理数据 整个过程需要分两步进行: 一.配置python连接oracle并测试成功 网上有不少教程,但大部分都没那么详细,并且也没有说明连接单实例和连接集群的区别,这里先介绍连接oracle单实例的方式,后续再补充连接oracle集群方式. 版本

  • python读取excel数据绘制简单曲线图的完整步骤记录

    python读写excel文件有很多种方法: 用xlrd和xlwt进行excel读写 用openpyxl进行excel读写 用pandas进行excel读写 本文使用xlrd读取excel文件(xls,sxls格式),使用xlwt向excel写入数据 一.xlrd和xlwt的安装 安装很简单,windos+r调出运行窗口,输入cmd,进入命令行窗口,输入以下命令. 安装xlrd: pip install xlrd 安装xlwt: pip install xlwt xlrd的API(applica

  • python pandas库读取excel/csv中指定行或列数据

    目录 引言 1.根据index查询 2.已知数据在第几行找到想要的数据 3.根据条件查询找到指定行数据 4.找出指定列 5.找出指定的行和指定的列 6.在规定范围内找出符合条件的数据 总结 引言 关键!!!!使用loc函数来查找. 话不多说,直接演示: 有以下名为try.xlsx表: 1.根据index查询 条件:首先导入的数据必须的有index 或者自己添加吧,方法简单,读取excel文件时直接加index_col 代码示例: import pandas as pd #导入pandas库 ex

  • Python读取Excel数据实现批量生成合同

    目录 一.背景 二.准备 三.实战 1.安装相关库 2.读取合同数据 3.批量合同生成 大家好,我是J哥. 在我们的工作中,面临着大量的重复性工作,通过人工方式处理往往耗时耗力易出错.而Python在自动化办公方面具有极大的优势,可以解决我们工作中遇到的很多重复性问题,分分钟搞定办公需求. 一.背景 在我们经济交往中,有时会涉及到销售合同的批量制作.比如我们需要根据如下合同数据(Excel),进行批量生成销售合同(Word). 二.准备 我们首先要准备好一份合同模板(Word),将需要替换的合同

  • Python matplotlib读取excel数据并用for循环画多个子图subplot操作

    读取excel数据需要用到xlrd模块,在命令行运行下面命令进行安装 pip install xlrd 表格内容大致如下,有若干sheet,每个sheet记录了同一所学校的所有学生成绩,分为语文.数学.英语.综合.总分 考号 姓名 班级 学校 语文 数学 英语 综合 总分 ... ... ... ... 136 136 100 57 429 ... ... ... ... 128 106 70 54 358 ... ... ... ... 110.5 62 92 44 308.5 画多张子图需要

  • python读取excel数据并且画图的实现示例

    一,要读取的数据的格式: 二,数据读取部分: b站视频参考:https://www.bilibili.com/video/BV14C4y1W7Nj?t=148 # 1930 workbook=xlrd.open_workbook('1930.xlsx') sheet= workbook.sheet_by_index(0) A1=[] B1=[] # sheet.cell_value(i,0):第i行的第0个元素 for i in range(1,sheet.nrows): A1.append(s

  • Python自动化办公之读取Excel数据的实现

    目录 前言 Excel 读取 - xlrd 常用函数介绍 获取 excel 对象 获取工作簿 读取工作簿内容 前言 之前的章节我们学习了 python 关于 word 文件相关操作的知识点,从今天开始讲学习关于 excel 的相关操作,来看一下关于即将学习的 excel 相关知识点都有哪些? 如何读取 excel 文件 如何生成 excel 文件 如何在 excel 中生成基础的图表 目标:实现对 excel 的最基础的读写内容 该篇章所使用的新的模块 xlrd —> excel 的读取模块 x

  • Python读取Excel数据实现批量生成PPT

    目录 背景 需求 准备 PPT数据 PPT模板 实战 导入相关模块 读取电影数据 读取PPT模板插入数据 背景 大家好,我是J哥. 我们常常面临着大量的重复性工作,通过人工方式处理往往耗时耗力易出错.而Python在办公自动化方面具有天然优势,分分钟解决你的办公需求,提前下班不是梦. 需求 前几天我发表了一篇办公自动化文章Python读取Excel数据并批量生成合同,获得许多小伙伴的认可和喜欢.其中有一位粉丝提议,能否出一篇PPT自动化的教程,通过读取Excel数据批量生成幻灯片.于是,我以豆瓣

随机推荐