Passing Python dictionaries

xlSlim supports Python dictionaries. Python dictionaries returned by Python functions are automatically cached by xlSlim and a cache handle is returned to Excel. These cache handles can be supplied to any function that expects a dictionary. Python dictionaries can also be created from Excel ranges, the first column is used for the dictionary keys and the second for the values.

Note

Type hints are essential for xlSlim to determine how to handle Python dictionaries passed between your functions.

This Python module defines functions that use dictionaries to store peoples’ details such as name and age. The create_person_dict() function creates a new dictionary with a person’s details. The describe() function accepts a dictionary and returns a description of the person. Finally, the average_age() function accepts a list of dictionaries and returns the average age of the people contained. As usual the code has no changes to support xlSlim.

This example is very similar to the Passing Python objects example.

from typing import Dict, List


def create_person_dict(first_name: str, last_name: str, age: int) -> Dict:
        """Creates a new Person dictionary."""
        return {"first_name": first_name, "last_name": last_name, "age": age}


def describe(p: Dict) -> str:
        """Returns a string describing the person in the dictionary."""
        return (
                f"{p.get('first_name')} {p.get('last_name')} is {p.get('age'):.0f} years old."
        )


def average_age(people: List[Dict]) -> float:
        """Returns the average age of the people."""
        return sum((p.get("age") for p in people)) / len(people)

Note

All the Python code and Excel files shown are available from github in the xlslim-code-samples repo. I highly recommend downloading the samples from github. The Excel workbooks contain many tips and tricks.

Save the Python code as a new file on your PC. I saved the file in my Documents folder.

Open Excel and enter this RegisterPyModule() formula (amending the location to match where you saved the file):

=RegisterPyModule("C:\Users\russe\Documents\passing_dicts.py")

Let’s create a new person, Bob.

The Python function created a new dictionary with a person’s details, however a string “[Book1]Sheet1$A$3@1” was passed back to Excel.

What is going on?

xlSlim put the dictionary created by create_person_dict() into a memory cache and returned a handle to the cached item. FYI, the number after the @ increments every time the object is updated. If you repeatedly call the Excel function (use F2 on the cell or Ctrl-Alt-F9 to recalc the workbook) you will see the number increasing. This allows you to create dependencies between cells using standard Excel dependency trees.

The cache handle can be used as an input to any function that expects a dictionary. Let’s call the describe_from_handle() function. We use the _from_handle version of the function as we are fetching the dictionary from the memory cache using a cache handle. Later in this document we will pass a dictionary directly from Excel.

Internally xlSlim fetches the dictionary from the memory cache and passes the dictionary to the Python describe_from_handle() function.

The function returns a string describing the person. xlSlim hid all the technical details around dictionary caching and no Python code changes were needed.

xlSlim can also create a dictionary from an Excel range. The first column is the range is used for the dictionary keys and the second column is used for the values.

Bob is still 25 as expected.

Passing multiple dictionaries

Our Python module contains this average_age() function that expects three dictionaries with peoples’ details.

def average_age(people: List[Dict]) -> float:
        """Returns the average age of the people."""
        return sum((p.get("age") for p in people)) / len(people)

xlSlim creates an Excel function that takes in a list of dictionaries.

Let’s create more people and calculate their average age.

The average age is 30 as expected.

Viewing Python objects

The xlSlim function ViewPyObject() can be used to view any Python object stored in the memory cache.

=ViewPyObject(A5)

Note

The ViewPyObject() function can be used to view any object xlSlim has cached.

Clearing cached Python objects

You may need to clear cached Python objects if your Excel spreadsheet is open for a long time or creates a large number of cached objects.

The xlSlim utility function ClearCaches() will clear all cached objects.

=ClearCaches()