4.6 Excel处理
Python的xlsxwriter、xlwings、openpyxl、pandas等库都可以用于操作Excel，如表4-14所示。
表4-14 Python中用于操作Excel的常用库
openpyxl是一款比较综合的工具，它不仅能够同时读取和修改Excel文件，而且可以详细设置Excel文件内的单元格，包括单元格样式等内容。它还支持图表插入、打印设置等内容。使用openpyxl可以读写xltm、xltx、xlsm、xlsx等类型的文件，且可以处理数据量较大的Excel文件，它的跨平台处理大量数据的能力是其他库没法相比的。因此，openpyxl成为处理Excel复杂问题的首选库。
openpyxl是一个非标准库，需要自行安装，它的安装过程并不复杂，Windows或macOS用户均可以在命令行或终端中使用pip安装openpyxl，命令为pip install openpyxl。
4.6.1 基本概念
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类有很多属性和方法，大部分方法都与表有关，如表4-15和表4-16所示。
表4-15 Workbook类的属性
表4-16 Workbook类的方法
2．Worksheet对象
我们可以通过Worksheet对象获取表的属性，得到单元格中的数据，修改表中的内容。openpyxl提供了非常灵活的方式来访问表中的单元格和数据，常用的Worksheet类的属性和方法如表4-17和表4-18所示。其中，行以数字1开始，列以字母A开始。
表4-17 Worksheet类的属性
表4-18 Worksheet类的方法
3．Cell对象
Cell对象比较简单，常用的属性如表4-19所示。Cell对象只存储两种数据类型——数字和字符串，除了纯数字，其他均为字符串类型。
表4-19 Cell常用属性
如前文所述，一个Excel文件Workbook由一个或者多个工作表Worksheet组成，一个Worksheet可以看作由多个行row组成，也可以看作由多个列column组成，而每一行每一列都由多个单元格Cell组成。下面简要讲解一下如何读取和写入Excel。
4．读取Excel
读取Excel的方式有如下4种。
（1）载入Excel：
from openpyxl import load_workbook
workbook = load_workbook(filename='测试.xlsx')
print(workbook.sheetnames)
注意，load_workbook只能打开已经存在的Excel，不能创建新的Excel。
（2）根据名称获取工作表：
from openpyxl import load_workbook
workbook = load_workbook(filename='其他.xlsx')
print(workbook.sheetnames)
sheet = workbook['工作业务']
（3）获取多个格子的值。Excel中每一列由字母确定，是字符型；每一行由一个数字确定，是整型。如果我们要输出每一个格子的值，那么需要遍历：
for cell in cells:
print(cell.value)
（4）读取所有的行：
for row in sheet.rows:
print(row)
5．Excel写入
Excel写入的方式有如下两种。
（1）保存Excel：
workbook.save(filename='Excel工作表1.xlsx')
如果读取和写入Excel的路径相同则对原文件进行修改，如果读取和写入Excel的路径不同则保存成新的文件。
（2）写入单元格：
cell = sheet['A1']
cell.value = '业务需求'
Excel样式调整
openpyxl处理Excel文件中的单元格样式，总共有6个属性类。分别是Font（字体类，可设置字号、字体颜色、下画线等）、PatternFill（填充类，可设置单元格填充颜色等）、Border（边框类，可以设置单元格各种类型的边框）、Alignment（位置类，可以设置单元格内数据各种对齐方式）。例如，通过语句From openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font导入相应的库。
4.6.2 封装示例
openpyxl是读写Excel 2010的xlsx、xlsm、xltx、xltm格式文件的Python库，简单易用，功能广泛，单元格格式调整、图表处理、公式处理、筛选、批注、文件保护等功能应有尽有，图表处理功能是其一大亮点。
openpyxl几乎可以实现所有的Excel功能，而且接口清晰，文档丰富，学习成本相对较低。封装一个可以读取任意Excel文件的方法，就可以指定读取的表单，当我们多次从Excel文件中读取数据时，不用重复地写代码，只需调用封装的类即可，如代码清单4-9所示。
# -*- coding: utf-8 -*-
# @Time : 2022/2/21 10:29 上午
# @Project : excelDemo
# @File : excelUtil.py
# @Author : hutong
# @Describe: 微信公众号：大话性能
# @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()
代码清单4-9 excelUtil
写入Excel使用了静态方法，原因是读取文件无须保存。如果修改文件后没有保存，其他地方又调用了该方法，则会引起报错，所以每次修改Excel文件，都要进行保存。
版权声明：51Testing软件测试网获得作者授权连载本书部分章节。
任何个人或单位未获得明确的书面许可，不得对本文内容复制、转载或进行镜像，否则将追究法律责
