Python处理Excel文件（三）

3　读取工作簿中的所有工作表

到目前为止，都在演示如何处理单个工作表。有些时候，你只需要处理一个工作表就可以了。在这些情况下，这里的示例可以告诉你如何使用 Python 程序去自动处理工作表。

但是，在很多情况下你需要处理多个工作表，多到使用手工处理效率非常低或者根本不可行。在这种情况下，Python 会给你惊喜，因为它可以让你自动化和规模化地进行数据处理，远远超过手工处理能够达到的限度。提供了两个示例，演示了如何在一个工作簿的所有工作表中筛选特定的行与列。

1　在所有工作表中筛选特定行

基础Python

要使用基础 Python 在所有工作表中筛选出销售额大于 $2000.00 的所有行，在文本编辑器中输入下列代码，然后将文件保存为 9excel_value_meets_condition_all_worksheets.py：

 1 #!/usr/bin/env python3
 2 import sys
 3 from datetime import date
 4 from xlrd import open_workbook, xldate_as_tuple
 5 from xlwt import Workbook
 6 input_file = sys.argv[1]
 7 output_file = sys.argv[2]
 8 output_workbook = Workbook()
 9 output_worksheet = output_workbook.add_sheet('filtered_rows_all_worksheets')
10 sales_column_index = 3
11 threshold = 2000.0
12 first_worksheet = True
13 with open_workbook(input_file) as workbook:
14     data = []
15     for worksheet in workbook.sheets():
16          if first_worksheet:
17               header_row = worksheet.row_values(0)
18               data.append(header_row)
19               first_worksheet = False
20          for row_index in range(1,worksheet.nrows):
21               row_list = []
22               sale_amount = worksheet.cell_value\
23               (row_index, sales_column_index)
24               if sale_amount > threshold:
25                    for column_index in range(worksheet.ncols):
26                         cell_value = worksheet.cell_value\
27                         (row_index,column_index)
28                         cell_type = worksheet.cell_type\
29                         (row_index, column_index)
30                         if cell_type == 3:
31                              date_cell = xldate_as_tuple\
32                              (cell_value,workbook.datemode)
33                              date_cell = date(*date_cell[0:3])\
34                              .strftime('%m/%d/%Y')
35                              row_list.append(date_cell)
36                         else:
37                              row_list.append(cell_value)
38               if row_list:
39                    data.append(row_list)
40    for list_index, output_list in enumerate(data):
41         for element_index, element in enumerate(output_list):
42               output_worksheet.write(list_index, element_index, element)
43 output_workbook.save(output_file)

第 10 行代码创建了一个变量 sales_column_index，保存 Sale Amount 列的索引值。同样，第 11 行代码创建了一个变量 threshold 来保存你所关心的销售额。我们要将 Sale Amount 列中的每个值与这个阈值进行比较，来确定哪一行要被写入到输出文件中。

第 15 行代码创建了一个 for 循环，用来在工作簿中的所有工作表之间迭代。它使用 workbook 对象的 sheets 属性来列出工作簿中所有的工作表。

第 16 行代码判断当前工作表是不是第一个工作表，如果是第一个工作表，我们就提取出标题行，将其追加到 data 中，然后将 first_worksheet 设为 False。代码继续处理余下的销售额大于阈值的数据行。

对于所有后续的工作表，first_worksheet 都是 False，所以脚本直接来到第 20 行代码处理每个工作表中的数据行。因为 range 函数不是从 0 开始，而是从 1 开始，所以你应该知道代码处理的是数据行，不是标题行。

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python 9excel_value_meets_condition_all_worksheets.py sales_2013.xlsx\
output_files\9output.xls

你可以打开输出文件 9output.xls 查看一下结果。

pandas

