data to a cell in Excel?

 

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? Eg add a attribute to the cell, eg "editable_cell", and do something like Excel.FindCellsWithAttribute("editable_cell") to find the sought after cells?

Thanks!

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.

    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...
}

    I don't recall a way to do exactly what you are asking. What I've seen done in the past was setting range names based on what you might want to look up. Another option is to hide a cell next to it or some other predetermined offset (eg always 3 cells to the right, or same position but on a hidden page). The hidden cell/page would have the data you would be looking for.

    One thing that seems to have accidentally emerged as a best practice at the accounting firm I used to work for was that you should push all your data into an "ugly" page that is hidden and use formulas/lookups to refer to your data. That way you can update the "ugly" page and know where data is while having a "pretty page" that the users can monkey up to their hearts' content.

    链接地址: http://www.djcxy.com/p/67834.html

    上一篇: 如何使用C＃/ VSTO检查Excel中是否存在范围

    下一篇: 数据到Excel中的单元格？