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
The programming only.
And here goes …
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
In my example, my aggregate file is named ‘user_dict.json’.
@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
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"
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.
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.
Reference links for the imports
json
xl_func xl_macro xl_app QInputDialog regex