用Python处理Excel

发表于 | 分类于 ， ，

Excel 是 Windows 环境下强大的电子表格程序，Python 提供了许多处理 Excel 文件的工具包，我们在本文中总结了 openpyxl（处理 xlsx 文件）、xlrd 和 xlwt（处理 xls 文件） 这三个工具包的基本应用方法。下文中使用的两个数据集 example.xlsx 和 censuspopdata.xlsx 可以从Automate the Boring Stuff with Python上下载到。

Excel文档

一个 Excel 电子表格文件称为一个工作簿（workbook），一个工作簿保存在扩展名为 .xlsx（或 .xls） 的文件中。每个工作簿可以包含多个工作表（sheet），用户当前查看的表或关闭 Excel 前最后查看的表称为活动表（active）。每个表都有一些列（column）和一些行（row），列的地址是从 A 开始的字母，行地址是从 1 开始的数字。指定列和行号就可以组合成为一个坐标（coordinate），该坐标明确指向一个单元格（cell），每个单元格都包含一个数字或文本的值（value）。

使用openpyxl处理Excel

导入 openpyxl 模块后，通过把 Excel 文档路径传入 openpyxl.load_workbook() 函数就可以打开该文档，返回一个 Workbook 对象。

1
2
3
4
import openpyxl

wb = openpyxl.load_workbook('./data/example.xlsx')
print(type(wb)) # <class 'openpyxl.workbook.workbook.Workbook'>

这个 WorkBook 对象包含了整个 Excel 文件的所有信息，我们可以从中进一步取得工作表：

1
2
3
4
5
6
7
8
print(wb.sheetnames) # ['Sheet1', 'Sheet2', 'Sheet3']

ws = wb['Sheet1']
print(type(ws)) # <class 'openpyxl.worksheet.worksheet.Worksheet'>
print(ws.title) # Sheet1

active_ws = wb.active
print(active_ws) # <Worksheet "Sheet1">

每个工作表由一个 Worksheet 对象表示，可以通过向 Workbook 进行索引（用表名字符串）获得。可以通过 Workbook 对象的 active 属性取得工作簿中的活动表。在取得 Worksheet 对象后，可以通过title 属性取得或修改它的名称。

进一步地，我们可以从 Worksheet 对象中获取每个单元格所对应的 Cell 对象：

1
2
3
4
c = ws['A1']
print(c) # <Cell 'Sheet1'.A1>
print(c.value) # 2015-04-05 13:34:02
print((c.row, c.column, c.coordinate)) # (1, 1, 'A1')

获取 Cell 对象最直接的方法就是通过将 列号与行号 组合成一个字符串（即坐标）直接作为 Worksheet 的索引，而单元格中的值可以通过 Cell 对象的 value 属性获取，同时 Cell 对象中还包含了单元格对应的行、列信息（都是从 1 开始计序的整数）以及坐标。

1
2
3
4
5
cell = ws.cell(row=1, column=2)
print(cell) # <Cell 'Sheet1'.B1>
print(cell.value) # Apples

print((ws.max_column, ws.max_row)) # (3, 7)

还有一种获取单元格的方法，就是调用表的 cell() 方法时，传入整数作为 row 和 column 参数的取值。另外，Worksheet 对象还包含了 max_column 和 max_row 属性，用以确定表的大小。

我们看到表中的列号是由字母表示的，并不利于计算，我们可以调用 column_index_from_string 函数将字母转换为数字；同样的，也有接口 get_column_letter() 实现数字到字母的转换：

1
2
3
4
from openpyxl.utils import get_column_letter,column_index_from_string

print(get_column_letter(27)) # AA
print(column_index_from_string('AB')) # 28

