Python-自动化指南-繁琐工作自动化-第三版-九-

Python 自动化指南（繁琐工作自动化）第三版（九）

原文：automatetheboringstuff.com/

译者：飞龙

协议：CC BY-NC-SA 4.0

14 电子表格

原文：automatetheboringstuff.com/3e/chapter14.html

虽然我们通常不把电子表格看作是编程工具，但几乎每个人都使用它们来将信息组织成二维数据结构，使用公式进行计算，并以图表的形式生成输出。在接下来的两个章节中，我们将把 Python 集成到两个流行的电子表格应用程序中：Microsoft Excel 和 Google Sheets。

Excel 是一个流行且功能强大的电子表格应用程序。openpyxl 模块允许你的 Python 程序读取和修改 Excel 电子表格文件。例如，你可能有一个无聊的任务，就是从一张电子表格中复制某些数据并将其粘贴到另一张电子表格中。或者你可能需要浏览数千行数据，只挑选其中的一小部分进行基于某些标准的编辑。或者你可能需要查看数百个部门的预算电子表格，寻找任何处于亏损状态的电子表格。这些正是 Python 可以为你完成的那些无聊、无脑的电子表格任务。

虽然 Excel 是微软的专有软件，但 LibreOffice 是一个免费的替代品，可以在 Windows、macOS 和 Linux 上运行。LibreOffice Calc 使用 Excel 的 .xlsx 文件格式来存储电子表格，这意味着 openpyxl 模块也可以处理来自此应用程序的电子表格。你可以从 www.libreoffice.org 下载它。即使你的电脑上已经安装了 Excel，你也可能觉得这个程序更容易使用。然而，本章中的所有截图都是基于云的 Office 365 Excel。

openpyxl 模块操作的是 Excel 文件，而不是桌面 Excel 应用程序或基于云的 Excel 网页应用程序。如果你使用的是基于云的 Office 365，你必须点击文件另存为下载副本 来下载电子表格，运行你的 Python 脚本来编辑电子表格文件，然后将电子表格重新上传到 Office 365 以查看更改。如果你有桌面 Excel 应用程序，你必须关闭电子表格，运行你的 Python 脚本来编辑电子表格文件，然后重新在 Excel 中打开它以查看更改。

Python 不自带 openpyxl，所以你必须安装它。附录 A 提供了如何使用 Python 的 pip 工具安装第三方包的信息。你可以在 openpyxl.readthedocs.io/en/stable/ 找到完整的 openpyxl 文档。

本章中你将使用几个示例电子表格文件。你可以从本书的在线材料中下载它们，网址为 nostarch.com/automate-boring-stuff-python-3rd-edition。

读取 Excel 文件

首先，让我们回顾一些基本定义。Excel 电子表格文档被称为 工作簿。单个工作簿以 .xlsx 扩展名保存的文件中。

每个工作簿可以包含多个 工作表（也称为 工作表）。用户当前查看的工作表（或关闭 Excel 前最后查看的工作表）称为 活动工作表。每个工作表都有列（以字母 A 开始）和行（以数字 1 开始）。特定列和行的框称为 单元格。每个单元格可以包含数字或文本值。单元格和数据网格构成了工作表。

本章的示例将使用一个名为 example3.xlsx 的电子表格，该电子表格存储在当前工作目录中。您可以自己创建电子表格或从本书的在线资源中下载它。图 14-1 显示了名为 Sheet1、Sheet2 和 Sheet3 的三个工作表的标签。

图 14-1：工作簿的工作表标签位于 Excel 的左下角。

示例文件中的 Sheet1 应该看起来像表 14-1. （如果您没有下载 example3.xlsx，您应该自己将此数据输入到工作表中。）

表 14-1：example3.xlsx 电子表格

	A	B	C
1	4/5/2035 1:34:02 PM	苹果	73
2	4/5/2035 3:41:23 AM	樱桃	85
3	4/6/2035 12:46:51 PM	梨	14
4	4/8/2035 8:59:43 AM	橙子	52
5	4/10/2035 2:07:00 AM	苹果	152
6	4/10/2035 6:10:37 PM	香蕉	23
7	4/10/2035 2:40:46 AM	草莓	98

现在我们有了示例电子表格，让我们看看我们如何使用 openpyxl 模块来操作它。

打开工作簿

一旦您导入了 openpyxl 模块，您将能够使用 openpyxl.load_workbook() 函数打开 .xlsx 文件。在交互式外壳中输入以下内容：

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

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

从工作簿获取工作表

您可以通过访问 sheetnames 属性来获取工作簿中所有工作表名称的列表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> wb.sheetnames # The workbook's sheets' names
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3'] # Get a sheet from the workbook.
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title # Get the sheet's title as a string.
'Sheet3'
>>> another_sheet = wb.active # Get the active sheet.
>>> another_sheet
<Worksheet "Sheet1">

每个工作表由一个 Worksheet 对象表示，您可以通过使用带有工作表名称字符串的方括号来获取它，就像字典键一样。最后，您可以使用 Workbook 对象的 active 属性来获取工作簿的活动工作表。活动工作表是当工作簿在 Excel 中打开时位于顶部的工作表。一旦您有了 Worksheet 对象，您可以从 title 属性中获取其名称。

从工作表中获取单元格

一旦你有了Worksheet对象，你可以通过其名称访问Cell对象。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1'] # Get a sheet from the workbook.
>>> sheet['A1'] # Get a cell from the sheet.
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value # Get the value from the cell.
datetime.datetime(2035, 4, 5, 13, 34, 2)
>>> c = sheet['B1'] # Get another cell from the sheet.
>>> c.value
'Apples'
>>>  # Get the row, column, and value from the cell.
>>> f'Row {c.row}, Column {c.column} is {c.value}'
'Row 1, Column 2 is Apples'
>>> f'Cell {c.coordinate} is {c.value}'
'Cell B1 is Apples'
>>> sheet['C1'].value
73

Cell对象有一个包含在该单元格中存储的值的value属性。它还有一个row、column和coordinate属性，这些属性提供了单元格的位置信息。在这里，访问我们的Cell对象 B1 的value属性给我们字符串'Apples'。row属性给我们整数1，column属性给我们2，coordinate属性给我们'B1'。

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

通过字母指定列可能难以编程，特别是因为在 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): # Go through every other row.
...     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关键字参数传递，并在每次调用方法时使用2作为column关键字参数。请注意，此方法接受整数2，而不是字符串'B'。

你可以使用Worksheet对象的max_row和max_column属性来确定工作表的大小。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.max_row # Get the highest row number.
7
>>> sheet.max_column # Get the highest column number.
3

注意，max_column属性是一个整数，而不是 Excel 中出现的字母。

在列字母和数字之间转换

要从数字转换为字母，调用openpyxl.utils.get_column_letter()函数。要从字母转换为数字，调用openpyxl.utils.column_index_from_string()函数。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1)  # Translate column 1 to a letter.
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A') # Get A's number.
1
>>> column_index_from_string('AA')
27

在从openpyxl.utils模块导入这两个函数之后，你可以调用get_column_letter()并传递一个整数，如27，以确定第 27 列的字母名称。函数column_index_from_string()执行相反的操作：你传递给它一个列的字母名称，它告诉你该列的数字。你不需要加载工作簿来使用这些函数。

获取行和列

您可以通过切片Worksheet对象来获取工作表中一行、一列或矩形区域的全部Cell对象。然后，您可以遍历切片中的所有单元格。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet['A1':'C3'] # Get cells A1 to 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 row_of_cell_objects in sheet['A1':'C3']: ❶
...     for cell_obj in row_of_cell_objects: ❷
...         print(cell_obj.coordinate, cell_obj.value)
...     print('--- END OF ROW ---')
...
A1 2035-04-05 13:34:02
B1 Apples
C1 73
--- END OF ROW ---
A2 2035-04-05 03:41:23
B2 Cherries
C2 85
--- END OF ROW ---
A3 2035-04-06 12:46:51
B3 Pears
C3 14
--- END OF ROW ---

在这里，我们指定['A1':'C3']以获取从 A1 到 C3 矩形区域中的Cell对象切片，我们得到一个包含该区域Cell对象的元组。

这个元组包含三个元组：每个元组对应于一个行，从所需区域的顶部到底部。这三个内部元组中的每一个都包含我们所需区域一行的Cell对象，从最左边的单元格到最右边的单元格。因此，我们的工作表切片包含从 A1 到 C3 区域中的所有Cell对象，从左上角单元格开始，到右下角单元格结束。

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

要访问特定行或列中单元格的值，您还可以使用Worksheet对象的rows和columns属性。在使用方括号和索引之前，必须使用list()函数将这些属性转换为列表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> list(sheet.columns)[1] # Get the second column's cells.
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell
'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cell_obj in list(sheet.columns)[1]:
...     print(cell_obj.value)
...
Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

使用传递给list()的Worksheet对象的rows属性将给我们一个元组列表。这些元组中的每一个代表一行，并包含该行中的Cell对象。传递给list()的columns属性也给我们一个元组列表，每个元组包含特定列中的Cell对象。例如，对于example3.xlsx，因为有七行和三列，list(sheet.rows)给我们一个包含七个元组的列表（每个元组包含三个Cell对象），而list(sheet.columns)给我们一个包含三个元组的列表（每个元组包含七个Cell对象）。

要访问特定的元组，您可以通过其在较大元组中的索引来引用它。例如，要获取表示列 B 的元组，您将使用list(sheet.columns)[1]。要获取包含列 A 中Cell对象的元组，您将使用list(sheet.columns)[0]。一旦您有一个表示一行或一列的元组，您就可以遍历其Cell对象并打印它们的值。

项目 9：收集人口统计数据

假设您有一个包含 2010 年美国人口普查数据的电子表格，并且您被分配了一个无聊的任务，即浏览其数千行数据，以计算每个县的人口和人口普查区的数量。（人口普查区是为了普查目的而定义的地理区域。）每一行代表一个单独的人口普查区。我们将电子表格文件命名为censuspopdata.xlsx，您可以从本书的在线资源中下载它。其内容如图 14-2 所示。

图 14-2：censuspopdata.xlsx 电子表格

尽管 Excel 可以自动计算多个选定单元格的总和，但你仍然需要首先手动选择每个 3,000 多个县的单元格。即使手动计算一个县的人口只需几秒钟，但整个电子表格的这项工作将需要数小时。

在这个项目中，你需要编写一个脚本，可以从人口普查电子表格文件中读取，并在几秒钟内为每个县计算统计数据。

这就是你的程序所做的事情：

从 Excel 电子表格中读取数据
统计每个县的人口普查区数量
统计每个县的总人口
打印结果

这意味着你的代码需要执行以下操作：

使用openpyxl模块打开并读取 Excel 文档的单元格。
计算所有普查区和人口数据，并将其存储在数据结构中。
使用pprint模块将数据结构写入扩展名为.py的文本文件中，以便以后导入。

第 1 步：读取电子表格数据

在censuspopdata.xlsx电子表格中只有一个工作表，名为'Population by Census Tract'，工作表中的每一行都包含一个普查区的数据。列包括普查区编号（A）、州缩写（B）、县名（C）和普查区人口（D）。

打开一个新的文件编辑标签，输入以下代码，然后将其保存为readCensusExcel.py：

# readCensusExcel.py - Tabulates county population and census tracts

import openpyxl, pprint # ❶
print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx') # ❷
sheet = wb['Population by Census Tract'] # ❸
county_data = {}

# TODO: Fill in county_data with each county's population and tracts.
print('Reading rows...')
for row in range(2, sheet.max_row + 1): # ❹
    # Each row in the spreadsheet has data for one census tract.
    state  = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop    = sheet['D' + str(row)].value

# TODO: Open a new text file and write the contents of county_data to it.

此代码导入了openpyxl模块以及你将用于打印最终县数据的pprint模块❶。然后，它打开censuspopdata.xlsx文件❷，获取包含人口数据的表❸，并开始遍历其行❹。

注意，你已经创建了一个名为county_data的变量，它将包含你为每个县计算的人口和普查区数量。然而，在存储任何内容之前，你应该确定数据结构内部的确切结构。

第 2 步：填充数据结构

在美国，各州有两位字母缩写，并进一步划分为县。存储在county_data中的数据结构将是一个字典，其键为州缩写。每个州缩写将映射到另一个字典，其键是该州县名的字符串。每个县名将映射到一个只包含两个键的字典，即'tracts'和'pop'。这些键分别映射到县的人口普查区数量和人口。例如，该字典将类似于以下结构：

