使用 XlsxWriter 创建数据验证

数据验证是 Excel 的一项功能：它可以限制用户在单元格中输入的数据，并显示相应的帮助和警告消息，也可以把输入限制为下拉列表中的值。典型用途是把输入限制为某个范围内的整数，用帮助消息说明要求，并在输入不符合条件时给出警告。使用 XlsxWriter 可以这样实现：

worksheet.data_validation('B25', {'validate': 'integer',
                                  'criteria': 'between',
                                  'minimum': 1,
                                  'maximum': 100,
                                  'input_title': 'Enter an integer:',
                                  'input_message': 'between 1 and 100'})

如果用户输入不符合指定条件的值，会显示错误消息：

有关数据验证的更多信息，参见 Microsoft 支持文章：向单元格应用数据验证。以下各节介绍 data_validation() 方法及其选项。

data_validation()

data_validation() 用于创建 Excel 数据验证。验证可以应用于单个单元格或一个区域。与其他方法一样，可以使用 A1 或行/列表示法。使用行/列表示法时必须指定区域的四个坐标：(first_row, first_col, last_row, last_col)。对于单个单元格，把 last_ 值设为对应的 first_ 值。A1 表示法可以直接引用一个单元格或一个区域：

worksheet.data_validation(0, 0, 4, 1, {...})
worksheet.data_validation('B1',       {...})
worksheet.data_validation('C1:E5',    {...})

data_validation() 的 options 参数必须是字典，用于描述验证类型和样式。主要参数如下：

下面分别说明这些参数。大多数是可选的，但通常需要三个主要选项 validate、criteria、value：

worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': '>',
                                 'value': 100})

validate

validate 设置需要验证的数据类型：

worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': '>',
                                 'value': 100})

它始终是必需的，没有默认值。允许的值如下：

integer
decimal
list
date
time
length
custom
any

• integer：只允许整数，Excel 称为“整数”（Whole number）。

• decimal：只允许小数。

• list：限制为用户指定的一组值，可以传入 Python 列表或 Excel 单元格区域。

• date：限制为日期值，可以使用 日期时间文档 中的 datetime 对象或日期公式。

• time：限制为时间值，可以使用同一文档中的 datetime 对象或时间公式。

• length：用整数长度限制单元格中的字符串；Excel 称为“文本长度”。

• custom：使用返回 TRUE/FALSE 的外部 Excel 公式限制输入。

• any：数据类型不受限制，主要用于只显示输入消息而不设置验证。

criteria

criteria 设置检验单元格数据的条件。除 list、custom、any 外，它几乎总是必需的，没有默认值：

worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': '>',
                                 'value': 100})

允许的值为：

可以使用第一列中 Excel 的文本描述，也可以使用更常见的符号。下面两种写法等价：

worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': '>',
                                 'value': 100})

worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': 'greater than',
                                 'value': 100})

list、custom、any 不需要 criteria；即使指定也会被忽略：

worksheet.data_validation('B13', {'validate': 'list',
                                  'source': ['open', 'high', 'close']})

worksheet.data_validation('B23', {'validate': 'custom',
                                  'value': '=AND(F5=50,G5=60)'})

value、minimum、source

value 设置应用 criteria 的限制值，始终必需且没有默认值。也可以使用同义参数 minimum 或 source，使写法更清晰，并更接近 Excel 对该参数的描述：

# Using 'value'.
worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': 'greater than',
                                 'value': 100})
# Using 'minimum'.
worksheet.data_validation('B11', {'validate': 'decimal',
                                  'criteria': 'between',
                                  'minimum': 0.1,
                                  'maximum': 0.5})

# Using 'source'.
worksheet.data_validation('B10', {'validate': 'list',
                                  'source': '=$E$4:$G$4'})
# Using 'source' with a string list.
worksheet.data_validation('B13', {'validate': 'list',
                                  'source': ['open', 'high', 'close']})

注意：对 list 验证使用字符串列表时，Excel 在内部把这些字符串存为逗号分隔的字符串，包括逗号在内的总长度不能超过 255 个字符。更长的数据集合应使用上例中的区域引用。此外，字符串中的双引号（例如 '"Hello"'）必须双写成 '""Hello""'。

maximum

当 criteria 为 'between' 或 'not between' 时，maximum 设置上限：

worksheet.data_validation('B11', {'validate': 'decimal',
                                  'criteria': 'between',
                                  'minimum': 0.1,
                                  'maximum': 0.5})

ignore_blank

