How to add more than 255 characters in a data validation in xlsxwriter Python

Question

I am creating an excel sheet where I have to make a drop down of all the states. Code looks like below:

excel_file_dir = os.path.join(os.path.dirname(__file__), 'template')
name = 'Template.xlsx'
excel_file = os.path.join(os.path.dirname(__file__), 'template', name)
workbook = xlsxwriter.Workbook(excel_file)
sheet = workbook.add_worksheet("Details")

sheet.write("C1", "Address", style_center)
sheet.write("D1", "State", style_center)

sheet.data_validation(1, 3, 5000, 3, {'validate': 'list', 'source': ["Alabama-AL", "Alaska-AK", "Arizona-AZ", "Arkansas-AR", "California-CA", "Colorado-CO", "Connecticut-CT",
                                              "Delaware-DE", "District of Columbia-DC", "Florida-FL", "Georgia-GA", "Hawaii-HI", "Idaho-ID", "Illinois-IL", "Indiana-IN",
                                              "Iowa-IA", "Kansas-KS", "Louisiana-LA", "Maine-ME", "Maryland-MD", "Massachusetts-MA", "Michigan-MI", "Minnesota-MN", "Mississippi-MS",
                                              "Missouri-MO", "Montana-MT", "Nebraska-NE", "Nevada-NV", "New Hampshire-NH", "New Jersey-NZ", "New Mexico-NM", "New York-NY",
                                              "North Carolina-NC", "North Dakota", "Ohio", "Oklahoma-OK", "Oregon-OR", "Pennsylvania-PA", "Rhode Island-RI", "South Dakota-SD",
                                              "Tennessee-TN", "Texas-TX", "Utah-UT", "Vermont-VT", "Virginia-VA", "Washington-WA", "West Virginia-WV", "Wisconsin-WI", "Wyoming-WY"]})
                                              
workbook.close()

I am getting below error:

UserWarning: Length of list items exceeds Excel's limit of 255, use a formula range instead

I am not able to understand formula range. I tried to find a way to do this, in some of other articles, it says add some data in some other cells lets say E1 and add rest of the information in say E2 and the combine these cells and put it where you want it in excel.

So I tried to do it

sheet.write("K10", "Alabama-AL, Alaska-AK, Arizona-AZ", border_format)
sheet.write("L11", "Arkansas-AR, California-CA, Colorado-CO, Connecticut-CT", border_format)

I wrote some information in K10 and L11 and then tried to merge it :

sheet.data_validation(1, 3, 5000, 3, {'validate': 'list', 'source': '=K10&" "&L11'})

But it actually didn't worked and shows empty drop down.

How can I put more then 255 char values in drop down using xlsxwriter in Python

EDIT:

state_list = ["Alabama-AL", "Alaska-AK", "Arizona-AZ", "Arkansas-AR", "California-CA", "Colorado-CO", "Connecticut-CT",
             "Delaware-DE", "District of Columbia-DC", "Florida-FL", "Georgia-GA", "Hawaii-HI", "Idaho-ID", "Illinois-IL", "Indiana-IN",
             "Iowa-IA", "Kansas-KS", "Louisiana-LA", "Maine-ME", "Maryland-MD", "Massachusetts-MA", "Michigan-MI", "Minnesota-MN", "Mississippi-MS",
             "Missouri-MO", "Montana-MT", "Nebraska-NE", "Nevada-NV", "New Hampshire-NH", "New Jersey-NZ", "New Mexico-NM", "New York-NY",
             "North Carolina-NC", "North Dakota", "Ohio", "Oklahoma-OK", "Oregon-OR", "Pennsylvania-PA", "Rhode Island-RI", "South Dakota-SD",
             "Tennessee-TN", "Texas-TX", "Utah-UT", "Vermont-VT", "Virginia-VA", "Washington-WA", "West Virginia-WV", "Wisconsin-WI", "Wyoming-WY"]
             
i = 10
for state in state_list:
    sheet.write("K{}".format(i), state, border_format)
    i = i + 1

sheet.data_validation(1, 3, 5000, 3, {'validate': 'list', 'source': '=Details!K10:K61'})

user_10238 · Answer

Just to be clear, the 255 character limit on a list/string validation is an Excel limitation. XlsxWriter is just enforcing that limitation.

The workaround suggested in the Excel (and XlsxWriter) documentation is to add the validation list data to a worksheet and refer to it with a formula range like =Details!$K$10:$K$61.

The edited example you added in your update should work. Here it is as a working example base on it:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet("Details")


state_list = ["Alabama-AL", "Alaska-AK", "Arizona-AZ", "Arkansas-AR",
              "California-CA", "Colorado-CO", "Connecticut-CT",
              "Delaware-DE", "District of Columbia-DC", "Florida-FL",
              "Georgia-GA", "Hawaii-HI", "Idaho-ID", "Illinois-IL", "Indiana-IN",
              "Iowa-IA", "Kansas-KS", "Louisiana-LA", "Maine-ME", "Maryland-MD",
              "Massachusetts-MA", "Michigan-MI", "Minnesota-MN", "Mississippi-MS",
              "Missouri-MO", "Montana-MT", "Nebraska-NE", "Nevada-NV",
              "New Hampshire-NH", "New Jersey-NZ", "New Mexico-NM", "New York-NY",
              "North Carolina-NC", "North Dakota", "Ohio", "Oklahoma-OK",
              "Oregon-OR", "Pennsylvania-PA", "Rhode Island-RI", "South Dakota-SD",
              "Tennessee-TN", "Texas-TX", "Utah-UT", "Vermont-VT",
              "Virginia-VA", "Washington-WA", "West Virginia-WV",
              "Wisconsin-WI", "Wyoming-WY"]

worksheet.write_column('K10', state_list)
# Same as the following with row/col notation:
# worksheet.write_column(9, 10, state_list)

worksheet.data_validation(1, 3, 5000, 3,
                          {'validate': 'list',
                           'source': '=Details!$K$10:$K$61'})


workbook.close()

Output:

How to add more than 255 characters in a data validation in xlsxwriter Python

1 Answers