{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
        'Aleutians West': {'pop': 5561, 'tracts': 2},
        'Anchorage': {'pop': 291826, 'tracts': 55},
        'Bethel': {'pop': 17013, 'tracts': 3},
        'Bristol Bay': {'pop': 997, 'tracts': 1},
        # --snip--

如果之前的字典存储在county_data中，以下表达式将评估如下：

>>> county_data['AK']['Anchorage']['pop']
291826
>>> county_data['AK']['Anchorage']['tracts']
55

更一般地，county_data字典的键将如下所示：

county_data[`state abbrev`][`county`]['tracts']
county_data[`state abbrev`][`county`]['pop']

现在你已经知道了 county_data 的结构，你可以编写将县数据填充到其中的代码。将以下代码添加到程序底部：

# readCensusExcel.py - Tabulates county population and census tracts

# --snip--

for row in range(2, sheet.max_row + 1):
    # Each row in the spreadsheet has data for one census tract.
    state  = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop    = sheet['D' + str(row)].value

 # Make sure the key for this state exists.
county_data.setdefault(state, {}) # ❶
 # Make sure the key for this county in this state exists.
county_data[state].setdefault(county, {'tracts': 0, 'pop': 0}) # ❷

 # Each row represents one census tract, so increment by one.
county_data[state][county]['tracts'] += 1 # ❸
 # Increase the county pop by the pop in this census tract.
county_data[state][county]['pop'] += int(pop) # ❹

# TODO: Open a new text file and write the contents of county_data to it.

代码的最后两行执行实际的计算工作，在 for 循环的每次迭代中增加 tracts ❸ 的值，并增加当前县 pop ❹ 的值。

其他代码存在是因为你不能在 county_data 中键不存在的情况下将其作为州缩写键的值添加。也就是说，如果 'AK' 键尚不存在，则 county_data['AK']['Anchorage']['tracts'] += 1 将导致错误。为了确保州缩写键存在于你的数据结构中，你需要调用 setdefault() 方法来设置一个值，如果尚未存在 state ❶。

正如 county_data 字典需要每个州缩写键的值为字典一样，每个这些字典将需要其自己的字典作为每个县键的值 ❷。而且，每个这些字典反过来又需要以整数值 0 开头的键 'tracts' 和 'pop'。（如果你失去了对字典结构的跟踪，请回顾本节开头示例字典。）

由于 setdefault() 如果键已存在则不会做任何事情，因此你可以在 for 循环的每次迭代中调用它而不会出现问题。

第 3 步：将结果写入文件

在 for 循环完成后，county_data 字典将包含所有按县和州键索引的人口和地块信息。此时，你可以编写更多代码将此数据写入文本文件或另一个 Excel 电子表格。现在，让我们只使用 pprint.pformat() 函数将 county_data 字典值作为一个巨大的字符串写入名为 census2010.py 的文件。将以下代码添加到程序底部（确保保持未缩进，以便它保持在 for 循环之外）：

# readCensusExcel.py - Tabulates county population and census tracts.

# --snip--

# Open a new text file and write the contents of county_data to it.
print('Writing results...')
result_file = open('census2010.py', 'w')
result_file.write('allData = ' + pprint.pformat(county_data))
result_file.close()
print('Done.')

pprint.pformat() 函数生成一个自身格式化为有效 Python 代码的字符串。通过将其输出到名为 census2010.py 的文本文件中，你已从你的 Python 程序中生成了一个 Python 程序！这看起来可能很复杂，但优点是现在你可以像导入任何其他 Python 模块一样导入 census2010.py。在交互式 shell 中，将当前工作目录更改为包含你新创建的 census2010.py 文件的文件夹，然后导入它：

>>> import census2010
>>> census2010.allData['AK']['Anchorage']
{'pop': 291826, 'tracts': 55}
>>> anchorage_pop = census2010.allData['AK']['Anchorage']['pop']
>>> print('The 2010 population of Anchorage was ' + str(anchorage_pop))
The 2010 population of Anchorage was 291826

readCensusExcel.py 程序是废弃代码：一旦你将其结果保存到 census2010.py，你就不需要再次运行该程序。每次你需要县数据时，只需运行 import census2010。

手动计算这些数据需要数小时；这个程序只需几秒钟就能完成。使用 openpyxl，你将能够轻松地从 Excel 电子表格中提取保存的信息并对其进行计算。你可以从本书的在线资源中下载完整的程序。

类似程序的思路

许多企业和办公室使用 Excel 来存储各种类型的数据，电子表格变得庞大且难以操作并不罕见。任何解析 Excel 电子表格的程序都有类似的架构：它加载电子表格文件，准备一些变量或数据结构，然后遍历电子表格中的每一行。这样的程序可以执行以下操作：

在电子表格中比较多行的数据。
打开多个 Excel 文件，并在工作表之间比较数据。
检查电子表格是否有空白行或任何单元格中的无效数据，并在必要时提醒用户。
从电子表格中读取数据，并将其用作 Python 程序的输入。

编写 Excel 文档

openpyxl 模块还提供了写入数据的方法，这意味着您的程序可以创建和编辑电子表格文件。使用 Python，创建包含数千行数据的电子表格非常简单。

创建和保存 Excel 文件

调用 openpyxl.Workbook() 函数创建一个新的、空的 Workbook 对象。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook() # Create a blank workbook.
>>> wb.sheetnames # The workbook starts with one sheet.
['Sheet']
>>> sheet = wb.active
>>> sheet.title
'Sheet'
>>> sheet.title = 'Spam Bacon Eggs Sheet' # Change the title.
>>> wb.sheetnames
['Spam Bacon Eggs Sheet']

工作簿将从一个名为 Sheet 的工作表开始。您可以通过将其 title 属性存储为新字符串来更改工作表的名称。

任何时候您修改 Workbook 对象或其工作表和单元格，电子表格文件将不会保存，直到您调用 save() 工作簿方法。在交互式外壳中输入以下内容（假设当前工作目录中存在 example3.xlsx）：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.title = 'Spam Spam Spam'
>>> wb.save('example3_copy.xlsx') # Save the workbook.

在这里，我们更改了我们工作表的名称。为了保存我们的更改，我们将一个字符串文件名传递给 save() 方法。传递与原始文件名不同的文件名，例如 'example3_copy.xlsx'，将更改保存到电子表格的副本中。

每次您编辑从文件加载的电子表格时，您都应该使用与原始文件不同的文件名保存新的、编辑过的电子表格。这样，在您的代码中的错误导致新保存的文件包含不正确或损坏的数据的情况下，您仍然可以保留原始的电子表格文件来工作。此外，如果电子表格当前在 Excel 桌面应用程序中打开，save() 方法将不起作用。您必须首先关闭电子表格，然后运行您的 Python 程序。

创建和删除工作表

您可以使用 create_sheet() 方法和 del 操作符创建或删除工作簿中的工作表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> wb.create_sheet() # Add a new sheet.
<Worksheet "Sheet1">
>>> wb.sheetnames
['Sheet', 'Sheet1']
>>> # Create a new sheet at index 0.
>>> wb.create_sheet(index=0, title='First Sheet')
<Worksheet "First Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Sheet1']
>>> wb.create_sheet(index=2, title='Middle Sheet')
<Worksheet "Middle Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']

create_sheet() 方法返回一个名为 SheetX 的新 Worksheet 对象，默认情况下它是工作簿中的最后一个工作表。您可以选择使用 index 和 title 关键字参数指定新工作表的索引和名称。

继续上一个示例，输入以下内容：

>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']
>>> del wb['Middle Sheet']
>>> del wb['Sheet1']
>>> wb.sheetnames
['First Sheet', 'Sheet']

您可以使用 del 操作符从工作簿中删除一个工作表，就像您可以使用它从字典中删除键值对一样。

记得在向工作簿中添加或删除工作表后调用 save() 方法来保存更改。

将值写入单元格

将值写入单元格与将值写入字典的键类似。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Hello, world!' # Edit the cell's value.
>>> sheet['A1'].value
'Hello, world!'

如果你有一个单元格坐标的字符串，你可以像在Worksheet对象上使用字典键一样使用它来指定要写入的单元格。

项目 10：更新电子表格

在这个项目中，你需要编写一个程序来更新产品销售电子表格中的单元格。你的程序将遍历电子表格，查找特定的产品种类，并更新它们的价格。从本书的在线资源中下载此produceSales3.xlsx电子表格。图 14-3 显示了电子表格的外观。

图 14-3：产品销售电子表格

每一行代表一次单独的销售。列包括销售产品的类型（A）、该产品的每磅成本（B）、销售的磅数（C）以及销售的总收入（D）。“总计”列设置为 Excel 公式，例如=ROUND(B2*C2, 2)，该公式将每行的每磅成本乘以销售的磅数，并将结果四舍五入到最接近的美分。使用此公式，如果“每磅成本”和“销售的磅数”列发生变化，总计列中的单元格将自动更新。

现在想象一下，大蒜、芹菜和柠檬的价格输入错误，让你不得不在这个电子表格中逐行检查数千行，以更新任何芹菜、大蒜和柠檬行的每磅成本。你不能简单地使用查找和替换来更改价格，因为可能还有其他具有相同价格的项目，你不想错误地“纠正”。对于数千行，手动完成这项工作将需要数小时。但你可以编写一个可以在几秒钟内完成这项工作的程序。

你的程序应该执行以下操作：

遍历所有行。
如果该行是芹菜、大蒜或柠檬，则更改价格。

这意味着你的代码需要执行以下操作：

打开电子表格文件。
对于每一行，检查列 A 中的值是否为Celery、Garlic或Lemon。
如果是，更新列 B 中的价格。
将电子表格保存到新文件中（以防万一，不要丢失原始电子表格）。

第 1 步：设置包含更新信息的数据库结构

需要更新的价格如下：

芹菜：1.19
大蒜：3.07
柠檬：1.27

你可以编写代码来设置这些新价格，如下所示：

if produce_name == 'Celery':
    cell_obj = 1.19
if produce_name == 'Garlic':
    cell_obj = 3.07
if produce_name == 'Lemon':
    cell_obj = 1.27

但像这样硬编码产品和更新价格数据有点不够优雅。如果你需要用不同的价格或不同的产品更新电子表格，你将不得不更改大量代码。每次更改代码，你都有引入错误的风险。

一个更灵活的解决方案是将修正后的价格信息存储在字典中，并编写代码以使用这种数据结构。在新文件编辑标签中，输入以下代码：

# updateProduce.py - Corrects costs in produce sales spreadsheet

import openpyxl

wb = openpyxl.load_workbook('produceSales3.xlsx')
sheet = wb['Sheet']

# The produce types and their updated prices
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}

# TODO: Loop through the rows and update the prices.

将其保存为 updateProduce.py。如果你需要再次更新电子表格，你只需更新 PRICE_UPDATES 字典，而不需要更新任何其他代码。

第 2 步：检查所有行并更新错误的价格

程序的下一部分将遍历电子表格中的所有行。将以下代码添加到 updateProduce.py 的底部：

# updateProduce.py - Corrects costs in produce sales spreadsheet

# --snip--

# Loop through the rows and update the prices.
for row_num in range(2, sheet.max_row + 1): # Skip the first row. # ❶
produce_name = sheet.cell(row=row_num, column=1).value # ❷
    if produce_name in PRICE_UPDATES: # ❸
 sheet.cell(row=row_num, column=2).value = PRICE_UPDATES[produce_name]

wb.save('updatedProduceSales3.xlsx')  # ❹

我们从第 2 行开始遍历行，因为第 1 行只是标题❶。第 1 列（即 A 列）的单元格将被存储在变量 produce_name❷中。如果 produce_name 作为 PRICE_UPDATES 字典中的键存在❸，你知道这一行需要其价格修正。正确的价格将在 PRICE_UPDATES[produce_name] 中。

注意使用 PRICE_UPDATES 如何使代码更简洁。它只使用一个 if 语句，而不是为每种产品更新单独一行，例如 if produce_name == 'Garlic':。由于代码使用 PRICE_UPDATES 字典而不是在 for 循环中硬编码产品名称和更新成本，因此如果产品销售电子表格需要额外的更改，你只需修改 PRICE_UPDATES 字典，而不需要修改代码的其他部分。

在遍历整个电子表格并做出更改后，代码将 Workbook 对象保存到 updatedProduceSales3.xlsx❹。它不会覆盖旧电子表格，以防程序中存在错误，更新的电子表格是错误的。在检查更新的电子表格看起来正确后，你可以删除旧电子表格。

类似程序的创意

由于许多办公人员经常使用 Excel 电子表格，一个可以自动编辑和写入 Excel 文件的程序可能非常有用。这样的程序可以执行以下操作：

从一个电子表格中读取数据并将其写入其他电子表格的部分。
从网站、文本文件或剪贴板读取数据并将其写入电子表格。
自动“清理”电子表格中的数据。例如，它可以使用正则表达式读取多种格式的电话号码并将它们编辑为单一的标准格式。

设置单元格的字体样式

对某些单元格、行或列进行样式设置可以帮助你在电子表格中强调重要区域。例如，在产品电子表格中，你的程序可以对土豆、大蒜和胡萝卜行应用粗体文本。或者你可能想要将每行成本每磅超过 5 美元的行设置为斜体。手动对大型电子表格的部分进行样式设置会很繁琐，但你的程序可以立即完成。

要在单元格中自定义字体样式，从 openpyxl.styles 模块导入 Font() 函数：

from openpyxl.styles import Font

以这种方式导入函数允许你写 Font() 而不是 openpyxl.styles.Font()。（有关更多信息，请参阅第三章的“导入模块”部分。）

以下示例创建一个新的工作簿并将单元格 A1 设置为 24 点斜体字体：

