开发者

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:

  1. Is there any way of getting a unique identifier of the worksheet?
  2. 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();
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