Question

本章的例子将使用一个电子表格example.xlsx，它保存在根文件夹中。你可以自己创建这个电子文档，或从http://nostarch.com/automatestuff/下载。图12-1展示了3个默认的表，名为Sheet1、Sheet2和Sheet3，这是Excel自动为新工作簿提供的（不同操作系统和电子表格程序，提供的默认表个数可能会不同）。

图12-1 工作簿中表的选项卡在Excel的左下角

示例文件中的 Sheet 1 应该看起来像表 12-1（如果你没有从网站下载example.xlsx，就应该在工作表中自己输入这些数据）。

表12-1 example.xlsx电子表格

A

B

C

1

4/5/2015 1:34:02 PM

Apples

73

2

4/5/2015 3:41:23 AM

Cherries

85

3

4/6/2015 12:46:51 PM

Pears

14

4

4/8/2015 8:59:43 AM

Oranges

52

5

4/10/2015 2:07:00 AM

Apples

152

6

4/10/2015 6:10:37 PM

Bananas

23

7

4/10/2015 2:40:46 AM

Strawberries

98

既然有了示例电子表格，就来看看如何用openpyxl模块来操作它。

12.3.1 用openpyxl模块打开Excel文档

在导入openpyxl模块后，就可以使用openpyxl.load_workbook()函数。在交互式环境中输入以下代码：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> type(wb)
< class 'openpyxl.workbook.workbook.Workbook'>

openpyxl.load_workbook()函数接受文件名，返回一个workbook数据类型的值。这个workbook对象代表这个Excel文件，有点类似File对象代表一个打开的文本文件。

要记住，example.xlsx需要在当前工作目录，你才能处理它。可以导入os，使用函数os.getcwd()弄清楚当前工作目录是什么，并使用os.chdir()改变当前工作目录。

12.3.2 从工作簿中取得工作表

调用get_sheet_names()方法可以取得工作簿中所有表名的列表。在交互式环境中输入以下代码：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb.get_sheet_by_name('Sheet3')
>>> sheet
< Worksheet "Sheet3">
>>> type(sheet)
< class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
>>> anotherSheet = wb.get_active_sheet()
>>> anotherSheet
< Worksheet "Sheet1">

每个表由一个Worksheet对象表示，可以通过向工作簿方法get_sheet_by_name()传递表名字符串获得。最后，可以调用Workbook对象的get_active_sheet()方法，取得工作簿的活动表。活动表是工作簿在Excel中打开时出现的工作表。在取得Worksheet对象后，可以通过title属性取得它的名称。

12.3.3 从表中取得单元格

有了Worksheet对象后，就可以按名字访问Cell对象。在交互式环境中输入以下代码：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> sheet['A1']
< Cell Sheet1.A1>
>>> sheet['A1'].value
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c = sheet['B1']
>>> c.value
'Apples'
>>> 'Row ' + str(c.row) + ', Column ' + c.column + ' is ' + c.value
'Row 1, Column B is Apples'
>>> 'Cell ' + c.coordinate + ' is ' + c.value
'Cell B1 is Apples'
>>> sheet['C1'].value
73

Cell对象有一个value属性，不出意外，它包含这个单元格中保存的值。Cell对象也有row、column和coordinate属性，提供该单元格的位置信息。

这里，访问单元格B1的Cell对象的value属性，我们得到字符串'Apples'。row属性给出的是整数1，column属性给出的是'B'，coordinate属性给出的是'B1'。

openpyxl模块将自动解释列A中的日期，将它们返回为datetime值，而不是字符串。datetime数据类型将在第16章中进一步解释。

用字母来指定列，这在程序中可能有点奇怪，特别是在Z列之后，列开时使用两个字母：AA、AB、AC等。作为替代，在调用表的cell()方法时，可以传入整数作为row和column关键字参数，也可以得到一个单元格。第一行或第一列的整数是1，不是0。输入以下代码，继续交互式环境的例子：

>>> sheet.cell(row=1, column=2)
< Cell Sheet1.B1>
>>> sheet.cell(row=1, column=2).value
'Apples'
>>> for i in range(1, 8, 2):
        print(i, sheet.cell(row=i, column=2).value)

1 Apples
3 Pears
5 Apples
7 Strawberries

可以看到，使用表的cell()方法，传入row=1和column=2，将得到单元格B1的Cell对象，就像指定sheet['B1']一样。然后，利用cell()方法和它的关键字参数，就可以编写for循环，打印出一系列单元格的值。

假定你想顺着B列，打印出所有奇数行单元格的值。通过传入2作为range()函数的“步长”参数，可以取得每隔一行的单元格（在这里就是所有奇数行）。for循环的i变量被传递作为cell()方法的row关键字参数，而column关键字参数总是取2。请注意传入的是整数2，而不是字符串'B'。

可以通过Worksheet对象的get_highest_row()和get_highest_column()方法，确定表的大小。在交互式环境中输入以下代码：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> sheet.get_highest_row()
7
>>> sheet.get_highest_column()
3

