如何修改Python xlsxwriter脚本实现依赖数据验证（禁止前置列空时随意输入）

我用Python的xlsxwriter库生成Excel文件，供领域专家填写书籍的章节、小节和子小节信息。为了保证输入符合程序处理要求，我给列添加了数据验证下拉列表。但目前有个问题：章节列未填写时，小节列仍能输入任意内容；小节列未填写时，子小节列也能随意输入。想修改脚本实现：章节列为空时小节列必须为空，小节列为空时子小节列必须为空。

最小可复现代码如下：

import xlsxwriter
from xlsxwriter.utility import xl_range_formula, xl_rowcol_to_cell

N = 10
chapters = {'chapter 1 (1)': ['section 1 (1.1)', 'section 2 (1.2)'],
            'chapter 2 (2)': ['section 1 (2.1), section 2 (2.2)']}
all_sections = {'section 1 (1.1)': ['subsection 1 (1.1.1)', 'subsection 2 (1.1.2)'],
                'section 2 (1.2)': ['subsection 1 (1.2.1)', 'subsection 2 (1.2.2)'],
                'section 1 (2.1)': ['subsection 1 (2.1.1)', 'subsection 2 (2.1.2)'],
                'section 2 (2.2)': ['subsection 1 (2.2.1)', 'subsection 2 (2.2.2)']}
key = 'Book'

workbook = xlsxwriter.Workbook('dependent_dv_mwe.xlsx')
main_worksheet = workbook.add_worksheet('Evaluation')
chapter_worksheet = workbook.add_worksheet('Chapters')
section_worksheet = workbook.add_worksheet('Sections')
keys_worksheet = workbook.add_worksheet('Keys')

chapter_start_col = 0
section_start_col = 0
chapter_max_row = 0
section_max_row = 0
keys_worksheet.write(0, 0, key)
for col, (chapter, sections) in enumerate(chapters.items()):
    chapter_worksheet.write(0, chapter_start_col + col, chapter)
    for j, section in enumerate(sections):
        chapter_worksheet.write(j + 1, chapter_start_col + col, section)
    chapter_max_row = max(chapter_max_row, j + 1)
workbook.define_name(key, xl_range_formula('Chapters',
                                           0, chapter_start_col,
                                           0, chapter_start_col + col))
chapter_start_col = chapter_start_col + col + 1
for col, (section, subsections) in enumerate(all_sections.items()):
    section_worksheet.write(0, section_start_col + col, section)
    for j, subsection in enumerate(subsections):
        section_worksheet.write(j + 1, section_start_col + col, subsection)
    section_max_row = max(section_max_row, j + 1)
section_start_col = section_start_col + col + 1

main_worksheet.write(xl_rowcol_to_cell(0, 0),
                     'Chapter')
main_worksheet.write(xl_rowcol_to_cell(0, 1),
                     'Section')
main_worksheet.write(xl_rowcol_to_cell(0, 2),
                     'Subsection')
for j in range(1, N + 1):
    cell1 = xl_rowcol_to_cell(j, 0)
    cell2 = xl_rowcol_to_cell(j, 1)
    cell3 = xl_rowcol_to_cell(j, 2)
    main_worksheet.data_validation(cell1, {'validate': 'list',
                                           'source': '=%s' % key,
                                           'ignore_blank': True})
    main_worksheet.data_validation(
        cell2,
        {'validate': 'list',
         'source': '=INDEX(%s,,MATCH(%s, %s, 0))'
         % (xl_range_formula('Chapters', 1, 0,
                             chapter_max_row,
                             chapter_start_col),
            cell1,
            xl_range_formula('Chapters', 0, 0, 0,
                             chapter_start_col)),
         'ignore_blank': True}
                                 )
    main_worksheet.data_validation(
        cell3, {'validate': 'list',
                'source': '=INDEX(%s,,MATCH(%s, %s, 0))'
                % (xl_range_formula('Sections', 1, 0,
                                    section_max_row,
                                section_start_col),
                   cell2,
                   xl_range_formula('Sections', 0, 0, 0,
                                        section_start_col)),
            'ignore_blank': True}
                                  )
workbook.close()

要实现上级为空时下级必须为空的约束，需要替换原有的ignore_blank: True设置，改用自定义数据验证规则，通过Excel公式同时实现两个逻辑：

1. 上级单元格为空时，当前单元格必须为空
2. 上级单元格不为空时，当前单元格必须属于对应的下拉选项列表

修改后的完整代码：

import xlsxwriter
from xlsxwriter.utility import xl_range_formula, xl_rowcol_to_cell

N = 10
chapters = {'chapter 1 (1)': ['section 1 (1.1)', 'section 2 (1.2)'],
            'chapter 2 (2)': ['section 1 (2.1)', 'section 2 (2.2)']}
