Recalculation of handles

When a spreadsheet containing RTD object handles is reopened, all the handles are immediately recreated. Consequently, all cells that depend on the handles are recalculated.

This behavior is time-consuming, but it is correct: the handle refers to an object in memory, but the object is no longer there. Therefore a new object must be created, and the handle must be changed.

Persistent handles

There is a solution to this problem: you can save the objects to a cache file (along with their handles) and restore them to memory when they are needed.

As a result, spreadsheets containing handles can be closed and reopened with no recalculations.

The XLL+ RtdHandle implementation contains a class to do most of the work for you: PersistentHandleCache<T>. PersistentHandleCache<T> works as follows:

1. When the add-in is opened, all saved objects are reloaded from persistent storage on disk.
2. When a workbook containing RTD handles is opened, the handle is searched for in the set that was loaded from persistent storage. If the handle is found, the object is restored to the object cache, and no recalculation takes place.
3. If a handle is not found in storage, then it is recreated as usual, and the cells that depend on it are recalculated.
4. When a workbook containing handles is saved, its handles are added to the persistent store in memory.
5. When the add-in is closed, the persistent store is saved to disk.

Implementation

There are 2 steps required for implementing persistent object handle storage:

1. Define streaming operators for the object to be saved. For an object of type T, these are:

   CopyC++

   CXlIStream& operator>>(CXlIStream& is, T*& t);
   CXlOStream& operator<<(CXlOStream& os, const T* t)

   Note that the operators act on pointers to T, not to the objects directly.

2. Create an instance of PersistentHandleCache<T>, and assign the location of the persistent storage file and the performance characteristics of the persistent cache.

These steps are discussed in detail below.

Streaming operators

The object to be serialized is PersistentThing:

class PersistentThing {
public:
    PersistentThing(const TCHAR* name_, int value_, double created_) 
      : name(name_), value(value_), created(created_) 
    {
    }
    std::tstring name;
    int value;
    double created;
};

To serialize the object so that it can be saved to disk, the following operator overload is used:

inline CXlOStream& operator<<(CXlOStream& os, const PersistentThing* t)
{
    os << t->name << t->value << t->created;
    return os;
}

This is a simple object, and can be serialized easily by using the streaming operators for the built-in types std::tstring, int and double. The CXlOStream and CXlIStream classes contain many other useful streaming operators, including support for std::vector, std::map and matrices of type ple::imtx; so it is easy to serialize complex objects as well.

To deserialize the object from disk into memory, the following operator overload is used:

inline CXlIStream& operator>>(CXlIStream& is, PersistentThing*& t)
{
    std::tstring name;
    int value;
    double created;
    is >> name >> value >> created;
    t = new PersistentThing(name.c_str(), value, created);
    return is;
}

Note that the PersistentThing instance must be created on the heap, using new, and the passed pointer t is set to point to the new object.

An alternative approach might be to set the properties directly, after using a parameterless constructor, as follows:

inline CXlIStream& operator>>(CXlIStream& is, PersistentThing*& t)
{
    t = new PersistentThing();
    is >> t->name >> t->value >> t->created;
    return is;
}

Using existing serialization code

If your object already has code that allows it to be serialized, then you can adapt this code to CXlIStream and CXlOStream by using CXlIStream::ReadByteArray and CXlOStream::WriteByteArray.

See the SerializedThing object in the PersistentHandleDemo sample for an example of this technique.

PersistentHandleCache

In the sample the instance of PersistentHandleCache is declared globally; you could equally well make it a member variable of the application class.

The constructor includes all the run-time parameters required for the persistent cache:

const TCHAR* pszCacheFile = _T("%TMP%\\PersistentThings.dat");

psl::PersistentHandleCache<PersistentThing> thePersistentCache(
    NULL,         // Use the default handle formatter
    pszCacheFile, // Data file 
    100,          // Maximum number of items in saved file 
    0,            // Maximum age in seconds (ignored if 0) 
    true);        // AutoSave

The path used for the location of the data file includes an environment variable, %TMP%. This will be expanded at run-time to a complete path. This is a sensible location to use for per-user storage, since the user will always have read and write permission to the location, regardless of the operating system or security setup.

The AutoSave parameter is set to true, which saves any further coding: the cache will be restored from file during the OnXllOpenEx() event and saved to file during the OnXllClose() event.

Sample code

As well as the PersistentHandleDemo sample discussed above, there is also a more real-world example in the MonteCarlo2 sample.

Next: Other handle implementations >>