在这一小节，我们将会学习如何使用Python来操作Excel文档以及如何利用Python语言的函数和表达式操纵Excel文档中的数据。虽然微软公司本身提供了一些函数，我们可以使用这些函数操作Excel文档。但是，使用Excel自带的函数受限于Excel软件的功能限制。换句话说，只有微软提供了某种功能，我们才能使用相应的功能解决问题。如果微软没有提供相应的函数应对一个复杂的功能，那么，我们只能进行重复性操作。使用Python语言操作Excel则不然，我们可以灵活应用Python语言的所有功能，读取、计算和编辑Excel文档中的数据。

除了使用Python语言操作Excel文档以外，读者还可以使用VBA操作Excel文档。VBA（Visual Basic for Applications）是Visual Basic的一种宏语言，是微软开发出来在其桌面应用程序中执行自动化任务的编程语言，主要用来扩展Windows应用程序（特别是Microsoft Office软件）的功能。灵活应用VBA这门宏语言能够在处理文档时显著提高工作效率，但是，VBA代码可读性差、应用领域有限。而Python语言拥有文档丰富、语法清晰、易于学习、跨平台等诸多优点。因此，笔者强烈建议使用Python语言来处理Excel文档，不要浪费时间学习VBA。

7.1.1 openpyxl简介与安装

根据官方文档的介绍，openpyxl是一个读写Excel 2010（xlsx/xlsm）文档的Python库，如果要处理更早格式的Excel文档，需要用到另外的库。openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。XlsxWriter也是一个与Excel处理相关的知名项目，仅支持创建和写入Excel文档，不支持读取Excel文档。

openpyxl是一个开源项目，因此，在使用之前需要先进行安装：

pip install openpyxl

7.1.2 使用openpyxl读取Excel文档

在使用openpyxl操作Excel文档之前，我们简单回顾一下Excel文档，帮助我们理解openpyxl对Excel文档的抽象。一个Excel文档称为一个工作簿，在Office 2010下，典型工作簿的文件扩展名为xlsx。一个工作簿可以包含多个表格（在Excel又称为sheet）。打开工作簿后会默认显示一个表格，这个表格一般称为活跃表。表格中包含若干单元格，所有单元格都有一个唯一的坐标。Excel通过行和列表示一个单元格，其中，行的坐标使用数字表示，列的坐标使用字母表示。例如，表格中左上角的单元格，其坐标为“A1”，该单元格下方的单元格坐标为“A2”，右边的单元格坐标为“B1”。

理解了Excel的构成以后，再来看openpyxl对Excel的抽象就会觉得很好理解。openxpyxl中有三个不同层次的类，分别是Workbook、Worksheet和Cell。Workbook是对Excel工作簿的抽象，Worksheet是对表格的抽象，Cell是对单元格的抽象。每一个类都包含了若干属性和方法，以便于我们通过这些属性和方法获取表格中的数据。

例如，我们要打开一个Excel表格或者创建一个Excel文档，都需要创建一个Workbook对象。我们需要获取Excel文档中的某一张表，应该先创建一个Workbook对象，然后使用该对象的方法来得到一个Worksheet对象。如果要读取或者修改某个单元格，我们需要先获得Worksheet对象，然后再获取代表单元格的Cell对象。

在接下来的例子中，我们将使用下面的Excel文档（见图7-1）进行实验。读者可以在本书的附件中找到该文档，文档的名称为example.xlsx。

openpyxl模块使用到的Excel文档

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

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')

调用完load_workbook函数以后，我们就得到了一个Workbook对象。Workbook对象有很多的属性和方法，其中，大部分方法都与sheet相关。Workbook对象的部分属性如下：

❑active：获取活跃的Worksheet；
❑read_only：是否以read_only模式打开Excel文档；
❑encoding：文档的字符集编码；
❑properties：文档的元数据，如标题，创建者，创建日期等；
❑worksheets：以列表的形式返回所有的Worksheet。