all_sections = {'section 1 (1.1)': ['subsection 1 (1.1.1)', 'subsection 2 (1.1.2)'],
                'section 2 (1.2)': ['subsection 1 (1.2.1)', 'subsection 2 (1.2.2)'],
                'section 1 (2.1)': ['subsection 1 (2.1.1)', 'subsection 2 (2.1.2)'],
                'section 2 (2.2)': ['subsection 1 (2.2.1)', 'subsection 2 (2.2.2)']}
key = 'Book'

workbook = xlsxwriter.Workbook('dependent_dv_fixed.xlsx')
main_worksheet = workbook.add_worksheet('Evaluation')
chapter_worksheet = workbook.add_worksheet('Chapters')
section_worksheet = workbook.add_worksheet('Sections')
keys_worksheet = workbook.add_worksheet('Keys')

chapter_start_col = 0
section_start_col = 0
chapter_max_row = 0
section_max_row = 0
keys_worksheet.write(0, 0, key)
for col, (chapter, sections) in enumerate(chapters.items()):
    chapter_worksheet.write(0, chapter_start_col + col, chapter)
    for j, section in enumerate(sections):
        chapter_worksheet.write(j + 1, chapter_start_col + col, section)
    chapter_max_row = max(chapter_max_row, j + 1)

# 定义章节相关的引用范围
chapters_header_range = xl_range_formula('Chapters', 0, 0, 0, len(chapters)-1)
chapters_data_range = xl_range_formula('Chapters', 1, 0, chapter_max_row, len(chapters)-1)
workbook.define_name(key, chapters_header_range)

for col, (section, subsections) in enumerate(all_sections.items()):
    section_worksheet.write(0, section_start_col + col, section)
    for j, subsection in enumerate(subsections):
        section_worksheet.write(j + 1, section_start_col + col, subsection)
    section_max_row = max(section_max_row, j + 1)

# 定义小节相关的引用范围
sections_header_range = xl_range_formula('Sections', 0, 0, 0, len(all_sections)-1)
sections_data_range = xl_range_formula('Sections', 1, 0, section_max_row, len(all_sections)-1)

# 写入表头
main_worksheet.write(xl_rowcol_to_cell(0, 0), 'Chapter')
main_worksheet.write(xl_rowcol_to_cell(0, 1), 'Section')
main_worksheet.write(xl_rowcol_to_cell(0, 2), 'Subsection')

for j in range(1, N + 1):
    row_num = j
    cell_chapter = xl_rowcol_to_cell(row_num, 0)
    cell_section = xl_rowcol_to_cell(row_num, 1)
    cell_subsection = xl_rowcol_to_cell(row_num, 2)
    
    # 章节列验证：保留原下拉列表，允许为空
    main_worksheet.data_validation(cell_chapter, {
        'validate': 'list',
        'source': f'={key}',
        'ignore_blank': True
    })
    
    # 小节列验证：自定义规则，章节为空则小节必须为空，否则必须匹配对应章节的小节
    section_validation_formula = (
        f'=OR(ISBLANK({cell_chapter}), '
        f'ISNUMBER(MATCH({cell_section}, INDEX({chapters_data_range},,MATCH({cell_chapter}, {chapters_header_range}, 0)), 0)))'
    )
    main_worksheet.data_validation(cell_section, {
        'validate': 'custom',
        'value': section_validation_formula,
        'error_title': '输入错误',
        'error_message': '章节为空时小节必须为空，或小节必须属于当前章节的可选列表'
    })
    
    # 子小节列验证：自定义规则，小节为空则子小节必须为空，否则必须匹配对应小节的子小节
    subsection_validation_formula = (
        f'=OR(ISBLANK({cell_section}), '
        f'ISNUMBER(MATCH({cell_subsection}, INDEX({sections_data_range},,MATCH({cell_section}, {sections_header_range}, 0)), 0)))'
    )
    main_worksheet.data_validation(cell_subsection, {
        'validate': 'custom',
        'value': subsection_validation_formula,
        'error_title': '输入错误',
        'error_message': '小节为空时子小节必须为空，或子小节必须属于当前小节的可选列表'
    })

workbook.close()

关键修改说明 #

1. 修正了原代码中chapters字典的语法错误，确保小节列表元素独立
2. 优化了章节、小节的引用范围定义，避免无效的列引用
3. 自定义验证公式逻辑：
   • ISBLANK(上级单元格)：上级为空时，当前单元格必须为空才会通过验证
   • ISNUMBER(MATCH(...))：上级不为空时，当前单元格必须在对应下拉列表中存在才会通过验证
4. 添加了错误提示信息，明确告知用户输入规则

内容的提问来源于stack exchange，提问作者Zorgoth