Python 的 xlsxwriter、xlwings、openpyxl、pandas 等库都可以用于操作 Excel,如表1所示。
表1:Python 中用于操作 Excel 的常用库
库 |
描述 |
xlwings |
非常方便地读写 Excel 文件中的数据和修改单元格格式。 |
xlsxwriter |
用于写 xlsx 格式的文件的库。它可以用来写文本、数字和公式,支持单元格格式化、文档配置、自动过滤等特性,但不能用于读取和修改 Excel 文件。 |
openpyxl |
通过工作簿“Workbook-工作表Sheet-单元格Cell”的模式对 xlsx 格式的文件进行读、写和改,还可以调整样式。 |
pandas |
用于数据处理和分析的强大的库,有时也可以用于自动化处理 Excel 文件。 |
openpyxl 是一款比较综合的工具,它不仅能够同时读取和修改 Excel 文件,而且可以详细设置 Excel 文件内的单元格,包括单元格样式等内容。它还支持图表插入、打印设置等内容。
使用 openpyxl 可以读写 xltm、xltx、xlsm、xlsx 等类型的文件,且可以处理数据量较大的 Excel 文件,它的跨平台处理大量数据的能力是其他库没法相比的。
总之,openpyxl 成为处理 Excel 复杂问题的首选库。
openpyxl 是一个非标准库,需要自行安装,它的安装过程并不复杂,Windows 或 macOS 用户均可以在命令行或终端中使用 pip 安装 openpyxl,命令为:
pip install openpyxl
openpyxl 的基本概念
openpyxl 中主要用到 3 个概念是 Workbook、Sheet 和 Cell。
-
Workbook 是一个 Excel 工作簿(Excel 文件);
-
Sheet 是工作簿中的一张表;
-
Cell 是一个简单的单元格。
openpyxl 就是围绕着这 3 个概念进行操作的,不论读写,操作步骤都是打开 Workbook,定位 Sheet,操作 Cell。
openpyxl 中有 3 个不同层次的类,Workbook 是对工作簿的抽象,Worksheet 是对工作表的抽象,Cell 是对单元格的抽象。这 3 个类中每一个类都包含很多属性和方法。
1) Workbook对象
一个 Workbook 对象表示一个 Excel 文件,在操作 Excel 之前,我们应该先创建一个 Workbook 对象。
-
如果需要创建一个新的 Excel 文档,直接调用 Workbook 类即可;
-
如果需要处理一个已经存在的 Excel 文件,可以使用 openpyxl 的 load_workbook 函数进行读操作。
Workbook 类和 load_workbook 函数相同,返回的都是一个 Workbook 对象。
Workbook 类有很多属性和方法,大部分方法都与表有关,如表2和表3所示。
表2:Workbook 类的属性
属性 |
描述 |
active |
获取当前活跃的 Worksheet。 |
worksheets |
以列表的形式返回所有的 Worksheet。 |
read_only |
判断是否以 read_only(只读)模式打开 Excel 文件。 |
encoding |
获取文档的字符集编码。 |
properties |
获取文档的元数据,如标题、创建者、创建日期等。 |
sheetnames |
获取工作簿中的表(列表)。 |
表3:Workbook 类的方法
方法 |
描述 |
get_sheet_names |
获取所有表的名称(不建议使用新版的 openpyxl,可通过 Workbook 的 sheetnames 属性获取)。 |
get_sheet_by_name |
通过表名称获取 Worksheet 对象(不建议使用新版的 openpyxl,可通过Worksheet ['表名'] 获取)。 |
get_active_sheet |
获取活跃的表(建议通过 active 属性获取新版的 openpyxl)。 |
remove_sheet |
删除一个表。 |
create_sheet |
创建一个空表。 |
copy_worksheet |
在 Workbook 内复制表。 |
2) Worksheet 对象
我们可以通过 Worksheet 对象获取表的属性,得到单元格中的数据,修改表中的内容。
openpyxl 提供了非常灵活的方式来访问表中的单元格和数据,常用的 Worksheet 类的属性和方法如表4和表5所示。其中,行以数字 1 开始,列以字母 A 开始。
表4:Worksheet 类的属性
属性 |
描述 |
title |
表的标题。 |
dimensions |
表的大小,这里的大小是指含有数据的表的大小,其值为“左上角的坐标:右下角的坐标”。 |
max_row |
表的最大行。 |
min_row |
表的最小行。 |
max_column |
表的最大列。 |
min_column |
表的最小列。 |
rows |
按行获取单元格(Cell 对象)生成器。 |
columns |
按列获取单元格(Cell 对象)生成器。 |
freeze_panes |
冻结窗格。 |
values |
按行获取表的内容(数据)生成器。 |
表5:Worksheet 类的方法
方法 |
描述 |
iter_rows |
按行获取所有单元格,内置属性有 min_row、max_row、min_col 和 max_col。 |
iter_columns |
按列获取所有的单元格。 |
append |
在表末尾添加数据。 |
merged_cells |
合并多个单元格。 |
unmerged_cells |
移除合并的单元格。 |
3) Cell 对象
Cell 对象比较简单,常用的属性如表6所示。Cell 对象只存储两种数据类型——数字和字符串,除了纯数字,其他均为字符串类型。
表6:Cell 常用属性
属性 |
描述 |
row |
单元格所在的行。 |
column |
单元格所在的列。 |
value |
单元格的值。 |
coordinate |
单元格的坐标。 |
如前文所述,一个 Excel 文件 Workbook 由一个或者多个工作表 Worksheet 组成,一个 Worksheet 可以看作由多个行 row 组成,也可以看作由多个列 column 组成,而每一行每一列都由多个单元格 Cell 组成。
下面简要讲解一下如何读取和写入 Excel。
4) 读取 Excel
读取 Excel 的方式有如下4种。
① 载入 Excel:
from openpyxl import load_workbook
workbook = load_workbook(filename='测试.xlsx')
print(workbook.sheetnames)
注意,load_workbook 只能打开已经存在的 Excel,不能创建新的 Excel。
② 根据名称获取工作表:
from openpyxl import load_workbook
workbook = load_workbook(filename='其他.xlsx')
print(workbook.sheetnames)
sheet = workbook['工作业务']
③ 获取多个格子的值。
Excel 中每一列由字母确定,是字符型;每一行由一个数字确定,是整型。如果我们要输出每一个格子的值,那么需要遍历:
for cell in cells:
print(cell.value)
④ 读取所有的行:
for row in sheet.rows:
print(row)
5) Excel 写入
Excel 写入的方式有如下两种。
① 保存 Excel:
workbook.save(filename='Excel工作表1.xlsx')
如果读取和写入 Excel 的路径相同则对原文件进行修改,如果读取和写入 Excel 的路径不同则保存成新的文件。
② 写入单元格:
cell = sheet['A1']
cell.value = '业务需求'
Excel 样式调整
openpyxl 处理 Excel 文件中的单元格样式,总共有 6 个属性类,分别是:
-
Font(字体类,可设置字号、字体颜色、下画线等);
-
PatternFill(填充类,可设置单元格填充颜色等);
-
Border(边框类,可以设置单元格各种类型的边框);
-
Alignment(位置类,可以设置单元格内数据各种对齐方式)。
例如,通过语句 From openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font 导入相应的库。
示例
openpyxl 是读写 Excel 2010 的 xlsx、xlsm、xltx、xltm 格式文件的 Python 库,简单易用,功能广泛,单元格格式调整、图表处理、公式处理、筛选、批注、文件保护等功能应有尽有,图表处理功能是其一大亮点。
openpyxl 几乎可以实现所有的 Excel 功能,而且接口清晰,文档丰富,学习成本相对较低。
封装一个可以读取任意 Excel 文件的方法,就可以指定读取的表单,当我们多次从 Excel 文件中读取数据时,不用重复地写代码,只需调用封装的类即可,如代码清单1所示。
代码清单1:excelUtil:
# -*- coding: utf-8 -*-
# @Time : 2022/7/12 10:29 上午
# @Project : excelDemo
# @File : excelUtil.py
# @Version: Python3.9.8
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
class ExcelHandler():
'''
操作Excel
'''
def __init__(self, file):
'''初始化函数'''
self.file = file
def open_sheet(self, sheet_name) -> Worksheet:
'''打开表单'''
wb = load_workbook(self.file)
sheet = wb[sheet_name]
return sheet
def read_header(self, sheet_name):
'''获取表单的表头'''
sheet = self.open_sheet(sheet_name)
headers = []
for i in sheet[1]:
headers.append(i.value)
return headers
def read_rows(self,sheet_name):
'''
读取除表头外所有数据(除第一行外的所有数据)
返回的内容是一个二维列表,若想获取每一行的数据,可使用for循环或使用*解包
'''
sheet = self.open_sheet(sheet_name)
rows = list(sheet.rows)[1:]
data = []
for row in rows:
row_data = []
for cell in row:
row_data.append(cell.value)
data.append(row_data)
return data
def read_key_value(self,sheet_name):
'''
获取所有数据,且将表头中的内容与数据结合展示(以字典的形式)
如:[
{'序号':1,'会员卡号': '680021685898','机场名称':'上海机场'},
{'序号':2,'会员卡号': '680021685899','机场名称':'广州机场'}
]
'''
sheet = self.open_sheet(sheet_name)
rows = list(sheet.rows)
# 获取标题
data = []
for row in rows[1:]:
row_data = []
for cell in row:
row_data.append(cell.value)
# 列表转换成字典,与表头内容一起使用zip函数进行打包
data_dict = dict(zip(self.read_header(sheet_name),row_data))
data.append(data_dict)
return data
@staticmethod
def write_change(file,sheet_name,row,column,data):
'''写入Excel数据'''
wb = load_workbook(file)
sheet = wb[sheet_name]
# 修改单元格
sheet.cell(row,column).value = data
# 保存
wb.save(file)
# 关闭
wb.close()
写入 Excel 使用了静态方法,原因是读取文件无须保存。如果修改文件后没有保存,其他地方又调用了该方法,则会引起报错,所以每次修改 Excel 文件,都要进行保存。
本文链接:http://task.lmcjl.com/news/16573.html