下面是我们附件中example.xlsx文件的属性，包括活跃的Worksheet、只读属性和字符集编码。如下所示：

>>>wb.active
<Worksheet "student">

>>>wb.read_only
False

>>>wb.encoding
'utf-8'

>>>wb.worksheets
[<Worksheet "student">, <Worksheet "teacher">]

Workbook对象的方法大都与Worksheet相关。常用的方法如下：

❑get_sheet_names：获取所有表格的名称；
❑get_sheet_by_name：通过表格名称获取Worksheet对象；
❑get_active_sheet：获取活跃的表格；
❑remove_sheet：删除一个表格；
❑create_sheet：创建一个空的表格；
❑copy_worksheet：在Workbook内拷贝表格。

附件的example.xlsx文件中包含了两个表格。其中，名为teacher的表格为活跃表格。如下所示：

>>>wb.get_sheet_names()
[u'student', u'teacher']
# 上面函数新版本以后不再使用，将使用如下wb.sheetnames 获取sheetnames
>>>wb.sheetnames
[u'student', u'teacher']

>>>wb.get_active_sheet() # 建议使用新版wb.active
<Worksheet "teacher">

>>> wb.get_sheet_by_name(u'student') # 建议使用新版 wb['student']
<Worksheet "student">

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

❑title：表格的标题；
❑dimensions表格的大小，这里的大小是指有含有数据的表格大小。例如，对于example.xlsx文件，dimensions属性的值为'A1:E11'；
❑max_row表格的最大行；
❑min_row表格的最小行；
❑max_column表格的最大列；
❑min_column表格的最小列；
❑rows按行获取单元格（Cell对象）；
❑columns按列获取单元格（Cell对象）；
❑freeze_panes冻结窗格；
❑values按行获取表格的内容（数据）。

对于附件中的example.xlsx文件，其拥有的属性如下所示：

>>>ws = wb.get_sheet_by_name('student')
>>>ws.title
u'student'

>>>ws.dimensions
'A1:E11'

>>>ws.max_column
5

>>>ws.min_column
1

>>>ws.max_row
11

>>>ws.min_row
1

>>>ws.columns
<generator object _cells_by_col at 0x7fe5a4d89640>

>>>ws.rows
<generator object _cells_by_row at 0x7fe5a5d32550>

>>>ws.values
<generator object values at 0x7fe5ac289780>

在这段代码中，我们首先通过Workbook的get_sheet_by_name方法获取Worksheet对象。接着，通过不同的属性名获取student这张表的属性。其中，columns、rows和values这几个属性都是通过生成器（生成器以后介绍）的方式返回数据。openpyxl并不知道我们的表格中有多少数据，在数据量大的情况下，如果一次获取所有数据，势必会占用较多的内存。因此，openpyxl的设计中，需要返回数据时都是通过生成器的方式返回。对于附件中的student表，因为记录较少，我们可以使用list函数或tuple函数获取所有的数值。需要注意的是，columns与rows返回的是Cell对象，values返回的是数据。

freeze_panes这个参数比较特别，主要用于在表格较大时冻结顶部的行或左边的列。对于冻结的行或列，就算用户滚动电子表格，也是始终可见的。每个Worksheet对象都有一个freeze_panes属性，可以设置为一个Cell对象或一个单元格坐标的字符串，单元格上面的行和左边的列将会冻结（注意单元格所在的行和列并不会冻结）。例如，我们需要冻结第一行，那么freeze_panes取值应该为A2，如果要冻结第一列，freeze_panes取值为B1。如果要同时冻结第一行和第一列，则freeze_panes取值为B2。freeze_panes取值为None表示不冻结任何窗格。

下面是Worksheet常用的一些方法：

❑iter_rows：按行获取所有单元格（Cell对象）；
❑iter_cols：按列获取所有的单元格；
❑append：在表格末尾添加数据；
❑merged_cells：合并多个单元格；
❑unmerge_cells：移除合并的单元格。

