how to attach a c# object to an excel worksheet and have it saved when the workbook is saved
I am creating an excel add-in in c#. In the add-in the user creates 'query' objects then the query is performed and data is displayed in excel. I want to save the 'query' object and to be able to fetch it given a worksheet to enable editing of it and re performing the query.
I have found the following possibility:
public static void SetDocumentProperty(string propertyName, String str)
{
DeleteDocumentProperty(propertyName);
var workbook = Globals.ThisAddIn.GetActiveWorkBook();
workbook.CustomDocumentProperties.Add(propertyName, false, Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString, str);
}
which saves the query as a string (after serializing the object). I still need a way to connect the query to the worksheet, I have tried using the sheet name - t开发者_JAVA技巧he trouble with this is the sheet name might change. so my question is:
- Is there any way of getting a unique identifier of the worksheet?
- Is there a better way of achieving what i am trying to do?
Thanks
I ended up using this:
public static void SetWorkSheetQuery(Microsoft.Office.Interop.Excel.Worksheet ws, EntityQuery q)
{
var cp = GetCustomProperty(ws,"query");
if (cp == null)
ws.CustomProperties.Add("query", q.ToJson());
else cp.Value = q.ToJson();
}
This attaches the object to a custom property of a worksheet after serializing it.
It can later be fetched by using (the name of the property being "query"
):
private static CustomProperty GetCustomProperty(Worksheet ws, String name)
{
for (int i = 1; i <= ws.CustomProperties.Count; i++)
{
if (ws.CustomProperties.get_Item(i).Name == name)
return ws.CustomProperties.get_Item(i);
}
return null;
}
It can be deleted by using:
var cp = GetCustomProperty(ws, "query");
if (cp != null)
cp.Delete();
精彩评论