openpyxl秘籍：打造高效Excel处理程序

openpyxl是一个用于读写Excel文件的Python库，它可以让你用Python操作Office Open XML格式的数据。它支持xlsx、xlsm、xltx和xltm格式。

由于日常会有报表需求，近期将推出一个系列教程：openpyxl秘籍：打造高效Excel处理程序。

目录如下：

1）openpyxl的安装和导入

2）openpyxl的基本概念（工作簿、工作表、单元格）

3）openpyxl的基本操作（创建、打开、保存、关闭Excel文件）

4）openpyxl的单元格操作（获取、设置、迭代、插入、删除单元格）

5）openpyxl的单元格样式（字体、对齐、边框、填充、数字格式等）

6）openpyxl的公式和数据验证

7）openpyxl的图表和图片

8）openpyxl的条件格式和筛选

9）openpyxl的合并和拆分单元格

10）openpyxl的保护和隐藏工作表

11）openpyxl的其他高级功能（如冻结窗格、分组、排序、打印设置等）

本篇文章内容为：openpyxl的公式和数据验证。

openpyxl的公式和数据验证

1）公式

公式是Excel中的一种特殊的单元格值，它可以根据一定的规则计算出结果。openpyxl可以读取和写入公式，但是不能执行公式的计算，这需要Excel本身来完成。openpyxl可以通过data_only参数来控制是否读取公式的结果，而不是公式本身。例如：

from openpyxl import load_workbook
# 读取公式本身
wb1 = load_workbook('test.xlsx')
ws1 = wb1.active
print(ws1['A1'].value) # '=SUM(B1:B10)'
# 读取公式的结果
wb2 = load_workbook('test.xlsx', data_only=True)
ws2 = wb2.active
print(ws2['A1'].value) # 55

要写入公式，只需要将公式字符串赋值给单元格即可，注意公式字符串必须以等号开头，否则会被当作普通字符串。例如：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 写入公式
ws['A1'] = '=SUM(B1:B10)'
# 写入普通字符串
ws['A2'] = 'SUM(B1:B10)'
wb.save('test.xlsx')

openpyxl支持Excel的大部分公式，但是有一些公式是不支持的，比如数组公式、动态数组公式、用户自定义函数等。如果遇到不支持的公式，openpyxl会抛出IllegalCharacterError异常。

要避免这种情况，可以使用guess_types参数来让openpyxl尝试猜测单元格的数据类型，而不是严格检查公式的合法性。例如：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 写入数组公式，会抛出异常
ws['A1'] = '{=SUM(B1:B10*C1:C10)}'
# 使用guess_types参数，不会抛出异常，但是也不会计算结果
wb = Workbook(guess_types=True)
ws = wb.active
ws['A1'] = '{=SUM(B1:B10*C1:C10)}'
wb.save('test.xlsx')

2）数据验证

数据验证是Excel中的一种功能，它可以对单元格的输入进行限制和检查，以保证数据的正确性和一致性。openpyxl可以创建和读取数据验证，但是不能执行数据验证的检查，这需要Excel本身来完成。openpyxl提供了一个DataValidation类来表示数据验证对象，它有以下几个参数：

type：数据类型，可以是list（列表）、whole（整数）、decimal（小数）、date（日期）、time（时间）、textLength（文本长度）或custom（自定义）之一。

operator：运算符，可以是between（介于）、notBetween（不介于）、equal（等于）、notEqual（不等于）、greaterThan（大于）、lessThan（小于）、greaterThanOrEqual（大于等于）或lessThanOrEqual（小于等于）之一。

formula1：第一个公式，用于指定数据验证的条件，比如列表的值、范围的下限、目标值等。

formula2：第二个公式，用于指定数据验证的条件，比如范围的上限等。

allow_blank：是否允许空值，默认为False。

error：错误信息，当数据验证失败时显示给用户的提示。