Worksheet 对象还支持切片操作，取到表格中一行、一列或一个矩形区域中的所有 Cell 对象，然后就可以循环遍历这个切片中的所有单元格来进行相应的数据处理与分析：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
print(ws['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 row_cells in ws['A1':'C3']:
    for cell in row_cells:
        print(cell.coordinate, cell.value)
'''
A1 2015-04-05 13:34:02
B1 Apples
C1 73
A2 2015-04-05 03:41:23
B2 Cherries
C2 85
A3 2015-04-06 12:46:51
B3 Pears
C3 14
'''

可以看到，切片操作返回的是一个元组，元组中的每个元素代表 Worksheet 的一行，该元素又是一个元组，其元素则是按列展开的 Cell 对象，遍历时要按这样的嵌套关系进行循环。

1
2
print(ws[1]) # (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
print(ws['B']) # (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)

如果切片操作给定的是一个单一的数字，则可以得到对应的行；同理给定一个单一的字母，则可以得到对应的列；返回的数据类型都是以 Cell 对象为元素的元组。

读取 Excel 进行数据处理与分析后我们可能需要把分析结果也写入到一个 Excel 文件中以方便呈现，此时，我们只需要创建一个新的空 Workbook 对象，把将相应的数据封装进 Workbook 对象的表、单元格等子对象中：

1
2
3
4
5
6
7
8
9
10
11
12
wb = openpyxl.Workbook()
print(wb.sheetnames) # ['Sheet']
sheet = wb.active
print(sheet.title) # Sheet
sheet.title = 'First Sheet'
print(wb.sheetnames) # ['First Sheet']

sheet = wb['First Sheet']
sheet['A1'] = "Hello world"
print(sheet['A1'].value) # Hello world

wb.save('./demo.xlsx')

完成 Workbook 对象或它的工作表、单元格的所有操作后，调用 save() 方法就可以将该 Workbook 以一个 Excel 文件的形式保存下来。

1
2
3
4
5
6
wb.create_sheet()
print(wb.sheetnames) # ['First Sheet', 'Sheet']
wb.create_sheet(index=1, title='Second Sheet')
print(wb.sheetnames) # ['First Sheet', 'Second Sheet', 'Sheet']
wb.remove(wb['Second Sheet'])
print(wb.sheetnames) # ['First Sheet', 'Sheet']

创建一个 Workbook 自然会得到一个激活的工作表，我们也可以通过 create_sheet() 方法继续创建新的工作表，新创建的工作表默认会排在最后，我们可以通过 index 参数指明它的位置，以及用 title 参数指定其名称。对于不需要的工作表，可以通过 remove() 方法删除，但要注意的是该方法的参数是 Worksheet 对象，而非工作表名称的字符串。

至此，我们已经介绍完了用 openpyxl 操作 Excel 文档的基本方法，下面我们通过一个实例来演示我们最常用的 Excel 处理与分析场景，即遍历 Excel 文档的行列数据，取出指定单元格的数据进行统计与分析：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import pprint

wb = openpyxl.load_workbook('./data/censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
# 存储普查区数目和人口数据
countryData = {}

for row in range(2,sheet.max_row+1):
    state = sheet['B'+str(row)].value # 州
    country = sheet['C'+str(row)].value # 县
    pop = sheet['D'+str(row)].value # 人口

    countryData.setdefault(state, {})
    countryData[state].setdefault(country, {'tracts':0, 'pop':0}) # 州县对应关系

    countryData[state][country]['tracts'] += 1 # 统计每个县的普查区数目
    countryData[state][country]['pop'] += int(pop) # 统计每个县的总人口

pprint.pprint(countryData, indent=4)
'''
{   'AK': {   'Aleutians East': {'pop': 3141, 'tracts': 1},
              'Aleutians West': {'pop': 5561, 'tracts': 2},
              'Anchorage': {'pop': 291826, 'tracts': 55},
              'Bethel': {'pop': 17013, 'tracts': 3},
              ... ...
'''

当我们用循环语句批量处理 Excel 文件时，需要处理好关闭文件的操作，由于 wb.save() 函数中包含了关闭文件的操作，所以只需要在读文件操作的最后增加一行关闭文件 wb.close() 的代码。

用xlrd、xlwt处理Excel

首先声明一下，尽管较老版本（1.2.0以前）的 xlrd 包支持对 xlsx 文件的读取与处理，但我们还是推荐统一用 openpyxl 去处理 xlsx 文件，用 xlrd 和 xlwt 处理 xls 文件。

使用 xlrd 与 xlwt 处理 Excel 文档的思想理念与前面介绍的是一致的，尽管相应的对象定义有出入，但仍然遵循 workbook -> sheet -> cell 这样的嵌套关系：

  • 获取工作簿对象：book = xlrd.open_workbook(‘./data/example.xlsx’)
  • 获取所有工作表名称：names = book.sheet_names()，结果工作表名称组成的列表
  • 根据索引获取工作表对象：sheet = book.sheet_by_index(0)，即 sheet.name == ‘Sheet1’
  • 根据名称获取工作表对象：sheet = book.sheet_by_name(‘Sheet1’)
  • 获取工作表行数：rows = sheet.nrows
  • 获取工作表列数：cols = sheet.ncols
  • 获取工作表某一行的内容：row = sheet.row_values(1)，结果为指定行的数据组成的列表
  • 获取工作表某一列的内容：col = sheet.col_values(2)，结果为指定列的数据组成的列表
  • 获取工作表某一单元格的内容：cell = sheet.cell_value(1, 2)，结果为指定单元格（1行2列）的内容

另外，获取单元格的方法还有 sheet.cell(m, n).value 或者 sheet.row(m)[n].value 或 sheet.col(n)[m].value（用函数加中括号联合索引）。

可以看出，xlrd 中每一种对象的索引都提供了按数字顺序的索引模式，且从 0 开始计序，这样的设计更符合我们处理二维数组的常规理念，所以上述对美国人口普查数据的处理场景可以如下实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import xlrd,pprint

wb = xlrd.open_workbook('./data/censuspopdata.xlsx')
sheet = wb.sheet_by_name('Population by Census Tract')
# 存储普查区数目和人口数据
countryData = {}

for row in range(2, sheet.nrows):
    state = sheet.cell_value(row, 1)  # 州
    country = sheet.cell_value(row, 2)  # 县
    pop = sheet.cell_value(row, 3)  # 人口

    countryData.setdefault(state, {})
    countryData[state].setdefault(country, {'tracts': 0, 'pop': 0})  # 州县对应关系

    countryData[state][country]['tracts'] += 1  # 统计每个县的普查区数目
    countryData[state][country]['pop'] += int(pop)  # 统计每个县的总人口

pprint.pprint(countryData, indent=4)

如果我们要输出一个 Excel 文档，则需要导入 xlwt 模块，它只能保存为 .xls 文件（最多支持65536行数据）：

  • 创建工作簿：book = xlwt.Workbook()，可设置 encoding 参数
  • 创建工作表：sheet = book.add_sheet(‘Sheet1’)
  • 向单元格写入内容：sheet.write(1, 2, ‘Hello world’)
  • 保存工作簿：book.save(‘./demo.xls’)

同前文所述，xlwt 模块的 save( ) 函数内置了关闭文件的操作，所以，我们也只需要在使用 xlrd 模块读取 Excel 文件的最后完成关闭文件的操作，但是 xlrd 并没有提供类似的 close( ) 函数，我们需要使用下面的两行代码来实现文件关闭的功能。

1
2
book.release_resources()
del book