            from openpyxl import load_workbook
            from openpyxl.workbook.defined_name import DefinedName
            from openpyxl.worksheet.datavalidation import DataValidation
            wb=load_workbook(folder.parent/'source.xlsx' if (folder.parent/'source.xlsx').exists() else folder/'source.xlsx');ws=wb['Form'];options=wb.create_sheet('Options');options.append(['Department'])
            for value in output['allowed_departments']:options.append([value])
            wb.defined_names.add(DefinedName('AllowedDepartments',attr_text="'Options'!$A$2:$A$4"));dv=DataValidation(type='list',formula1='=AllowedDepartments',allow_blank=True);dv.errorStyle='stop';dv.showErrorMessage=True;dv.error='请选择列表中的部门';dv.add('B2:B4');ws.add_data_validation(dv);wb.save(folder/'validated-form.xlsx');artifacts.append('validated-form.xlsx')
            r=load_workbook(folder/'validated-form.xlsx');v=list(r['Form'].data_validations.dataValidation)[0];allowed=set(output['allowed_departments']);bad=[{'id':x['id'],'value':x['department'],'reason':'not-in-list'} for x in data['rows'] if x['department'] and x['department'] not in allowed]
            check('验证器确实绑定B2:B4',str(v.sqref)=='B2:B4' and v.type=='list');check('跨表名称指向真实选项区域',r.defined_names['AllowedDepartments'].attr_text=="'Options'!$A$2:$A$4");check('空值允许但非法既有值单列',v.allow_blank and bad==[{'id':'F3','value':'待确认','reason':'not-in-list'}]);cout('invalid-existing-values.csv',bad);result={'allowed':sorted(allowed),'invalid_existing':bad,'native_validation_formula':v.formula1,'scope_note':'openpyxl保存规则但不执行Excel输入拦截；本例另做既有值程序检查。'}
