Excel是Windows环境下流行的、强大的电子表格应用。openpyxl模块让Python程序能读取和修改Excel电子表格文件。例如，可能有一个无聊的任务，需要从一个电子表格拷贝一些数据，粘贴到另一个电子表格中。或者可能需要从几千行中挑选几行，根据某种条件稍作修改。或者需要查看几百份部门预算电子表格，寻找其中的赤字。正是这种无聊无脑的电子表格任务，可以通过Python来完成。
LibreOffice Calc和OpenOffice Calc都能处理Excel的电子表格文件格式，这意味着 openpyxl 模块也能处理来自这些应用程序的电子表格。你可以从https://www. libreoffice.org/和http://www.openoffice.org/下载这些软件。即使你的计算机上已经安装了Excel，可能也会发现这些程序更容易使用。但是，本章中的截屏图都来自于Windows 7上的Excel 2010。
12.1 Excel文档
首先，让我们来看一些基本定义。一个Excel电子表格文档称为一个工作簿。一个工作簿保存在扩展名为.xlsx的文件中。每个工作簿可以包含多个表（也称为工作表）。用户当前查看的表（或关闭Excel前最后查看的表），称为活动表。
每个表都有一些列（地址是从A开始的字母）和一些行（地址是从1开始的数字）。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。单元格形成的网格和数据构成了表。
12.2 安装openpyxl模块
Python没有自带openpyxl，所以必须安装。按照附录A中安装第三方模块的指令，模块的名称是 openpyxl。要测试它是否安装正确，就在交互式环境中输入以下代码：
>>> import openpyxl 如果该模块正确安装，这应该不会产生错误消息。记得在运行本章的交互式环境例子之前，要导入 openpyxl 模块，否则会得到错误，NameError: name 'openpyxl'is not defined。
本书介绍了openpyxl的2.1.4版，但OpenPyXL团队会经常发布新版本。不过不用担心，新版本应该在相当长的时间内向后兼容，支持本书中使用的指令。如果你有新版本，想看看它提供了什么新功能，可以查看OpenPyXL的完整文档：http://openpyxl.readthedocs.org/。
12.3 读取Excel文档
本章的例子将使用一个电子表格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'].valuedatetime.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'].value73 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 Apples3 Pears5 Apples7 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_row7>>> sheet.get_highest_column3 请注意，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)ApplesCherriesPearsOrangesApplesBananasStrawberries 利用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属性。
12.4 项目：从电子表格中读取数据
假定你有一张电子表格的数据，来自于2010年美国人口普查。你有一个无聊的任务，要遍历表中的几千行，计算总的人口，以及每个县的普查区的数目（普查区就是一个地理区域，是为人口普查而定义的）。每行表示一个人口普查区。我们将这个电子表格文件命名为censuspopdata.xlsx，可以从http://nostarch.com/automatestuff/下载它。它的内容如图12-2所示。
尽管Excel是要能够计算多个选中单元格的和，你仍然需要选中3000个以上县的单元格。即使手工计算一个县的人口只需要几秒钟，整张电子表格也需要几个小时时间。
图12-2 censuspopdata.xlsx电子表格
在这个项目中，你要编写一个脚本，从人口普查电子表格文件中读取数据，并在几秒钟内计算出每个县的统计值。
下面是程序要做的事：
- 从Excel电子表格中读取数据。
- 计算每个县中普查区的数目。
- 计算每个县的总人口。
- 打印结果。
这意味着代码需要完成下列任务：
- 用openpyxl模块打开Excel文档并读取单元格。
- 计算所有普查区和人口数据，将它保存到一个数据结构中。
- 利用pprint模块，将该数据结构写入一个扩展名为.py的文本文件。
第1步：读取电子表格数据
censuspopdata.xlsx电子表格中只有一张表，名为'Population by Census Tract'。每一行都保存了一个普查区的数据。列分别是普查区的编号（A），州的简称（B），县的名称（C），普查区的人口（D）。
打开一个新的文件编辑器窗口，输入以下代码。将文件保存为readCensusExcel.py。
#! python3 # readCensusExcel.py - Tabulates population and number of census tracts for # each county.❶ import openpyxl, pprint print('Opening workbook...')❷ wb = openpyxl.load_workbook('censuspopdata.xlsx')❸ sheet = wb.get_sheet_by_name('Population by Census Tract') countyData = {} # TODO: Fill in countyData with each county's population and tracts. print('Reading rows...')❹ for row in range(2, sheet.get_highest_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 countyData to it. 这段代码导入了openpyxl模块，也导入了pprint模块，你用后者来打印最终的县的数据❶。然后代码打开了censuspopdata.xlsx文件❷，取得了包含人口普查数据的工作表❸，开始迭代它的行❹。
请注意，你也创建了一个countyData变量，它将包含你计算的每个县的人口和普查区数目。但在它里面存储任何东西之前，你应该确定它内部的数据结构。
第2步：填充数据结构
保存在countyData中的数据结构将是一个字典，以州的简称作为键。每个州的简称将映射到另一个字典，其中的键是该州的县的名称。每个县的名称又映射到一个字典，该字典只有两个键，'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-- 如果前面的字典保存在countyData中，下面的表达式求值结果如下：
>>> countyData['AK']['Anchorage']['pop']291826>>> countyData['AK']['Anchorage']['tracts']55 一般来说，countyData字典中的键看起来像这样：
countyData[state abbrev][county]['tracts']countyData[state abbrev][county]['pop'] 既然知道了countyData的结构，就可以编写代码，用县的数据填充它。将下面的代码添加到程序的末尾：
#! python 3 # readCensusExcel.py - Tabulates population and number of census tracts for # each county. --snip-- for row in range(2, sheet.get_highest_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.❶ countyData.setdefault(state, {}) # Make sure the key for this county in this state exists.❷ countyData[state].setdefault(county, {'tracts': 0, 'pop': 0}) # Each row represents one census tract, so increment by one.❸ countyData[state][county]['tracts'] += 1 # Increase the county pop by the pop in this census tract.❹ countyData[state][county]['pop'] += int(pop) # TODO: Open a new text file and write the contents of countyData to it. 最后的两行代码执行实际的计算工作，在for循环的每次迭代中，针对当前的县，增加tracts的值❸，并增加pop的值❹。
其他代码存在是因为，只有countyData中存在的键，你才能引用它的值。（也就是说，如果'AK'键不存在，countyData['AK']['Anchorage']['tracts'] += 1将导致一个错误）。为了确保州简称的键存在，你需要调用setdefault方法，在state还不存在时设置一个默认值❶。
正如countyData字典需要一个字典作为每个州缩写的值，这样的字典又需要一个字典，作为每个县的键的值❷。这样的每个字典又需要键'tracts'和'pop'，它们的初始值为整数0（如果这个字典的结构令你混淆，回去看看本节开始处字典的例子）。
如果键已经存在，setdefault不会做任何事情，因此在for循环的每次迭代中调用它不会有问题。
第3步：将结果写入文件
for循环结束后，countyData字典将包含所有的人口和普查区信息，以县和州为键。这时，你可以编写更多代码，将数据写入文本文件或另一个Excel电子表格。目前，我们只是使用pprint.pformat函数，将变量字典的值作为一个巨大的字符串，写入文件census2010.py。在程序的末尾加上以下代码（确保它没有缩进，这样它就在for循环之外）：
#! python 3# readCensusExcel.py - Tabulates population and number of census tracts for# each county.--snip--for row in range(2, sheet.get_highest_row + 1):--snip--# Open a new text file and write the contents of countyData to it.print('Writing results...')resultFile = open('census2010.py', 'w')resultFile.write('allData = ' + pprint.pformat(countyData))resultFile.closeprint('Done.') pprint.pformat函数产生一个字符串，它本身就是格式化好的、有效的Python代码。将它输出到文本文件census2010.py，你就通过Python程序生成了一个Python程序！这可能看起来有点复杂，但好处是你现在可以导入census2010.py，就像任何其他Python模块一样。在交互式环境中，将当前工作目录变更到新创建的文件所在的文件夹（在我的笔记本上，就是C:/Python34），然后导入它：
>>> import os>>> os.chdir('C://Python34')>>> import census2010>>> census2010.allData['AK']['Anchorage']{'pop': 291826, 'tracts': 55}>>> anchoragePop = census2010.allData['AK']['Anchorage']['pop']>>> print('The 2010 population of Anchorage was ' + str(anchoragePop))The 2010 population of Anchorage was 291826 readCensusExcel.py程序是可以扔掉的代码：当你把它的结果保存为census2010.py之后，就不需要再次运行该程序了。任何时候，只要需要县的数据，就可以执行import census2010。
手工计算这些数据可能需要数小时，这个程序只要几秒钟。利用OpenPyXL，可以毫无困难地提取保存在 Excel 电子表格中的信息，并对它进行计算。从http://nostarch. com/automatestuff/可以下载这个完整的程序。
第4步：类似程序的思想
许多公司和组织机构使用Excel来保存各种类型的数据，电子表格会变得庞大，这并不少见。解析Excel电子表格的程序都有类似的结构：它加载电子表格文件，准备一些变量或数据结构，然后循环遍历电子表格中的每一行。这样的程序可以做下列事情：
- 比较一个电子表格中多行的数据。
- 打开多个Excel文件，跨电子表格比较数据。
- 检查电子表格是否有空行或无效的数据，如果有就警告。
- 从电子表格中读取数据，将它作为Python程序的输入。
12.5 写入Excel文档
OpenPyXL也提供了一些方法写入数据，这意味着你的程序可以创建和编辑电子表格文件。利用Python，创建一个包含几千行数据的电子表格是非常简单的。
12.5.1 创建并保存Excel文档
调用openpyxl.Workbook函数，创建一个新的空Workbook对象。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> wb.get_sheet_names['Sheet']>>> sheet = wb.get_active_sheet>>> sheet.title'Sheet'>>> sheet.title = 'Spam Bacon Eggs Sheet'>>> wb.get_sheet_names['Spam Bacon Eggs Sheet'] 工作簿将从一个工作表开始，名为Sheet。你可以将新的字符串保存在它的title属性中，从而改变工作表的名字。
当修改Workbook对象或它的工作表和单元格时，电子表格文件不会保存，除非你调用save工作簿方法。在交互式环境中输入以下代码（让example.xlsx处于当前工作目录）：
>>> import openpyxl>>> wb = openpyxl.load_workbook('example.xlsx')>>> sheet = wb.get_active_sheet>>> sheet.title = 'Spam Spam Spam'>>> wb.save('example_copy.xlsx') 这里，我们改变了工作表的名称。为了保存变更，我们将文件名作为字符串传递给save方法。传入的文件名与最初的文件名不同，例如'example_copy.xlsx'，这将变更保存到电子表格的一份拷贝中。
当你编辑从文件中加载的一个电子表格时，总是应该将新的、编辑过的电子表格保存到不同的文件名中。这样，如果代码中有缺陷，导致新的保存到文件中数据不对或讹误，还有最初的电子表格文件可以处理。
12.5.2 创建和删除工作表
利用create_sheet and remove_sheet方法，可以在工作簿中添加或删除工作表。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> wb.get_sheet_names['Sheet']>>> wb.create_sheet< Worksheet "Sheet1">>>> wb.get_sheet_names['Sheet', 'Sheet1']>>> wb.create_sheet(index=0, title='First Sheet')< Worksheet "First Sheet">>>> wb.get_sheet_names['First Sheet', 'Sheet', 'Sheet1']>>> wb.create_sheet(index=2, title='Middle Sheet')< Worksheet "Middle Sheet">>>> wb.get_sheet_names['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1'] create_sheet方法返回一个新的Worksheet对象，名为SheetX，它默认是工作簿的最后一个工作表。或者，可以利用index和title关键字参数，指定新工作表的索引或名称。
继续前面的例子，输入以下代码：
>>> wb.get_sheet_names['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']>>> wb.remove_sheet(wb.get_sheet_by_name('Middle Sheet'))>>> wb.remove_sheet(wb.get_sheet_by_name('Sheet1'))>>> wb.get_sheet_names['First Sheet', 'Sheet'] remove_sheet方法接受一个Worksheet对象作为其参数，而不是工作表名称的字符串。如果你只知道要删除的工作表的名称，就调用get_sheet_by_name，将它的返回值传入remove_sheet。
在工作簿中添加或删除工作表之后，记得调用save方法来保存变更。
12.5.3 将值写入单元格
将值写入单元格，很像将值写入字典中的键。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> sheet = wb.get_sheet_by_name('Sheet')>>> sheet['A1'] = 'Hello world!'>>> sheet['A1'].value'Hello world!' 如果你有单元格坐标的字符串，可以像字典的键一样，将它用于Worksheet对象，指定要写入的单元格。
12.6 项目：更新一个电子表格
这个项目需要编写一个程序，更新产品销售电子表格中的单元格。程序将遍历这个电子表格，找到特定类型的产品，并更新它们的价格。请从http://nostarch.com/ automatestuff/下载这个电子表格。图12-3展示了这个电子表格。
图12-3 产品销售的电子表格
每一行代表一次单独的销售。列分别是销售产品的类型（A）、产品每磅的价格（B）、销售的磅数（C），以及这次销售的总收入。TOTAL列设置为Excel公式，将每磅的成本乘以销售的磅数，并将结果取整到分。有了这个公式，如果列B或C发生变化，TOTAL列中的单元格将自动更新。
现在假设Garlic、Celery和Lemons的价格输入的不正确。这让你面对一项无聊的任务：遍历这个电子表格中的几千行，更新所有garlic、celery和lemon行中每磅的价格。你不能简单地对价格查找替换，因为可能有其他的产品价格一样，你不希望错误地“更正”。对于几千行数据，手工操作可能要几小时。但你可以编写程序，几秒钟内完成这个任务。
你的程序做下面的事情：
- 循环遍历所有行。
- 如果该行是Garlic、Celery或Lemons，更新价格。
这意味着代码需要做下面的事情：
- 打开电子表格文件。
- 针对每一行，检查列A的值是不是Celery、Garlic或Lemon。
- 如果是，更新列B中的价格。
- 将该电子表格保存为一个新文件（这样就不会丢失原来的电子表格，以防万一）。
第1步：利用更新信息建立数据结构
需要更新的价格如下：
Celery 1.19
Garlic 3.07
Lemon 1.27
你可以像这样编写代码：
if produceName == 'Celery': cellObj = 1.19if produceName == 'Garlic': cellObj = 3.07if produceName == 'Lemon': cellObj = 1.27 这样硬编码产品和更新的价格有点不优雅。如果你需要用不同的价格，或针对不同的产品，再次更新这个电子表格，就必须修改很多代码。每次修改代码，都有引入缺陷的风险。
更灵活的解决方案，是将正确的价格信息保存在字典中，在编写代码时，利用这个数据结构。在一个新的文件编辑器窗口中，输入以下代码：
#! python3# updateProduce.py - Corrects costs in produce sales spreadsheet.import openpyxlwb = openpyxl.load_workbook('produceSales.xlsx')sheet = wb.get_sheet_by_name('Sheet')# The produce types and their updated pricesPRICE_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的末尾：
#! python3 # updateProduce.py - Corrects costs in produce sales spreadsheet. --snip-- # Loop through the rows and update the prices.❶ for rowNum in range(2, sheet.get_highest_row): # skip the first row❷ produceName = sheet.cell(row=rowNum, column=1).value❸ if produceName in PRICE_UPDATES: sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]❹ wb.save('updatedProduceSales.xlsx') 我们从第二行开始循环遍历，因为第1行是标题❶。第1列的单元格（即列A）将保存在变量produceName中❷。如果produceName的值是PRICE_ UPDATES字典中的一个键❸，你就知道，这行的价格必须修改。正确的价格是PRICE_UPDATES[produceName]。
请注意，使用PRICE_UPDATES让代码变得多么干净。只需要一条if语句，而不是像if produceName == 'Garlic'这样的代码，就能够更新所有类型的产品。因为代码没有硬编码产品名称，而是使用PRICE_UPDATES字典，在for循环中更新价格，所以如果产品销售电子表格需要进一步修改，你只需要修改PRICE_UPDATES字典，不用改其他代码。
在遍历整个电子表格并进行修改后，代码将Workbook对象保存到updatedProduceSales. xlsx❹。它没有覆写原来的电子表格，以防万一程序有缺陷，将电子表格改错。在确认修改的电子表格正确后，你可以删除原来的电子表格。
你可以从http://nostarch.com/automatestuff/下载这个程序的完整源代码。
第3步：类似程序的思想
因为许多办公室职员一直在使用Excel电子表格，所以能够自动编辑和写入Excel文件的程序，将非常有用。这样的程序可以完成下列任务：
- 从一个电子表格读取数据，写入其他电子表格的某些部分。
- 从网站、文本文件或剪贴板读取数据，将它写入电子表格。
- 自动清理电子表格中的数据。例如，可以利用正则表达式，读取多种格式的电话号码，将它们转换成单一的标准格式。
12.7 设置单元格的字体风格
设置某些单元格行或列的字体风格，可以帮助你强调电子表格中重点的区域。例如，在这个产品电子表格中，程序可以对potato、garlic和parsnip等行使用粗体。或者也许你希望对每磅价格超过5美元的行使用斜体。手工为大型电子表格的某些部分设置字体风格非常令人厌烦，但程序可以马上完成。
为了定义单元格的字体风格，需要从openpyxl.styles模块导入Font和Style函数。
from openpyxl.styles import Font, Style 这让你能输入Font，代替openpyxl.styles.Font（参见2.8节“导入模块”，复习这种方式的import语句）。
这里有一个例子，它创建了一个新的工作簿，将A1单元格设置为24点、斜体。在交互式环境中输入以下代码：
>>> import openpyxl >>> from openpyxl.styles import Font, Style >>> wb = openpyxl.Workbook >>> sheet = wb.get_sheet_by_name('Sheet')❶ >>> italic24Font = Font(size=24, italic=True)❷ >>> styleObj = Style(font=italic24Font)❸ >>> sheet['A'].style/styleObj >>> sheet['A1'] = 'Hello world!' >>> wb.save('styled.xlsx') OpenPyXL模块用Style对象来表示单元格字体风格设置的集合，字体风格保存在Cell对象的style属性中。将Style对象赋给style属性，可以设置单元格的字体风格。
在这个例子中，Font(size=24, italic=True)返回一个 Font 对象，保存在italic24Font中❶。Font的关键字参数size和italic，配置了Font对象的style属性。这个Font对象被传递给Style(font=italic24Font)调用，该函数的返回值保存在styleObj中❷。如果styleObj被赋给单元格的style属性❸，所有字体风格的信息将应用于单元格A1。
12.8 Font对象
Font对象的style属性影响文本在单元格中的显示方式。要设置字体风格属性，就向Font函数传入关键字参数。表12-2展示了Font函数可能的关键字参数。
表12-2 Font style属性的关键字参数
关键字参数
数据类型
描述
name
字符串
字体名称，诸如'Calibri' 或'Times New Roman'
size
整型
大小点数
bold
布尔型
True表示粗体
italic
布尔型
True表示斜体
可以调用Font来创建一个Font对象，并将这个Font对象保存在一个变量中。然后将它传递给Style，得到的Style对象保存在一个变量中，并将该变量赋给Cell对象的style属性。例如，下面的代码创建了各种字体风格：
>>> import openpyxl >>> from openpyxl.styles import Font, Style >>> wb = openpyxl.Workbook >>> sheet = wb.get_sheet_by_name('Sheet') >>> fontObj1 = Font(name='Times New Roman', bold=True) >>> styleObj1 = Style(font=fontObj1) >>> sheet['A1'].style/styleObj >>> sheet['A1'] = 'Bold Times New Roman' >>> fontObj2 = Font(size=24, italic=True) >>> styleObj2 = Style(font=fontObj2) >>> sheet['B3'].style/styleObj >>> sheet['B3'] = '24 pt Italic' >>> wb.save('styles.xlsx') 这里，我们将一个Font对象保存在fontObj1中，并用它创建一个Style对象，该对象保存在styleObj1中，然后将A1的Cell对象的style属性设置为styleObj。我们针对另一个Font对象和Style对象重复这个过程，设置第二个单元格的字体风格。运行这段代码后，电子表格中A1和B3单元格的字体风格将设置为自定义的字体风格，如图12-4所示。
图12-4 带有自定义字体风格的电子表格
对于单元格A1，我们将字体名称设置为'Times New Roman'，并将bold设置为true，这样我们的文本将以粗体Times New Roman的方式显示。我们没有指定大小，所以使用openpyxl的默认值11。在单元格B3中，我们的文本是斜体，大小是24。我们没有指定字体的名称，所以使用openpyxl的默认值Calibri。
12.9 公式
公式以一个等号开始，可以配置单元格，让它包含通过其他单元格计算得到的值。在本节中，你将利用openpyxl模块，用编程的方式在单元格中添加公式，就像添加普通的值一样。例如：
>>> sheet['B9'] = '=SUM(B1:B8)' 这将=SUM(B1:B8)作为单元格B9的值。这将B9单元格设置为一个公式，计算单元格B1到B8的和。图12-5展示了它的效果。
图12-5 单元格B9包含了一个公式，计算单元格B1到B8的和
为单元格设置公式就像设置其他文本值一样。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> sheet = wb.get_active_sheet>>> sheet['A1'] = 200>>> sheet['A2'] = 300>>> sheet['A3'] = '=SUM(A1:A2)'>>> wb.save('writeFormula.xlsx') 单元格A1和A2分别设置为200和300。单元格A3设置为一个公式，求出A1和A2的和。如果在Excel中打开这个电子表格，A3的值将显示为500。
也可以读取单元格中的公式，就像其他值一样。但是，如果你希望看到该公式的计算结果，而不是原来的公式，就必须将load_workbook的data_only关键字参数设置为True。这意味着Workbook对象要么显示公式，要么显示公式的结果，不能兼得（但是针对一个电子表格文件，可以加载多个Workbook对象）。在交互式环境中输入以下代码，看看有无data_only关键字参数时，加载工作簿的区别：
>>> import openpyxl>>> wbFormulas = openpyxl.load_workbook('writeFormula.xlsx')>>> sheet = wbFormulas.get_active_sheet>>> sheet['A3'].value'=SUM(A1:A2)'>>> wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)>>> sheet = wbDataOnly.get_active_sheet>>> sheet['A3'].value500 这里，如果调用load_workbook时带有data_only=True，A3单元格就显示为500，即公式的结果，而不是公式的文本。
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代码的可读性要好得多。
12.10 调整行和列
在Excel中，调整行和列的大小非常容易，只要点击并拖动行的边缘，或列的头部。但如果你需要根据单元格的内容来设置行或列的大小，或者希望设置大量电子表格文件中的行列大小，编写Python程序来做就要快得多。
行和列也可以完全隐藏起来。或者它们可以“冻结”，这样就总是显示在屏幕上，如果打印该电子表格，它们就出现在每一页上（这很适合做表头）。
12.10.1 设置行高和列宽
Worksheet对象有row_dimensions和column_dimensions属性，控制行高和列宽。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> sheet = wb.get_active_sheet>>> sheet['A1'] = 'Tall row'>>> sheet['B2'] = 'Wide column'>>> sheet.row_dimensions[1].height = 70>>> sheet.column_dimensions['B'].width = 20>>> wb.save('dimensions.xlsx') 工作表的row dimensions和column dimensions是像字典一样的值，row dimensions包含RowDimension对象，column dimensions包含ColumnDimension对象。在row_dimensions中，可以用行的编号来访问一个对象（在这个例子中，是1或）。在column dimensions中，可以用列的字母来访问一个对象（在这个例子中，是A或B）。
dimensions.xlsx电子表格如图12-6所示。
图12-6 行1和列B设置了更大的高度和宽度
一旦有了RowDimension对象，就可以设置它的高度。一旦有了ColumnDimension对象，就可以设置它的宽度。行的高度可以设置为0到409之间的整数或浮点值。这个值表示高度的点数。一点等于1/72英寸。默认的行高是12.75。列宽可以设置为0到255之间的整数或浮点数。这个值表示使用默认字体大小时（11点），单元格可以显示的字符数。默认的列宽是8.43个字符。列宽为零或行高为零，将使单元格隐藏。
12.10.2 合并和拆分单元格
利用merge_cells工作表方法，可以将一个矩形区域中的单元格合并为一个单元格。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> sheet = wb.get_active_sheet>>> sheet.merge_cells('A1:D3')>>> sheet['A1'] = 'Twelve cells merged together.'>>> sheet.merge_cells('C5:D5')>>> sheet['C5'] = 'Two merged cells.'>>> wb.save('merged.xlsx') merge_cells的参数是一个字符串，表示要合并的矩形区域左上角和右下角的单元格：'A1:D3'将12个单元格合并为一个单元格。要设置这些合并后单元格的值，只要设置这一组合并单元格左上角的单元格的值。
如果运行这段代码，merged.xlsx看起来如图12-7所示。
图12-7 在电子表格中合并单元格
要拆分单元格，就调用 unmerge_cells工作表方法。在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.load_workbook('merged.xlsx')>>> sheet = wb.get_active_sheet>>> sheet.unmerge_cells('A1:D3')>>> sheet.unmerge_cells('C5:D5')>>> wb.save('merged.xlsx') 如果保存变更，然后查看这个电子表格，就会看到合并的单元格恢复成一些独立的单元格。
12.10.3 冻结窗格
对于太大而不能一屏显示的电子表格，“冻结”顶部的几行或最左边的几列，是很有帮助的。例如，冻结的列或行表头，就算用户滚动电子表格，也是始终可见的。这称为“冻结窗格”。在OpenPyXL中，每个Worksheet对象都有一个freeze_panes属性，可以设置为一个Cell对象或一个单元格坐标的字符串。请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。
要解冻所有的单元格，就将freeze_panes设置为None或'A1'。表12-3展示了freeze_panes设定的一些例子，以及哪些行或列会冻结。
表12-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
没有冻结窗格
确保你有来自http://nostarch.com/automatestuff/的产品销售电子表格。然后在交互式环境中输入以下代码：
>>> import openpyxl>>> wb = openpyxl.load_workbook('produceSales.xlsx')>>> sheet = wb.get_active_sheet>>> sheet.freeze_panes = 'A2'>>> wb.save('freezeExample.xlsx') 如果将freeze_panes属性设置为'A2'，行1将永远可见，无论用户将电子表格滚动到何处，如图12-8所示。
图12-8 将reeze_panes设置为'A2'，行1将永远可见，无论用户如何向下滚动
12.10.4 图表
openpyxl支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。要创建图表，需要做下列事情：
1．从一个矩形区域选择的单元格，创建一个Reference对象。
2．通过传入Reference对象，创建一个Series对象。
3．创建一个Chart对象。
4．将Series对象添加到Chart对象。
5．可选地设置Chart对象的drawing.top、drawing.left、drawing.width和drawing.height变量。
6．将Chart对象添加到Worksheet对象。
Reference对象需要一些解释。Reference对象是通过调用openpyxl.charts. Reference函数并传入3个参数创建的：
1．包含图表数据的Worksheet对象。
2．两个整数的元组，代表矩形选择区域的左上角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。请注意第一行是1，不是0。
3．两个整数的元组，代表矩形选择区域的右下角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。
图12-9展示了坐标参数的一些例子。
图12-9 从左到右：(1, 1), (10, 1); (3, 2), (6, 4); (5, 3), (5, 3)
在交互式环境中输入以下代码，创建一个条形图，将它添加到电子表格中：
>>> import openpyxl>>> wb = openpyxl.Workbook>>> sheet = wb.get_active_sheet>>> for i in range(1, 11): # create some data in column Asheet['A' + str(i)] = i>>> refObj = openpyxl.charts.Reference(sheet, (1, 1), (10, 1))>>> seriesObj = openpyxl.charts.Series(refObj, title='First series')>>> chartObj = openpyxl.charts.BarChart>>> chartObj.append(seriesObj)>>> chartObj.drawing.top = 50 # set the position>>> chartObj.drawing.left = 100>>> chartObj.drawing.width = 300 # set the size>>> chartObj.drawing.height = 200>>> sheet.add_chart(chartObj)>>> wb.save('sampleChart.xlsx') 得到的电子表格，如图12-10所示。
图12-10 添加了一个图表的电子表格
我们可以调用 openpyxl.charts.BarChart，创建一个条形图。也可以调用openpyxl.charts.LineChart、openpyxl.charts.ScatterChart和openpyxl.charts.PieChart，创建折线图、散点图和饼图。
遗憾的是，在OpenPyXL的当前版本中（2.1.4），load_workbook不会加载Excel文件中的图表。即使Excel文件包含图表，加载的Workbook对象也不会包含它们。如果加载一个Workbook对象，然后马上保存到同样的.xlsx文件名中，实际上就会删除其中的图表。
12.11 小结
处理信息是比较难的部分，通常不是处理本身难，而是为程序得到正确格式的数据较难。一旦你将电子表格载入Python，就可以提取并操作它的数据，比手工操作要快得多。
你也可以生成电子表格，作为程序的输出。所以如果同事需要将包含几千条销售合同的文本文件或PDF转换成电子表格文件，你就不需要无聊地将它拷贝粘贴到Excel中。
有了openpyxl模块和一些编程知识，你会发现处理很大的电子表格也是小事一桩。
12.12 习题
对于以下的问题，设想你有一个Workbook对象保存在变量wb中，一个Worksheet对象保存在sheet中，一个Cell对象保存在cell中，一个Comment对象保存在comm中，一个Image对象保存在img中。
1．openpyxl.load_workbook函数返回什么？
2．get_sheet_names工作簿方法返回什么？
3．如何取得名为'Sheet1'的工作表的Worksheet对象？
4．如何取得工作簿的活动工作表的Worksheet对象？
5．如何取得单元格C5中的值？
6．如何将单元格C5中的值设置为"Hello"？
7．如何取得表示单元格的行和列的整数？
8．工作表方法get_highest_column和get_highest_row返回什么？这些返回值的类型是什么？
9．如果要取得列'M'的整数下标，需要调用什么函数？
10．如果要取得列14的字符串名称，需要调用什么函数？
11．如何取得从A1到F1的所有Cell对象的元组？
12．如何将工作簿保存到文件名example.xlsx？
13．如何在一个单元格中设置公式？
14．如果需要取得单元格中公式的结果，而不是公式本身，必须先做什么？
15．如何将第5行的高度设置为100？
16．如何设置列C的宽度？
17．列出一些openpyxl 2.1.4不会从电子表格文件中加载的功能。
18．什么是冻结窗格？
19．创建一个条形图，需要调用哪5个函数和方法？
12.13 实践项目
作为实践，编程执行以下任务。
12.13.1 乘法表
创建程序multiplicationTable.py，从命令行接受数字N，在一个Excel电子表格中创建一个N×N的乘法表。例如，如果这样执行程序：
py multiplicationTable.py 6 它应该创建一个图12-11所示的电子表格。
图12-11 在电子表格中生成的乘法表
行1和列A应该用做标签，应该使用粗体。
12.13.2 空行插入程序
创建一个程序blankRowInserter.py，它接受两个整数和一个文件名字符串作为命令行参数。我们将第一个整数称为N，第二个整数称为M。程序应该从第N行开始，在电子表格中插入M个空行。例如，如果这样执行程序：
python blankRowInserter.py 3 2 myProduce.xlsx 执行之前和之后的电子表格，应该如图12-12所示。
图12-12 之前（左边）和之后（右边）在第三行插入两个空行
程序可以这样写：读入电子表格的内容，然后在写入新的电子表格时，利用for循环拷贝前面N行。对于剩下的行，行号加上M，写入输出的电子表格。
12.13.3 电子表格单元格翻转程序
编写一个程序，翻转电子表格中行和列的单元格。例如，第5行第3列的值将出现在第3行第5列（反之亦然）。这应该针对电子表格中所有单元格进行。例如，之前和之后的电子表格应该看起来如图12-13所示。
图12-13 翻转之前（上面）和之后（下面）的电子表格
程序可以这样写：利用嵌套的for循环，将电子表格中的数据读入一个列表的列表。这个数据结构用sheetData[x][y]表示列x和行y处的单元格。然后，在写入新电子表格时，将sheetData[y][x]写入列x和行y处的单元格。
12.13.4 文本文件到电子表格
编写一个程序，读入几个文本文件的内容（可以自己创造这些文本文件），并将这些内容插入一个电子表格，每行写入一行文本。第一个文本文件中的行将写入列A中的单元格，第二个文本文件中的行将写入列B中的单元格，以此类推。
利用File对象的readlines方法，返回一个字符串的列表，每个字符串就是文件中的一行。对于第一个文件，将第一行输出到列1行1。第二行应该写入列1行2，以此类推。下一个用readlines读入的文件将写入列2，再下一个写入列3，以此类推。
12.13.5 电子表格到文本文件
编写一个程序，执行前一个程序相反的任务。该程序应该打开一个电子表格，将列A中的单元格写入一个文本文件，将列B中的单元格写入另一个文本文件，以此类推。