使用XlsxWriter实现Excel验证：仅允许字母及下划线输入

Hey there! Let's tweak your code to meet the requirement of restricting input to only uppercase/lowercase letters and underscores, while keeping your original max length check intact.

The key change here is switching from a length validation to a custom validation using an Excel formula that checks both the character type and length. Also, I spotted a small typo in your original code (you used workbook.add_worksheet() instead of wb.add_worksheet()), so I've fixed that too.

Modified Code #

import xlsxwriter

# Create workbook and worksheet
wb = xlsxwriter.Workbook('staff.xlsx')
ws = wb.add_worksheet()  # Fixed the typo here (was using `workbook` instead of `wb`)

# Define your max length requirement
firstname_max_length = 10

# Apply data validation to target cells (adjust the range as needed)
ws.data_validation(
    1, 0, 10, 0,  # Applies to rows 1-10 (Excel rows 2-11), column 0 (Excel column A)
    {
        'validate': 'custom',
        'input_title': 'Enter Value',
        'input_message': f"Only letters/underscores allowed (max length: {firstname_max_length})",
        'error_title': 'Invalid Input',
        'error_message': f"Please enter only letters and underscores, with length less than {firstname_max_length}",
        # Custom formula to check character type AND length
        'formula': f'=AND(NOT(ISERROR(SEARCH("^[A-Za-z_]+$",A1))),LEN(A1)<{firstname_max_length})'
    }
)

# Save the workbook
wb.close()

Breakdown of the Formula #

The custom formula =AND(NOT(ISERROR(SEARCH("^[A-Za-z_]+$",A1))),LEN(A1)<10) handles two checks:

1. Character Type Validation: NOT(ISERROR(SEARCH("^[A-Za-z_]+$",A1)))
   • The regex ^[A-Za-z_]+$ ensures the input only contains uppercase letters, lowercase letters, and underscores. The ^ and $ anchors make sure the entire cell content matches (no extra characters allowed).
   • SEARCH looks for this pattern in cell A1; if it fails to find a match, it returns an error. NOT(ISERROR(...)) converts that to a boolean TRUE only when the input is valid.
2. Length Validation: LEN(A1)<{firstname_max_length} preserves your original requirement of limiting input length to less than 10 characters.

If You Don't Need the Length Limit #

If you only want to restrict to letters/underscores (no length cap), simplify the formula to:

'formula': '=NOT(ISERROR(SEARCH("^[A-Za-z_]+$",A1)))'

And remove the length-related text from input_message and error_message.

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