VSTO Excel: Restore ListObject data source when reopening a file

Question

I am working on an Excel 2010 template project. In my template I have many sheets with static ListObject controls in each of them. To initialize my ListObject, I bind a BindingList<MyCustomType> so it generates a column for each of my MyCustomType public properties. It is really handy because when the user some rows in the ListObject, it automatically fills up my BindingList instance. I added a button in the Excel ribbon so that the program can validate and commit these rows through an EDM. This is how I bind my data to the ListObject in the startup event handler of one of my Excel sheet.

public partial class MyCustomTypesSheet
{
    private BindingList<MyCustomType> myCustomTypes;

    private void OnStartup(object sender, System.EventArgs e)
    {
        ExcelTools.ListObject myCustomTypeTable = this.MyCustomTypeData;
        BindingList<MyCustomType> customTypes = new BindingList<MyCustomType>();

        myCustomTypeTable.SetDataBinding(customTypes);
    }

    // Implementation detail...
}

Now my issue is that it is very likely that the user of this template will enter these rows in many sessions. It means that he will enter data, save the file, close it, reopen it, enter some new rows and eventually try to commit these rows when he thinks he is done. What I noticed is that when the Excel file created from the template is reopened, the DataSource property of my ListObject controls is null. Which means I have no way to get back the data from the ListObject into a BindingList<MyCustomType>. I have been searching and I found no automatic way to do that and I don't really want to make a piece of code that would crawl through all of the columns to recreate my MyCustomType instances. In an ideal world I would have done like this.

private void OnStartup(object sender, System.EventArgs e)
{
    ExcelTools.ListObject myCustomTypeTable = this.MyCustomTypeData;
    BindingList<MyCustomType> customTypes = null;

    if (myCustomTypeTable.DataSource == null) // Will always be null and erase previous data.
    {
        customTypes = new BindingList<MyCustomType>();
        myCustomTypeTable.SetDataBinding(customTypes);
    }
    else
    {
        customTypes = myCustomTypeTable.DataSource as BindingList<MyCustomType>;
    }
}

I have been doing a lot of research on this but I was not able to find a solution so I hope some of your can help me to resolve this issue.

Thanks.

Answer 1

As a last solution I decided that I would serialize my object list in XML and then add it as a XML custom part to my Excel file on save. But when I got into MSDN documentation to achieve this, I found out that there was 2 ways to persist data: XML custom part and data caching. And actually data caching was exactly the functionality I was looking for.

So I have been able to achieve my goal by simply using the CachedAttribute.

public partial class MyCustomTypesSheet
{
    [Cached]
    public BindingList<MyCustomType> MyCustomTypesDataSource { get; set; }

    private void OnStartup(object sender, System.EventArgs e)
    {
        ExcelTools.ListObject myCustomTypeTable = this.MyCustomTypeData;

        if (this.MyCustomTypesDataSource == null)
        {
            this.MyCustomTypesDataSource = new BindingList<MyCustomType>();
            this.MyCustomTypesDataSource.Add(new MyCustomType());
        }

        myCustomTypeTable.SetDataBinding(this.MyCustomTypesDataSource);
    }

    private void InternalStartup()
    {
        this.Startup += new System.EventHandler(OnStartup);
    }
}

It works like a charm. You can find more information about data caching in MSDN documentation.