在 pandas 中，通过在 read_excel 函数中设置 sheetname=None，可以一次性读取工作簿中的所有工作表。pandas 将这些工作表读入一个数据框字典，字典中的键就是工作表的名称，值就是包含工作表中数据的数据框。所以，通过在字典的键和值之间迭代，你可以使用工作簿中所有的数据。当你在每个数据框中筛选特定行时，结果是一个新的筛选过的数据框，所以你可以创建一个列表保存这些筛选过的数据框，然后将它们连接成一个最终数据框。

在下面这个示例中，我们想在所有工作表中筛选出销售额大于 $2000.00 的所有行。要使用 pandas 筛选出这些行，在文本编辑器中输入下列代码，然后将文件保存为 pandas_value_meets_condition_all_worksheets.py：

#!/usr/bin/env python3
import pandas as pd
import sys
input_file = sys.argv[1]
output_file = sys.argv[2]
data_frame = pd.read_excel(input_file, sheetname=None, index_col=None)
row_output = []
for worksheet_name, data in data_frame.items():
    row_output.append(data[data['Sale Amount'].astype(float) > 2000.0])
filtered_rows = pd.concat(row_output, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
filtered_rows.to_excel(writer, sheet_name='sale_amount_gt2000', index=False)
writer.save()

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python pandas_value_meets_condition_all_worksheets.py sales_2013.xlsx\
output_files\pandas_output.xls

你可以打开输出文件 pandas_output.xls 查看一下结果。

2　在所有工作表中选取特定列

有些时候，Excel 工作簿中包含了多个工作表，每个工作表中包含的列并不都是你需要的。在这种情况下，你可以使用 Python 读取所有工作表，筛选掉不需要的列，只保留需要的列。

从前面的内容可知，至少有两种方法可以从工作表中选取一组列：使用列索引值和列标题。下面的示例演示了如何使用列标题从一个工作簿的所有工作表中选取特定的列。

基础Python

要使用基础 Python 在所有工作表中选取 Customer Name 和 Sale Amount 列，在文本编辑器中输入下列代码，然后将文件保存为 10excel_column_by_name_all_worksheet.py：

 1 #!/usr/bin/env python3
 2 import sys
 3 from datetime import date
 4 from xlrd import open_workbook, xldate_as_tuple
 5 from xlwt import Workbook
 6 input_file = sys.argv[1]
 7 output_file = sys.argv[2]
 8 output_workbook = Workbook()
 9 output_worksheet = output_workbook.add_sheet('selected_columns_all_worksheets')
10 my_columns = ['Customer Name', 'Sale Amount']
11 first_worksheet = True
12 with open_workbook(input_file) as workbook:
13    data = [my_columns]
14    index_of_cols_to_keep = []
15    for worksheet in workbook.sheets():
16         if first_worksheet:
17              header = worksheet.row_values(0)
18              for column_index in range(len(header)):
19                   if header[column_index] in my_columns:
20                        index_of_cols_to_keep.append(column_index)
21              first_worksheet = False
22        for row_index in range(1, worksheet.nrows):
23             row_list = []
24             for column_index in index_of_cols_to_keep:
25                  cell_value = worksheet.cell_value\
26                  (row_index, column_index)
27                  cell_type = worksheet.cell_type(row_index, column_index)
28                  if cell_type == 3:
29                       date_cell = xldate_as_tuple\
30                       (cell_value,workbook.datemode)
31                       date_cell = date(*date_cell[0:3])\
32                       .strftime('%m/%d/%Y')
33                       row_list.append(date_cell)
34                  else:
35                       row_list.append(cell_value)
36              data.append(row_list)
37    for list_index, output_list in enumerate(data):
38         for element_index, element in enumerate(output_list):
39               output_worksheet.write(list_index, element_index, element)
40 output_workbook.save(output_file)

第 10 行代码创建了一个列表变量 my_columns，包含了我们要保留的两列的名称。

第 13 行代码将 my_columns 放入 data，作为 data 中的第一个列表，因为它是要写入输出文件的列的列标题。第 14 行代码创建了一个空列表 index_of_cols_to_keep，用来保存 Customer Name 和 Sale Amount 列的索引值。

第 16 行代码检验当前是否在处理第一个工作表。如果是第一个工作表，我们就识别出 Customer Name 和 Sale Amount 列的索引值，并将其追加到列表 index_of_cols_to_keep 中。然后，将 first_worksheet 的值设为 False。代码继续处理余下的数据行，第 24 行代码仅用于处理 Customer Name 和 Sale Amount 列中的值。

对于所有后续的工作表，first_worksheet 都是 False，所以脚本直接来到第 22 行代码处理每个工作表中的数据行。对于这些工作表，只处理索引值在 index_of_cols_to_keep 中的那些列。如果这些列中有日期型数据，就将其格式化。在组合好一行要写入输出文件的数据之后，使用第 36 行代码将这个数据列表追加到 data 中。

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python 10excel_column_by_name_all_worksheets.py sales_2013.xlsx\
output_files\10output.xls

你可以打开输出文件 10output.xls 查看一下结果。

pandas

我们再一次使用 pandas 中的 read_excel 函数将所有工作表读入一个字典。然后，使用 loc 函数在每个工作表中选取特定的列，创建一个筛选过的数据框列表，并将这些数据框连接在一起，形成一个最终数据框。

在这个示例中，我们想在所有工作表中选取 Customer Name 和 Sale Amount 列。要使用 pandas 选取这些列，在文本编辑器中输入下列代码，然后将文件保存为 pandas_column_by_name_all_worksheets.py：

#!/usr/bin/env python3
import pandas as pd
import sys
input_file = sys.argv[1]
output_file = sys.argv[2]
data_frame = pd.read_excel(input_file, sheetname=None, index_col=None)
column_output = []
for worksheet_name, data in data_frame.items():
    column_output.append(data.loc[:, ['Customer Name', 'Sale Amount']])
selected_columns = pd.concat(column_output, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
selected_columns.to_excel(writer, sheet_name='selected_columns_all_worksheets',\
index=False)
writer.save()

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python pandas_column_by_name_all_worksheets.py sales_2013.xlsx\
output_files\pandas_output.xls

你可以打开输出文件 pandas_output.xls 查看一下结果。

4　在Excel工作簿中读取一组工作表

有些情况下，你只需要处理工作簿中的一组工作表。例如，你的工作簿可能包含很多工作表，但是你只需要处理其中的 20 个。在这种情况下，可以使用工组簿的 sheet_by_index 或 sheet_by_name 函数来处理一组工作表。

提供了一个示例来演示如何在工作簿的一组工作表中筛选特定的行。之所以这样做，是因为到目前为止，你应该能够将前面各个示例中的筛选与选择操作集成到这个示例中了。

在一组工作表中筛选特定行

基础Python

在这个示例中，我们想从第一个和第二个工作表中筛选出销售额大于 $1900.00 的那些行。要使用基础 Python 从第一个和第二个工作表中筛选出这样的行，在文本编辑器中输入下列代码，然后将文件保存为 11excel_value_meets_condition_set_of_worksheets.py：

 1 #!/usr/bin/env python3
 2 import sys
 3 from datetime import date
 4 from xlrd import open_workbook, xldate_as_tuple
 5 from xlwt import Workbook
 6 input_file = sys.argv[1]
 7 output_file = sys.argv[2]
 8 output_workbook = Workbook()
 9 output_worksheet = output_workbook.add_sheet('set_of_worksheets')
10 my_sheets = [0,1]
11 threshold = 1900.0
12 sales_column_index = 3
13 first_worksheet = True
14 with open_workbook(input_file) as workbook:
15     data = []
16     for sheet_index in range(workbook.nsheets):
17          if sheet_index in my_sheets:
18               worksheet = workbook.sheet_by_index(sheet_index)
19               if first_worksheet:
20                    header_row = worksheet.row_values(0)
21                    data.append(header_row)
22                    first_worksheet = False
23               for row_index in range(1,worksheet.nrows):
24                    row_list = []
25                    sale_amount = worksheet.cell_value\
26                    (row_index, sales_column_index)
27                    if sale_amount > threshold:
28                         for column_index in range(worksheet.ncols):
29                              cell_value = worksheet.cell_value\
30                              (row_index,column_index)
31                              cell_type = worksheet.cell_type\
32                              (row_index, column_index)
33                              if cell_type == 3:
34                                   date_cell = xldate_as_tuple\
35                                   (cell_value,workbook.datemode)
36                                   date_cell = date(*date_cell[0:3])\
37                                   .strftime('%m/%d/%Y')
38                                   row_list.append(date_cell)
39                              else:
40                                   row_list.append(cell_value)
41                    if row_list:
42                         data.append(row_list)
43    for list_index, output_list in enumerate(data):
44         for element_index, element in enumerate(output_list):
45               output_worksheet.write(list_index, element_index, element)
46 output_workbook.save(output_file)

第 10 行代码创建了一个列表变量 my_sheets，其中包含两个整数，表示要处理的工作表的索引值。

第 16 行代码创建了工作簿中所有工作表的索引值，并在这些索引值上应用一个 for 循环。

第 17 行代码检验 for 循环中要处理的索引值是否是 my_sheets 中的一个索引值。这个检验确保代码只处理那些我们想处理的工作表。

因为我们在工作表索引值之间迭代，所以在第 18 行代码中，需要使用工作簿的 sheet_by_index 函数与索引值一起引用当前工作表。

对于要处理的第一个工作表，第 19 行代码为 True，所以我们将标题行追加到 data 中，然后将 first_worksheet 设为 False。此后，和前面的示例一样，以同样的方法处理余下的数据行。对于第二个和此后要处理的工作表，脚本直接转到第 23 行代码来处理工作表中的数据行。

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python 11excel_value_meets_condition_set_of_worksheets.py sales_2013.xlsx\
output_files\11output.xls

你可以打开输出文件 11output.xls 查看一下结果。

pandas

使用 pandas 在工作簿中选择一组工作表非常容易。你只需在 read_excel 函数中将工作表的索引值或名称设置成一个列表就可以了。在这个示例中，我们创建一个索引值列表 my_sheets，然后在 read_excel 函数中设定 sheetname 等于 my_sheets。

要使用 pandas 选择一组工作表，在文本编辑器中输入下列代码，然后将文件保存为 pandas_value_meets_condition_set_of_worksheets.py：

#!/usr/bin/env python3
import pandas as pd
import sys
input_file = sys.argv[1]
output_file = sys.argv[2]
my_sheets = [0,1]
threshold = 1900.0
data_frame = pd.read_excel(input_file, sheetname=my_sheets, index_col=None)
row_list = []
for worksheet_name, data in data_frame.items():
    row_list.append(data[data['Sale Amount'].astype(float) > threshold])
filtered_rows = pd.concat(row_list, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
filtered_rows.to_excel(writer, sheet_name='set_of_worksheets', index=False)
writer.save()

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python pandas_value_meets_condition_set_of_worksheets.py\
sales_2013.xlsx output_files\pandas_output.xls

你可以打开输出文件 pandas_output.xls 查看一下结果。

5　处理多个工作簿

演示了如何为单个工作表、工作簿中所有的工作表和工作簿中的一组工作表筛选出特定的行与特定的列。这些处理工作簿的技术是非常有用的。但是，有时你需要处理多个工作簿。在这种情况下，Python 会给你惊喜，因为它可以让你自动化和规模化地进行数据处理，远远超过手工处理能够达到的限度。

重新引入了 Python 内置的 glob 模块，之前曾介绍过这个模块。前面几个示例的基础上，下面演示一下如何处理多个工作簿。

为了使用多个工作簿，首先需要创建多个工作簿。那么让我们再创建另外两个 Excel 工作簿，这样就一共有 3 个工作簿了。但是，请记住这里介绍的技术可以扩展为处理任意多的文件，只要计算机能力允许。

先从下面这个步骤开始。

(1) 打开现有的工作簿 sales_2013.xlsx。

现在，创建第二个工作簿。

(2) 将现有的 3 个工作表名称改为 january_2014、february_2014 和 march_2014。

(3) 在 3 个工作表中，将 Purchase Date 列中的年份改成 2014。

每个工作表中有 6 行数据，所以你一共需要进行 18 次修改（6 行 *3 个工作表）。除了修改年份以外，不需要修改其他内容。

(4) 将第二个工作簿保存为 sales_2014.xlsx。

图 3-10 展示了修改过日期后的 january_2014 工作表中的内容。

图 3-10：修改第一个工作簿中的数据，创建第二个工作簿

现在，创建第三个工作簿。

(5) 将现有的 3 个工作表名称改为 january_2015、february_2015 和 march_2015。

(6) 在 3 个工作表中，将 Purchase Date 列中的年份改成 2015。

每个工作表中有 6 行数据，所以你一共需要进行 18 次修改（6 行 *3 个工作表）。除了修改年份以外，不需要修改其他内容。

(7) 将第三个工作簿保存为 sales_2015.xlsx。

图 3-11 展示了修改过日期后的 january_2015 工作表中的内容。

图 3-11：修改第二个工作簿中的数据，创建第三个工作簿

工作表计数以及每个工作表中的行列计数

在某些情况下，你知道要处理的工作簿中的内容。但是，有些时候工作簿不是你创建的，所以你不清楚其中的内容。与 CSV 文件不同，Excel 工作簿可以包含多个工作表，所以如果你不清楚这些工作表中的内容，那么在开始处理工作表之前，获取一些关于工作表的描述性信息则是非常重要的。

如果想知道一个文件夹中工作簿的数量，每个工作簿中工作表的数量，以及每个工作表中行与列的数量，在文本编辑器中输入下列代码，然后将文件保存为 12excel_introspect_all_workbooks.py：

1 #!/usr/bin/env python3
 2 import glob
 3 import os
 4 import sys
 5 from xlrd import open_workbook
 6 input_directory = sys.argv[1]
 7 workbook_counter = 0
 8 for input_file in glob.glob(os.path.join(input_directory, '*.xls*')):
 9     workbook = open_workbook(input_file)
10     print('Workbook: %s' % os.path.basename(input_file))
11     print('Number of worksheets: %d' % workbook.nsheets)
12     for worksheet in workbook.sheets():
13          print('Worksheet name:', worksheet.name, '\tRows:',\
14                    worksheet.nrows, '\tColumns:', worksheet.ncols)
15     workbook_counter += 1
16 print('Number of Excel workbooks: %d' % (workbook_counter))

第 2 和 3 行代码分别导入 Python 内置的 glob 模块和 os 模块，以使我们可以使用其中的函数识别和解析待处理文件的路径名。

第 8 行代码使用 Python 内置的 glob 模块和 os 模块创建了一个要处理的输入文件列表，并对这个输入文件列表应用 for 循环，这行代码可以使我们对所有要处理的工作簿进行迭代。

第 10~14 行代码在屏幕上打印出每个工作簿的信息。第 10 行代码打印工作簿的名称。第 11 行代码打印工作簿中工作表的数量。第 13 和 14 行代码打印出工作簿中工作表的名称和每个工作表中行与列的数量。

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python 12excel_introspect_all_workbooks.py "C:\Users\Clinton\Desktop"

你应该可以看到输出被打印到屏幕上，如图 3-12 所示。

图 3-12：处理多个工作簿的 Python 脚本的输出

输出显示，脚本处理了 3 个工作簿，还打印出了 3 个工作簿的名称（例如：sales_2013.xls）、每个工作簿中 3 个工作表的名称（例如：january_2013），以及每个工作表中行与列的数量（例如：7 行和 5 列）。

当你对要处理的文件不太熟悉的时候，打印出文件的一些描述性信息是非常有用的。知道了文件的数量以及每个文件中行与列的数量，你就可以大致了解文件处理任务量和文件内容的一致性了。

从多个工作簿中连接数据

基础Python

要使用基础 Python 将多个工作簿中所有工作表的数据垂直连接成一个输出文件，在文本编辑器中输入下列代码，然后将文件保存为 13excel_concat_data_from_multiple_workbook.py：

1 #!/usr/bin/env python3
 2 import glob
 3 import os
 4 import sys
 5 from datetime import date
 6 from xlrd import open_workbook, xldate_as_tuple
 7 from xlwt import Workbook
 8 input_folder = sys.argv[1]
 9 output_file = sys.argv[2]
10 output_workbook = Workbook()
11 output_worksheet = output_workbook.add_sheet('all_data_all_workbooks')
12 data = []
13 first_worksheet = True
14 for input_file in glob.glob(os.path.join(input_folder, '*.xls*')):
15     print os.path.basename(input_file)
16     with open_workbook(input_file) as workbook:
17          for worksheet in workbook.sheets():
18               if first_worksheet:
19                    header_row = worksheet.row_values(0)
20                    data.append(header_row)
21                    first_worksheet = False
22               for row_index in range(1,worksheet.nrows):
23                    row_list = []
24                    for column_index in range(worksheet.ncols):
25                         cell_value = worksheet.cell_value\
26                         (row_index,column_index)
27                         cell_type = worksheet.cell_type\
28                         (row_index, column_index)
29                         if cell_type == 3:
30                              date_cell = xldate_as_tuple\
31                              (cell_value,workbook.datemode)
32                              date_cell = date(*date_cell[0:3])\
33                              .strftime('%m/%d/%Y')
34                              row_list.append(date_cell)
35                         else:
36                              row_list.append(cell_value)
37                    data.append(row_list)
38 for list_index, output_list in enumerate(data):
39     for element_index, element in enumerate(output_list):
40               output_worksheet.write(list_index, element_index, element)
41 output_workbook.save(output_file)

第 13 行代码创建了一个布尔型（就是 True/False）变量 first_worksheet，用来区别要处理的第一个工作表和其他后续工作表。对于要处理的第一个工作表，第 18 行代码为 True，所以我们将标题行追加到 data 中，然后将 first_worksheet 设为 False。

对于第一个工作表中余下的数据行和后续工作表中的所有行，我们跳过标题行，处理数据行。因为第 22 行代码中的 range 函数不是从 0 开始，而是从 1 开始的，所以我们知道是从第二行开始处理的。

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python 13excel_ concat_data_from_multiple_workbooks.py "C:\Users\Clinton\Desktop"\
output_files\13output.xls

你可以打开输出文件 13output.xls 查看一下结果。

pandas

pandas 提供了 concat 函数来连接数据框。如果你想把数据框一个一个地垂直堆叠起来，那么就要设置参数 axis=0。如果你想把数据框一个一个地平行连接起来，那么就要设置参数 axis=1。此外，如果你需要基于某个关键字列连接数据框，pandas 中的 merge 函数可以提供类似 SQL join 的操作。

要使用 pandas 将多个工作簿中所有工作表的数据垂直连接成一个输出文件，在文本编辑器中输入下列代码，然后将文件保存为 pandas_concat_data_from_multiple_workbook.py：

#!/usr/bin/env python3
import pandas as pd
import glob
import os
import sys
input_path = sys.argv[1]
output_file = sys.argv[2]
all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))
data_frames = []
for workbook in all_workbooks:
    all_worksheets = pd.read_excel(workbook, sheetname=None, index_col=None)
    for worksheet_name, data in all_worksheets.items():
         data_frames.append(data)
all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='all_data_all_workbooks',\
index=False)
writer.save()

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python pandas_concat_data_from_multiple_workbooks.py "C:\Users\Clinton\Desktop"\
output_files\pandas_output.xls

