共用方式為

Dynamically Creating a VSTO Worksheet in Visual Studio 2008 SP1

  • 發行項

Note that the approach described in this article is not supported by Microsoft. The GetVstoObject method is designed to be used primarily in application-level add-ins. Using this method in the way described in this article is an undocumented feature that could change at any time or in any release of VSTO. The official documentation does not recommend this approach.

One additional problem commonly encountered in the VSTO document programming model is how to attach code and data bindings to dynamically created worksheet objects. The new GetVstoObject feature described in the previous blog post can also be used in document projects to dynamically create a VSTO host item for a worksheet.

Make sure your ThisWorkbook.cs file has the following line of code added at the top of the file with the other using statements:

 using Microsoft.Office.Tools.Excel.Extensions;

Then code like this can be written to obtain a VSTO host item for the dynamically created worksheet:

 private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
  Excel.Worksheet worksheet = Sheets.Add(missing, missing, 1, missing)
    as Excel.Worksheet;

  // Force VBA to initialize
  object temp = this.VBProject;
  vstoSheet = worksheet.GetVstoObject();
}

The one mysterious line of code here is the call made to the VBProject property. This line of code does nothing except force VBA to initialize so VSTO can get a valid CodeName for the VSTO host item. It does not add a VBA project to the workbook or anything else of that nature. However, it does access the VBProject object. For a solution that dynamically creates host items in Excel, you must make sure that users of your solution have Trust access to Visual Basic project object model is checked in the Macro Security settings (Office Menu > Excel Options > Trust Center > Trust Center Settings > Macro Settings). Otherwise, this line of code will fail:

 object temp = this.VBProject;

Once you have vstoSheet as shown in the code above, you can then use all the features of a Worksheet host item including the Controls collection to dynamically add host item controls. Just as dynamic host controls are not re-created when a document containing them is saved and then reloaded, dynamic host items are also not re-created. You must hook up any worksheets that weren’t there when the VSTO project was created each time the document is loaded.

Thanks to Igor Zinkovsky for helping to implement this feature and for his help on this particular topic.

Comments