Python 自动化办公（一）：读取 Excel - 1

读取 Excel

openpyxl 是个读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库。简单易上手、功能全面，

几乎可以实现所有的 Excel 功能，无论是单元格的样式还是图表功能都应有尽有，作为自动化办公的第一篇，下面开始 读取 Excel 的学习。

更多请参阅 官方文档。

工作簿的结构

工作簿->表->单元格

workbook->worksheet->cell

读取 Excel 工作簿

步骤：

1. 导入加载函数 load_workbook
2. 创建工作簿（workbook）

from openpyxl import load_workbook

workbook = load_workbook("filepath")
print(workbook)  # <openpyxl.workbook.workbook.Workbook object at 地址>

# f = open("uri", mode="rb")  # uri 可是本地或网络路径
# wb = load_workbook(f)
# f.close()

• 正确的文件路径将得到正确的结果：<openpyxl.workbook.workbook.Workbook object at 地址>。
• 可以看到最后是创建了一个 Workbook 对象的实例。
• 打开本地文件一般使用第一种方式。

读取 Sheets表格

一个工作簿中可以有多张表格（sheet）。

选中一张表大致有两种模式：

1. 根据表序号选择表，需要引用所有表
2. 根据表名选择表，只需要先拿到表名，引用一张表

# 根据表序号选择表
sheets = workbook.worksheets
print(sheets)  # [<Worksheet "Table 0">, <Worksheet "Table 1">, <Worksheet "Table 2">]
sheet0 = sheets[0]
print(sheet0)  # <Worksheet "Table 0">

# 根据表名选择表
sheetnames = workbook.sheetnames  # ['Table 0', 'Table 1', 'Table 2']
print(sheetnames)
sheetname_1 = sheetnames[1]
sheet1 = workbook[sheetname_1]
print(sheet1)  # <Worksheet "Table 1">

• 工作簿.worksheets 的结果是：多张表对象组成的列表，可以通过索引进行检阅。
• 工作簿.sheetnames 的结果是：所有表的表名组成的列表。
• 可以看到结果是工作表对象 Worksheet，打印结果包含表名，如 Table 0。

读取指定单元格

选中指定单元格有两种方式：

1. 使用 cell 方法
2. 直接对表进行索引 ：其一，行列值索引；其二，坐标值索引。

# 使用 `cell` 方法
cell_2_3 = sheet0.cell(2, 3)
print(cell_2_3)  # <Cell 'Table 0'.C2>

# 直接对表进行 行列值索引 
cell_5_3 = sheet0[5][2]  # 列从 0 开始索引
print(cell_5_3)

cell_C_5 = sheet0["C5"]	 # 坐标的列是使用大写字母来表示的
print(cell_C_5 is cell_5_3)

• 表·.cell(行, 列, 默认值) 的结果是一个单元格对象 Cell，二行三列的位置（C2：2行C列）。当给单元格没有值时，会将默认值给到单元格对象，但不会修改表格。
• 注意：直接对表进行行列索引获取单元格时，行从 1 开始索引，列从 0 开始索引。这是因为，sheets[5] 是获取第五行的所有单元格并放入一个元组，再对元组索引就要从 0 开始了。
• 注意：使用坐标值进行索引，列是用大写字母表示的，且放在行的前面。

读取单元格的值

按照个人理解，一个单元格的值包括除了样式外的几乎所有属性。其不仅包括表面值，还包括坐标值、行列数、注释、数据类型、

1.value，值，internal_value，内部值。
2. coordinate，单元格坐标：字母表示列的格式。
3. row，行；column，列。
4. comment，注释：没有则为 None。
5. hyperlink，超链接：没有则为 None。
6. parent，父节点，隶属于的表名。
7. encoding，编码格式，一般为 UTF-8。

value_2_3 = cell_2_3.value
internal_value_2_3 = cell_2_3.internal_value

coordinate = cell_2_3.coordinate  # 单元格坐标：C2
row = cell_2_3.row				  # 2 行
column = cell_2_3.column		  # 3 列
comment = cell_2_3.comment		  # None：没有注释
hyperlink = cell_2_3.hyperlink	  # None：没有超链接
parent = cell_2_3.parent		  # 表名 'Table 0'

• 空单元格的值返回 None，或创建单元格实例时设置的默认值（不修改）。
• value 和 internal_value 的区别

读取单元格的样式

单元格可以说是工作簿中最小、最基本的结构，得益于其复杂多变的样式，表格以及工作簿才能美观地展示内容。

要实现复杂又美观的样式是不简单的。下面简要介绍几种常用样式：

1. font，字体样式
2. fill，填充样式
3. border，单元格边框
4. alignment，单元格对齐方式
5. style，样式
6. protection，安全模式：是否锁定？是否隐藏？

