Resolver Hacks contains information and code for doing fun and useful things with Resolver One. This site is created and maintained by Michael Foord, not by Resolver Systems. Please read the disclaimer before using any of the code on this site.
IronPython in Action
Caching Data Across Recalculations
Introduction
When Resolver recalculates a spreadsheet it starts with a new workbook and executes all of the user code and generated code. This means that the data from the previous calculation are gone forever.
Sometimes you might want to keep some of the values from the previous recalc and do new calculations based on them. A toy example of this is the Game of Life, which bases the new state of the grid on the previous state. A real life example might be where you want to do projections based on recursive formulae. By storing the results you can step through the projections over time.
The answer to this problem is so short that it really deserves to be in the Snippets section. However, it is a piece of code that I have found so useful that it deserves greater prominence.
Introducing the Cache
So here is the solution, in all its glory. Save the following code as Cache.py in the same directory as the spreadsheet:
def GetCache(cacheId):
return _cache.setdefault(cacheId, {})
def ClearCacheEntry(cacheId):
_cache[cacheId] = {}
This provides two functions: GetCache and ClearCacheEntry. You can import these into your spreadsheets using from Cache import GetCache, ClearCacheEntry.
Note
You can also save this into the Library directory of your Resolver installation. That makes it available for import to all spreadsheets.
If you do this, you will need to import it using from Library.Cache import GetCache, ClearCacheEntry.
How it Works
When you import from a module, Python does some magic behind the scenes. The module is only loaded from disk the first time, it is then cached and subsequent imports use the cached version. (Hence the need for Import with Reload.)
Because Cache stores its data as a module level global, the data is preserved in between recalculations.
GetCache
The GetCache function returns you a Python dictionary associated with an 'id'. The underlying data-store can hold as many caches as you like, so you can use this module from several different spreadsheets simultaneously - so long as you use different identifiers for your cache.
The easiest way to make sure that all your spreadsheets have different identifiers, is to use __file__ as the identifier. __file__ is a magic variable containing the full path to the spreadsheet. Two warnings though:
- This will be none if the spreadsheet has never been saved
- It changes if you resave your spreadsheet with a different name or in a different location
ClearCacheEntry
If you want to reset your cache back to an empty dictionary (triggered by a button perhaps), call ClearCacheEntry(cacheId).
Using the Cache
The cache example spreadsheet creates a cache, which it uses to store a single value in between recalcs.
cache = GetCache(__file__)
# Fetch the current value - defaulting to 0
val = cache.setdefault('value', 0)
val += 1
# Store the new value
cache['value'] = val
cache is a Python dictionary, so the actual value is stored as the 'value' member in the cache. If you put the formula =val [1] in a cell, then you will see the number increase every time you press F9 (which triggers a recalculation).
The line val = cache.setdefault('value', 0) fetches the current value from the cache. If it doesn't exist, it sets it to a default value of 0 [2]. This is important because the first time the cache is fetched it doesn't have the value stored in it.
After calculating the new value, it is set back in the cache (cache['value'] = val) so that it is available for the next calculation.
An alternative approach is to use get, which returns None if the key you have asked for doesn't exist:
cache = GetCache(__file__)
val = cache.get('value')
if val is None:
# This is the first time
val = CalculateInitialValue()
else:
# Calculate new vaue
val = CalculateNewValue(val)
# Store the new value
cache['value'] = val
| [1] | Unfortunately we can't use the variable name value because this is a spreadsheet function. |
| [2] | See this page for the different methods available on dictionaries. |
Last edited Wed Jun 20 18:13:26 2007.