你可以打开输出文件 pandas_output.xls 查看一下结果。

为每个工作簿和工作表计算总数和均值

基础Python

要使用基础 Python 为多个工作簿计算工作表级别和工作簿级别的统计量，在文本编辑器中输入下列代码，然后将文件保存为 14excel_sum_average_multiple_workbook.py：

1 #!/usr/bin/env python3
 2 import glob
 3 import os
 4 import sys
 5 from datetime import date
 6 from xlrd import open_workbook, xldate_as_tuple
 7 from xlwt import Workbook
 8 input_folder = sys.argv[1]
 9 output_file = sys.argv[2]
10 output_workbook = Workbook()
11 output_worksheet = output_workbook.add_sheet('sums_and_averages')
12 all_data = []
13 sales_column_index = 3
14 header = ['workbook', 'worksheet', 'worksheet_total', 'worksheet_average',\
15                         'workbook_total', 'workbook_average']
16 all_data.append(header)
17 for input_file in glob.glob(os.path.join(input_folder, '*.xls*')):
18     with open_workbook(input_file) as workbook:
19          list_of_totals = []
20          list_of_numbers = []
21          workbook_output = []
22          for worksheet in workbook.sheets():
23               total_sales = 0
24               number_of_sales = 0
25               worksheet_list = []
26               worksheet_list.append(os.path.basename(input_file))
27               worksheet_list.append(worksheet.name)
28               for row_index in range(1,worksheet.nrows):
29                    try:
30                         total_sales += float(str(worksheet.cell_value\
31                         (row_index,sales_column_index))\
32                         .strip('$').replace(',',''))
33                         number_of_sales += 1.
34                    except:
35                          total_sales += 0.
36                          number_of_sales += 0.
37                average_sales = '%.2f' % (total_sales / number_of_sales)
38                worksheet_list.append(total_sales)
39                worksheet_list.append(float(average_sales))
40                list_of_totals.append(total_sales)
41                list_of_numbers.append(float(number_of_sales))
42                workbook_output.append(worksheet_list)
43           workbook_total = sum(list_of_totals)
44           workbook_average = sum(list_of_totals)/sum(list_of_numbers)
45           for list_element in workbook_output:
46                list_element.append(workbook_total)
47                list_element.append(workbook_average)
48           all_data.extend(workbook_output)
49  
50  for list_index, output_list in enumerate(all_data):
51      for element_index, element in enumerate(output_list):
52           output_worksheet.write(list_index, element_index, element)
53  output_workbook.save(output_file)

