Search code examples
pythonexcel-formulaopenpyxlxlwt

Is there a way to limit character length using openpyxl?

Is there a way to restrict the maximum number of characters in a column using openpyxl?

I know I could open excel and set the character length limit through data validation, but I would like to find a way to maybe place a formula or set a max number of characters for a cell using python.

Solution

  • How to limit character length using openpyxl

    from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

# Create the workbook and worksheet we'll be working with
wb = Workbook()
ws = wb.active

# Create a data-validation object with character length validation
dv = DataValidation(type="textLength",
                    operator="lessThanOrEqual"),
                    formula1=15)  # 15 characters max

# Optionally set a custom error message
dv.error ='Your entry exceeds the max character length of 15 characters'
dv.errorTitle = 'String length is too long!'

# Apply the validation to a range of cells
dv.add('B1:B1048576') # This is the same as for the whole of column B

# Add the data-validation object to the worksheet
ws.add_data_validation(dv)

    ref: https://openpyxl.readthedocs.io/en/default/validation.html