使用xlwings添加含INDIRECT的Excel数据验证失败求助

问题分析 #

报错的核心原因有两个：

• 公式中的双引号未正确转义：Excel COM接口（xlwings的api调用的是该接口）要求公式内的字符串双引号需用两个双引号表示（遵循VBA字符串转义规则）。
• Operator参数设置错误：列表类型的数据验证（Type=3）不需要指定Operator=1（xlBetween），该参数会干扰验证规则的创建。

修正后的代码 #

替换原添加数据验证的代码部分：

# Use xlwings to apply data validation
app = xw.App(visible=False)
wb = xw.Book(file_path)
ws = wb.sheets['CSR24_Checklist']

# Apply data validation using xlwings to ADD dynamic validation to cells C100-C140 if "Option 2: Existing Endorsement" is selected in column a on the same row.
for row in range(100, 140):
    cell = f'C{row}'
    try:
        ws.range(cell).api.Validation.Delete()  # Clear any existing validation
        # 转义公式中的双引号，符合VBA字符串规则
        validation_formula = f'IF(A{row}=""Option 2: Existing Endorsement"", ""EndorsementRange"", """")'
        print(f"Applying validation to cell {cell} with formula: {validation_formula}")  # Debugging statement
        ws.range(cell).api.Validation.Add(
            Type=3,  # Type 3 is for list validation
            AlertStyle=1,
            Operator=0,  # 使用xlEqual（值为0），列表验证无需其他Operator
            Formula1=f'=INDIRECT({validation_formula})'
        )
        ws.range(cell).api.Validation.IgnoreBlank = True
        ws.range(cell).api.Validation.InCellDropdown = True
        ws.range(cell).api.Validation.ShowInput = True
        ws.range(cell).api.Validation.ShowError = True
        ws.range(cell).api.Validation.InputTitle = 'Select Endorsement'
        ws.range(cell).api.Validation.InputMessage = 'Select an endorsement from the list if "Option 2: Existing Endorsement" is selected in column A.'
        ws.range(cell).api.Validation.ErrorTitle = 'Invalid Entry'
        ws.range(cell).api.Validation.ErrorMessage = 'Invalid entry'
        print(f"Data validation applied to cell {cell} with formula =INDIRECT({validation_formula})")
    except Exception as e:
        print(f"Error applying data validation to cell {cell}: {e}")

# Save and close the workbook with xlwings
wb.save()
wb.close()
app.quit()

额外说明 #

• 若使用辅助列方案，需确保辅助列的公式正确，且在DataValidation的Formula1中直接引用辅助列单元格（如=INDIRECT(D{row})），同样要注意引号转义问题。
• 手动在Excel中可正常运行的公式，通过COM接口传入时必须遵循VBA的字符串转义规则，这是最容易忽略的差异点。

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