>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> italic_24_font = Font(size=24, italic=True)  # ❶
>>> sheet['A1'].font = italic_24_font  # ❷
>>> sheet['A1'] = 'Hello, world!'
>>> wb.save('styles3.xlsx')

在此示例中，Font(size=24, italic=True)返回一个Font对象，我们将其存储在italic_24_font ❶中。传递给Font()的关键字参数配置了对象的样式信息，将italic _24_font对象分配给sheet['A1'].font ❷将所有这些字体样式信息应用到单元格 A1。

要设置字体属性，请将关键字参数传递给Font()。表 14-2 显示了Font()函数可能的关键字参数。

表 14-2：Font对象的关键字参数

关键字参数	数据类型	描述
`name`	字符串	字体名称，例如`'Calibri'`或`'Times New Roman'`
`size`	整数	点大小
`bold`	布尔值	`True`，表示粗体字体
`italic`	布尔值	`True`，表示斜体字体

您可以调用Font()来创建一个Font对象并将其存储在变量中。然后，将此变量分配给Cell对象的font属性。例如，此代码创建各种字体样式：

>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> bold_font = Font(name='Times New Roman', bold=True)
>>> sheet['A1'].font = bold_font
>>> sheet['A1'] = 'Bold Times New Roman'

>>> italic_font = Font(size=24, italic=True)
>>> sheet['B3'].font = italic_font
>>> sheet['B3'] = '24 pt Italic'

>>> wb.save('styles3.xlsx')

在这里，我们将Font对象存储在bold_font中，然后设置 A1 Cell对象的font属性为bold_font。我们使用另一个Font对象重复此过程以设置第二个单元格的字体。运行此代码后，电子表格中 A1 和 B3 单元格的样式将具有自定义字体样式，如图 14-4 所示。

图 14-4：具有自定义字体样式的电子表格

对于单元格 A1，我们将字体名称设置为'Times New Roman'并将bold设置为true，因此文本以粗体 Times New Roman 显示。我们没有指定点大小，因此文本使用openpyxl默认值，11。在单元格 B3 中，文本是斜体，点大小为 24。我们没有指定字体名称，因此文本使用openpyxl默认值，Calibri。

公式

以等号开头的 Excel 公式可以配置单元格包含从其他单元格计算得出的值。在本节中，您将使用openpyxl模块以编程方式向单元格添加公式，就像添加任何正常值一样。以下是一个示例：

>>> sheet['B9'] = '=SUM(B1:B8)'

此代码将在 B9 中存储公式=SUM(B1:B8)，将单元格的值设置为 B1 到 B8 单元格中值的总和。您可以在图 14-5 中看到这一效果。

图 14-5：单元格 B9 包含将单元格 B1 到 B8 中的值相加的公式。

您可以像在单元格中的任何其他文本值一样设置 Excel 公式。例如，在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 200
>>> sheet['A2'] = 300
>>> sheet['A3'] = '=SUM(A1:A2)' # Set the formula.
>>> wb.save('writeFormula3.xlsx')

A1 和 A2 单元格分别设置为 200 和 300。A3 单元格中的值设置为公式，该公式将 A1 和 A2 中的值相加。当电子表格在 Excel 中打开时，A3 将显示其值为 500。

openpyxl 模块没有计算 Excel 公式并将结果填充到单元格中的能力。但是，如果你在 Excel 中打开这个 writeFormula3 .xlsx 文件，Excel 本身会填充单元格中的公式结果。你可以将文件保存在 Excel 中，然后打开它，同时将 data_only=True 关键字参数传递给 openpyxl.load_workbook()，单元格值应显示计算结果而不是公式字符串：

>>> # Be sure to open writeFormula3.xlsx in Excel and save it first.
>>> import openpyxl
>>> wb = openpyxl.load_workbook('writeFormula3.xlsx')  # Open without data_only.
>>> wb.active['A3'].value  # Get the formula string.
'=SUM(A1:A2)'
>>> wb = openpyxl.load_workbook('writeFormula3.xlsx', data_only=True)  # Open with data_only.
>>> wb.active['A3'].value  # Get the formula result.
500

再次强调，只有当你以 Excel 打开并保存文件，以便 Excel 能够运行公式计算并将结果存储在电子表格文件中时，你才会看到电子表格文件中的 500 结果。这是 openpyxl 在你将 data_only=True 传递给 openpyxl.load_workbook() 时读取的值。

Excel 公式为电子表格提供了一定程度的可编程性，但对于复杂任务来说，它们可能会很快变得难以管理。例如，即使你非常熟悉 Excel 公式，尝试解读 =IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), " ", ""),"")), "") 实际上做什么也是一个头疼的问题。Python 代码要容易阅读得多。

调整行和列

在 Excel 中，调整行和列的大小就像点击并拖动行或列标题的边缘一样简单。但如果你需要根据单元格的内容设置行或列的大小，或者你想要在大量的电子表格文件中设置大小，编写一个 Python 程序来做这件事会更快。

你还可以隐藏行和列以从视图中消失，或者“冻结”它们的位置，以便它们始终可见，打印电子表格时出现在每一页上（这对于标题很有用）。

设置行高和列宽

Worksheet 对象有 row_dimensions 和 column_dimensions 属性，用于控制行高和列宽。例如，在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Tall row'
>>> sheet['B2'] = 'Wide column'
>>> sheet.row_dimensions[1].height = 70
>>> sheet.column_dimensions['B'].width = 20
>>> wb.save('dimensions3.xlsx')

一个工作表的 row_dimensions 和 column_dimensions 是类似字典的值；row_dimensions 包含 RowDimension 对象，而 column_dimensions 包含 ColumnDimension 对象。在 row_dimensions 中，你可以使用行号（在这种情况下，1 或 2）访问其中一个对象。在 column_dimensions 中，你可以使用列字母（在这种情况下，A 或 B）访问其中一个对象。

*dimensions3.xlsx* 电子表格看起来像图 14-6。

图 14-6：第 1 行和 B 列设置为更大的高度和宽度

单元格的默认宽度和高度在 Excel 和 openpyxl 的不同版本之间有所不同。

合并和取消合并单元格

你可以使用 merge_cells() 工作表方法将矩形单元格组合并为一个单元格。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet.merge_cells('A1:D3') # Merge all these cells.
>>> sheet['A1'] = 'Twelve cells merged together.'
>>> sheet.merge_cells('C5:D5') # Merge these two cells.
>>> sheet['C5'] = 'Two merged cells.'
>>> wb.save('merged3.xlsx')

merge_cells() 函数的参数是要合并的矩形区域的左上角和右下角单元格的单个字符串：'A1:D3' 将 12 个单元格合并为一个单元格。要设置这些合并单元格的值，只需设置合并组左上角单元格的值。

当你运行此代码时，merged.xlsx 将看起来像图 14-7。

图 14-7：电子表格中的合并单元格

要取消合并单元格，调用 unmerge_cells() 工作表方法：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('merged3.xlsx')
>>> sheet = wb['Sheet']
>>> sheet.unmerge_cells('A1:D3') # Split these cells up.
>>> sheet.unmerge_cells('C5:D5')
>>> wb.save('unmerged3.xlsx')

如果你保存了更改并查看电子表格，你会看到合并的单元格已经恢复为单个单元格。

冻结窗格

对于太大而无法一次性显示的电子表格，在屏幕上“冻结”几行或最左边的列很有帮助。例如，冻结的列或行标题，即使在用户滚动电子表格时，也会始终对用户可见。这些被称为 冻结窗格。

在 openpyxl 中，每个 Worksheet 对象都有一个 freeze_panes 属性，你可以将其设置为单元格对象或单元格坐标的字符串。请注意，此属性将冻结此单元格上方所有行和左侧所有列，但不会冻结单元格本身的行和列。要解冻所有窗格，将 freeze_panes 设置为 None 或 'A1'。表 14-3 显示了 freeze_panes 的某些示例设置冻结哪些行和列。

表 14-3：冻结窗格示例

freeze_panes 设置	冻结的行和列
`sheet.freeze_panes` `=` `'A2'`	第 1 行（没有冻结列）
`sheet.freeze_panes` `=` `'B1'`	列 A（没有冻结行）
`sheet.freeze_panes` `=` `'C1'`	列 A 和 B（没有冻结行）
`sheet.freeze_panes` `=` `'C2'`	第 1 行和列 A 和 B
`sheet.freeze_panes` `=` `'A1'` 或 `sheet.freeze_panes` `=` `None`	没有冻结的行或列

下载 produceSales3.xlsx 电子表格的另一个副本，然后在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('produceSales3.xlsx')
>>> sheet = wb.active
>>> sheet.freeze_panes = 'A2' # Freeze the rows above A2.
>>> wb.save('freezeExample3.xlsx')

你可以在图 14-8 中看到结果。

图 14-8：冻结第 1 行

因为将 freeze_panes 属性设置为 'A2'，所以无论用户在电子表格中滚动到何处，第 1 行都将保持可见。

图表

openpyxl 模块支持使用工作表单元格中的数据创建条形图、折线图、散点图和饼图。要创建图表，你需要执行以下操作：

1. 从矩形单元格选择中创建一个Reference对象。

2. 通过传递Reference对象来创建一个Series对象。

3. 如何创建一个Chart对象？

4. 将Series对象追加到Chart对象中。

5. 将Chart对象添加到Worksheet对象中，可选地指定哪个单元格应该是图表的左上角。

Reference对象需要一些解释。要创建Reference对象，你必须调用openpyxl.chart.Reference()函数并传递五个参数：

包含你的图表数据的Worksheet对象。
包含图表数据的矩形单元格选择中顶部左单元格的列和行整数：元组中的第一个整数是行，第二个是列。注意，1是第一行，不是0。
包含图表数据的矩形单元格选择中底部右单元格的列和行整数：元组中的第一个整数是行，第二个是列。

输入以下交互式示例以创建条形图并将其添加到电子表格中：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> for i in range(1, 11): # Create some data in column A.
... sheet['A' + str(i)] = i * i
...
>>> ref_obj = openpyxl.chart.Reference(sheet, 1, 1, 1, 10)

>>> series_obj = openpyxl.chart.Series(ref_obj, title='First series')

>>> chart_obj = openpyxl.chart.BarChart()
>>> chart_obj.title = 'My Chart'
>>> chart_obj.append(series_obj)

>>> sheet.add_chart(chart_obj, 'C5')
>>> wb.save('sampleChart3.xlsx')

此代码生成一个类似于图 14-9 的电子表格。

图 14-9：添加了图表的电子表格

我们通过调用openpyxl.chart.BarChart()创建了条形图。你也可以通过调用openpyxl.chart.LineChart()、openpyxl.chart.ScatterChart()和openpyxl.chart.PieChart()来创建折线图、散点图和饼图。

摘要

通常，处理信息困难的部分不是处理本身，而是仅仅是将数据格式化为程序所需的格式。但是一旦你将 Excel 电子表格加载到 Python 中，你就可以比手动更快地提取和操作其数据。

你也可以从你的程序生成电子表格作为输出。因此，如果同事需要将数千个销售联系人转移到电子表格文件中，你就不必费力地将它们全部复制粘贴到 Excel 中。有了openpyxl模块和一些编程知识，你会发现处理最大的电子表格就像小菜一碟。

在下一章中，我们将探讨如何使用 Python 与另一个电子表格程序交互：流行的在线 Google Sheets 应用程序。

实践问题

对于以下问题，假设你有一个名为wb的Workbook对象，一个名为sheet的Worksheet对象，以及一个名为sheet的Sheet对象。

1. openpyxl.load_workbook()函数返回什么？

2. wb.sheetnames工作簿属性包含什么？

3. 你将如何检索名为'Sheet1'的工作表的Worksheet对象？

4. 你将如何检索工作簿活动工作表的Worksheet对象？

5. 你将如何检索 C5 单元格的值？

如何将单元格 C5 的值设置为"Hello"？
如何检索单元格的行和列作为整数？
sheet.max_column和sheet.max_row工作表属性包含什么，这些属性的数据类型是什么？
如果需要获取列'M'的整数索引，你需要调用什么函数？
如果需要获取行14的字符串名称，你需要调用什么函数？
如何检索从 A1 到 F1 的所有Cell对象的元组？
如何将工作簿保存为example3.xlsx文件名？
如何在单元格中设置公式？
如果你想检索单元格公式的结果而不是单元格公式本身，你首先必须做什么？
如何将第 5 行的行高设置为 100？
如何隐藏列 C？
冻结窗格是什么？
创建条形图需要调用哪些五个函数和方法？

练习程序

为了练习，编写程序来完成以下任务。

乘法表制作器

创建一个名为multiplicationTable.py的程序，该程序从命令行接收一个数字N并创建一个N×N的乘法表电子表格。例如，当程序以这种方式运行时

py multiplicationTable.py 6

应该创建一个看起来像图 14-10 的电子表格。

图 14-10：在电子表格中生成的乘法表

第 1 行和列 A 应包含标签并加粗。

空行插入器

创建一个名为blankRowInserter.py的程序，该程序接受两个整数和一个文件名字符串作为命令行参数。让我们称第一个整数为N，第二个整数为M。程序应从行N开始，在电子表格中插入M个空白行。例如，当程序以这种方式运行时

