Search code examples
pythonexcelxlsxwriter

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

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 understanding the formula range. I tried to find a way to do this. In some other articles it says: add some data in some other cells, let's say E1, and add the rest of the information in say E2, and then 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'})
Solution

  • 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:

    enter image description here