第 12 行代码创建了一个空列表 all_data，用来保存要写入输出文件的所有行。第 13 行代码创建了一个变量 sales_column_index，保存 Sale Amount 列的索引值。

第 14 行代码为输出文件创建了一个列标题列表，并使用第 16 行代码将其追加到 all_data 中。

在第 19、20 和 21 行代码中，分别创建了 3 个列表。list_of_totals 用来保存工作簿中所有工作表的销售额总计。同样，list_of_numbers 用来保存工作簿的所有工作表中用来计算总销售额的销售额数据个数。第三个列表，workbook_output，用来保存要写入输出文件的所有输出列表。

第 25 行代码创建了一个列表 worksheet_list，用来保存要保留的所有工作表的信息。在第 26 和 27 行代码中，将工作簿名称和工作表名称追加到 worksheet_list 中。同样，在第 38 和 39 行代码中，将销售额总计和均值追加到 worksheet_list 中。在第 42 行代码中，将 worksheet_list 追加到 workbook_output 中，在工作簿级别保存信息。

在第 40 和 41 行代码中，将工作表的销售额总计和销售额数据个数分别追加到 list_of_totals 和 list_of_numbers 中，这样我们可以对所有工作表保存这些值。在第 43 和 44 行代码中，使用这两个列表计算出工作簿的销售额总计和销售额均值。

