CODEWITHSUNDEEP
excelpersistencevstoadd-in
Oriol Terradas
Oriol Terradas

Reputation: 1798

How to store information in a Excel WorkBook

I'm programing an Excel Addin with VS2008 and VSTO(C#) over Office 2007. This addin needs to store information embedded or inside the Workbook to recover some status when the user opens again the Workbook with this kind of information. Before save the Workbook, the Addin serialize in XML all the information, and after open a WorkBook the Addin try to deserialize this information if it found it.

I tried to use the Office.DocumentProperties but each string element is truncated (max.256 chars)

At events Excel.AppEvents_WorkbookOpenEventHandler and Excel.AppEvents_WorkbookBeforeCloseEventHandler :

Office.DocumentProperties properties = (Office.DocumentProperties)this.Application.ActiveWorkbook.CustomDocumentProperties;
properties.Add(Constants.ADDIN_PERSISTENCE, false, Office.MsoDocProperties.msoPropertyTypeString, serialize(configurationInstance), null);

At event AppEvents_WorkbookOpenEventHandler:

Office.DocumentProperties properties = (Office.DocumentProperties)Application.ActiveWorkbook.CustomDocumentProperties;
Configuration configurationInstance = deserialize((string)properties[Constants.ADDIN_PERSISTENCE]);

But this doesn't work because the serialization returns a string longer than 256 chars.

In other Addin, over Excel 2003, I store the information in the first cell on a "veryhidden" sheet with a strange name. I don't know if there are better solutions.

Upvotes: 2

Views: 1237

Answers (1)

Oriol Terradas
Oriol Terradas

Reputation: 1798

OK, I found "the good method". Office.CustomXMLParts is a collection to store XML data.

You can find information directly at Msdn1

MSDN example:

private void AddCustomXmlPartToWorkbook(Excel.Workbook workbook){
string xmlString =
    "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
    "<employees xmlns=\"http://schemas.microsoft.com/vsto/samples\">" +
        "<employee>" +
            "<name>Karina Leal</name>" +
            "<hireDate>1999-04-01</hireDate>" +
            "<title>Manager</title>" +
        "</employee>" +
    "</employees>";
Office.CustomXMLPart employeeXMLPart = workbook.CustomXMLParts.Add(xmlString, missing);
}

Upvotes: 4

Related Questions