ignore_blank 控制 Excel 数据验证对话框中的“忽略空值”。启用后不验证空单元格；默认启用：

    worksheet.data_validation('B5', {'validate': 'integer',
                                     'criteria': 'between',
                                     'minimum': 1,
                                     'maximum': 10,
                                     'ignore_blank': False,
                                     })

dropdown

dropdown 控制 Excel 数据验证对话框中的“提供下拉箭头”。启用时，list 验证会显示下拉列表；默认启用。

input_title

input_title 设置选中单元格时显示的输入消息标题。没有默认值，且只有输入消息显示时才显示标题，参见下面的 input_message。标题最长 32 个字符。

input_message

input_message 设置选中单元格时显示的输入消息，没有默认值：

worksheet.data_validation('B25', {'validate': 'integer',
                                  'criteria': 'between',
                                  'minimum': 1,
                                  'maximum': 100,
                                  'input_title': 'Enter an integer:',
                                  'input_message': 'between 1 and 100'})

上例生成的输入消息如下：

可以使用换行符将消息分为多行。消息最长 255 个字符。

show_input

show_input 控制数据验证对话框中的“选定单元格时显示输入信息”。关闭后，即使通过 input_message 设置了消息，也不会显示；默认启用。

error_title

error_title 设置不符合验证条件时显示的错误消息标题，默认是“Microsoft Excel”，最长 32 个字符。

error_message

error_message 设置输入单元格数据时显示的错误消息。默认消息的意思是：“输入的值无效，用户限制了可输入此单元格的值。”可以这样显示自定义消息：

worksheet.data_validation('B27', {'validate': 'integer',
                                  'criteria': 'between',
                                  'minimum': 1,
                                  'maximum': 100,
                                  'input_title': 'Enter an integer:',
                                  'input_message': 'between 1 and 100',
                                  'error_title': 'Input value not valid!',
                                  'error_message': 'It should be an integer between 1 and 100'})

这会产生如下消息：

消息可以用换行符分为多行，最长 255 个字符。

error_type

error_type 指定错误对话框类型，有三个选项：

'stop'
'warning'
'information'

默认值为 'stop'。

show_error

show_error 控制数据验证对话框中的“输入无效数据时显示出错警告”。关闭后，即使通过 error_message 设置了消息，也不会显示；默认启用。

multi_range

multi_range 将数据验证扩展到不连续的区域。可以多次调用 data_validation() 来验证工作表中的不同区域；作为一项小优化，Excel 还允许把同一个数据验证应用到多个不连续区域。

XlsxWriter 用 multi_range 实现这一功能。该参数必须包含主验证区域和其他区域，各区域之间用空格分隔。例如，把一个验证应用于 'B3:K6' 和 'B9:K12'：

worksheet.data_validation('B3:K6', {'validate': 'integer',
                                    'criteria': 'between',
                                    'minimum': 1,
                                    'maximum': 100,
                                    'multi_range': 'B3:K6 B9:K12'})

数据验证示例

示例 1：只允许大于固定值的整数。

worksheet.data_validation('A1', {'validate': 'integer',
                                 'criteria': '>',
                                 'value': 0,
                                 })

示例 2：只允许大于某个值的整数，该限制值引用另一个单元格。

worksheet.data_validation('A2', {'validate': 'integer',
                                 'criteria': '>',
                                 'value': '=E3',
                                 })

示例 3：只允许固定范围内的小数。

worksheet.data_validation('A3', {'validate': 'decimal',
                                 'criteria': 'between',
                                 'minimum': 0.1,
                                 'maximum': 0.5,
                                 })

示例 4：只允许下拉列表中的值。

worksheet.data_validation('A4', {'validate': 'list',
                                 'source': ['open', 'high', 'close'],
                                 })

示例 5：只允许下拉列表中的值，列表由单元格区域指定。

worksheet.data_validation('A5', {'validate': 'list',
                                 'source': '=$E$4:$G$4',
                                 })

示例 6：只允许固定范围内的日期。

from datetime import date
worksheet.data_validation('A6', {'validate': 'date',
                                 'criteria': 'between',
                                 'minimum': date(2013, 1, 1),
                                 'maximum': date(2013, 12, 12),
                                 })

示例 7：选中单元格时显示消息。

worksheet.data_validation('A7', {'validate': 'integer',
                                 'criteria': 'between',
                                 'minimum': 1,
                                 'maximum': 100,
                                 'input_title': 'Enter an integer:',
                                 'input_message': 'between 1 and 100',
                                 })

另见数据验证示例。

---

来源：Working with Data Validation。© 2013–2025 John McNamara，jmcnamara@cpan.org。译文按原文顺序保留示例。遵循 BSD 2-Clause。