在第 45~47 行代码中，我们在 workbook_output 的各个列表之间迭代（每个工作簿有 3 个列表，因为每个工作簿有 3 个工作表），并将工作簿级别的销售额总计和均值追加到每个列表中。

当获得了所有要为工作簿保留的信息之后（就是 3 个列表，每个工作表有一个列表），就将这些列表扩展到 all_data 中。我们使用 extend，不是 append，以使 workbook_output 中的每个列表都会成为 all_data 中的一个独立元素。这样的话，在处理完所有工作簿之后，all_data 就是一个具有 9 个元素的列表，每个元素都是一个列表。否则，如果使用 append，all_data 中就会只有 3 个元素，每个元素都是一个列表的列表。

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python 14excel_sum_average_multiple_workbooks.py "C:\Users\Clinton\Desktop"\
output_files\14output.xls

你可以打开输出文件 14output.xls 查看一下结果。

pandas

pandas 可以直接在多个工作簿之间迭代，并可以同时在工作簿级别和工作表级别计算统计量。在下面的脚本中，为工作簿中的每个工作表计算统计量，然后将结果连接成一个数据框。接下来，计算工作簿级别的统计量，将它们转换成一个数据框，然后通过基于工作簿名称的左连接将两个数据框合并在一起，并将结果数据框添加到一个列表中。当所有工作簿级别的数据框都进入列表之后，将这些数据框连接成一个独立数据框，并写入输出文件。

