Last week I attended VSLive and promised to post some of the sample code (I emphasize demo code J) I discussed at the conference. Specifically, in the couple of sessions that I did I discussed SharePoint integration with Word (showing a lightweight OBA) and the creation of Outlook add-ins using Visual Studio 2008 (VSTO 3.0). In an earlier post, I showed you how to create an Outlook add-in and integrate with external data using a custom task pane. I also showed you how to create an Outlook Form Region. I also published an article on this in the most recent MSDN magazine http://msdn2.microsoft.com/en-us/magazine/cc164242.aspx.
So, I’m going to focus on the OBA side of things through six posts, which I’ll break out as follows:
The Lightweight OBA scenario is essentially an annual review process that uses a custom task pane to load employee data and then map it to a set of Word content controls in the document. The data comes from a SQL server and can be updated from the custom task pane as well. Further, the document is tied to a SharePoint content type and has some workflow associated with it. (I currently have some oob workflow associated with it, but I’ll create some custom workflow in the next couple of weeks and associate that with the document-level solution). To make things a little easier to get started, I’ve posted my VSLive deck to this post so you can walk through the deck…I’ve included screenshots with the deck so you can get a sense for what my demo looked like. Note that it is a work in progress!
So, since this is the first of these five posts I’ll cover the creation of the VSTO document-level customization. Alright, here goes:
The first thing you’ll want to do is to make sure you have an employee database (keep it simple with a table) with some records in it. In my solution, I have a few records each with an employee name, ID, title, and review scores for three years. I’ve also got some fields for the comments and ratings that you’ll use when uploading the comments. Take a look at the screenshots in the attached deck to get a sense for what fields I’ve created in the SQL Server table.
After you’ve got your table created, you’ll want to now go ahead and create your VSTO project. Note that when creating a document-level solution you can either create a document and then use that as the document in your development project or you can create and edit a blank document in the VS IDE. Depending on how complex you want your template to be, you can do either…but if you’re looking to build a complex template/document, I’d consider building it first and then using it in the project. Okay, open Visual Studio 2008, click File, New, Project, and then select either Visual C# or Visual Basic, Office, and Word 2007 Document. When prompted, select whether you want to use an existing document (if you created one here’s where you would browse to it) or not (in which case you would create/edit the document in VS).
With your VSTO document-level solution created, you’ll want first add the data source (the employee table you created) to your project. To do this, click Data, Add New Data Source, select your SQL server instance, and the database within which the employee table resides.
After you’ve got your data added, you’ll now want to add a user control that will host a data view. Right click your project, click Add, User Control, and give it a name. The designer view will open by default, and you can drag over the fields that you want to load from the SQL server table (mine was called employee) onto the designer, which will automatically create a data view for you. In my custom task pane, I also created a bottom part to the custom task pane (see slide 13 for the entire custom task pane) which I mapped to the data that the manager would enter in as rating data for the annual review process. For each of the custom task pane sections I had a button which had a load event (took the currently selected data and added it to the document) and a Sync button (which mapped the completed fields in the document to the fields in the custom task pane). You could then subsequently save the data using the data view back into SQL server.
Okay, you should now have the data source added to your project and the user control created. The next thing you’ll want to do is customize the Word document. In my document, I opened the Word document in the designer view and then dragged a few content controls from the Word Controls part of the Toolbox and create a document that could then house some external data (see slide 21).
After you’ve done all of this, we now need to do a couple of things to finish Part One: 1) add some code to load an instance of the user control into a custom task pane when the assembly is loaded by the host application and 2) add some mapping code between the data from the custom task pane to and from the document.
1) The code for adding the user control to the custom task pane will be added to the core ThisDocument.cs file:
using Office = Microsoft.Office.Core;
using Word = Microsoft.Office.Interop.Word;
public partial class ThisDocument
private AnnualReviewTaskPane ctrl = new AnnualReviewTaskPane();
private void ThisDocument_Startup(object sender, System.EventArgs e)
private void ThisDocument_Shutdown(object sender, System.EventArgs e)
2) Mapping code between custom task pane and Word document.
public partial class AnnualReviewTaskPane : UserControl
private void employeeReviewBindingNavigatorSaveItem_Click(object sender, EventArgs e)
//You’ll want to add some more sophisticated error-handling code here.
catch (Exception exceptError)
private void AnnualReviewTaskPane_Load(object sender, EventArgs e)
private void button1_Click(object sender, EventArgs e)
private void mapTextboxData()
empName = employee_NameTextBox.Text;
empNumber = employee_NumTextBox.Text;
empTitle = employee_TitleTextBox.Text;
empReviewScore06 = review_Score_06TextBox.Text;
empReviewScore07 = review_Score_07TextBox.Text;
private void mapContentControls()
Globals.ThisDocument.wccName.Text = empName;
Globals.ThisDocument.wccEmpNumber.Text = empNumber;
Globals.ThisDocument.wccTitle.Text = empTitle;
Globals.ThisDocument.wcc2006ReviewScore.Text = empReviewScore06;
Globals.ThisDocument.wcc2007ReviewScore.Text = empReviewScore07;
private void button3_Click(object sender, EventArgs e)
//You can definitely optimize this for one string mapping as opposed to a //two step process.
strWorkEthic = Globals.ThisDocument.ddlistWorkEthic.Text;
strLeadership = Globals.ThisDocument.ddlistLeadership.Text;
strProdKnowledge = Globals.ThisDocument.ddlistProdknowledge.Text;
strSkillsDev = Globals.ThisDocument.ddlistSkillsDev.Text;
strPromotion = Globals.ThisDocument.ddlistPromotion.Text;
str2008ReviewScore = Globals.ThisDocument.ddlist2008ReviewScore.Text;
strAdditionalComments = Globals.ThisDocument.wccAdditionalComments.Text;
strDate = Globals.ThisDocument.wccSignatureDate.Text;
strSignature = Globals.ThisDocument.wccManagerSignature.Text;
work_EthicTextBox.Text = strWorkEthic;
leadershipTextBox.Text = strLeadership;
prod_KnowledgeTextBox.Text = strProdKnowledge;
tech_SkillsTextBox.Text = strSkillsDev;
promotionTextBox.Text = strPromotion;
review_CommentsTextBox.Text = strAdditionalComments;
review_Score_08TextBox.Text = str2008ReviewScore;
Note: Where you see “…” I’ve not included this as this is auto-generated code.
So, at this point you should have a VSTO document-level solution that does the following:
1. Loads data into a custom task pane.2. Enables the user to browse the data.3. Load the employee data into the Word document.4. The Word document enables you to add ratings.5. Sync the ratings with the custom task pane.6. Save the employee’s annual review data to the SQL server.
In the next post, I’ll discuss deployment.
Have fun coding!