Excel — Dictionary References with PyXLL Jan 2024

Example 1 — On the Fly Dictionary

Thanks to the generous guidance of Tony Roberts, I programmed a simple example using a dictionary.

I created two PyXLL functions:

• the first function is called from Excel; using PyXLL, it creates an ad hoc dictionary from cells in Excel
  Reference: PyXLL dictionary types
• the second function is called from Excel; it provides access to the newly created dictionary

Several such dictionaries can be easily created.

Details

The user enters keys and values in an Excel range. In my example, the entries are in the range G7:H10. The keys are in Column G; the values are in Column H.

Keys	Values
a	apple
b	banana
c	carrot
d	256

In cell H3, I enter the formula =make_adhoc_dict(G7:H10). This PyXLL function populates the dictionary.

In cells B7:B10, I enter arbitrary key values.

In cell C7, I enter the formula =get_adhoc_dict(B7). I copy the formula to C8:C10 . The dictionary values are retrieved in cells C7:C10.

Please notice that for each formula, I display the formula text in the cell adjacent (to the right). A red font indicates a user entry; a blue font indicates a formula entry.

Excel spreadsheet

With precedent arrows

Code

I offer the Python code and the PyXLL functions.

from pyxll import xl_app, xl_func

#--------------------------------------

@xl_func("dict: object")
def make_adhoc_dict(x):
    """
    twocol: two column range (col 1: key; col 2: val)
    key and value types are optional and default to var if not specified
    """
    return x

@xl_func("object, var: var")
def get_adhoc_dict(dictobject, xkey):
    return dictobject[xkey]

---

Example 2 — A prebuilt dictionary

Using PyXLL, I programmed a custom example using a PyXLL object.

Background

Several years ago, I created a new class, a dictionary containing all the AISC structural shapes. For each of the (over 2000) AISC shapes, I have 89 attributes. The attributes are the structural properties associated with each shape.

Example

The ‘magic’ in this example is that the dictionary value reference will be kept in a single Excel cell. This is our object reference.

Using the first function, the reference is established. The second function retrieves attributes from the object reference.

Details

AISC Shapes (excerpt)

AISC Shape Attributes (excerpt)

Then I create two PyXLL functions:

• the first function is called from Excel; it creates an object reference to my shape
• the second function is called from Excel; it provides access to the shape attributes

Excel cell C3 is named ‘my_shape’
Excel cell C15 is named ‘my_2nd_shape’

In cell B3, I enter an arbitrary AISC shape. In cell C3, I enter the formula =get_object_shape(B3).

In cell B15, I enter an arbitrary AISC attribute. In cell C15, I enter the formula =get_object_shape_prop(my_shape, B15);. The attribute value is retrieved in cell G8. I then copy down the formulas from G8:H8 down to G9:H12.

The pattern is repeated for my other object reference.

Please notice that for each formula, I display the formula text in the cell adjacent (to the right). A red font indicates a user entry; a blue font indicates a formula entry.

Excel spreadsheet

With precedent arrows

Code

Again, I offer the Python code and the PyXLL functions.

from pyxll import xl_app, xl_func

#--------------------------------------

@xl_func("str x: object")
def get_object_shape(x):
    # V1415S is the alias of an imported module
	# 'ga_static_dict' is my dictionary of AISC shapes
	gasd15 = getattr(V1415S, 'ga_static_dict')
    return gasd15[x]


@xl_func("object my_obj, str p: var")
def get_object_shape_prop(my_obj, p):
    return getattr(my_obj, p.upper())

---

Of course, as Raymond Hettinger says, “there must be a better way”

And here it is, in a post from 2017.

---