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
Triggering Recalculations
Introduction
Often a spreadsheet will need to include data from an external source. This may be a database, data-file, a web service or an application that has an API for fetching information.
For databases, Resolver provides a mechanism for trigerring a recalculation when the database changes: database trigger listeners. By default database worksheets execute the query every time they are recalculated, so triggering a new recalc will fetch the new data. In this article we explore how to use this feature to trigger a recalc from an external application.
Database Trigger Listeners
Database trigger listeners depend on having a trigger (or stored procedure) that sends a UDP packet when the database is updated. You add a trigger listener to a workbook with:
workbook.AddDatabaseTriggerListener(server, database, table)
This causes Resolver to listen to udp packets in the format SERVER:DATABASE:TABLE. Resolver listens for packets on port 45874 to the multicast group "226.31.33.7".
So to trigger a recalc, we need to send a UDP packet in the format that Resolver is listening for.
Ther is a small caveat to this approach. Because this feature is designed for working with database updates, it waits for half a second after receiving the packet to start the recalculation. This is because when a database has a trigger to send packets on updates, it sends one packet per update. This means that even simple changes to a database can cause a flurry of packets to be sent...
Sending Udp Packets
Sending Udp packets is easy. We just need to borrow the code from the Sending Udp Packets page on the IronPython cookbook.
With the external recalc spreadsheet (see the next section for a download link), comes a Python file called UdpSender.py. This contains a UdpSender class, and the port and multicast group definitions.
To try it out, create a Resolver spreadsheet with the following line in the user code:
workbook.AddDatabaseTriggerListener('TEST', 'TEST', 'TEST')
Resolver is now listening for Udp packets on port 45874, multicast group "226.31.33.7", for packets containing "TEST:TEST:TEST".
Neext execute the following code from an IronPython console:
>>> sender = UdpSender(port, group)
>>>
>>> sender.send('TEST:TEST:TEST')
You should see the recalc throbber (top right corner of Resolver) start to spin when it receives the packet.
Monitoring a File
So is this actually useful? Well... suppose that part of your spreadsheet data is contained in a separate data-file that is autogenerated by another program. If the file is updated, you want your spreadsheet to be recalculated.
Using a FileSystemWatcher we can monitor a file for changes, and send a Udp packet when we get notification that the file has changed. The code to do this is in the file DataFileMonitor.py.
It's watching for changes in a file called 'data.txt':
watcher.Path = directory
watcher.Filter = dataFile
sender = UdpSender(port, group)
def onChanged(source, event):
print 'Changed:', event.ChangeType, event.FullPath
sender.send('DATA:FILE:CHANGED')
watcher.Changed += onChanged
watcher.EnableRaisingEvents = True
Run DataFileMonitor.py with IronPython in the background.
The ExternalRecalcs spreadsheet uses the Cache, and displays the number of recalcs in cell B1. It reads the data from data.txt and displays the names and (uhm... made up) ages of some of the Resolver team in columns D and E.
If you edit the file and add a new line, you should see Resolver recalculate and the new row appear.
The Next Step
This would be even more useful if, when pulling in data from several data-sources, the values were cached and the data only pulled in from data sources that have changed. This isn't very difficult technically, but will have to wait for another article.
Last edited Mon Dec 10 21:17:22 2007.