2. openpyxl

openpyxl模块介绍¶

openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，
openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。

Excel数据的类型及组织方式¶

openpyxl中定义了多种数据格式，我只涉及到了其中最重要的三种： - NULL：空值，对应于python中的None，表示这个cell里面没有数据。 - numberic：数字型，统一按照浮点数来进行处理。对应于python中的float。 - string：字符串型，对应于python中的unicode。

每一个Excel数据文件从上至下分为三个层级的对象： - workbook：每一个Excel文件就是一个workbook。 - sheet：每一个workbook中可以包含多个sheet，具体就对应Excel中我们在左下脚所看到的“sheet1”,“sheet2”等。 - cell：每一个sheet就是我们通常所看到的一个表格，可以含有m行，n列，每个确定的行号，列号所对应的一个格子就是一个cell。

安装openpyxl模块¶

pip3 install openpyxl

openpyxl基本用法¶

想要操作Excel首先要了解Excel基本概念，Excel中列以字幕命名，行以数字命名，比如左上角第一个单元格的坐标为A1，下面的为A2，右边的B1。
openpyxl中有三个不同层次的类，Workbook是对工作簿的抽象，Worksheet是对表格的抽象，
Cell是对单元格的抽象，每一个类都包含了许多属性和方法。

操作Excel的一般场景:¶

打开或者创建一个Excel需要创建一个Workbook对象
获取一个表则需要先创建一个Workbook对象，然后使用该对象的方法来得到一个Worksheet对象
如果要获取表中的数据，那么得到Worksheet对象以后再从中获取代表单元格的Cell对象
Workbook对象一个Workbook对象代表一个Excel文档，因此在操作Excel之前，都应该先创建一个Workbook对象。
对于创建一个新的Excel文档，直接进行Workbook类的调用即可，对于一个已经存在的Excel文档，
可以使用openpyxl模块的load_workbook函数进行读取，该函数包涵多个参数，但只有filename参数为必传参数。filename 是一个文件名，也可以是一个打开的文件对象。

Workbook对象¶

一个Workbook对象代表一个Excel文档，因此在操作Excel之前，都应该先创建一个Workbook对象。
对于创建一个新的Excel文档，直接进行Workbook类的调用即可，对于一个已经存在的Excel文档，
可以使用openpyxl模块的load_workbook函数进行读取，该函数包涵多个参数，但只有filename参数为必传参数。
filename 是一个文件名，也可以是一个打开的文件对象。

PS：Workbook和load_workbook相同，返回的都是一个Workbook对象

Workbook对象提供了很多属性和方法，其中，大部分方法都与sheet有关，部分属性如下：

active：    获取当前活跃的Worksheet
worksheets：以列表的形式返回所有的Worksheet(表格)
read_only： 判断是否以read_only模式打开Excel文档
encoding：  获取文档的字符集编码
properties：获取文档的元数据，如标题，创建者，创建日期等
sheetnames：获取工作簿中的表（列表）

- 例子

import openpyxl

excel2 = openpyxl.load_workbook("example.xlsx")
excel2.active
print("excel2 = ", excel2)
print("excel2.read_only = ",excel2.read_only)
print("excel2.worksheets = ",excel2.worksheets)
print("excel2.properties = ",excel2.properties)
print("excel2.encoding = ",excel2.encoding)

运行结果

excel2 =  <openpyxl.workbook.workbook.Workbook object at 0x000001B072D2D7F0>
excel2.read_only =  False
excel2.worksheets =  [<Worksheet "Sheet1">, <Worksheet "Sheet2">, <Worksheet "Sheet3">]
excel2.properties =  <openpyxl.packaging.core.DocumentProperties object>
Parameters:
creator='cmz', title=None, description=None, subject=None, identifier=None, language=None, created=datetime.datetime(2019, 1, 23, 7, 26), modified=datetime.datetime(2019, 1, 23, 7, 26, 46), lastModifiedBy='爱一直在线1409537494', category=None, contentStatus=None, version=None, revision=None, keywords=None, lastPrinted=None
excel2.encoding =  utf-8

Workbook提供的方法如下：

