【导语】：openpyxl 和 formulas 是两个成熟的开源库，在Python中借助这两个库，处理Excel电子表格，可以实现自动访问、处理表格中数据的功能，省时高效，不易出错，是处理Excel表格的一种好办法。
简介
Excel在工作中很常见，许多公司的软件项目都会用到它。对于应用程序开发者而言，我们经常需要将Excel文件转换为应用程序。大多数情况下我们都把Excel作为数据的导出格式，有时也将其作为数据的输入格式。
虽然Excel并不是一个软件领域的专用语言，但一些软件领域的专家常常需要用其处理问题。这就导致会“说”Excel的软件更受用户的青睐。即使从长远来看，我们的目标是把Excel变成一个应用程序或者软件领域的一种语言(DSL)，但能自动处理Excel中的数据也是一个重要方向。
在以下情景中，自动处理Excel数据将非常有用：
例如在一个项目中，许多用户需要使用某些相同的Excel文件，而应用程序通过处理这些Excel文件，让用户使用起来更加方便。或者当一些用户想用Excel中的数据生成带有附加图表和formulas的报告时，应用程序可以生成一个电子表格，用来输出一些查询或计算结果，方便用户使用这些数据。
在本文中，我们将探索如何使用openpyxl库和其他工具，结合Python处理Excel电子表格。主要分为以下几点:
-
处理Excel文件，用不同的方法访问其中的数据;
-
使用formulas;
-
输出Excel文件。
注意:本文中我们处理的只是“现代”(2010)基于xml的Excel文件，文件扩展名为.xlsx。Openpyxl不支持之前的二进制Excel文件(.xls)，针对这类文件有其他的解决方案，在本文中不做讨论。本文中的所有示例代码均可在GitHub仓库中找到。
项目地址：
https://github.com/Strumenta/article-python-excel
安装
下面，我们设定一个类似unix的环境-Linux、OSX或者WSL。第一步，创建一个目录来存放我们的项目:
mkdir python-excel
cd python-excel
如果想要把代码放到Github上，我们可以在Github上创建一个仓库，然后把它克隆到本地：
git clone <repo-url> python-excel
cd python-excel
这将使我们能在GitHub为Python生成一个.gitignore文件，为我们生成一个README文件和一个license文件。在创建了项目根目录之后，我们可以使用pip安装来openpyxl。我们需要设置一个虚拟环境，这样就不会影响到系统的Python安装:
python -m venv .venv
source .venv/bin/activate
激活环境后，我们应该可以在shell prompt中看到：
(.venv) python-excel (main*) »
随后我们写一个requirements.txt文件,列出我们的依赖包：
echo openpyxl > requirements.txt
即使当前只有一个依赖包，也要将它列在一个可以被机器解析的文件中，这样做会使其他开发人员更容易地使用我们的项目-即使在之后的版本中我们已经忘记了这个依赖包。之后用pip安装所需的依赖包:
python -m pip install -r requirements.txt
简单使用
1.加载一个Excel文件
在Openpyxl中，我们把Excel文件称为"workbook"，用openpyxl.workbook.Workbook类的实例来表示，打开一个Excel文件非常简单：
wb = load_workbook(path)
调用load_workbook的结果
Openpyxl打开文件后，一般可以同时进行读取和写入工作，除非我们给load_workbook设置一个read_only=True参数，表示只读取文件，当我们使用完一个Excel文件后，必须关闭它:
wb.close()
不幸的是，Workbook不是一个“文件管理者”，所以不能用Python中的语句来自动关闭它。即使在一些exceptions的情况中，也必须得手动关闭文件：
wb = load_workbook(path)
try:
# Use wb...
finally:
wb.close()
2.处理一个Excel文件 - 通用案例
通常，Workbooks中可能有几个表，我们需要选择Excel文件中的一个表，访问其中的数据。随后，我们再学习如何处理多个表。现在，假设我们对active工作表中的数据比较感兴趣——当用户在他们的应用中打开文件就会看到的工作表:
sheet = wb.active
这实际上是文档中最常用的表。我们可以用不同的方法访问表格中的数据。我们可以使用Pythonic生成器每次处理一行数据
(1)对行进行遍历:
for row in sheet.rows():
# Do something with the row
rows()产生的行,本身就是一个生成器，我们可以遍历它们:
for row in sheet.rows():
for cell in row:
# Do something with the cell
还可以根据索引访问数据：
for row in sheet.rows():
header = row[2]
实际上，表格本身就是可以按行进行迭代的，所以我们可以忽略所有行:
for row in sheet:
pass
(2)使用cols方法对列进行遍历。
for col in sheet.cols():
# Use the column
遍历列与遍历行的操作基本相同:它们本身都是可迭代的，并且可以通过索引寻址。
(3)通过地址访问单元格。
如果我们需要某个单元格中的数据，那么并不需要遍历整个表格去找;可以使用excel样式的坐标来访问这个单元格：
cell = sheet['C5']
在随后的章节中，将展示如何从一行，一列，或者一些单元格中获取一个生成器。
3.处理单元格
在任何情况下，想要处理电子表格中的数据，就必须访问每个单元格。在Openpyxl中，单元格有一个值和许多仅用于编写的其他信息，比如样式信息。更方便地是，我们可以把单元格中的值作为Python对象(数字、日期、字符串等)，用Openpyxl将它们转换为Excel类型。因此，单元格内容就不一定要是字符串。例如，我们以数字的形式读取单元格的内容:
tax_percentage = sheet['H16'].value
tax_amount = taxable_amount * tax_percentage
然而，我们并不能保证用户一定在单元格中输入了数字;如果它包含字符串"bug"，如果比较幸运的话，在运行上面的代码后，我们会得到一个运行错误:
TypeError: can't multiply sequence by non-int of type 'float'
然而，在不那么幸运的情况下，例如，当taxable_amount是一个整数时——因为我们在示例中处理的数据是钱，所以应该是整数——我们将得到一个重复taxable_amount次的“bug”。这是因为Python把*操作符当成了字符串，整数就意味着“重复字符串n次”。这可能会导致进一步的类型错误，或者在Python无法放置如此大的字符串时出现内存错误。因此，我们应该验证程序的输入，包括Excel文件。在这个特殊的例子中，我们可以使用Python的isinstance函数来检查单元格中值的类型:
if isinstance(cell.value, numbers.Number):
# Use the value
我们还可以询问单元格它存储的数据类型是什么：
if cell.data_type == TYPE_NUMERIC:
# Use the numeric value
4.单元格高级寻址
到目前为止，我们已经使用了访问单元格最简单、最直接的方法。然而，这并不是所有方法;让我们来看看更复杂的方案。
(1)除了active之外的其他表。
我们可以通过在workbook中通过名称来访问它们:
sheet = wb['2020 Report']
然后我们就可以像之前看到的那样访问单元格了。
(2)单元格范围
我们不一定要一个一个的寻址单元格-还可以设定范围来访问单元格：
-
sheet['D']是指一整行（本例中是D这一行）
-
sheet[7]是指一整列(本例中是第7列)
-
sheet['B:F']代表许多行
-
sheet['4:10'] 代表许多列
-
sheet['C3:H5']是最通用的选择，代表任意范围的单元格
以上任何一种情况，结果都是一个按行迭代所有单元格(除非迭代的范围以列为标准，在这种情况下，单元格按列顺序进行迭代):
for cell in sheet['B2:F10']:
# B2, B3, ..., F1, F2, ..., F10
for cell in sheet['4:10']:
# A4, B4, ..., A10, B10, ...
sheet['B2:F10']中的单元格
sheet['4:10']中的单元格
5.单元格迭代器
如果上述寻址方案解决不了问题，那我们可以考虑一些简单的方法iter_rows和iter_columns，它们分别按行和列返回单元格生成器。需要指出，这些方法都需要5个参数：
-
min_row - 起始行的编号(1就是A,2就是B，以此类推)
-
min_col - 起始列的编号
-
max_row - 最后一行的编号
-
max_col - 最后一列的编号
-
values_only - 生成器将只显示每个单元格的值，而不是整个单元格对象。所以，我们不需要用cell.value，而只要value。另一方面，我们不能访问单元格的其他属性，比如data_type。例如，如果我们想按列在B2:F10的范围上进行迭代，可以这样写：
for cell in sheet.iter_columns(min_row=2, min_col=2, max_row=6, max_col=10):
# Use the cell
6.编写一个Excel文件
要写一个Excel文件，我们只需在workbook上调用save方法：
wb.save('someFile.xlsx')
知道如何保存一个workbook后，让我们看看如何修改它，这将会很有趣。我们可以修改文件中的workbook，也可以修改在Python中创建的workbook。
7.修改单个单元格
我们可以用指定的方式来改变一个单元格中的值：
cell.value = 42
这会自动更新单元格的数据类型以存储新的值。除了基本类型(整数、浮点数、字符串)之外，还包括datetime模块中的各种类，如果你安装了NumPy，那么NumPy数字类型也可以使用。不仅可以设置值和类型，我们还可以设置单元格的其他属性，特别是样式信息(字体、颜色等)，这对做一个好看的报告很有用。
Openpyxl的文档中有许多关于调整样式的详细信息，我们可以在这里查询:
https://openpyxl.readthedocs.io/en/stable/styles.html
8.添加或移除表格
到目前为止，我们已经看到了如何处理一些对象，特别是workbooks和worksheets——就像处理字典一样，访问其中的细节:工作表、行、列、单个单元格、单元格范围。现在，我们将学习如何添加新信息，以及如何更改现有信息。我们先从表格开始。
使用 create_sheet方法来创建worksheet:
new_sheet = wb.create_sheet()
这样就可以在workbook中的其他表格之后添加一个新表，我们可以给这个新表一个标题：
new_sheet = wb.create_sheet(title = 'My new sheet')
如果我们想把这个表格放在其他位置，我们可以指定它的索引(从0开始):
# The new sheet will be inserted as the third sheet
new_sheet = wb.create_sheet(index = 2)
要删除一个表格的话有两种方法。可以根据名字进行删除：
del wb['My sheet']
我们可以使用in操作符来查看给出的表格名称是否在workbook中：
name = 'My sheet'
if name in workbook:
del workbook[name]
或者还能调用remove方法来删除表格：
wb.remove(sheet)
9.增加或移除行、列、单元格
看看下面这些例子。首先通过访问一个单元格，可以为创建行和列腾出空间：
wb = Workbook()
# Initially, an empty worksheet has a single row and column, A and 1
self.assertEqual(wb.active.max_row, 1)
self.assertEqual(wb.active.max_column, 1)
# We set the value of the cell at C3;
# openpyxl creates rows B, C and columns 2, 3 automatically
wb.active['C3'].value = 12
# Now the sheet has 3 rows and columns
self.assertEqual(wb.active.max_row, 3)
self.assertEqual(wb.active.max_column, 3)
wb.close()
此外，我们还可以用insert_rows和insert_cols方法在表格中添加行或列。当新创建一行或 一列时，单元格会自动调整：
wb = Workbook()
self.assertEqual(wb.active.max_row, 1)
wb.active['A1'].value = 11
# Insert 3 rows, starting at index 0 (i.e. row 1)
wb.active.insert_rows(0, 3)
self.assertEqual(wb.active.max_row, 4)
# Note how the cell, A1, has automatically moved by 3 rows to A4
self.assertEqual(wb.active['A4'].value, 11)
与此相对应，还可以使用delete_rows和delete_cols来删除行或列：
# Delete 2 columns, starting from index 1, i.e. column B
sheet.delete_columns(1, 2)
10.使用formulas
电子表格非常强大，因为它还支持用formulas来计算单元格中的值。当其他单元格发生变化时，通过计算取得值的单元格也会自动更新。让我们看看如何使用在Openpyxl中使用formulas吧。首先，如果我们只想读取一个Excel文件，我们可以完全忽略formulas。
此时，以“data only”模式打开它，这种模式将会隐藏formulas，所有单元格中的值都是固定的-也就是上次Excel文件计算后的结果:
wb = load_workbook(filename, data_only=True)
只有在修改Excel文件时，才需要重新使用formulas。虽然openpyxl有一些对解析 formulas的支持(例如，检查是否只调用了已知函数)，但openpyxl自身不能产生formulas。因此，如果我们想要使用formulas，就必须求助于第三方库。进入一个叫做“formulas”的库。让我们把它添加到requirements.txt文件中并安装它:
$ cat requirements.txt
openpyxl
formulas
pip install -r requirements.txt
我们有两种方法来使用formulas库:
(1)像Excel那样，使用所有formulas，计算workbook中的值;
(2)将单独的formulas写入Python函数，这样就可以放入不同的参数，来使用这些formulas。
11.计算所有formulas中的值：
第一个示例比较枯燥，因为功能与前面的data_only方法作用相似。事实上，在那 种模式下，我们加载不了workbook，修改不了它，也不能重新使用其中的formulas。我们必须:
-
把修改后的workbook保存到一个文件中；
-
用formulas重新加载文件；
-
调用API使用formulas；
-
把计算后的值保存到文件中；
-
在data_only模式下用openpyxl打开文件，查看计算后的结果。
这简直是在浪费时间！
当然，formulas库的这个特性也有价值，因为它支持一些高级的用法:
-
同时在多个workbook中使用formulas
Excel中，可以在另一个文件中引用其他文件中的formulas。formulas可以在同一个集合中加载多个workbook，以解决这种跨文件引用问题，这是Excel中很少使用的特性。
-
将整个Excel工作簿编译为Python函数
我们可以将某些单元格定义为输入单元格，把剩下的定义为输出单元格，得到一个函数，在给定输入后使用formulas，并在计算后把值返回给输出单元格。然而为了保持本文的简洁性，就不做详述。
12.把单独的formulas编译为Python函数：
让我们将重点放在单个formulas上，以便更好的使用openpyxl。操作如下：
func = formulas.Parser().ast(value)[1].compile()
由于某些原因，ast方法返回一个由2个对象组成的元组，其中第二个对象builder是最有用的。尽管这是内部APl，但也应该被包装在一个更友好的用户界面中。无论如何，当我们对上面的代码求值时，得到的func将是一个带有许多参数的函数，这些参数与formulas的输入一样：
func = formulas.Parser().ast('=A1+B1')[1].compile()
func(1, 2) == 3 # True
13.处理formulas的依赖项
因此，我们可以将单个单元格的formulas编译成一个函数。但是，当formulas依赖于其他单元格本身的formulas时，会发生什么呢?formulas库这时候就失去了作用;我们必须计算所有的输入。让我们来看看怎么做。
首先，如何区分含formulas的单元格和含常规值的单元格?Openpyxl没有提供这样的区分方法，所以我们必须检查单元格的值是否以等号字符开头:
def has_formula(cell: Cell)
return isinstance(cell.value, str) and cell.value.startswith('=')
这样我们就知道了如何处理不包含formulas的单元格了：
def compute_cell_value(cell: Cell):
if not has_formula(cell):
return cell.value
现在需要处理的是含有formulas的单元格：
func = formulas.Parser().ast(cell.value)[1].compile()
args = []
# TODO: compute function arguments
return func(*args)
我们将formulas编译成一个Python函数，然后调用它。因为输入是对单元格的 值，所以我们递归地调用compute_cell_value来获取它们的值:
sheet = cell.parent
for key in func.inputs.keys():
args.append(compute_cell_value(sheet[key]))
我们利用这样一个关系:每个单元格都与包含它的工作表有关。我们还使用formulas保留的信息，这样就可以检查函数的输入——包含单元格的字典。注意，它不支持跨表或跨文件的使用。
14.基于单元格范围使用formulas
到目前为止，compute_cell_value函数使用基于其他单元格的formulas，成功地计算了单元格的值。然而，对于那些不依赖于单个单元格，而是依赖于许多单元格的formulas，又该如何计算呢? 在这种情况下，函数的输入是一个范围表达式，例如=SUM(A1:21)中的A1:Z1。我们给compute_cell_ value传入以下信息:
sheet[key]
当key是单个单元格的地址时，我们将得到一个单元格对象;但是当key是许多单元格时，我们将得到一个元组。compute_cell_value不知道如何处理这样的输入，所以我们必须修改它，来应对这种情况:
if isinstance(input, Tuple):
return tuple(map(compute_cell_value, input))
函数的完整版本如下：
def compute_cell_value(input: Union[Cell, Tuple]):
if isinstance(input, Tuple):
return tuple(map(compute_cell_value, input))
if not has_formula(input):
return input.value
func = formulas.Parser().ast(input.value)[1].compile()
args = []
sheet = input.parent
for key in func.inputs.keys():
args.append(compute_cell_value(sheet[key]))
return func(*args)
15.添加新的formula函数
formulas支持许多内置的Excel函数，但不包括所有函数。当然，它也不支持VBA中的自定义函数。但是，我们可以添加一些新的Python函数，这样就可以在formulas中调用这些函数：
def is_number(number):
... # This is actually defined in formulas, but strangely not exposed as the Excel function
FUNCTIONS = formulas.get_functions()
FUNCTIONS['ISNUMBER'] = is_number
函数的输入值就是Python中的值，比如字符串、数字、日期等，而不是cell类中的值。此外，与普通Python函数相比，我们需要防止XIError，它表示计算中的错误，例如#DIV/0!或#REF! (当我们在输入formulas中犯了一些错误时，通常会在Excel中看到这些):
def is_number(number):
if isinstance(number, XlError):
return False
...
结论
通过使用openpyxl和formulas这两个成熟的开源库，我们可以更高效地用Python处理Excel。对于那些经常使用Excel的用户来说，能够处理复杂的Excel文件是一个非常有用的功能。
在本文中，我们学习了如何读写带有formulas的Excel文件。你还可以在样式，图表，合并单元格中学到其他相关的知识。
扫一扫
724
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