iter_rows方法和iter_cols方法在参数取默认值时，与rows属性和columns属性的作用相同。区别在于，iter_rows方法和iter_cols方法可以通过函数参数限定访问表格的范围。如下所示：

>>>list(ws.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3))
[(<Cell u'student'.A2>, <Cell u'student'.B2>, <Cell u'student'.C2>),
(<Cell u'student'.A3>, <Cell u'student'.B3>, <Cell u'student'.C3>),
(<Cell u'student'.A4>, <Cell u'student'.B4>, <Cell u'student'.C4>)]

从Worksheet的属性和方法的使用中可以看到，很多属性和方法返回的不是某一个具体的数值，而是一个Cell对象。一个Cell对象就代表一个单元格，我们可以直接使用Excel坐标的方式获取Cell对象，也可以使用Worksheet的cell方法获取Cell对象。如下所示：

>>> ws['A1']
<Cell u'student'.A1>

>>>ws['A2']
<Cell u'student'.A2>

>>>ws.cell(row=1, column=2)
<Cell u'student'.B1>

>>>ws.cell(row=2, column=1)
<Cell u'student'.A2>

# 也可以只读一列数据:
>>>ws['B']
# 只读一行数据:
>>>ws[83]

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

❑row：单元格所在的行；
❑column：单元格所在的列；
❑value：单元格的取值；
❑cordinate：单元格的坐标。

为了熟悉openpyxl提供的各种API，接下来我们使用4种不同的方法来打印student表中的内容。为了对数据的格式进行控制，我们使用print函数而不是print语句进行打印。

下面是通过Worksheet的values方法打印表格中的数据，这也是打印数据最简单的方法。values通过生成器访问数据并按行返回，因此，我们使用for循环遍历表格的内容。

>>>from __future__ import print_function

>>>for row in ws.values:
...:      print(＊row)

image.png

我们也可以使用Worksheet的rows属性来遍历表格中的数据。rows属性按行返回Cell对象，因此，我们使用列表推导来获取每一个Cell对象的值。如下所示：

>>>for row in ws.rows:

...:      print(＊[cell.value for cell in row])

image.png

Worksheet的iter_rows方法在不加任何参数的情况下，与rows属性效果相同，因此，这种方法与前一种方法看起来很像。

>>>for row in ws.iter_rows():

...:      print(＊[cell.value for cell in row])

image.png

最后这种方式是最麻烦的方式，也是大家最容易想到的方式。我们首先获取表格的最小行数和最大行数，然后获取最小列数与最大列数，通过行和列的索引确定一个唯一单元格。确定单元格以后，打印单元格的值。这种方式是每确定一个单元格打印一次，因此，我们在print函数中将end参数取值为空格来避免换行，并在内层for循环结束以后，显示地进行换行。如下所示：

>>>for i in range(ws.min_row, ws.max_row + 1):
...:      for j in range(ws.min_column, ws.max_column + 1):
...:           print(ws.cell(row=i, column=j).value, end=' ')
...:      print()

7.1.3 使用openpyxl修改Excel文档

openpyxl不但可以读取Excel文档，而且还可以修改Excel文档，包括修改单元格的数据、合并单元格、修改单元格的字体、在Excel文档中画图等。我们接下来将介绍如何使用openpyxl创建工作簿，创建和删除表格，修改单元格的数据。然后，我们通过计算example.xlsx文件中每位同学的平均分来演示如何修改一份Excel文档。

一个Workbook对象就代表了一个工作簿，因此，新建一个工作簿就是创建一个Workbook对象。创建完Workbook对象以后，默认会有一个名为“sheet1”的表格，我们可以通过表格的名称或get_active_sheet方法来获取这个表格。获取表格以后，可以通过给表格的title属性赋值的方式来修改表格的名称。