get_sheet_names：获取所有表格的名称(新版已经不建议使用，通过Workbook的sheetnames属性即可获取)
get_sheet_by_name：通过表格名称获取Worksheet对象(新版也不建议使用，通过Worksheet[‘表名‘]获取)
get_active_sheet：获取活跃的表格(新版建议通过active属性获取)
remove_sheet：删除一个表格
create_sheet：创建一个空的表格
copy_worksheet：在Workbook内拷贝表格

Worksheet对象

有了Worksheet对象以后，我们可以通过这个Worksheet对象获取表格的属性，得到单元格中的数据，修改表格中的内容。openpyxl提供了非常灵活的方式来访问表格中的单元格和数据，常用的Worksheet属性如下：

title          ：表格的标题
dimensions     ：表格的大小，这里的大小是指含有数据的表格的大小，即：左上角的坐标:右下角的坐标
max_row        ：表格的最大行
min_row        ：表格的最小行
max_column     ：表格的最大列
min_column     ：表格的最小列
rows           ：按行获取单元格(Cell对象) - 生成器
columns        ：按列获取单元格(Cell对象) - 生成器
freeze_panes   ：冻结窗格
values         ：按行获取表格的内容(数据)  - 生成器

Cell对象 Cell对象比较简单，常用的属性如下:

row       ：单元格所在的行
column    ：单元格坐在的列
value     ：单元格的值
coordinate：单元格的坐标

print(excel2['Sheet1'].cell(row=1,column=2).row)
print(excel2['Sheet1'].cell(row=1,column=2).column)
print(excel2['Sheet1'].cell(row=1,column=2).value)
print(excel2['Sheet1'].cell(row=1,column=2).coordinate)

结果

1
B
年龄
B1

1. 读取Excel文档¶

通过调用方法load_workbook(filename)进行文件读取，该方法中还有一个read_only参数用于设置文件打开方式，
默认为可读可写，该方法最终将返回一个workbook的数据对象

# 文件必须是xlsx格式，如果是其他格式在执行前可利用win32辅助转化
wb = openpyxl.load_workbook('example.xlsx')

1.1 获取工作表¶

每一个Excel表格中都会有很多张sheet工作表，在对表格操作前需要先选定一张工作表

# 获取所有工作表名(返回一个列表)
sheets = wb.get_sheet_names()

# 获取某一特定的工作表
sheet = wb.get_sheet_by_name('Sheet2') # 过时写法
sheet = wb['Sheet1']                   # 现在写法

# 获取工作表的表名
sheet_name = sheet.title

# 一般来说，表格大多数用到的是打开时显示的工作表，这时可以用active来获取当前工作表
sheet = wb.active

1.2 获取单元格¶

对Excel表格的操作最终都落于对单元格的操作，获取单元格有两种获取方法：sheet[列行名]和sheet.cell(row,column)

# 通过sheet[列行名]获取
a = sheet['A2']

# 通过sheet.cell(row,column)获取
b = sheet.cell(1, 2)  # 即sheet['B1']

# 获取单元格内容
print(a.value)

# 获取单元格所在列和行
print('a is '+str((a.column,a.row)))

需要注意的是，sheet.cell(row,column)中参数分别是行和列，且必须为整数，如果列为英文字母，可以利用 openpyxl.utils 中的 column_index_from_string(char)进行字母数字的转化。顺便一说，同理也可以利用get_column_letter(number)进行数字字母间的转化

from openpyxl.utils import get_column_letter, column_index_from_string

# 对列进行字母/数字转化
c_num = column_index_from_string('B')  # c_num = 2
c_char = get_column_letter(5)          # c_char = 'E‘

1.3 获取行和列¶

在处理Excel表格有时可能需要对表格进行遍历查找，openpyxl中便提供了一个行和列的生成器(sheet.rows和sheet.columns),这两个生成器里面是每一行（或列）的数据，每一行（或列）又由一个tuple包裹，借此可以很方便地完成对行和列的遍历

# 对行进行遍历,输出A1,B1,C1
for row in sheet.rows:
    for cell in row:
        print(cell.value)

# 对列进行遍历,输出A1,A2,A3
for column in sheet.columns:
    for cell in column:
        print(cell.value)

tip

学习时还发现也可以通过list(sheet.rows)[index](或list(sheet.columns)[index])对某一行或列进行遍历，而在此值得注意的是，由于sheet.rows(或sheet.columns)是生成器类型，是不能直接调用的，需将其转化为一个list类型，然后再通过索引遍历