python blankRowInserter.py 3 2 myProduce.xlsx

“之前”和“之后”的电子表格应类似于图 14-11。

图 14-11：在行 3 之前（左）和之后（右）插入两个空白行

您可以通过读取电子表格的内容来编写此程序。然后，在写入新的电子表格时，使用一个for循环来复制前N行。对于剩余的行，在输出电子表格中添加M到行号。

读取 Excel 文件

首先，让我们回顾一些基本定义。Excel 电子表格文档被称为工作簿。单个工作簿以.xlsx扩展名保存的文件中。

每个工作簿可以包含多个工作表（也称为工作表）。用户当前查看的工作表（或关闭 Excel 之前最后查看的工作表）称为活动工作表。每个工作表都有列（从字母A开始编号）和行（从数字 1 开始编号）。特定列和行的矩形称为单元格。每个单元格可以包含数字或文本值。单元格的网格及其数据构成了一个工作表。

本章的示例将使用一个名为example3.xlsx的工作表，该工作表存储在当前工作目录中。您可以自己创建该工作表或从本书的在线资源中下载它。图 14-1 显示了名为Sheet1、Sheet2和Sheet3的三个工作表的标签页。

图 14-1：工作簿的工作表标签位于 Excel 的左下角。

示例文件中的Sheet1应类似于表 14-1。（如果您没有下载example3.xlsx，您应自己将此数据输入到工作表中。）

表 14-1：example3.xlsx 工作表

	A	B	C
1	4/5/2035 1:34:02 PM	Apples	73
2	4/5/2035 3:41:23 AM	Cherries	85
3	4/6/2035 12:46:51 PM	Pears	14
4	4/8/2035 8:59:43 AM	Oranges	52
5	4/10/2035 2:07:00 AM	Apples	152
6	4/10/2035 6:10:37 PM	Bananas	23
7	4/10/2035 2:40:46 AM	Strawberries	98

现在我们有了示例工作表，让我们看看如何使用openpyxl模块来操作它。

打开工作簿

一旦您导入了openpyxl模块，您将能够使用openpyxl.load_workbook()函数打开.xlsx文件。在交互式外壳中输入以下内容：

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

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

从工作簿中获取工作表

您可以通过访问sheetnames属性来获取工作簿中所有工作表名称的列表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> wb.sheetnames # The workbook's sheets' names
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3'] # Get a sheet from the workbook.
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title # Get the sheet's title as a string.
'Sheet3'
>>> another_sheet = wb.active # Get the active sheet.
>>> another_sheet
<Worksheet "Sheet1">

每个工作表由一个Worksheet对象表示，您可以通过使用带有工作表名称字符串的方括号来获取它，就像字典键一样。最后，您可以使用Workbook对象的active属性来获取工作簿的活动工作表。活动工作表是当工作簿在 Excel 中打开时位于顶部的工作表。一旦您有了Worksheet对象，您可以从title属性中获取其名称。

从工作表中获取单元格

一旦您有了Worksheet对象，您可以通过其名称访问一个Cell对象。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1'] # Get a sheet from the workbook.
>>> sheet['A1'] # Get a cell from the sheet.
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value # Get the value from the cell.
datetime.datetime(2035, 4, 5, 13, 34, 2)
>>> c = sheet['B1'] # Get another cell from the sheet.
>>> c.value
'Apples'
>>>  # Get the row, column, and value from the cell.
>>> f'Row {c.row}, Column {c.column} is {c.value}'
'Row 1, Column 2 is Apples'
>>> f'Cell {c.coordinate} is {c.value}'
'Cell B1 is Apples'
>>> sheet['C1'].value
73

Cell 对象有一个 value 属性，它意外地包含存储在该单元格中的值。它还有一个 row、column 和 coordinate 属性，这些属性提供了单元格的位置信息。在这里，访问我们 Cell 对象单元格 B1 的 value 属性给我们字符串 'Apples'。row 属性给我们整数 1，column 属性给我们 2，coordinate 属性给我们 'B1'。

openpyxl 模块将自动将列 A 中的日期解释为 datetime 值，而不是字符串。第十九章进一步解释了 datetime 数据类型。

通过字母指定列可能难以编程，特别是因为在列 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): # Go through every other row.
...     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 并打印奇数行号中每个单元格的值。通过为 range() 函数的“步长”参数传递 2，您可以获取每行第二个单元格（在这种情况下，所有奇数行）。此示例将 for 循环的 i 变量作为 cell() 方法的 row 关键字参数传递，并在每次调用方法时使用 2 作为 column 关键字参数。请注意，此方法接受整数 2，而不是字符串 'B'。

您可以使用 Worksheet 对象的 max_row 和 max_column 属性来确定工作表的大小。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.max_row # Get the highest row number.
7
>>> sheet.max_column # Get the highest column number.
3

注意，max_column 属性是整数，而不是 Excel 中出现的字母。

在列字母和数字之间转换

要从数字转换为字母，请调用 openpyxl.utils.get_column_letter() 函数。要从字母转换为数字，请调用 openpyxl.utils.column_index_from_string() 函数。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1)  # Translate column 1 to a letter.
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A') # Get A's number.
1
>>> column_index_from_string('AA')
27

在从 openpyxl.utils 模块导入这两个函数之后，您可以调用 get_column_letter() 并传入一个整数，例如 27，以确定第 27 列的字母名称。函数 column_index_from_string() 执行相反的操作：您传入列的字母名称，它告诉您该列的数字。您不需要加载工作簿即可使用这些函数。

获取行和列

您可以通过切片 Worksheet 对象来获取工作表中行、列或矩形区域的全部 Cell 对象。然后，您可以遍历切片中的所有单元格。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet['A1':'C3'] # Get cells A1 to 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 row_of_cell_objects in sheet['A1':'C3']: ❶
...     for cell_obj in row_of_cell_objects: ❷
...         print(cell_obj.coordinate, cell_obj.value)
...     print('--- END OF ROW ---')
...
A1 2035-04-05 13:34:02
B1 Apples
C1 73
--- END OF ROW ---
A2 2035-04-05 03:41:23
B2 Cherries
C2 85
--- END OF ROW ---
A3 2035-04-06 12:46:51
B3 Pears
C3 14
--- END OF ROW ---

在这里，我们指定['A1':'C3']以获取从 A1 到 C3 的矩形区域中的Cell对象切片，并且我们得到一个包含该区域Cell对象的元组。

此元组包含三个元组：每个元组代表一个行，从所需区域的顶部到底部。这三个内部元组中的每一个都包含我们所需区域的一行的Cell对象，从最左边的单元格到最右边的单元格。因此，我们的工作表切片包含从 A1 到 C3 区域的全部Cell对象，从左上角单元格开始，到右下角单元格结束。

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

要访问特定行或列的单元格值，您还可以使用Worksheet对象的rows和columns属性。在使用方括号和索引之前，必须使用list()函数将这些属性转换为列表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> list(sheet.columns)[1] # Get the second column's cells.
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell
'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cell_obj in list(sheet.columns)[1]:
...     print(cell_obj.value)
...
Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

在Worksheet对象上使用rows属性，并将其传递给list()，将给我们一个元组列表。这些元组中的每一个都代表一行，并包含该行的Cell对象。将columns属性传递给list()也给我们一个元组列表，每个元组包含特定列的Cell对象。例如，对于example3.xlsx，因为有七行和三列，list(sheet.rows)给我们一个包含七个元组的列表（每个元组包含三个Cell对象），而list(sheet.columns)给我们一个包含三个元组的列表（每个元组包含七个Cell对象）。

要访问特定的元组，您可以引用它在较大元组中的索引。例如，要获取表示列 B 的元组，您将使用list(sheet.columns)[1]。要获取包含列 ACell对象的元组，您将使用list(sheet.columns)[0]。一旦您有一个表示一行或一列的元组，您就可以遍历其Cell对象并打印它们的值。

项目 9：收集人口普查统计数据

假设您有一个包含 2010 年美国人口普查数据的电子表格，并且您被分配了一个无聊的任务，即遍历其数千行以计算每个县的人口和人口普查区的数量。（人口普查区是为了普查目的而定义的地理区域。）每一行代表一个单独的人口普查区。我们将电子表格文件命名为censuspopdata.xlsx，您可以从本书的在线资源中下载它。其内容如图 14-2 所示。

图 14-2：censuspopdata.xlsx 电子表格

即使 Excel 可以自动计算多个选中单元格的总和，你仍然需要手动为 3,000 多个县中的每一个选择单元格。即使手动计算一个县的常住人口只需几秒钟，但整个电子表格的计算也需要数小时。

在这个项目中，你需要编写一个脚本，可以从人口普查电子表格文件中读取，并在几秒钟内为每个县计算统计数据。

这就是你的程序所做的事情：

从 Excel 电子表格中读取数据
统计每个县的人口普查区数量
统计每个县的总人口
打印结果

这意味着你的代码需要执行以下操作：

使用 openpyxl 模块打开并读取 Excel 文档的单元格。
计算所有人口普查区和人口数据，并将其存储在数据结构中。
使用 pprint 模块将数据结构写入具有 .py 扩展名的文本文件，以便以后导入。

第 1 步：读取电子表格数据

在 censuspopdata.xlsx 电子表格中只有一个名为 'Population by Census Tract' 的表，表中的每一行都包含一个单独的人口普查区的数据。列包括人口普查区编号（A）、州缩写（B）、县名（C）和人口普查区的人口（D）。

打开一个新的文件编辑标签，输入以下代码，然后将其保存为 readCensusExcel.py：

# readCensusExcel.py - Tabulates county population and census tracts

import openpyxl, pprint # ❶
print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx') # ❷
sheet = wb['Population by Census Tract'] # ❸
county_data = {}

# TODO: Fill in county_data with each county's population and tracts.
print('Reading rows...')
for row in range(2, sheet.max_row + 1): # ❹
    # Each row in the spreadsheet has data for one census tract.
    state  = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop    = sheet['D' + str(row)].value

# TODO: Open a new text file and write the contents of county_data to it.

此代码导入 openpyxl 模块以及你将用于打印最终县数据的 pprint 模块 ❶。然后，它打开 censuspopdata.xlsx 文件 ❷，获取包含人口普查数据的表 ❸，并开始遍历其行 ❹。

注意，你已创建了一个名为 county_data 的变量，它将包含你为每个县计算的人口和人口普查区数量。然而，在你可以将其存储之前，你应该确切地确定你将如何在该变量内部结构化数据。

第 2 步：填充数据结构

在美国，各州有两个字母的缩写，并进一步划分为县。存储在 county_data 中的数据结构将是一个字典，其键为州缩写。每个州缩写将映射到另一个字典，其键为该州县名的字符串。每个县名将依次映射到一个只包含两个键的字典，即 'tracts' 和 'pop'。这些键映射到县的人口普查区数量和人口。例如，该字典看起来可能如下所示：