In [1]: from openpyxl import Workbook
In [2]: wb = Workbook()
In [3]: wb.get_sheet_names()
Out[3]: [u'Sheet']
In [4]: ws = wb.get_active_sheet()
In [5]: ws.title
Out[5]: u'Sheet'
In [6]: ws.title = 'student'
In [7]: ws.title
Out[7]: u'student'

创建完Workbook以后，就可以使用create_sheet方法创建新的表格，也可以使用remove_sheet方法删除表格。如下所示：

In [8]: wb.create_sheet(index=0, title="new sheet")
Out[8]: <Worksheet "new sheet">
In [9]: wb.get_sheet_names()
Out[9]: [u'new sheet', u'student']
In [10]: wb.remove_sheet(wb.get_sheet_by_name('student'))
In [11]: wb.get_sheet_names()
Out[11]: [u'new sheet']
In [12]: ws = wb.get_active_sheet()
In [13]: ws.title
Out[13]: u'new sheet'

如果要填充单元格的数据，可以直接对单元格赋值。openpyxl还能够自动处理Python数据类型到Excel数据类型之间的转换。如下所示：

In [14]: ws['A1'] = 'Hello, world'
In [15]: import datetime
In [16]: ws['A2'] = datetime.datetime.now()

我们已经创建了工作簿，同时在工作簿中创建了表格，并为表格中的部分单元格进行了赋值。此时，磁盘上还没有一个Excel文档保存了这些信息，我们需要调用Workbook的save方法将数据保存到磁盘中。如下所示：

In [17]: wb.save('sample.xlsx')

下面来看一个案例，用来巩固openpyxl读取和修改Excel文档的知识。附件里的example.xlsx文件保存了十位学生的信息，其中，最后三列分别是学生的语文、英语和数学成绩。现在，我们希望计算每一位学生的平均分和总分，并保存到表格的右侧中。

为了计算学生的成绩，我们首先需要打开Excel文档，并创建一个Workbook对象。有了Workbook对象以后，通过表格的名称获取我们需要操作的表格。在我们下面的代码中，将表格传递给process_worksheet函数。在process_worksheet函数中，我们首先获取了表格的最大列，用来确定平均分与总分的列坐标。随后，我们需要使用Worksheet的ifer_rows方法遍历每一位学生的成绩，iter_rows方法支持指定遍历的起点和终点。在我们的表格中，第一行保存的是表头信息，不需要计算。因此，我们通过传递min_row为2表示从第二行开始遍历。表格的第一列保存的是学生的学号，第二列保存的是学生的姓名，这也是我们在计算平均分和总分时不会使用的数据。因此，我们通过传递min_col为3表示从第三列开始遍历。iter_rows函数按行返回单元格，因此，我们只需要循环遍历iter_rows函数的结果，就实现了计算每一位学生的平均分和总分的功能。需要注意的是，iter_rows函数返回的是Cell对象，所以，我们在计算成绩之前需要先通过一个列表推导表达式，得到每一个单元格的取值。随后计算平均分和总分，并且通过每一行的第一个单元格获取平均分和总分的行坐标。有了行坐标和列坐标以后，直接通过Worksheet对象的cell方法为单元格复制。这就实现了计算平均分和总分，并且保存到表格右侧的功能。

process_worksheet函数处理完毕以后，当前所做的修改都还在内存之中，我们需要调用Workbook的save方法将Workbook所表示的工作簿保存到磁盘文件中。如下所示：

#! /usr/bin/python
#-＊- coding: UTF-8 -＊-
import openpyxl

def process_worksheet(sheet):
    avg_column = sheet.max_column + 1
    sum_column = sheet.max_column + 2
    for row in sheet.iter_rows(min_row=2, min_col=3):
        scores = [cell.value for cell in row]
        sum_score = sum(scores)
        avg_score = sum_score / len(scores)
          # 计算平均分和总分，并且保存到最后两列
        sheet.cell(row=row[0].row, column=avg_column).value = avg_score
        sheet.cell(row=row[0].row, column=sum_column).value = sum_score
    # 设置平均分和总分的标题部分
    sheet.cell(row=1, column=avg_column).value = 'avg'
    sheet.cell(row=1, column=sum_column).value = 'sum'

