Visual Studio Hacks [Electronic resources] نسخه متنی

Hack 84. Create a Command Bar in Word 2003

How about a .NET-powered toolbar in Word?
You'll want to click it for no good reason at all,
just knowing how cool it is that you're calling into
managed code.

In this hack, you will be creating a command bar item with a list of customers
from the Northwind database.

If your installation of the Visual Studio Tools for Office is
complete, you should see some additional project types in Visual
Studio's New Project dialog. I'll
show you how to create a new Visual C# Project for a Microsoft Word
template called CommandBar (as shown in Figure 11-1).

Figure 11-1. New Project dialog

Once you have selected the project template and named the project,
click OK and you'll be presented with the
Microsoft Office Project Wizard (Figure 11-2). Since you are going to be working with a new
document template, select the option to Create a New Document. If you
want to change the name or location of the document template that
will be created, you can change the corresponding text box.

On the Security Settings
tab, the wizard will automatically update your security settings to
allow the managed code document to run. There is not space here to
explain how to set all of the security settings, so until you can
spend some time figuring out the Code Access Security model for the
project, I suggest that you let the wizard make the necessary
changes. When you click Finish, the wizard will create the required
document templates and a code-behind class for the document.

Figure 11-2. Microsoft Office Project Wizard

11.3.1. Create the Command Bar

When the wizard
has completed, you will be placed into the main code file named
ThisDocument.cs. Most of your code will execute
when a user creates a new instance of your document. This event is
handled by the following method:

protected void ThisDocument_New( )
{
}

At this point, you are going to need to create some class-level
variables to work with as you build your command bar. The main class
is OfficeCodeBehind, so your code should look
something like this:

public class OfficeCodeBehind
{
private object oMissing = System.Reflection.Missing.Value;
private Office.CommandBar CBar;
private Office.CommandBarComboBox CBarComboBox;
private Office.CommandBarButton CBarButton;
// ...
}

Next, you will need to write some code to create the different user
interface elements that you plan to place on the command bar. In this
example, you will need a combo listbox and a button. (You may need to
change the connection string, highlighted in bold, to suit your SQL
Server or MSDE installation.)

You will notice that in this section of code, you will be using the oMissing object you defined in the preceding code. This is because these are optional variables in the Word document and C# does not support optional variables, so you must pass in a default value.