{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
        'Aleutians West': {'pop': 5561, 'tracts': 2},
        'Anchorage': {'pop': 291826, 'tracts': 55},
        'Bethel': {'pop': 17013, 'tracts': 3},
        'Bristol Bay': {'pop': 997, 'tracts': 1},
        # --snip--

如果之前的字典存储在 county_data 中，以下表达式将评估如下：

>>> county_data['AK']['Anchorage']['pop']
291826
>>> county_data['AK']['Anchorage']['tracts']
55

更一般地，county_data 字典的键将看起来如下：

county_data[`state abbrev`][`county`]['tracts']
county_data[`state abbrev`][`county`]['pop']

现在你已经知道了 county_data 的结构，你可以编写将填充其内容的代码。将以下代码添加到程序底部：

# readCensusExcel.py - Tabulates county population and census tracts

# --snip--

for row in range(2, sheet.max_row + 1):
    # Each row in the spreadsheet has data for one census tract.
    state  = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop    = sheet['D' + str(row)].value

 # Make sure the key for this state exists.
county_data.setdefault(state, {}) # ❶
 # Make sure the key for this county in this state exists.
county_data[state].setdefault(county, {'tracts': 0, 'pop': 0}) # ❷

 # Each row represents one census tract, so increment by one.
county_data[state][county]['tracts'] += 1 # ❸
 # Increase the county pop by the pop in this census tract.
county_data[state][county]['pop'] += int(pop) # ❹

# TODO: Open a new text file and write the contents of county_data to it.

代码的最后两行执行实际的计算工作，在for循环的每次迭代中增加tracts ❸的值和pop ❹的值，针对当前县。

其他代码存在是因为您不能在county_data中存在键之前将县字典作为州缩写键的值。也就是说，如果'AK'键尚不存在，county_data['AK']['Anchorage']['tracts'] += 1将导致错误。为了确保州缩写键存在于您的数据结构中，您需要调用setdefault()方法来设置一个值，如果state ❶尚未存在。

正如county_data字典需要字典作为每个州缩写键的值一样，每个这些字典也将需要其自己的字典作为每个县键的值 ❷。然后，这些字典中的每个也将需要以整数值0开头的'tracts'和'pop'键。（如果您迷失了字典结构，请回顾本节开头的示例字典。）

由于setdefault()在键已存在时不会执行任何操作，因此您可以在for循环的每次迭代中调用它而不会出现问题。

第 3 步：将结果写入文件

在for循环完成后，county_data字典将包含所有按县和州键索引的人口和区域信息。此时，您可以编写更多代码将此数据写入文本文件或另一个 Excel 电子表格。现在，让我们只使用pprint.pformat()函数将county_data字典值作为一个大字符串写入名为census2010.py的文件。将以下代码添加到程序底部（确保不要缩进，以便它保持在for循环之外）：

# readCensusExcel.py - Tabulates county population and census tracts.

# --snip--

# Open a new text file and write the contents of county_data to it.
print('Writing results...')
result_file = open('census2010.py', 'w')
result_file.write('allData = ' + pprint.pformat(county_data))
result_file.close()
print('Done.')

pprint.pformat()函数生成一个格式化为有效 Python 代码的字符串。通过将其输出到名为census2010.py的文本文件中，您已经从您的 Python 程序中生成了一个 Python 程序！这听起来可能很复杂，但优点是您现在可以像导入任何其他 Python 模块一样导入census2010.py。在交互式 shell 中，将当前工作目录更改为包含您新创建的census2010.py文件的文件夹，然后导入它：

>>> import census2010
>>> census2010.allData['AK']['Anchorage']
{'pop': 291826, 'tracts': 55}
>>> anchorage_pop = census2010.allData['AK']['Anchorage']['pop']
>>> print('The 2010 population of Anchorage was ' + str(anchorage_pop))
The 2010 population of Anchorage was 291826

readCensusExcel.py程序是废弃代码：一旦您将其结果保存到census2010.py中，您就不再需要运行该程序。无论何时需要县数据，您只需运行import census2010。

手动计算这些数据需要数小时；这个程序在几秒钟内就完成了。使用openpyxl，您将能够轻松提取保存到 Excel 电子表格中的信息，并对其进行计算。您可以从本书的在线资源中下载完整的程序。

相似程序的思路

在电子表格中比较多行的数据。
打开多个 Excel 文件，并在电子表格之间比较数据。
检查电子表格是否有空白行或任何单元格中的无效数据，并在有这种情况时提醒用户。
从电子表格中读取数据并将其用作 Python 程序的输入。

打开工作簿

一旦您导入了openpyxl模块，您就可以使用openpyxl.load_workbook()函数打开.xlsx文件。在交互式外壳中输入以下内容：

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

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

从工作簿中获取工作表

您可以通过访问sheetnames属性来获取工作簿中所有工作表的名称列表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> wb.sheetnames # The workbook's sheets' names
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3'] # Get a sheet from the workbook.
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title # Get the sheet's title as a string.
'Sheet3'
>>> another_sheet = wb.active # Get the active sheet.
>>> another_sheet
<Worksheet "Sheet1">

每个工作表都由一个Worksheet对象表示，您可以通过使用带有工作表名称字符串的方括号来获取它，就像字典键一样。最后，您可以使用Workbook对象的active属性来获取工作簿的活动工作表。活动工作表是当工作簿在 Excel 中打开时位于顶部的工作表。一旦您有了Worksheet对象，您就可以通过其title属性获取其名称。

从工作表中获取单元格

一旦您有了Worksheet对象，您就可以通过其名称访问一个Cell对象。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1'] # Get a sheet from the workbook.
>>> sheet['A1'] # Get a cell from the sheet.
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value # Get the value from the cell.
datetime.datetime(2035, 4, 5, 13, 34, 2)
>>> c = sheet['B1'] # Get another cell from the sheet.
>>> c.value
'Apples'
>>>  # Get the row, column, and value from the cell.
>>> f'Row {c.row}, Column {c.column} is {c.value}'
'Row 1, Column 2 is Apples'
>>> f'Cell {c.coordinate} is {c.value}'
'Cell B1 is Apples'
>>> sheet['C1'].value
73

Cell对象有一个包含在该单元格中存储的值的value属性，不出所料。它还具有row、column和coordinate属性，这些属性提供了单元格的位置信息。在这里，访问我们的Cell对象单元格 B1 的value属性给我们字符串'Apples'。row属性给我们整数1，column属性给我们2，coordinate属性给我们'B1'。

openpyxl模块将自动将列 A 中的日期解释为datetime值，而不是字符串。第十九章进一步解释了datetime数据类型。

通过字母指定列可能难以编程，尤其是在 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): # Go through every other row.
...     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 并打印奇数行号中的每个单元格的值。通过为 range() 函数的“步长”参数传递 2，您可以获取每行的单元格（在这种情况下，所有奇数行）。此示例将 for 循环的 i 变量作为 cell() 方法的 row 关键字参数传递，并在每次调用方法时使用 2 作为 column 关键字参数。请注意，此方法接受整数 2，而不是字符串 'B'。

您可以使用 Worksheet 对象的 max_row 和 max_column 属性来确定工作表的大小。在交互式 shell 中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.max_row # Get the highest row number.
7
>>> sheet.max_column # Get the highest column number.
3

注意，max_column 属性是一个整数，而不是 Excel 中出现的字母。

在列字母和数字之间转换

要将数字转换为字母，调用 openpyxl.utils.get_column_letter() 函数。要将字母转换为数字，调用 openpyxl.utils.column_index_from_string() 函数。在交互式 shell 中输入以下内容：

>>> import openpyxl
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1)  # Translate column 1 to a letter.
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A') # Get A's number.
1
>>> column_index_from_string('AA')
27

在从 openpyxl.utils 模块导入这两个函数之后，您可以调用 get_column_letter() 并传递一个整数，例如 27，以确定第 27 列的字母名称。函数 column_index_from_string() 执行相反的操作：您传递给它一个列的字母名称，它告诉您该列的数字。您不需要加载工作簿即可使用这些函数。

获取行和列

您可以通过切片 Worksheet 对象来获取工作表中一行、一列或矩形区域的所有 Cell 对象。然后，您可以遍历切片中的所有单元格。在交互式 shell 中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet['A1':'C3'] # Get cells A1 to 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 row_of_cell_objects in sheet['A1':'C3']: ❶
...     for cell_obj in row_of_cell_objects: ❷
...         print(cell_obj.coordinate, cell_obj.value)
...     print('--- END OF ROW ---')
...
A1 2035-04-05 13:34:02
B1 Apples
C1 73
--- END OF ROW ---
A2 2035-04-05 03:41:23
B2 Cherries
C2 85
--- END OF ROW ---
A3 2035-04-06 12:46:51
B3 Pears
C3 14
--- END OF ROW ---

在这里，我们指定 ['A1':'C3'] 以获取从 A1 到 C3 的矩形区域中的 Cell 对象的切片，并得到一个包含该区域 Cell 对象的元组。

此元组包含三个元组：每个元组对应于所需区域的每一行，从顶部到底部。这三个内部元组中的每一个都包含我们所需区域的一行的 Cell 对象，从最左边的单元格到最右边的单元格。因此，我们的工作表切片包含从 A1 到 C3 的区域中的所有 Cell 对象，从左上角单元格开始，到右下角单元格结束。

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

要访问特定行或列中单元格的值，你可以使用Worksheet对象的rows和columns属性。在使用方括号和索引之前，必须使用list()函数将这些属性转换为列表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> list(sheet.columns)[1] # Get the second column's cells.
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell
'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cell_obj in list(sheet.columns)[1]:
...     print(cell_obj.value)
...
Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

使用传递给list()的Worksheet对象的rows属性，将给我们一个元组列表。每个元组代表一行，并包含该行中的Cell对象。传递给list()的columns属性也给我们一个元组列表，其中每个元组包含特定列中的Cell对象。对于example3.xlsx，因为有七行和三列，list(sheet.rows)给我们一个包含七个元组的列表（每个元组包含三个Cell对象），而list(sheet.columns)给我们一个包含三个元组的列表（每个元组包含七个Cell对象）。

要访问一个特定的元组，你可以通过其在较大元组中的索引来引用它。例如，要获取表示列 B 的元组，你会使用list(sheet.columns)[1]。要获取包含列 A 中Cell对象的元组，你会使用list(sheet.columns)[0]。一旦你有一个表示一行或一列的元组，你可以遍历其Cell对象并打印它们的值。

项目 9：收集人口普查统计数据

假设你有一个 2010 年美国人口普查的数据工作表，并且你被分配了一个无聊的任务，就是逐行检查数千行数据，计算每个县的常住人口和人口普查区的数量。（人口普查区是为了普查目的而定义的地理区域。）每一行代表一个单独的人口普查区。我们将工作表文件命名为censuspopdata.xlsx，你可以从本书的在线资源中下载它。其内容如图 14-2 所示。

图 14-2：censuspopdata.xlsx 工作表

尽管 Excel 可以自动计算多个选中单元格的总和，但你仍然需要首先手动选择 3,000 多个县中的每个单元格。即使手动计算一个县的常住人口只需几秒钟，但整个工作表的计算也需要数小时。

在这个项目中，你需要编写一个脚本，可以从人口普查工作表文件中读取，并在几秒钟内计算每个县的统计数据。

这就是你的程序要做的：

从 Excel 工作表中读取数据
计算每个县的普查区数量
计算每个县的总人口
打印结果

这意味着你的代码需要执行以下操作：

使用openpyxl模块打开并读取 Excel 文档的单元格。
计算所有人口普查区和人口数据，并将其存储在数据结构中。
使用pprint模块将数据结构写入具有.py扩展名的文本文件，以便以后导入。

第一步：读取电子表格数据

在censuspopdata.xlsx电子表格中只有一张工作表，名为'Population by Census Tract'，并且工作表中的每一行都包含一个单一人口普查区的数据。列包括区域编号（A）、州缩写（B）、县名（C）和区域人口（D）。

打开一个新的文件编辑标签，输入以下代码，然后将其保存为readCensusExcel.py：

# readCensusExcel.py - Tabulates county population and census tracts

import openpyxl, pprint # ❶
print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx') # ❷
sheet = wb['Population by Census Tract'] # ❸
county_data = {}

# TODO: Fill in county_data with each county's population and tracts.
print('Reading rows...')
for row in range(2, sheet.max_row + 1): # ❹
    # Each row in the spreadsheet has data for one census tract.
    state  = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop    = sheet['D' + str(row)].value

# TODO: Open a new text file and write the contents of county_data to it.

此代码导入了openpyxl模块，以及你将用于打印最终县数据的pprint模块❶。然后，它打开censuspopdata.xlsx文件❷，获取包含人口普查数据的表❸，并开始遍历其行❹。

注意，你已创建了一个名为county_data的变量，它将包含你为每个县计算的人口和区域数量。然而，在你可以存储任何内容之前，你应该确定你将如何结构化其中的数据。

第二步：填充数据结构

在美国，各州有两个字母的缩写，并进一步划分为县。存储在county_data中的数据结构将是一个字典，其键为州缩写。每个州缩写将映射到另一个字典，其键是该州县名的字符串。每个县名将依次映射到一个只包含两个键的字典，即'tracts'和'pop'。这些键映射到县的人口普查区和人口。例如，该字典看起来可能如下所示：