请注意，get_highest_column()方法返回一个整数，而不是Excel中出现的字母。

12.3.4 列字母和数字之间的转换

要从字母转换到数字，就调用openpyxl.cell.column_index_from_string()函数。要从数字转换到字母，就调用openpyxl.cell.get_column_letter()函数。在交互式环境中输入以下代码：

>>> import openpyxl
>>> from openpyxl.cell import get_column_letter, column_index_from_string
>>> get_column_letter(1)
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> get_column_letter(sheet.get_highest_column())
'C'
>>> column_index_from_string('A')
1
>>> column_index_from_string('AA')
27

在从openpyxl.cell模块引入这两个函数后，可以调用get_column_letter()，传入像27这样的整数，弄清楚第27列的字母是什么。函数column_index_string()做的事情相反：传入一列的字母名称，它告诉你该列的数字是什么。要使用这些函数，不必加载一个工作簿。如果你愿意，可以加载一个工作簿，取得Worksheet对象，并调用Worksheet对象的方法，如get_highest_column()，来取得一个整数。然后，将该整数传递给get_column_letter()。

12.3.5 从表中取得行和列

可以将Worksheet对象切片，取得电子表格中一行、一列或一个矩形区域中的所有Cell对象。然后可以循环遍历这个切片中的所有单元格。在交互式环境中输入以下代码：

　>>> import openpyxl
　>>> wb = openpyxl.load_workbook('example.xlsx')
　>>> sheet = wb.get_sheet_by_name('Sheet1')
　>>> tuple(sheet['A1':'C3'])
　((< Cell Sheet1.A1>, < Cell Sheet1.B1>, < Cell Sheet1.C1>), (< Cell Sheet1.A2>,
　< Cell Sheet1.B2>, < Cell Sheet1.C2>), (< Cell Sheet1.A3>, < Cell Sheet1.B3>,
　< Cell Sheet1.C3>))
❶ >>> for rowOfCellObjects in sheet['A1':'C3']:
❷         for cellObj in rowOfCellObjects:
　            print(cellObj.coordinate, cellObj.value)
　        print('--- END OF ROW ---')
　A1 2015-04-05 13:34:02
　B1 Apples
　C1 73
　--- END OF ROW ---
　A2 2015-04-05 03:41:23
　B2 Cherries
　C2 85
　--- END OF ROW ---
　A3 2015-04-06 12:46:51
　B3 Pears
　C3 14
　--- END OF ROW ---

这里，我们指明需要从A1到C3的矩形区域中的Cell对象，得到了一个Generator对象，它包含该区域中的Cell对象。为了帮助我们看清楚这个Generator对象，可以使用它的tuple()方法，在一个元组中列出它的Cell对象。

这个元组包含3个元组：每个元组代表1行，从指定区域的顶部到底部。这3个内部元组中的每一个包含指定区域中一行的Cell对象，从最左边的单元格到最右边。所以总的来说，工作表的这个切片包含了从A1到C3区域的所有Cell对象，从左上角的单元格开始，到右下角的单元格结束。

要打印出这个区域中所有单元格的值，我们使用两个for循环。外层for循环遍历这个切片中的每一行❶。然后针对每一行，内层for循环遍历该行中的每个单元格❷。

要访问特定行或列的单元格的值，也可以利用Worksheet对象的rows和columns属性。在交互式环境中输入以下代码：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_active_sheet()
>>> sheet.columns[1]
(< Cell Sheet1.B1>, < Cell Sheet1.B2>, < Cell Sheet1.B3>, < Cell Sheet1.B4>,
< Cell Sheet1.B5>, < Cell Sheet1.B6>, < Cell Sheet1.B7>)
>>> for cellObj in sheet.columns[1]:
        print(cellObj.value)

Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

利用Worksheet对象的rows属性，可以得到一个元组构成的元组。内部的每个元组都代表1行，包含该行中的Cell对象。columns属性也会给你一个元组构成的元组，内部的每个元组都包含1列中的Cell对象。对于example.xlsx，因为有7行3列，rows给出由7个元组构成的一个元组（每个内部元组包含3个Cell对象）。columns给出由3个元组构成的一个元组（每个内部元组包含7个Cell对象）。

要访问一个特定的元组，可以利用它在大的元组中的下标。例如，要得到代表B列的元组，可以用sheet.columns[1]。要得到代表A列的元组，可以用sheet.columns[0]。在得到了代表行或列的元组后，可以循环遍历它的对象，打印出它们的值。

12.3.6 工作簿、工作表、单元格

作为快速复习，下面是从电子表格文件中读取单元格涉及的所有函数、方法和数据类型。

1．导入openpyxl模块。

2．调用openpyxl.load_workbook()函数。

3．取得Workbook对象。

4．调用get_active_sheet()或get_sheet_by_name()工作簿方法。

5．取得Worksheet对象。

6．使用索引或工作表的cell()方法，带上row和column关键字参数。

7．取得Cell对象。

8．读取Cell对象的value属性。