XlsxWriter自定义波斯日期数据验证公式失效问题排查

背景 #

需要验证自定义波斯日期格式(yyyy/mm/dd)的有效性。

公式情况 #

已获取满足需求的自定义验证公式，XlsxWriter能解析该公式，但生成的Excel文件中公式无法直接生效。

根因猜测 #

怀疑是Excel打开时弹出的错误提示导致，手动点击提示框的“确定”后公式即可正常工作，不确定该提示是否会影响XlsxWriter的功能输出。

问题复现步骤 #

1. 创建测试DataFrame：

df = pd.DataFrame(data = {'date' : ['1369/05/18',
                                    '1386/05/1',
                                    '1369/5/18',
                                    '1369/05/',
                                    '1369//15',
                                    '/05/08',
                                    '1369/051/18',
                                    '1369/0/518',
                                    '136/5/18',
                                    '1369/13/18',
                                    '1369/05/31',
                                    '1369-05-31',
                                    '1369//05//18',
                                    None
                                   ],
                          'case' : ['valid format - correct',
                                    '1 digit day - correct',
                                    '1 digit month - correct',
                                    'No Day ',
                                    'No Month',
                                    'No Year',
                                    '3 digit month',
                                    '3 digit day',
                                    '3 digit year',
                                    'Invalid month',
                                    'Invalid day',
                                    '31st may - correct',
                                    'Invalid format',
                                    'Invalid format'
                                   ],
                          'gender' : '',
                          'date_input':''
                         }
                 )

2. 将DataFrame写入Excel并添加数据验证：

writer = pd.ExcelWriter('dates_validation.xlsx', engine='xlsxwriter') # xlsx writer
workbook = writer.book # Workbook object
df.to_excel(writer, sheet_name = 'sheet_1' ,index=False) # Writing to file
worksheet = writer.sheets['sheet_1'] # Worksheet object
worksheet.data_validation(
    'C2:C1048576', {'validate': 'list',
                    'source': ['Male','Female'],
                    'dropdown': True
                   })
worksheet.data_validation(
    'D2:D1048576', {'validate': 'custom',
                    'value': '=LET(s,TEXTSPLIT(D2,"/"),y,AND(--INDEX(s,1)>=1278,--INDEX(s,1)<=9378),m,AND(--INDEX(s,2)>=1, --INDEX(s,2)<=12),d, AND(--INDEX(s,3)>=1, --INDEX(s,3)<=(30+(--INDEX(s,2)<=6))), AND(y,m,d))',
                    'ignore_blank': True
                   }
)
writer.close()

生成的文件中D2单元格的验证公式已存在但无法生效，切换到其他单元格并点击弹出提示框的“确定”后，公式恢复正常。

内容的提问来源于stack exchange，提问作者Mohamed Mostafa El-Sayyad