errorTitle：错误标题，当数据验证失败时显示给用户的标题。

prompt：提示信息，当用户选中单元格时显示给用户的提示。

promptTitle：提示标题，当用户选中单元格时显示给用户的标题。

要创建一个数据验证对象，只需要实例化DataValidation类，并传入相应的参数即可。例如：

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
ws = wb.active
# 创建一个列表数据验证，只允许输入Dog、Cat或Bat
dv1 = DataValidation(type='list', formula1='"Dog,Cat,Bat"', allow_blank=True)
# 设置错误信息和提示信息
dv1.error = 'Your entry is not in the list'
dv1.errorTitle = 'Invalid Entry'
dv1.prompt = 'Please select from the list'
dv1.promptTitle = 'List Selection'
# 创建一个整数数据验证，只允许输入大于100的整数
dv2 = DataValidation(type='whole', operator='greaterThan', formula1=100)
# 设置错误信息和提示信息
dv2.error = 'Your entry is not greater than 100'
dv2.errorTitle = 'Invalid Entry'
dv2.prompt = 'Please enter a number greater than 100'
dv2.promptTitle = 'Number Selection'

要将数据验证对象应用到工作表中，需要使用add_data_validation方法，并传入数据验证对象作为参数。然后，可以使用add方法，将数据验证对象添加到单个单元格或单元格范围中。例如：

# 将数据验证对象添加到工作表中
ws.add_data_validation(dv1)
ws.add_data_validation(dv2)
# 将数据验证对象添加到单个单元格中
ws['A1'].value = 'Dog'
dv1.add(ws['A1'])
ws['B1'].value = 101
dv2.add(ws['B1'])
# 将数据验证对象添加到单元格范围中
dv1.add('C1:C10')
dv2.add('D1:D10')
wb.save('test.xlsx')

要读取一个工作表中的数据验证对象，可以使用data_validations属性，它会返回一个包含所有数据验证对象的列表。然后，可以使用cells属性，获取数据验证对象所应用的单元格或单元格范围。例如：

from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
ws = wb.active
# 获取工作表中的数据验证对象
dvs = ws.data_validations
# 遍历数据验证对象
for dv in dvs:
    # 打印数据验证对象的类型和条件
    print(dv.type, dv.formula1, dv.formula2)
    # 打印数据验证对象所应用的单元格或单元格范围
    for cell in dv.cells:
        print(cell.coordinate, end=' ')

本文介绍了如何使用openpyxl来处理公式和数据验证，以及一些注意事项和技巧。openpyxl是一个功能强大的Python库，可以方便地读写Excel文件，但是它也有一些局限性，比如不能执行公式和数据验证的计算和检查，这需要借助Excel本身来完成。因此，使用openpyxl时，需要根据具体的需求和场景，选择合适的参数和方法，以达到最佳的效果。

感兴趣的小伙伴，赠送全套Python学习资料，包含面试题、简历资料等具体看下方。

一、Python所有方向的学习路线

Python所有方向的技术点做的整理，形成各个领域的知识点汇总，它的用处就在于，你可以按照下面的知识点去找对应的学习资源，保证自己学得较为全面。

二、Python必备开发工具

工具都帮大家整理好了，安装就可直接上手！

三、最新Python学习笔记

当我学到一定基础，有自己的理解能力的时候，会去阅读一些前辈整理的书籍或者手写的笔记资料，这些笔记详细记载了他们对一些技术点的理解，这些理解是比较独到，可以学到不一样的思路。

四、Python视频合集

观看全面零基础学习视频，看视频学习是最快捷也是最有效果的方式，跟着视频中老师的思路，从基础到深入，还是很容易入门的。

五、实战案例

纸上得来终觉浅，要学会跟着视频一起敲，要动手实操，才能将自己的所学运用到实际当中去，这时候可以搞点实战案例来学习。

六、面试宝典

简历模板

若有侵权，请联系删除