private bool SetupCommandBar( )
{
try
{
// Create command bar for this document
ThisApplication.CustomizationContext=ThisDocument;
CBar = ThisApplication.CommandBars.Add("Customer Form",
oMissing, oMissing, (object)true);
// Add a combo box to the command bar
object objType = Office.MsoControlType.msoControlDropdown;
CBarComboBox = (Office.CommandBarComboBox)
((CBar.Controls.Add(objType, oMissing, oMissing,
oMissing, (object)true)));
CBarComboBox.Caption = "Select:";
CBarComboBox.Style = Office.MsoComboStyle.msoComboLabel;
CBarComboBox.ListIndex = 0;
CBarComboBox.Width = 250;
CBarComboBox.BeginGroup=true;
//Add items to combo box from database
SqlConnection conn = 
new SqlConnection("data source=localhost;
initial catalog=Northwind;integrated security=true");
SqlCommand cmd = new SqlCommand("select CustomerID, 
CompanyName from customers", conn);
SqlDataReader dr;
conn.Open( );
dr = cmd.ExecuteReader( );
int i = 0;
while (dr.Read( ))
{
i += 1;
CBarComboBox.AddItem(dr["CustomerID"].ToString( ) 
+ "-" + dr["CompanyName"].ToString( ), i);
}
dr.Close( );
conn.Close( );
// Add a button to the command bar to insert data
CBarButton = (Office.CommandBarButton)
(CBar.Controls.Add((object)1, oMissing, oMissing, 
oMissing, oMissing));
CBarButton.Style=Office.MsoButtonStyle.msoButtonCaption;
CBarButton.Caption = "Insert";
// Make the new command bar visible.
CBar.Visible=true;
return true;
}
catch (Exception ex)
{
MessageBox.Show("Error creating CommandBar: " + ex.Message, 
"Customer Form", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return false;
}
}

In the preceding code, the first thing you need to create a command
bar is a reference to the current instance of the Microsoft Word
application you will be working in. Once you have this reference, you
can create a new command bar by calling the Add method of the
CommandBars collection.

The next step is to create the drop-down combo box
(Office.MsoControlType.msoControlDropdown). Once
you have created the combo box, you can set the properties for how
you want the control to appear to the user.

After the combo box is created, you are ready to populate the control
with values from a database. In this example, the data will be coming
from the Northwind database on the SQL Server of the machine that is
running the code. Since you will need only the
CustomerID and CustomerName
from the database, it is a good practice to select only that data.
Since you will be using a SqlDataReader to get the
data, you will be responsible for opening the connection in your code
before executing your SqlCommand object. The
ExecuteReader object will create a
SqlDataReader object and pass the reference back
to the variable we have declared in order to read the data. At this
point, you have not read any data.

The while loop begins reading the data by grabbing the next available
row. As long as the SqlDataReader successfully
returns data, the code that adds the item to the combo box executes.
Unfortunately, in this example, the primary key that we need to use
to identify the customer is a string-based key, so you cannot use it
as the index parameter of the AddItem method. For
now, you will make the key part of the display text and retrieve the
key value when you handle the button click later. After reading the
data, you need to close the SqlDataReader and the
SqlConnection.

Next, you need to create the button on the toolbar
(Office.CommandBarButton) and set its display
properties. Finally, show the command bar to the user with the
Show method.

Now that the method to create the command bar is complete, you will
need to call the method to initialize the command
bar's creation when a new document is created:

protected void ThisDocument_New( )
{
SetupCommandBar( );
}

Now, test your new command bar by executing the application. Visual
Studio should start an instance of Word, create a new document from
your blank template, and finally raise the
ThisDocument_New event, which will create your
command bar as shown in Figure 11-3.

Figure 11-3. Command bar in Word

11.3.2. Handle the Command Bar Events

Now that you have a
command bar in Word, you need to
listen for the events that the command bar raises. In this section,
you will learn how to wire up the command bar events to your custom
code.

The first order of business is to create a class-level delegate for
the events you want to handle:

public class OfficeCodeBehind
{
private Office._CommandBarButtonEvents_ClickEventHandler 
CBarButtonEvent;
...

Second, you will need to create a method containing the code you want
to run when the command bar raises the event. In this code, you will
be taking the combo box selection and looking up the customer record
to type the address into the Word document:

private void CBarButton_Click(
Office.CommandBarButton btn, 
ref bool Cancel)
{
// Get Word ready to receive data
Word.Selection sln = null;
sln = ThisApplication.Selection;
ThisApplication.Options.Overtype = false;
// Get customer ID back into individual fields
string wrkString = CBarComboBox.Text;
string[  ] Customer = wrkString.Split('-');
// Get customer information from database
SqlConnection conn = new SqlConnection("data source=localhost;
initial catalog=Northwind;integrated security=true");
SqlCommand cmd = new SqlCommand("select * from customers 
where customerid = @CustID", conn);
cmd.Parameters.Add("@CustID", Customer[0]);
SqlDataReader dr;
conn.Open( );
dr = cmd.ExecuteReader( );
//Output customer Address Data to Word
while (dr.Read( ))
{
sln.TypeText(dr["CustomerID"].ToString( ) + "\n");
sln.TypeText(dr["CompanyName"].ToString( ) + "\n");
sln.TypeText(dr["ContactName"].ToString( ) + "\n");
sln.TypeText(dr["Address"].ToString( ) + "\n");
sln.TypeText(dr["City"].ToString( ) + ", ");
sln.TypeText(dr["Region"].ToString( ) + "  ");
sln.TypeText(dr["PostalCode"].ToString( ) + "\n");
sln.TypeText(dr["Country"].ToString( ));
}
dr.Close( );
conn.Close( );
}

In the preceding code, you first create a variable for the current
selection in Word. The next step is to take the selection from the
combo box and split the text into an array containing two items.

In a scenario in which you are able to use an integer primary key, you would want to use that value as the indexer when calling the AddItem method as you build the command bar: CBarComboBox.AddItem(dr["CompanyName"].ToString( ), dr["CompanyID"]); If you take this approach, you would be able to query for the key value by using the following code: int CustomerID = CBarComboBox.Value;

Next, you need to create a SqlConnection,
SqlCommand, and SqlDataReader
to retrieve the data from the database. Once you have retrieved the
record, you will output the records to the
Word.Selection variable that you created earlier
using the TypeText method.

Now that you have created the method to output the data record to
your Word document, you need to wire that code into the button by
attaching the delegate to your event handler:

private void WireUpEvents( )
{
// Set up the Click events for the command bar buttons.
CBarButtonEvent = 
new Office._CommandBarButtonEvents_ClickEventHandler
(CBarButton_Click);
CBarButton.Click += CBarButtonEvent;
}

One last order of business is to finish the document initialization
process by updating the ThisDocument_New method to
call the event wire-up code after the command bar has been created:

protected void ThisDocument_New( )
{
SetupCommandBar( );
WireUpEvents( );
}

Now, build and test the application once again. This time, when you
click the button on the command bar, the name and address for the
customer will be typed into the Word document.

Brian Sherwin