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.