# 对某一特定的行进行遍历
for cell in list(sheet.rows)[0]:  # 第0行
    print(cell.value)

# 对某一特定的列进行遍历
for cell in list(sheet.columns)[0]:  # 第0列
    print(cell.value)

同时，也可以通过使用sheet[行列值:行列值]来对给定单元格范围进行遍历

# 对某一单元格范围进行遍历
for spaces in sheet['A1':'B2']:
    for cell in spaces:
        print(cell.value)

另外，有时候我们还可能需要确定表格的大小，即获取表格行和列的最大值，可以用max_row和max_column来获取

# 获得最大列和最大行
print(sheet.max_row)
print(sheet.max_column)

2. 档写入Excel文档¶

在开头读取时已经介绍，默认的打开方式为可读可写，那么使用load_workbook(filename)读取Excel文档后也就可以直接写入了。另外，如果需要新建一个Excel文件，可以使用Workbook()方法，同时它会自动提供一个sheet工作表。对于删除一个工作表，则可以使用workbook对象的remove(sheet)方法删除

# 新建一个Excel文档
wb = openpyxl.Workbook()

# 删除某个工作表 
wb.remove(sheet)

2.1 写入单元格¶

获取工作表和之前一样，如果使用load_workbook(filename)读取，那么获取工作表后可以直接通过sheet[行列值]写入单元格。学习时，有资料介绍还可以传入Excel中的公式进行赋值，不过要注意，在读取文件时需要加上参数data_only=True，这样才能返回数字，否则将返回字符串，即公式本身

# 直接赋值
sheet['A1'].value = 2

# 公式赋值
sheet['A6'].value = '=SUM(A1:A5)'

另外，也可使用sheet.append(parameters)一行或多行写入

# 写入一行
row = [1 ,2, 3, 4, 5]
sheet.append(row)

# 写入多行
rows = [
    ['ID', 'Name', 'Department'],
    ['001', 'Lee','CS'],
    ['002', 'John','MA'],
    ['003', 'Amy','IS']
]
sheet.append(rows)

2.2 操作文件¶

写完文件后，使用workbook.save(path+filename)进行保存，不过要注意文件扩展名一定要是xlsx格式

# 保存文件至当前目录
wb.save('new_file.xlsx')

3. 设置单元格样式¶

单元格样式主要包括字体、边框、颜色以及对齐方式等，这些均位于openpyxl.styles库中

# 导入字体、边框、颜色以及对齐方式相关库
from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment

3.1 字体¶

通过sheet单元格font属性设置字体风格

# 设置字体风格为Times New Roman，大小为16，粗体、斜体，颜色蓝色
sheet['A1'].font = Font(name='Times New Roman', size=16, bold=True, italic=True, color=colors.BLUE)

3.2 对齐方式¶

通过sheet单元格alignment属性设置文本对齐风格

# 通过参数horizontal和vertical来设置文字在单元格里的对齐方式，此外设置值还可为left和right
sheet['B1'].alignment = Alignment(horizontal='center',vertical='center')

3.3 边框¶

通过sheet单元格border属性设置字体风格

# 首先设置边框四个方向的线条种类
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
# 再将各方向线条作为参数传入Border方法
sheet['C1'].border = Border(left=left, right=right, top=top, bottom=bottom)

3.4 设置行高和列宽¶

行和列的长度大小可以通过row_dimensions[序号].height和column_dimensions[标号].width来设置

# 设置行高
sheet.row_dimensions[1].height = 25

# 设置列宽
sheet.column_dimensions['D'].width = 15.5

3.5 合并和拆分单元格¶

对单元格的合并与拆分，主要是通过sheet的merge_cells(args1:args2)和unmerge_cells(args1:args2)两个方法来实现的

# 合并单元格
sheet.merge_cells('A1:B2')

# 拆分单元格
sheet.unmerge_cells('A1:B2')

当然，除了对角矩形区域化合并，也可以对一行或一列进行合并，只需相应修改参数即可。不过，这里要注意的是，合并后单元格显示的文本内容是合并前最左上角单元格的内容，而其他单元格内容则会自动清除。

多关于openpyxl的介绍，可以参阅readthedocs相关文档