Demonstrates the use of persistent RTD handles to represent objects in Excel

Overview

This add-in contains the code described in the User Guide topic Persistent object handles.

RTD handles (as implemented in the RtdHandles.xpe extension) are impermanent. When you reopen a workbook that contains RTD handles, they are all automatically refreshed - and any cell that depends on them is also recalculated.

This add-in demonstrates techniques for avoiding this recalculation by storing the objects themselves in persistent storage on disk.

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.

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;
}

PersistentHandleCache

In this 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.

Requirements

To edit the functions in this add-in, you need to load the extension file RtdHandles.xpe. See Loading an extension file for instructions.

You should also make sure that none of the following extension files is loaded, since the various types of handles are mutually exclusive.

• NumericHandles.xpe
• StringHandles.xpe
• StringPtrHandles.xpe

Classes and functions used

PersistentHandleCache<T> | CXlIStream | CXlOStream

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file PersistentHandleDemo.sln or the project file PersistentHandleDemo.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files PersistentHandleDemo.help.xml and PersistentHandleDemo.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects | Using object handles | RtdHandleDemo sample