Create drop down with sheet elements in XlsxWriter

Question

So I'm creating a spreadsheet with certain data. Some cells in the spreadsheet need to have a drop down list with pre-defined data. For some cells the process has been pretty straightforward:

worksheet.data_validation(start_row, start_column, end_row, end_column, {'validate': 'list', 'source': options }

where options is a list of items.

It turns out that this list of items is converted to a string, if the string has more than 255 characters then (due to excel limitations) is not possible to created the drop down.

Since different cells may have different options, I'm checking if the length of options (converted to string) is >= 255, if so then I'm creating a new sheet and writing down the options there.

def create_options_sheet(self, name, values):

    options_worksheet = self.workbook.add_worksheet(name)

    opt_col = 0
    opt_row = 0

    for elem in values:
        options_worksheet.write(opt_row, opt_col, elem)
        opt_row += 1

So I'm planning to access these options from the current sheet taking an approach similar to this:

current_worksheet.data_validation(start_row, start_column, end_row, end_column, {'validate': 'list', 'source': options_sheet})

Where options_sheet would be a reference to the options sheet using a range like =$A$1:$A$10. My question is: how can I load that sheet and access it from my current sheet?

Answer 1

Ok, I figured it out! Since I've got access to the names of the different spreadsheets that hold the options I can do the following:

sheet_reference_str = '=' + sheet_name + '!$A$1:$A$10'
current_worksheet.data_validation(start_row, start_column, last_row, last_column, {'validate': 'list', 'source': sheet_reference_str})

The given ranges are as intended as examples just to illustrate how it would work

Answer 2

There are a lot of options available for data validation and creating drop-down boxes that can be found in the related documents.

I just made one that allows users to select boolean variables True or False (hence no quotation marks around them) which I can then later read back in to Pandas for use:

worksheet.data_validation(2,1,len(df)+1,10,{'validate':'list','source':[True,False]})