{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
        'Aleutians West': {'pop': 5561, 'tracts': 2},
        'Anchorage': {'pop': 291826, 'tracts': 55},
        'Bethel': {'pop': 17013, 'tracts': 3},
        'Bristol Bay': {'pop': 997, 'tracts': 1},
        # --snip--

如果之前的字典存储在county_data中，以下表达式将评估如下：

>>> county_data['AK']['Anchorage']['pop']
291826
>>> county_data['AK']['Anchorage']['tracts']
55

更一般地，county_data字典的键将看起来像这样：

county_data[`state abbrev`][`county`]['tracts']
county_data[`state abbrev`][`county`]['pop']

现在你已经知道了county_data的结构，你可以编写将县数据填充到其中的代码。将以下代码添加到程序底部：

# readCensusExcel.py - Tabulates county population and census tracts

# --snip--

for row in range(2, sheet.max_row + 1):
    # Each row in the spreadsheet has data for one census tract.
    state  = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop    = sheet['D' + str(row)].value

 # Make sure the key for this state exists.
county_data.setdefault(state, {}) # ❶
 # Make sure the key for this county in this state exists.
county_data[state].setdefault(county, {'tracts': 0, 'pop': 0}) # ❷

 # Each row represents one census tract, so increment by one.
county_data[state][county]['tracts'] += 1 # ❸
 # Increase the county pop by the pop in this census tract.
county_data[state][county]['pop'] += int(pop) # ❹

# TODO: Open a new text file and write the contents of county_data to it.

最后两行代码执行实际的计算工作，在每次for循环迭代中对当前县的tracts值进行递增❸，并对pop值进行递增❹。

其他代码存在是因为你不能在county_data中添加作为州缩写键值的县字典，直到该键本身存在于county_data中。（也就是说，如果'AK'键尚不存在，则'county_data['AK']['Anchorage']['tracts']' += '1'将引发错误。）为了确保州缩写键存在于你的数据结构中，你需要调用setdefault()方法来设置一个值，如果该值对于state尚不存在❶。

正如 county_data 字典需要字典作为每个州简称键的值一样，每个这些字典将需要其自己的字典作为每个县键的值 ❷。然后，这些字典中的每个也将需要以整数值 0 开头的键 'tracts' 和 'pop'。（如果你失去了对字典结构的跟踪，请回顾本节开头示例中的字典。）

由于 setdefault() 如果键已存在则不会执行任何操作，因此你可以在 for 循环的每次迭代中调用它而不会出现问题。

第 3 步：将结果写入文件

在 for 循环完成后，county_data 字典将包含所有按县和州键索引的人口和区域信息。此时，你可以编写更多代码将此数据写入文本文件或另一个 Excel 电子表格。现在，让我们使用 pprint.pformat() 函数将 county_data 字典值作为一个大字符串写入名为 census2010.py 的文件。将以下代码添加到程序底部（确保不要缩进，以便它保持在 for 循环之外）：

# readCensusExcel.py - Tabulates county population and census tracts.

# --snip--

# Open a new text file and write the contents of county_data to it.
print('Writing results...')
result_file = open('census2010.py', 'w')
result_file.write('allData = ' + pprint.pformat(county_data))
result_file.close()
print('Done.')

pprint.pformat() 函数产生一个格式化为有效 Python 代码的字符串。通过将其输出到名为 census2010.py 的文本文件，你已经从你的 Python 程序中生成了一个 Python 程序！这看起来可能很复杂，但优点是现在你可以像导入任何其他 Python 模块一样导入 census2010.py。在交互式外壳中，将当前工作目录更改为包含你新创建的 census2010.py 文件的文件夹，然后导入它：

>>> import census2010
>>> census2010.allData['AK']['Anchorage']
{'pop': 291826, 'tracts': 55}
>>> anchorage_pop = census2010.allData['AK']['Anchorage']['pop']
>>> print('The 2010 population of Anchorage was ' + str(anchorage_pop))
The 2010 population of Anchorage was 291826

readCensusExcel.py 程序是临时代码：一旦你将结果保存到 census2010.py，你就不需要再次运行该程序。每次你需要县数据时，你只需运行 import census2010。

手动计算这些数据需要数小时；这个程序在几秒钟内就完成了。使用 openpyxl，你将能够轻松地从 Excel 电子表格中提取信息并对其进行计算。你可以从本书的在线资源中下载完整的程序。

相似程序的思路

许多企业和办公室使用 Excel 存储各种类型的数据，电子表格变得庞大且难以操作并不罕见。任何解析 Excel 电子表格的程序都有类似的架构：它加载电子表格文件，准备一些变量或数据结构，然后遍历电子表格中的每一行。这样的程序可以执行以下操作：

比较电子表格中多行的数据。
打开多个 Excel 文件，比较电子表格之间的数据。
检查电子表格是否有空白行或任何单元格中的无效数据，并在有这种情况时提醒用户。
从电子表格中读取数据并将其用作 Python 程序的输入。

编写 Excel 文档

openpyxl模块还提供了写入数据的方法，这意味着您的程序可以创建和编辑电子表格文件。使用 Python，创建包含数千行数据的电子表格非常简单。

创建和保存 Excel 文件

调用openpyxl.Workbook()函数创建一个新的、空的Workbook对象。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook() # Create a blank workbook.
>>> wb.sheetnames # The workbook starts with one sheet.
['Sheet']
>>> sheet = wb.active
>>> sheet.title
'Sheet'
>>> sheet.title = 'Spam Bacon Eggs Sheet' # Change the title.
>>> wb.sheetnames
['Spam Bacon Eggs Sheet']

工作簿将从一个名为Sheet的单个工作表开始。您可以通过在其title属性中存储一个新的字符串来更改工作表的名称。

任何时候您修改Workbook对象或其工作表和单元格，电子表格文件将不会保存，直到您调用save()工作簿方法。在交互式外壳中输入以下内容（在当前工作目录中为example3.xlsx）：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.title = 'Spam Spam Spam'
>>> wb.save('example3_copy.xlsx') # Save the workbook.

在这里，我们更改了我们工作表的名称。为了保存我们的更改，我们将一个字符串文件名传递给save()方法。传递与原始文件名不同的文件名，例如'example3_copy.xlsx'，将更改保存到电子表格的副本中。

每次您编辑从文件加载的电子表格时，您都应该使用与原始文件不同的文件名保存新的、编辑过的电子表格。这样，在您的代码中存在错误导致新保存的文件包含不正确或损坏的数据时，您仍然可以保留原始电子表格文件以供工作。此外，如果电子表格当前在 Excel 桌面应用程序中打开，save() 方法将无法工作。您必须首先关闭电子表格，然后运行您的 Python 程序。

创建和删除工作表

您可以使用create_sheet()方法和del运算符创建或删除工作簿中的工作表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> wb.create_sheet() # Add a new sheet.
<Worksheet "Sheet1">
>>> wb.sheetnames
['Sheet', 'Sheet1']
>>> # Create a new sheet at index 0.
>>> wb.create_sheet(index=0, title='First Sheet')
<Worksheet "First Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Sheet1']
>>> wb.create_sheet(index=2, title='Middle Sheet')
<Worksheet "Middle Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']

create_sheet()方法返回一个名为SheetX 的新Worksheet对象，默认情况下它是工作簿中的最后一个工作表。您可以选择使用index和title关键字参数指定新工作表的索引和名称。

通过输入以下内容继续上一个示例：

>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']
>>> del wb['Middle Sheet']
>>> del wb['Sheet1']
>>> wb.sheetnames
['First Sheet', 'Sheet']

您可以使用del运算符从工作簿中删除一个工作表，就像您可以使用它从字典中删除键值对一样。

记得在向工作簿中添加工作表或从工作簿中删除工作表后调用save()方法来保存更改。

将值写入单元格

将值写入单元格与将值写入字典中的键类似。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Hello, world!' # Edit the cell's value.
>>> sheet['A1'].value
'Hello, world!'

如果您有一个单元格的坐标作为字符串，您可以使用它就像在Worksheet对象上使用字典键一样来指定要写入哪个单元格。

项目 10：更新电子表格

在这个项目中，您将编写一个程序来更新销售产品的电子表格中的单元格。您的程序将遍历电子表格，找到特定的产品种类，并更新它们的价格。从本书的在线资源下载此produceSales3.xlsx电子表格。图 14-3 显示了电子表格的外观。

图 14-3：产品销售电子表格

每一行代表一次单独的销售。列是销售产品的类型（A）、该产品的每磅成本（B）、售出的磅数（C）和销售的总收入（D）。总收入列设置为 Excel 公式如 =ROUND(B2C2, 2)*，该公式将行的每磅成本乘以售出的磅数，并将结果四舍五入到最接近的美分。使用此公式，如果成本每磅和售出磅数列发生变化，总收入列的单元格将自动更新。

现在想象一下，大蒜、芹菜和柠檬的价格输入错误，这让你有了枯燥的任务，需要逐行检查这个电子表格，以更新任何芹菜、大蒜和柠檬行的每磅成本。你不能简单地使用查找和替换来更改价格，因为可能还有其他具有相同价格的项目，你不想错误地“修正”。对于数千行，手动操作需要数小时。但你可以编写一个可以在几秒钟内完成这项任务的程序。

你的程序应该执行以下操作：

遍历所有行。
如果行是关于芹菜、大蒜或柠檬的，则更改价格。

这意味着你的代码需要执行以下操作：

打开电子表格文件。
对于每一行，检查 A 列中的值是否为Celery、Garlic或Lemon。
如果是，更新 B 列中的价格。
将电子表格保存到新文件中（以防万一，不要丢失原始电子表格）。

第 1 步：设置包含更新信息的结构化数据

需要更新的价格如下：

芹菜：1.19
大蒜：3.07
柠檬：1.27

你可以编写代码来设置这些新价格，如下所示：

if produce_name == 'Celery':
    cell_obj = 1.19
if produce_name == 'Garlic':
    cell_obj = 3.07
if produce_name == 'Lemon':
    cell_obj = 1.27

但像这样硬编码产品和更新后的价格数据有点不优雅。如果你需要用不同的价格或不同的产品更新电子表格，你将不得不更改大量代码。每次更改代码，你都有引入错误的风险。

一个更灵活的解决方案是将修正后的价格信息存储在字典中，并编写代码以使用这种数据结构。在新文件编辑标签页中，输入以下代码：

# updateProduce.py - Corrects costs in produce sales spreadsheet

import openpyxl

wb = openpyxl.load_workbook('produceSales3.xlsx')
sheet = wb['Sheet']

# The produce types and their updated prices
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}

# TODO: Loop through the rows and update the prices.

将其保存为 updateProduce.py。如果你需要再次更新电子表格，你只需更新 PRICE_UPDATES 字典，而无需更新任何其他代码。

第 2 步：检查所有行并更新错误的价格

程序的下一部分将遍历电子表格中的所有行。将以下代码添加到 updateProduce.py 的底部：

# updateProduce.py - Corrects costs in produce sales spreadsheet

# --snip--

# Loop through the rows and update the prices.
for row_num in range(2, sheet.max_row + 1): # Skip the first row. # ❶
produce_name = sheet.cell(row=row_num, column=1).value # ❷
    if produce_name in PRICE_UPDATES: # ❸
 sheet.cell(row=row_num, column=2).value = PRICE_UPDATES[produce_name]

wb.save('updatedProduceSales3.xlsx')  # ❹

我们从第 2 行开始循环遍历行，因为第 1 行只是标题❶。第 1 列（即 A 列）的单元格将被存储在变量produce_name❷中。如果produce_name作为PRICE_UPDATES字典中的一个键❸存在，则知道此行需要更正价格。正确的价格将在PRICE_UPDATES[produce_name]中。

注意使用PRICE_UPDATES如何使代码更简洁。它只使用一个if语句，而不是为每种产品更新单独的一行，例如if produce_name == 'Garlic':。由于代码使用PRICE_UPDATES字典而不是将产品名称和更新成本硬编码到for循环中，因此如果需要更改产品销售电子表格，您只需修改PRICE_UPDATES字典，而无需修改代码的其他部分。

在遍历整个电子表格并做出更改后，代码将Workbook对象保存到updatedProduceSales3.xlsx❹。它不会覆盖旧电子表格，以防程序中存在错误且更新的电子表格不正确。在检查更新后的电子表格看起来正确后，您可以删除旧电子表格。

相似程序的思路

由于许多办公人员经常使用 Excel 电子表格，因此一个可以自动编辑和写入 Excel 文件的程序可能非常有用。这样的程序可以执行以下操作：

从一个电子表格中读取数据并将其写入其他电子表格的部分。
从网站、文本文件或剪贴板中读取数据并将其写入电子表格。
自动“清理”电子表格中的数据。例如，它可以使用正则表达式读取多种格式的电话号码并将它们编辑成单一的标准格式。

创建和保存 Excel 文件

调用openpyxl.Workbook()函数来创建一个新的、空的Workbook对象。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook() # Create a blank workbook.
>>> wb.sheetnames # The workbook starts with one sheet.
['Sheet']
>>> sheet = wb.active
>>> sheet.title
'Sheet'
>>> sheet.title = 'Spam Bacon Eggs Sheet' # Change the title.
>>> wb.sheetnames
['Spam Bacon Eggs Sheet']

工作簿将从一个名为Sheet的单个工作表开始。您可以通过存储一个新的字符串在其title属性中来更改工作表的名称。

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example3.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.title = 'Spam Spam Spam'
>>> wb.save('example3_copy.xlsx') # Save the workbook.

在这里，我们更改我们工作表的名称。为了保存我们的更改，我们将一个文件名作为字符串传递给save()方法。传递与原始文件名不同的文件名，例如'example3_copy.xlsx'，将更改保存到工作表的副本中。

每次您编辑从文件加载的电子表格时，您都应该使用与原始文件不同的文件名保存新的、编辑过的电子表格。这样，您仍然有原始的电子表格文件可以工作，以防您的代码中的错误导致新保存的文件包含不正确或损坏的数据。此外，如果电子表格当前在 Excel 桌面应用程序中打开，save() 方法将不起作用。您必须首先关闭电子表格，然后运行您的 Python 程序。

创建和删除工作表

您可以使用 create_sheet() 方法以及 del 操作符来创建或删除工作簿中的工作表。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> wb.create_sheet() # Add a new sheet.
<Worksheet "Sheet1">
>>> wb.sheetnames
['Sheet', 'Sheet1']
>>> # Create a new sheet at index 0.
>>> wb.create_sheet(index=0, title='First Sheet')
<Worksheet "First Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Sheet1']
>>> wb.create_sheet(index=2, title='Middle Sheet')
<Worksheet "Middle Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']

create_sheet() 方法返回一个名为 SheetX 的新 Worksheet 对象，默认情况下它是工作簿中的最后一个工作表。您可以使用 index 和 title 关键字参数来指定新工作表的索引和名称。

通过输入以下内容继续上一个示例：

>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']
>>> del wb['Middle Sheet']
>>> del wb['Sheet1']
>>> wb.sheetnames
['First Sheet', 'Sheet']

您可以使用 del 操作符从工作簿中删除一个工作表，就像您可以使用它从字典中删除键值对一样。

在向工作簿中添加工作表或从工作簿中删除工作表后，请记住调用 save() 方法以保存更改。

