Universal Dropdowns in Excel Dec 2024

Dropping Down
A few years back, I wrote a couple functions to provide universal Excel dropdowns.
Universal Dropdowns Using PyXLL

What does that mean?

The idea is so simple: what if a user wanted to access standard dropdown choices on all his workbooks, with pure workbooks (no macros, no validation lists, no piddlin’ setups).

Refer to Excel Dropdown with NO worksheet data list (with video) Nov 2020

So what’s this post about?

The programming only.

And here goes …

The first function allows the user to build a dropdown list — directly from an Excel range. It’s just a helper function.

build_dropdown_dict(pathname_inp, filename_inp, dataname_inp, rang_inp)
from pyxll import xl_macro, xl_func
import pyxll

import json as js
import regex as re

from PySide2.QtWidgets import QInputDialog

# the QInputDialog class is used instead of the native Excel Data Validation List
class Form0(QInputDialog):
    def __init__(self, parent=None):
        super(Form0, self).__init__(parent)
        self.setWindowTitle("Form0")

        layout = QVBoxLayout()

        # Set dialog layout
        self.setLayout(layout)

@xl_func(
    "string pathname_inp, string filename_inp, string dataname_inp, string[] rang_inp: string"
)
def build_dropdown_dict(pathname_inp, filename_inp, dataname_inp, rang_inp):
    """build a JSON file from an Excel list,
    the JSON file is used later by my PyXLL function
    to populate my Pyside QT dropdown"""
    result = "FAIL"
    try:
        filename = pathname_inp + filename_inp + ".json"
        # data_name = cell_inp
        data_list = [e for e in rang_inp]
        with open(filename, "w") as f:
            f.write("".join(['{"', dataname_inp, '": [\n']))
            data_list_len = len(data_list)
            # all but last item
            for e in data_list[:-1]:
                # with comma
                f.write("".join(['"', e, '",\n']))
            # last item, therefore no comma
            f.write("".join(['"', data_list[-1], '"\n']))
            f.write("]}\n")
        result = "SUCCESS"
    except:
        pass
    return result

The second function allows the user to append the dropdown list to a user-defined aggregate of dropdowns — from Excel. It’s just a helper function.

In my example, my aggregate file is named ‘user_dict.json’.

append_to_USER_PyXLL_dropdown(pathname_inp, filename_inp)
@xl_func("string pathname_inp, string filename_inp: string")
def append_to_USER_PyXLL_dropdown(pathname_inp, filename_inp):
    """
    Append a new dropdown to the existing user dictionary of dropdowns
    """
    result = "FAIL"
    try:

        cfg = pyxll.get_config()
        path_fn = "E:/my_pyxll_modules/user_dict.json"
        temp_tuple = "KARL", "user_dict_path"
        if cfg.has_option(*temp_tuple):
            path_fn = cfg.get(*temp_tuple)

        # reading the data from the existing file
        with open("E:/my_pyxll_modules/user_dict.json") as f:
            data = f.read()

        # reconstruct the data as a dictionary
        js_exist_dict = js.loads(data)
        print("Data type js_exist_dict type: ", type(js_exist_dict))
        print("Data type js_exist_dict: ", js_exist_dict)

        # reading the data from the new file (to be appended)
        filename = os.path.join(pathname_inp, filename_inp + ".json")
        with open(filename) as f:
            data = f.read()

        # reconstruct the data as a dictionary
        js_new_dict = js.loads(data)
        print("Data type js_new_dict type: ", type(js_new_dict))
        print("Data type js_new_dict: ", js_new_dict)

        # see if first_key in new dict is found in exist dict
        first_key = list(js_new_dict)[0]
        if list(js_new_dict)[0] in js_exist_dict:
            xl = pyxll.xl_app()
            title = "* IMPORTANT *"
            prompt = (
                "Are you sure you want to replace ["
                + filename_inp
                + "] ? (True/False or 1/0)"
            )  # set prompt
            mtype = 4  # boolean
            response = xl.Application.InputBox(Prompt=prompt, Title=title, Type=mtype)
            if response:
                # user says okay; therefore proceed
                # first delete
                del js_exist_dict[first_key]
                # then replace
                js_exist_dict.update(js_new_dict)

                # Convert and write JSON object to file
                with open(path_fn, "w") as outfile:
                    js.dump(js_exist_dict, outfile)
                result = "SUCCESS"
            else:
                pass
        else:
            # didn't find existing; therefore proceed
            js_exist_dict.update(js_new_dict)

            # Convert and write JSON object to file
            with open(path_fn, "w") as outfile:
                js.dump(js_exist_dict, outfile)
            result = "SUCCESS"

    except:
        pass
    return result