font = cell_2_3.font                    # 1.字体样式：
fill = cell_2_3.fill                    # 2.填充样式：
border = cell_2_3.border                # 3.单元格边框：
alignment = cell_2_3.alignment          # 4.单元格对齐方式：
style = cell_2_3.style					# 5.样式：常规...
protection = cell_2_3.protection        # 6.保护(安全)模式

• 每个样式都有一些子属性，比如说，字体、填充和边框都有颜色属性，对齐也有水平方向和垂直方向等。
• 要了解更多样式属性，自行查阅，使用 Excel 打开一个工作簿文件，选中某个单元格->右键->设置单元格格式
  

读取行

所有读取行相关方法大致为三种：

1. 行索引，语法为：表[行数]，返回该行的所有单元格。
2. 生成器，语法为：表.rows，返回一个行生成器，可以使用生成器的语法再进行获取详细的值；也可以转换为迭代器再进一步操作。更多请参阅 Python 专题：生成器。
3. 迭代器，语法为：表.iter_rows(min_row, max_row, min_col, max_col, values_only)，可以传五个参数，可以规定区域（相对来说，功能最强大）， Python 专题：迭代器。

读取一行

1. 如果知道行数，直接进行行索引是最方便的。
2. 如果不知道行数，但是知道改行的某个单元格的值（具体的姓名或身份id），可以使用迭代器遍历寻找该行，或者使用生成器。

i = 2
row_i = sheet0[i]		# 获取第二行
print(row_i[3].value)	# 第二行第四列单元格的值

rows = sheet0.rows		# 生成器
row = rows.send(None)	# 启动
while row[0].value != "Mary":	# name column has the one 'Mary' at least
	row = rows.send(0)
rows.close()

• 注意：得到一行的单元格后存放在列表里，所以后续的索引是从 0 开始的。

读取多行

获取多行一般使用迭代器：

row_iterator = sheet0.iter_rows(min_row=2, value_only=False)	# 第二行开始检索，第一行是列描述
for row in row_iterator:
	print(row[2])	# row 是一个元组，这里打印第二列

• 使用行迭代器时，应该注意其参数中的行列选择可以锁定区域。
• value_only 参数
  • 为 False 时， row 获取的是一行的完整单元格
  • 为 Ture 时， row 获取的是一行的完整单元格的值

一个统计总分的简单案例

from openpyxl import load_workbook
import os
import setting


filepath = os.path.join(setting.DATA_DIR, 'score.xlsx')

wb = load_workbook(filepath)

data = {}

for sheetname in wb.sheetnames:
    sheet = wb[sheetname]
    s1, s2, s3, s4 = 0, 0, 0, 0
    for row in sheet.iter_rows(min_row=2, min_col=2, values_only=True):
        s1 += row[0]
        s2 += row[1]
        s3 += row[2]
        s4 += row[3]
    data[sheetname] = {'语文': s1, '数学': s2, '英语': s3, '理综': s4}

print(data)
# {'开学考': {'语文': 2950, '数学': 3475, '英语': 3400, '理综': 6550}, '期中考': {'语文': 2950, '数学': 3475, '英语': 3400, '理综': 6550}, '期末考': {'语文': 2950, '数学': 3475, '英语': 3400, '理综': 6550}}

• 根据表结构，在选择区域的时候些许偏差。。

加载工作簿的函数

Python 中可以使用包 openpyxl 中的 load_workbook() 函数加载一个工作簿文件，并返回一个工作簿对象。

需要了解更多 openpyxl 的内容可以打开包进行查看，没有非常复杂的知识点，懂点英语和工作簿的专业术语能够快速体会理解各种功能。

下面对 load_workbook 函数进行参数以及步骤的微注解：

def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
                  data_only=False, keep_links=True):
    """Open the given filename and return the workbook
    
        使用lazy load的话，工作簿将只读，并且所有的表都是`openpyxl.worksheet.iter_worksheet.IterableWorksheet`。
    """
    reader = ExcelReader(filename, read_only, keep_vba,
                        data_only, keep_links)
    reader.read()
    return reader.wb

• 参数值

  • filename，一方面，和 open 函数一样，需要的是一个文件路径；另一方面，又可以是 open 函数得到的句柄。
  • read_only，顾名思义，就是是否只读：Ture，只读；False，非只读。
  • keep_vba，即 Visual Basic for Applications，尽管选择为 True， VB 脚本也不一定可以使用。更多请参阅来自 MSN 的 Office VBA 入门。
  • data_only，是否只读取表面值，有的单元格的值可能是表达式（formula）计算获得的，如果保持默认的 False，则读取的是表达式。
  • keep_links，是否要保持联系到其他工作簿的链接，默认是 True。

• 返回值

  • openpyxl.workbook.Workbook，即返回一个 Workbook 对象，工作簿对象。

• 函数体

  • 函数体很简洁，就是一个阅读器读取一下 ExcelReader().read()，然后返回工作簿。