将值写入单元格

将值写入单元格与将值写入字典中的键类似。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Hello, world!' # Edit the cell's value.
>>> sheet['A1'].value
'Hello, world!'

如果您有一个单元格坐标的字符串，您可以使用它就像在 Worksheet 对象上使用字典键一样来指定要写入的单元格。

项目 10：更新电子表格

在本项目，您将编写一个程序来更新产品销售电子表格中的单元格。您的程序将遍历电子表格，找到特定的产品种类，并更新它们的价格。从本书的在线资源下载此 produceSales3.xlsx 电子表格。图 14-3 显示了电子表格的外观。

图 14-3：产品销售电子表格

每一行代表一次单独的销售。列包括销售产品的类型（A）、该产品的每磅成本（B）、销售的磅数（C）以及销售的总收入（D）。TOTAL 列设置为类似于 =ROUND(B2C2, 2)* 的 Excel 公式，该公式将每行的每磅成本乘以销售的磅数，并将结果四舍五入到最接近的美分。使用此公式，如果成本每磅和磅数销售列发生变化，TOTAL 列中的单元格将自动更新。

现在想象一下，大蒜、芹菜和柠檬的价格输入错误，您将面临一个无聊的任务，即通过数千行电子表格来更新每磅芹菜、大蒜和柠檬的成本。您不能简单地使用查找和替换来更新价格，因为可能还有其他具有相同价格的项目，您不希望错误地“纠正”。对于数千行，手动完成这项工作将需要数小时。但您可以编写一个可以在几秒钟内完成这项任务的程序。

您的程序应执行以下操作：

遍历所有行。
如果行是关于芹菜、大蒜或柠檬的，则更改价格。

这意味着您的代码需要执行以下操作：

打开电子表格文件。
对于每一行，检查列 A 中的值是否为 Celery、Garlic 或 Lemon。
如果是，更新列 B 中的价格。
将电子表格保存到新文件中（以防万一，这样就不会丢失原始电子表格）。

第 1 步：使用更新信息设置数据结构

需要更新的价格如下：

芹菜：1.19
大蒜：3.07
柠檬：1.27

您可以编写代码来设置这些新价格，如下所示：

if produce_name == 'Celery':
    cell_obj = 1.19
if produce_name == 'Garlic':
    cell_obj = 3.07
if produce_name == 'Lemon':
    cell_obj = 1.27

但像这样硬编码产品和更新价格数据有点不优雅。如果您需要使用不同的价格或不同的产品再次更新电子表格，您将不得不更改大量代码。每次更改代码，您都冒着引入错误的风险。

更灵活的解决方案是将修正后的价格信息存储在字典中，并编写代码以使用这种数据结构。在新文件编辑标签中，输入以下代码：

# updateProduce.py - Corrects costs in produce sales spreadsheet

import openpyxl

wb = openpyxl.load_workbook('produceSales3.xlsx')
sheet = wb['Sheet']

# The produce types and their updated prices
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}

# TODO: Loop through the rows and update the prices.

将其保存为 updateProduce.py。如果您需要再次更新电子表格，您只需更新 PRICE_UPDATES 字典，而无需更新任何其他代码。

第 2 步：检查所有行并更新错误的价格

程序的下一部分将遍历电子表格中的所有行。将以下代码添加到 updateProduce.py 的底部：

# updateProduce.py - Corrects costs in produce sales spreadsheet

# --snip--

# Loop through the rows and update the prices.
for row_num in range(2, sheet.max_row + 1): # Skip the first row. # ❶
produce_name = sheet.cell(row=row_num, column=1).value # ❷
    if produce_name in PRICE_UPDATES: # ❸
 sheet.cell(row=row_num, column=2).value = PRICE_UPDATES[produce_name]

wb.save('updatedProduceSales3.xlsx')  # ❹

我们从第 2 行开始遍历行，因为第 1 行只是标题 ❶。列 1（即列 A）中的单元格将存储在变量 produce_name ❷ 中。如果 produce_name 作为 PRICE_UPDATES 字典中的键存在 ❸，则知道此行需要其价格修正。正确的价格将在 PRICE_UPDATES[produce_name] 中。

注意使用 PRICE_UPDATES 如何使代码更简洁。它只使用一个 if 语句，而不是为每种要更新的产品单独使用一行，如 if produce_name == 'Garlic':。由于代码使用 PRICE_UPDATES 字典而不是将产品名称和更新成本硬编码到 for 循环中，因此如果产品销售电子表格需要额外的更改，您只需修改 PRICE_UPDATES 字典，而无需修改代码的其他部分。

在对整个工作表进行修改后，代码将Workbook对象保存到updatedProduceSales3.xlsx ❹。它不会覆盖旧的工作表，以防程序中存在错误并且更新后的工作表是错误的。在确认更新后的工作表看起来正确后，您可以删除旧的工作表。

类似程序的创意

由于许多办公人员经常使用 Excel 工作表，因此一个可以自动编辑和写入 Excel 文件的程序可能非常有用。这样的程序可以执行以下操作：

从一个工作表中读取数据并将其写入其他工作表的部分。
从网站、文本文件或剪贴板读取数据并将其写入工作表。
自动“清理”工作表中的数据。例如，它可以使用正则表达式读取多个格式的电话号码并将它们编辑为单一的标准格式。

设置单元格的字体样式

通过对某些单元格、行或列进行样式设置，可以帮助您强调工作表中的重要区域。例如，在产品工作表中，您的程序可以为土豆、大蒜和胡萝卜行应用加粗文本。或者，也许您想将每行成本每磅超过 5 美元的行设置为斜体。手动对大型工作表的部分进行样式设置可能会很繁琐，但您的程序可以立即完成。

要在单元格中自定义字体样式，从openpyxl.styles模块导入Font()函数：

from openpyxl.styles import Font

以这种方式导入函数允许您使用Font()而不是openpyxl.styles.Font()。有关更多信息，请参阅第三章的“导入模块”。

以下示例创建了一个新的工作表并将单元格 A1 设置为 24 点的斜体字体：

>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> italic_24_font = Font(size=24, italic=True)  # ❶
>>> sheet['A1'].font = italic_24_font  # ❷
>>> sheet['A1'] = 'Hello, world!'
>>> wb.save('styles3.xlsx')

在此示例中，Font(size=24, italic=True)返回一个Font对象，我们将其存储在italic_24_font ❶。传递给Font()的关键字参数配置对象的样式信息，将italic_24_font对象分配给sheet['A1'].font ❷将所有这些字体样式信息应用于单元格 A1。

要设置font属性，向Font()传递关键字参数。表 14-2 显示了Font()函数的可能关键字参数。

表 14-2：Font对象的关键字参数

关键字参数	数据类型	描述
`name`	字符串	字体名称，例如`'Calibri'`或`'Times New Roman'`
`size`	整数	字体点大小
`bold`	布尔值	`True`，表示加粗字体
`italic`	布尔值	`True`，表示斜体字体

您可以调用Font()来创建Font对象并将该Font对象存储在变量中。然后，将此变量分配给Cell对象的font属性。例如，此代码创建各种字体样式：

>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> bold_font = Font(name='Times New Roman', bold=True)
>>> sheet['A1'].font = bold_font
>>> sheet['A1'] = 'Bold Times New Roman'

>>> italic_font = Font(size=24, italic=True)
>>> sheet['B3'].font = italic_font
>>> sheet['B3'] = '24 pt Italic'

>>> wb.save('styles3.xlsx')

在这里，我们将Font对象存储在bold_font中，然后设置 A1 Cell对象的font属性为bold_font。我们使用另一个Font对象重复此过程以设置第二个单元格的字体。运行此代码后，工作表中的 A1 和 B3 单元格的样式将具有自定义的字体样式，如图 14-4 所示。

图 14-4：具有自定义字体样式的电子表格

公式

以等号开头的 Excel 公式可以配置单元格包含从其他单元格计算得出的值。在本节中，你将使用openpyxl模块以编程方式向单元格添加公式，就像添加任何正常值一样。以下是一个示例：

>>> sheet['B9'] = '=SUM(B1:B8)'

此代码将公式 =SUM(B1:B8) 存储在 B9 中，将单元格的值设置为 B1 到 B8 单元格中值的总和。你可以在图 14-5 中看到这一效果。

图 14-5：单元格 B9 包含将单元格 B1 到 B8 中的值相加的公式。

你可以像在单元格中的任何其他文本值一样设置 Excel 公式。例如，将以下内容输入到交互式外壳中：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 200
>>> sheet['A2'] = 300
>>> sheet['A3'] = '=SUM(A1:A2)' # Set the formula.
>>> wb.save('writeFormula3.xlsx')

A1 和 A2 单元格分别设置为 200 和 300。单元格 A3 中的值设置为公式，该公式将 A1 和 A2 中的值相加。当在 Excel 中打开电子表格时，A3 将显示其值为 500。

openpyxl模块没有计算 Excel 公式并将结果填充到单元格中的能力。然而，如果你在 Excel 中打开这个 writeFormula3.xlsx 文件，Excel 本身将填充单元格中的公式结果。你可以将文件保存为 Excel 格式，然后在打开它时传递data_only=True关键字参数给openpyxl.load_workbook()，单元格值应显示计算结果而不是公式字符串：

>>> # Be sure to open writeFormula3.xlsx in Excel and save it first.
>>> import openpyxl
>>> wb = openpyxl.load_workbook('writeFormula3.xlsx')  # Open without data_only.
>>> wb.active['A3'].value  # Get the formula string.
'=SUM(A1:A2)'
>>> wb = openpyxl.load_workbook('writeFormula3.xlsx', data_only=True)  # Open with data_only.
>>> wb.active['A3'].value  # Get the formula result.
500

再次强调，只有当你以 Excel 打开并保存文件，以便 Excel 可以运行公式计算并将结果存储在电子表格文件中时，你才能在电子表格文件中看到500的结果。这是当你将data_only=True传递给openpyxl.load_workbook()时openpyxl读取的值。

调整行和列

在 Excel 中，调整行和列的大小就像点击并拖动行或列标题的边缘一样简单。但是，如果你需要根据单元格的内容设置行或列的大小，或者如果你想要在大量工作表文件中设置大小，编写一个 Python 程序来做这件事会更快。

你还可以隐藏行和列以供查看，或将它们“冻结”在原位，以便它们始终可见，打印工作表时出现在每一页上（这对于标题很有用）。

设置行高和列宽

Worksheet 对象具有控制行高和列宽的 row_dimensions 和 column_dimensions 属性。例如，在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Tall row'
>>> sheet['B2'] = 'Wide column'
>>> sheet.row_dimensions[1].height = 70
>>> sheet.column_dimensions['B'].width = 20
>>> wb.save('dimensions3.xlsx')

工作表的 row_dimensions 和 column_dimensions 是类似字典的值；row_dimensions 包含 RowDimension 对象，而 column_dimensions 包含 ColumnDimension 对象。在 row_dimensions 中，你可以使用行号（在这种情况下，1 或 2）访问其中一个对象。在 column_dimensions 中，你可以使用列字母（在这种情况下，A 或 B）访问其中一个对象。

dimensions3.xlsx 工作表看起来像图 14-6。

图 14-6：第 1 行和列 B 设置为更大的高度和宽度

Excel 和 openpyxl 的单元格默认宽度和高度在不同版本之间有所不同。

合并和取消合并单元格

你可以使用 merge_cells() 工作表方法将一组矩形单元格合并为一个单元格。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet.merge_cells('A1:D3') # Merge all these cells.
>>> sheet['A1'] = 'Twelve cells merged together.'
>>> sheet.merge_cells('C5:D5') # Merge these two cells.
>>> sheet['C5'] = 'Two merged cells.'
>>> wb.save('merged3.xlsx')

merge_cells() 的参数是要合并的矩形区域的左上角和右下角单元格的单个字符串：'A1:D3' 将 12 个单元格合并为一个单元格。要设置这些合并单元格的值，只需设置合并组左上角单元格的值。

当你运行此代码时，merged.xlsx 将看起来像图 14-7。

图 14-7：工作表中的合并单元格

要取消合并单元格，请调用 unmerge_cells() 工作表方法：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('merged3.xlsx')
>>> sheet = wb['Sheet']
>>> sheet.unmerge_cells('A1:D3') # Split these cells up.
>>> sheet.unmerge_cells('C5:D5')
>>> wb.save('unmerged3.xlsx')

如果你保存了更改并查看工作表，你会看到合并的单元格已经恢复为单个单元格。

冻结窗格

对于太大而无法一次性显示的工作表，冻结屏幕上的一些顶部行或最左侧列是有帮助的。例如，冻结的列或行标题即使在用户滚动通过工作表时也始终可见。这些被称为 冻结窗格。

在 openpyxl 中，每个 Worksheet 对象都有一个 freeze_panes 属性，你可以将其设置为 Cell 对象或单元格坐标的字符串。请注意，此属性将冻结此单元格上方所有行以及左侧所有列，但不会冻结单元格本身的行和列。要取消冻结所有窗格，请将 freeze_panes 设置为 None 或 'A1'。表 14-3 展示了 freeze_panes 的某些示例设置会冻结哪些行和列。