要使用 pandas 计算工作表级别和工作簿级别的统计量，在文本编辑器中输入下列代码，然后将文件保存为 pandas_sum_average_multiple_workbook.py：

#!/usr/bin/env python3
import pandas as pd
import glob
import os
import sys
input_path = sys.argv[1]
output_file = sys.argv[2]
all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))
data_frames = []
for workbook in all_workbooks:
    all_worksheets = pd.read_excel(workbook, sheetname=None, index_col=None)
    workbook_total_sales = []
    workbook_number_of_sales = []
    worksheet_data_frames = []
    worksheets_data_frame = None
    workbook_data_frame = None
    for worksheet_name, data in all_worksheets.items():
         total_sales = pd.DataFrame([float(str(value).strip('$').replace(\
         ',',''))
         for value in data.loc[:, 'Sale Amount']]).sum()
         number_of_sales = len(data.loc[:, 'Sale Amount'])
         average_sales = pd.DataFrame(total_sales / number_of_sales)
　
         workbook_total_sales.append(total_sales)
         workbook_number_of_sales.append(number_of_sales)
         data = {'workbook': os.path.basename(workbook),
                   'worksheet': worksheet_name,
                   'worksheet_total': total_sales,
                   'worksheet_average': average_sales}
　
         worksheet_data_frames.append(pd.DataFrame(data, \
         columns=['workbook', 'worksheet', \
         'worksheet_total', 'worksheet_average']))
    worksheets_data_frame = pd.concat(\
    worksheet_data_frames, axis=0, ignore_index=True)
    workbook_total = pd.DataFrame(workbook_total_sales).sum()
    workbook_total_number_of_sales = pd.DataFrame(\
    workbook_number_of_sales).sum()
    workbook_average = pd.DataFrame(\
    workbook_total / workbook_total_number_of_sales)
　
    workbook_stats = {'workbook': os.path.basename(workbook),
                         'workbook_total': workbook_total,
                         'workbook_average': workbook_average}
    workbook_stats = pd.DataFrame(workbook_stats, columns=\
    ['workbook', 'workbook_total', 'workbook_average'])
    workbook_data_frame = pd.merge(worksheets_data_frame, workbook_stats, \
    on='workbook', how='left')
    data_frames.append(workbook_data_frame)
all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='sums_and_averages', \
index=False)
writer.save()

要运行这个脚本，在命令行中输入以下命令，然后按回车键：

python pandas_sum_average_multiple_workbooks.py "C:\Users\Clinton\Desktop"\
output_files\pandas_output.xls

你可以打开输出文件 pandas_output.xls 查看一下结果。