def main():
    wb = openpyxl.load_workbook('example.xlsx')
    sheet = wb.get_sheet_by_name('student')
    process_worksheet(sheet)
    wb.save('example_copy.xlsx')
if __name__ == '__main__':
    main()

修改完成以后，当前目录下存在一个名为example_copy.xlsx的文件，该文件的内容相对于附件中的example.xlsx文件增加了两列，分别是学生的平均分和总分，如图7-2所示。

image.png

图7-2 计算平均分和总分以后的结果

7.1.4 案例：合并多个Excel文档到一个Excel文档

我们也可以使用Excel的函数实现计算学生平均分和总分的例子，而且更加简单快捷。如果说计算平均分和总分的例子达到的是事倍功半的效果，那么，接下来这个例子将实现事半功倍！

现在，假设你是公司的人力资源管理部人才发展中心的员工，需要组织公司2019年的技术分享。因此，你需要将一个类似于图7-3的报名表格发送给公司的各位同事，让公司的同事自行填写。填写完成以后，你需要将每一位同事返回的Excel文档进行合并。需要注意的是，部分同事可能填写多行。同事们填写完毕以后，会将报名表再发送给你。你收到了每位同事的报名表后，接下来要做的是，将这些报名表合并到一张汇总表中。

图7-3 技术分享报名表

将多个Excel文档合并成单个文件，表面上看是个简单的需求。但是，因为我们的数据在不同的Excel文档中，即便是熟练的Excel操作人员也没有办法快速进行处理。这个时候，只能依次打开各个文档，并将内容手动拷贝到汇总表中。对于有编程背景的技术人员，我们不可能允许这么低效率的重复工作。这个时候就显现出使用Python语言处理Excel的优势。

对于这里的需求，使用Python语言处理将会非常简单。如下所示：

#! /usr/bin/python
import os
import glob
import openpyxl

def merge_xlsx_files(xlsx_files):
    wb = openpyxl.load_workbook(xlsx_files[0])
    ws = wb.active
    ws.title = "merged result"
    for filename in xlsx_files[1:]:
        workbook = openpyxl.load_workbook(filename)
        sheet = workbook.active
        for row in sheet.iter_rows(min_row=2):
            values = [cell.value for cell in row]
            ws.append(values)
    return wb

def get_all_xlsx_files(path):
    xlsx_files = glob.glob(os.path.join(path, '＊.xlsx'))
    sorted(xlsx_files, key=str.lower)
    return xlsx_files

def main():
    xlsx_files = get_all_xlsx_files(os.path.expanduser('～lmx'))
    wb = merge_xlsx_files(xlsx_files)
    wb.save('merged_form.xlsx')

if __name__ == '__main__':
    main()

在这个例子中，我们首先通过glob获取了指定目录下所有的Excel文档。然后，我们将这些文档按照文件名称进行了排序。排序以后，在merge_xlsx_files函数中尝试合并多个Excel文档。我们合并Excel的思路也很简单：

1）获取第一个文档中的表格（我们的Excel文档中只有一个表格）;
2）依次遍历其他文件中的报名表，并通过iter_rows函数忽略报名表中的首行内容；
3）通过列表推导获取报名表中的数据，然后调用Worksheet的append函数将数据添加到汇总表的末尾。

上述操作完成以后，返回Workbook对象。在main函数中，我们调用Workbook的save方法将汇总表保存到merged_form.xlsx文件中。

我们只花费几分钟时间编写了不到30行的Python代码，就实现了将多个Excel文档合并成单个文件的功能。因为是Python程序进行处理，所以无论有多少张表需要合并都能够快速处理，并且不会出错，也不会抱怨处理的表格太多。