表 14-3：冻结窗格示例

freeze_panes 设置	冻结的行和列
`sheet.freeze_panes` `=` `'A2'`	第一行（没有冻结列）
`sheet.freeze_panes` `=` `'B1'`	列 A（没有冻结行）
`sheet.freeze_panes` `=` `'C1'`	列 A 和 B（没有冻结行）
`sheet.freeze_panes` `=` `'C2'`	第一行和列 A 和 B
`sheet.freeze_panes` `=` `'A1'` 或 `sheet.freeze_panes` `=` `None`	没有冻结行或列

下载 produceSales3.xlsx 电子表格的另一个副本，然后在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('produceSales3.xlsx')
>>> sheet = wb.active
>>> sheet.freeze_panes = 'A2' # Freeze the rows above A2.
>>> wb.save('freezeExample3.xlsx')

你可以在图 14-8 中看到结果。

图 14-8：冻结第一行

由于你将 freeze_panes 属性设置为 'A2'，因此无论用户在电子表格中滚动到何处，第一行都将保持可见。

设置行高和列宽

Worksheet 对象有 row_dimensions 和 column_dimensions 属性，用于控制行高和列宽。例如，在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Tall row'
>>> sheet['B2'] = 'Wide column'
>>> sheet.row_dimensions[1].height = 70
>>> sheet.column_dimensions['B'].width = 20
>>> wb.save('dimensions3.xlsx')

工作表的 row_dimensions 和 column_dimensions 是类似字典的值；row_dimensions 包含 RowDimension 对象，而 column_dimensions 包含 ColumnDimension 对象。在 row_dimensions 中，你可以使用行号（在这种情况下，为 1 或 2）访问其中一个对象。在 column_dimensions 中，你可以使用列字母（在这种情况下，为 A 或 B）访问其中一个对象。

dimensions3.xlsx 电子表格看起来像图 14-6。

图 14-6：第一行和 B 列设置为更大的高度和宽度

单元格的默认宽度和高度在不同版本的 Excel 和 openpyxl 之间有所不同。

合并和取消合并单元格

你可以使用 merge_cells() 工作表方法将一组矩形单元格合并为一个单元格。在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet.merge_cells('A1:D3') # Merge all these cells.
>>> sheet['A1'] = 'Twelve cells merged together.'
>>> sheet.merge_cells('C5:D5') # Merge these two cells.
>>> sheet['C5'] = 'Two merged cells.'
>>> wb.save('merged3.xlsx')

merge_cells() 的参数是合并矩形区域左上角和右下角单元格的单个字符串：'A1:D3' 将 12 个单元格合并为一个单元格。要设置这些合并单元格的值，只需设置合并组左上角单元格的值。

当你运行此代码时，merged.xlsx 将看起来像图 14-7。

图 14-7：工作表中的合并单元格

要取消合并单元格，请调用 unmerge_cells() 工作表方法：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('merged3.xlsx')
>>> sheet = wb['Sheet']
>>> sheet.unmerge_cells('A1:D3') # Split these cells up.
>>> sheet.unmerge_cells('C5:D5')
>>> wb.save('unmerged3.xlsx')

如果你保存了更改并查看工作表，你会看到合并的单元格已经恢复为单个单元格。

冻结窗格

对于太大而无法一次性显示的工作表，将屏幕上的顶部几行或最左侧的几列“冻结”是有帮助的。例如，冻结的列或行标题，即使在用户滚动通过工作表时，用户也能始终看到它们。这些被称为 冻结窗格。

在 openpyxl 中，每个 Worksheet 对象都有一个 freeze_panes 属性，你可以将其设置为 Cell 对象或单元格坐标的字符串。请注意，此属性将冻结此单元格以上的所有行和此单元格左侧的所有列，但不会冻结单元格本身的行和列。要将所有窗格解冻，将 freeze_panes 设置为 None 或 'A1'。表 14-3 显示了 freeze_panes 的某些示例设置会冻结哪些行和列。

表 14-3：冻结窗格示例

freeze_panes 设置	冻结的行和列
`sheet.freeze_panes` `=` `'A2'`	第 1 行（没有冻结列）
`sheet.freeze_panes` `=` `'B1'`	A 列（没有冻结行）
`sheet.freeze_panes` `=` `'C1'`	A 列和 B 列（没有冻结行）
`sheet.freeze_panes` `=` `'C2'`	第 1 行和 A 列及 B 列
`sheet.freeze_panes` `=` `'A1'` 或 `sheet.freeze_panes` `=` `None`	没有冻结的行或列

下载另一个副本的 produceSales3.xlsx 工作表，然后在交互式外壳中输入以下内容：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('produceSales3.xlsx')
>>> sheet = wb.active
>>> sheet.freeze_panes = 'A2' # Freeze the rows above A2.
>>> wb.save('freezeExample3.xlsx')

你可以在图 14-8 中看到结果。

图 14-8：冻结第 1 行

由于你将 freeze_panes 属性设置为 'A2'，无论用户在工作表中滚动到何处，第 1 行都将保持可见。

图表

openpyxl 模块支持使用工作表单元格中的数据创建条形图、折线图、散点图和饼图。要创建图表，你需要执行以下操作：

1. 从单元格的选择区域创建一个 Reference 对象。

2. 通过传递 Reference 对象创建一个 Series 对象。

3. 创建一个 Chart 对象。

4. 将 Series 对象追加到 Chart 对象中。

5. 将 Chart 对象添加到 Worksheet 对象中，可选地指定图表的左上角单元格。

Reference 对象需要一些解释。要创建 Reference 对象，你必须调用 openpyxl.chart.Reference() 函数并传递五个参数：

包含你的图表数据的 Worksheet 对象。
包含图表数据的单元格矩形选择的最左上角单元格的列和行整数：元组中的第一个整数是行，第二个是列。请注意，1 是第一行，而不是 0。
包含图表数据的单元格矩形选择的最右下角单元格的列和行整数：元组中的第一个整数是行，第二个是列。

输入此交互式 shell 示例以创建柱状图并将其添加到电子表格中：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> for i in range(1, 11): # Create some data in column A.
... sheet['A' + str(i)] = i * i
...
>>> ref_obj = openpyxl.chart.Reference(sheet, 1, 1, 1, 10)

>>> series_obj = openpyxl.chart.Series(ref_obj, title='First series')

>>> chart_obj = openpyxl.chart.BarChart()
>>> chart_obj.title = 'My Chart'
>>> chart_obj.append(series_obj)

>>> sheet.add_chart(chart_obj, 'C5')
>>> wb.save('sampleChart3.xlsx')

此代码生成一个看起来像图 14-9 的电子表格。

图 14-9：添加了图表的电子表格

我们通过调用 openpyxl.chart.BarChart() 创建了一个柱状图。您也可以通过调用 openpyxl.chart.LineChart()、openpyxl.chart.ScatterChart() 和 openpyxl.chart.PieChart() 来创建折线图、散点图和饼图。

摘要

通常，处理信息困难的部分不是处理本身，而是仅仅是将数据以正确的格式放入你的程序中。但是一旦你将 Excel 电子表格加载到 Python 中，你就可以比手动更快地提取和操作其数据。

您还可以从程序生成电子表格作为输出。因此，如果同事需要将数千个销售联系人文本文件或 PDF 转换为电子表格文件，您就不必费力地将所有内容复制粘贴到 Excel 中。有了 openpyxl 模块和一些编程知识，你会发现处理最大的电子表格就像小菜一碟。

在下一章中，我们将探讨如何使用 Python 与另一个电子表格程序交互：流行的在线 Google Sheets 应用程序。

练习问题

对于以下问题，假设你有一个名为 wb 的 Workbook 对象，一个名为 sheet 的 Worksheet 对象，以及一个名为 sheet 的 Sheet 对象。

openpyxl.load_workbook() 函数返回什么？
wb.sheetnames 工作簿属性包含什么？
你会如何检索名为 'Sheet1' 的工作表的 Worksheet 对象？
你会如何检索工作簿活动工作表的 Worksheet 对象？
你会如何检索单元格 C5 中的值？
你会如何将单元格 C5 的值设置为 "Hello"？
如何以整数形式检索单元格的行和列？
sheet.max_column 和 sheet.max_row 工作表属性包含什么，这些属性的数据类型是什么？
如果你需要获取列 'M' 的整数索引，你需要调用什么函数？
如果你需要获取行 14 的字符串名称，你需要调用什么函数？
你如何检索从 A1 到 F1 的所有 Cell 对象的元组？
你会如何将工作簿保存到名为 example3.xlsx 的文件名中？
如何在单元格中设置公式？
如果您想检索单元格公式的结果而不是单元格公式本身，您必须先做什么？
如何将第 5 行的高度设置为 100？
如何隐藏列 C？
冻结窗格是什么？
创建条形图需要调用哪些五个函数和方法？

练习程序

为了练习，编写程序来完成以下任务。

乘法表制作器

创建一个名为 multiplicationTable.py 的程序，该程序从命令行接收一个数字 N 并在 Excel 工作表中创建一个 N×N 的乘法表。例如，当程序以这种方式运行时

py multiplicationTable.py 6

应创建一个看起来像图 14-10 的工作表。

图 14-10：在电子表格中生成的乘法表

第 1 行和列 A 应包含标签并加粗。

空行插入器

创建一个名为 blankRowInserter.py 的程序，该程序接受两个整数和一个文件名字符串作为命令行参数。让我们称第一个整数为 N，第二个整数为 M。从行 N 开始，程序应在电子表格中插入 M 个空行。例如，当程序以这种方式运行时

python blankRowInserter.py 3 2 myProduce.xlsx

“之前”和“之后”的工作表应类似于图 14-11。

图 14-11：在第三行插入两个空行之前（左）和之后（右）

您可以通过读取电子表格的内容来编写此程序。然后，在写入新电子表格时，使用 for 循环复制前 N 行。对于剩余的行，将 M 添加到输出电子表格中的行号。

乘法表制作器

创建一个名为 multiplicationTable.py 的程序，该程序从命令行接收一个数字 N 并在 Excel 工作表中创建一个 N×N 的乘法表。例如，当程序以这种方式运行时

py multiplicationTable.py 6

应创建一个看起来像图 14-10 的工作表。

图 14-10：在电子表格中生成的乘法表

第 1 行和列 A 应包含标签并加粗。

空行插入器

创建一个名为 blankRowInserter.py 的程序，该程序接受两个整数和一个文件名字符串作为命令行参数。让我们称第一个整数为 N，第二个整数为 M。程序应从第 N 行开始，在电子表格中插入 M 个空行。例如，当程序以这种方式运行时

python blankRowInserter.py 3 2 myProduce.xlsx

“之前”和“之后”的电子表格应类似于图 14-11。

图 14-11：在第三行插入两个空行之前（左）和之后（右）

您可以通过读取电子表格的内容来编写此程序。然后，在写入新电子表格时，使用一个 for 循环来复制前 N 行。对于剩余的行，将 M 添加到输出电子表格中的行号。

posted @ 2026-02-06 10:27 绝不原创的飞龙阅读(0) 评论(0) 收藏举报

刷新页面返回顶部

龙哥盟

掠夺·扩张·投机·博弈

Python-自动化指南-繁琐工作自动化-第三版-九-

Python 自动化指南（繁琐工作自动化）第三版（九）

14 电子表格

读取 Excel 文件

打开工作簿

从工作簿获取工作表

从工作表中获取单元格

在列字母和数字之间转换

获取行和列

第 1 步：读取电子表格数据

第 2 步：填充数据结构

第 3 步：将结果写入文件

类似程序的思路

编写 Excel 文档

创建和保存 Excel 文件

创建和删除工作表

将值写入单元格

第 1 步：设置包含更新信息的数据库结构

第 2 步：检查所有行并更新错误的价格

类似程序的创意

设置单元格的字体样式

公式

调整行和列

设置行高和列宽

合并和取消合并单元格

冻结窗格

图表

摘要

实践问题

练习程序

乘法表制作器

空行插入器

读取 Excel 文件

打开工作簿

从工作簿中获取工作表

从工作表中获取单元格

在列字母和数字之间转换

获取行和列

第 1 步：读取电子表格数据

第 2 步：填充数据结构

第 3 步：将结果写入文件

相似程序的思路

打开工作簿

从工作簿中获取工作表

从工作表中获取单元格

在列字母和数字之间转换

获取行和列

第一步：读取电子表格数据

第二步：填充数据结构

第 3 步：将结果写入文件

相似程序的思路

编写 Excel 文档

创建和保存 Excel 文件

创建和删除工作表

将值写入单元格

第 1 步：设置包含更新信息的结构化数据

第 2 步：检查所有行并更新错误的价格

相似程序的思路

创建和保存 Excel 文件

创建和删除工作表

将值写入单元格

第 1 步：使用更新信息设置数据结构

第 2 步：检查所有行并更新错误的价格

类似程序的创意

设置单元格的字体样式

公式

调整行和列

设置行高和列宽

合并和取消合并单元格

冻结窗格

设置行高和列宽

合并和取消合并单元格

冻结窗格

图表

摘要

练习问题

练习程序

乘法表制作器