The third (macro) function allows the user to access the dropdown list.

select_from_USER_PyXLL_dropdown()


for reference while reading the code

def index_containing_substring(the_list, substring):
    for i, s in enumerate(the_list):
        if substring in s.upper():
            return i
    return -1


@xl_macro()
def select_from_USER_PyXLL_dropdown():
    """
    Execute a PyQt dialog based on a list from a dictionary
    """
    xl = pyxll.xl_app()

    # EXCERPT (see my note below this code block)
	# EXCEPT START #
	cfg = pyxll.get_config()
    path_fn = "E:/my_pyxll_modules/user_dict.json"
    temp_tuple = "KARL", "user_dict_path"
    if cfg.has_option(*temp_tuple):
        path_fn = cfg.get(*temp_tuple)

    print(temp_tuple)
    print(path_fn)
    # reading the data from the file
    with open(path_fn) as f:
        data = f.read()

    # print(data)
    # print("Data type before reconstruction: ", type(data))

    # reconstructing the data as a dictionary
    js_main_dict = js.loads(data)
    # print("Data type js_main_dict type: ", type(js_main_dict))
    # print("Data type js_main_dict: ", js_main_dict)
	
	# EXCERPT END #

    r_entry = xl.Selection
    # next row up
    r_init_last_range = r_entry.GetOffset(RowOffset=-1, ColumnOffset=0)
    # next row up
    r_filter_value = r_entry.GetOffset(RowOffset=-2, ColumnOffset=0).Value
    # next row up
    r_named_variable = r_entry.GetOffset(RowOffset=-3, ColumnOffset=0).Value

    r_init_last_text = None
    rilr = r_init_last_range.Value
    if rilr:
        if isinstance(rilr, (int, float)):
            rilr = str(rilr)
        r_init_last_text = rilr.upper()

    # set default for picked_list
    # picked_list = js_dict["three letter names"]

    if r_named_variable:
        picked_list = js_main_dict[r_named_variable]

        if r_filter_value:
            try:
                regex = re.compile(r_filter_value.upper())
                filtered_list = filter(lambda x: regex.search(x.upper()), picked_list)
                picked_list = list(filtered_list)
            except:
                pass

        # make sure Qt has been initialized
        app = get_qt_app()

        # create the custom form
        dlg = Form0()

        # determine index position within dialog list
        # set default value
        dropdown_index = 0
        if r_init_last_text:
            try:
                dropdown_index = index_containing_substring(
                    picked_list, r_init_last_text
                )
            except:
                pass

        text = QInputDialog.getItem(
            dlg,
            "PyQt Dropdown"
            + " — "
            + r_named_variable,  # append dropdown variable to dialog title
            "Selection:",
            picked_list,
            max(0, min(dropdown_index, len(picked_list) - 1)),
        )

        # set default for picked item
        picked_item = r_entry
        if text[1]:
            picked_item = text[0]

        # post entry results
        r_entry.Value = picked_item
        r_init_last_range.Value = picked_item
    else:
        r_init_last_range.Value = "ERROR"
NOTE

Of course, in my actual implementation,

I placed the excerpt (lines 15 through 37) outside of
the function, select_from_USER_PyXLL_dropdown,
and made js_main_dict a global variable.


Thanks to Tony Roberts

Do you like great support from a company? I do.

I sent a link to my post to Tony and he offered …

One thing I noticed in your approach is that you have hard coded the location of your json file. You could put this location in the pyxll.cfg file instead, and then if you ever wanted to move it or use a different path then it would just be a case of updating that config rather than editing your code (and might make it easier for someone else if you were giving them this code since they would have it in a different place to you).

You can access the PyXLL config using pyxll.get_config(), and the object returned is a normal Python ConfigParser object (https://docs.python.org/3/library/configparser.html). You can add new sections and PyXLL will simply ignore them, for example you could add a new section “[KARL]” with a setting “user_dict_path” and access it from your Python code as follows

Here’s a link for more information on get_config.

cfg = pyxll.get_config()
path = "your_default_path_here"
if cfg.has_option("KARL", "user_dict_path"):
    path = cfg.get("KARL", "user_dict_path")

Thanks again.


Leave a Reply

Your email address will not be published. Required fields are marked *