VSTO: Attach meta-data to a cell in Excel?

Question

I'm using VSTO to create an Excel Add-on. This add-on retrieves and display alot of data from a sql-server. This works great, but later on I plan to access some of the data inside excel and modify it in some ways. My problem is that I need a way of classify cells that I want to modify. Is there any way to add meta-data to a cell to know if it is a cell that should be modified? E.g. add a attribute to the cell, e.g. "editable_cell", and do something like Excel.FindCellsWithAttribute("editable_cell") to find the sought after cells?

Thanks!

Answer 1

There are several way to do this. I do not know your specific requirements, so I will briefly outline some solutions.

• Create a Named Range, but adding/removing data can affect the defined Named Range if you don't do it right. Sometimes, it is better to define a single cell named range to act as a bookmark then "select range" will get you all the data.

• Create a Style. Apply this style to each data cell you wish to "find". Define a method that returns a Range base on which cells have the specified style.

• Create a PivotCache Object. This object has the ability to update itself, and reduces the file size, especially if the cache is used repeatedly in a workbook. It is also one way to work around the limitation in the number of rows in a worksheet.

• Create a List. This has many advantages. You can add/remove data at will. Add/remove columns. Think of a list as a table.

• Use XML Mapping (http://msdn.microsoft.com/en-us/library/aa203737(office.11).aspx) as "code4life" mentions.

• If the workbook is XMLSS, then define a new namespace and adorn the cells with an attribute from the namespace. You can then "query" using XPath. This is a very powerful because you can embed whatever is needed into a workbook.

Each has its advantages/disadvantages. I have used each solution multiple times.

Answer 2

AMissico listed some very good solutions. I'd like to add one that worked for me:

In C# (ExcelDNA/VSTO) or VBA you can do something like:

var app = (Application) ExcelDnaUtil.Application;
app.ActiveCell.AddComment("This is an editable cell");

The obvious drawback would be that the user can see this comment - in my case it worked out very well because I could provide useful diagnostics to the user, and also parse the same text to get the meta-data I wanted.

You can also hide the comment using:

app.DisplayCommentIndicator = XlCommentDisplayMode.xlNoIndicator;

However, note that this hides all comments, not just the one you added. To iterate over comments in a sheet, you can use the following:

var comments = ((Worksheet)app.ActiveSheet).Comments;
foreach(Comment comment in comments)
{
    var text = comment.Text();
    // do something...
}

Answer 3

You might want to look at XML mapping: http://msdn.microsoft.com/en-us/library/aa203737(office.11).aspx