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
Persistent Cells and Worksheets
Introduction
Some calculations take a long time, but produce the same result every time or only change occasionally. There is another recipe, the cache, showing how to store values and data between recalcs. Even better for long calculations, would be to store the results rather than having to repeat the calculation every time the spreadsheet is loaded.
These examples show you how to persist values in cells, or a whole worksheet, to a file saved alongside the spreadsheet. It is based on code submuitted by Johannes Kersten (many thanks!).
You can download both examples in a single zip file:
Serialization
Resolver One stores 'objects' in worksheets. An object is a techie term meaning 'anything'! Usually these objects will be values, numbers or text, but they could in theory be whatever you wanted. Taking the computers internal representation of objects and storing them on disk is called 'serialization', and unfortunately it isn't straightforward. In the .NET world of private and protected members [1] we can only serialize objects that we know how to handle.
Because of this, serialization is a job for libraries. As IronPython is a faithful implentation of Python, we have a choice between using the .NET libraries or the Python ones. As most data in spreadsheets tend to be basic datatypes (numbers or text), the Python ones are slightly easier to work with. There are two basic Python serialization libraries, marshal and pickle. Both are easy to use, and for this example Johannes chose to work with marshal.
Robert Smithson, one of the Resolver founders, has also posted an example of persisting worksheets to the Resolver Discussion Forum. Robert has chosen to implement his own serialization for worksheets using the XmlTextWriter and XmlTextReader.
Persistent Cells
The persistent cells spreadsheet will load and save the values stored in specific cells. You provide the worksheet name and a list of the cells you want to persist. You also specify a filename (which will be loaded and saved from the same directory as the spreadsheet). At the start of the spreadsheet (in the pre-constants user code), the values will be loaded into the cells (if the file exists). At the end of the spreadsheet, post-formulae user code, the values are written back out.
Loading Cell Values
The code for loading cell values:
import os
import marshal
sheetname = 'Sheet1'
memcells = ['A1'] #list of cells you like to save
filename = 'persistent-cells.txt'
filepath = os.path.join(os.path.dirname(__file__), filename)
# ---
# ---
data = {}
if os.path.isfile(filepath):
try:
savefile = open(filepath, 'rb')
try:
data = marshal.load(savefile)
except Exception, e:
print 'Failed to load data. Error:', e
savefile.close()
except IOError, e:
print 'Failed to open file for load.', e
for key, value in data.iteritems():
if value is None:
value = Empty
workbook[sheetname][key] = value
The worksheet we are using is specified in the sheetname variable. The cells we want to store is the list memcells.
The data is stored as a dictionary of cell names to values. The important code is the line that loads the dictionary back from the marshaled file: data = marshal.load(savefile).
After this, the data is put back into the worksheet by iterating over the cell locations and values stored in the dictionary.
Saving Cell Values
Of course loading cell values only works if you already have some persisted data. The code for saving the values in the cells is:
for key in memcells:
val = workbook[sheetname][key]
if val is Empty:
val = None
backup[key] = val
try:
savefile = open(filepath, 'wb')
try:
marshal.dump(backup, savefile)
except Exception, e:
print 'Failed to save data. Error:', e
savefile.close()
except IOError, e:
print 'Failed to open file for save.', e
# ---
The line that does the hard work is marshal.dump(backup, savefile).
One important thing to notice, is how we handle empty cells. Cells with no values use a value called Empty. This can't be marshaled, so we store None instead. The code for loading (above) knows about this, and replaces None` with the Empty value when loading back in.
Persistent Worksheet
The second example uses the same technique, but instead of loading/saving a list of cells it works with all the values in a worksheet. The code for loading values is very similar to the first example (except it stores values as a list of tuples - (location, value)).
The code for saving values has to be different, it needs to store all the populated cells in a worksheet. There is not yet a convenient API for doing this on Resolver worksheets (this will probably change soon), but we can iterate over every location in the bounds (the populated area) of the worksheet.
sheet = workbook[sheetname]
for col in range(sheet.MinCol, sheet.MaxCol + 1):
for row in range(sheet.MinRow, sheet.MaxRow + 1):
val = sheet[col, row]
if val is Empty:
val = None
data.append(((col, row), val))
try:
savefile = open(filepath, 'wb')
try:
marshal.dump(data, savefile)
except Exception, e:
print 'Failed to save data. Error:', e
savefile.close()
except IOError, e:
print 'Failed to open file for save.', e
Conclusion
These are obviously basic examples, but provide a good foundation for you to build something specific to your needs. This implementation loads and saves the values fro mdisk with every calculation. A more sophisticated solution could combine this with the cache, to only load from disk on the first calculation and only save when some of the data changes. A more advanced version could also knows about worksheet and cell properties (the traits), like boldness, backcolor, column height and so on. This would be a truly persistent worksheet rather than just storing values.
| [1] | In fact the problem is more general than just being a '.NET problem'. Objects rarely work in isolation, but usually have references to other objects or external resources. Merely recreating a duplicate of an object will often not do the 'right thing', which is why we need special libraries for serialization. |
Last edited Sat Jan 26 12:50:19 2008.