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

Hack 85. Display a Windows Form from Excel 2003

Create a graphical application to gather
information for Excel.

In this hack, you'll learn
how to make an Excel workbook pop up a Windows Form application to
collect information and insert it into to the worksheet.

11.4.1. Create a Windows Form

Start by creating a new project in Visual
Studio. Choose the Visual Studio Tools for Office project group and
then the project type, Excel Workbook. For this example,
I'm going to create a graph of orders for customers,
so I'll use CustomerOrders for
the name of the project, as shown in Figure 11-4.

Figure 11-4. New Excel Workbook Project dialog

In the Microsoft Office Project Wizard, select the radio button to
create a new document (Figure 11-5). Next, give the
document a name and choose the location to store the document on your
machine. At this point, you do not need to make any changes to the
security tab, so click Finish to complete the wizard.

Figure 11-5. New Project Wizard

Once the Visual Studio Wizard has completed the project setup, you
will be dropped into the solution. At this point, you will need to
add a Windows Form to the application and name it
GetCustomer.

Next, create a form that looks similar to Figure 11-6 and set the controls'
properties according to Table 11-1.

Figure 11-6. GetCustomer Windows Form

11.4.2. Initialize the Windows Form

In order to communicate with the code behind
the Excel document, you will need to pass the
ThisWorkbook object as a parameter to your Windows
Form. So, first, you will need to create a class-level variable in
your Windows Form:

private OfficeCodeBehind excelCode;

The next step is to modify the Windows Forms constructor to accept
the ThisWorkbook object as a parameter. In C#, the
form constructor will have the same name as your form class.
Here's the empty constructor for the
GetConstructor form:

public GetCustomer( )
{
}

Update this constructor to take the CodeBehind
object as a parameter of type OfficeCodeBehind and
add code to store the object in the excelCode
variable you created for this class. This should be done after the
call to InitializeComponent (which initializes the
form):

public GetCustomer (OfficeCodeBehind targetExcelCode)
{
InitializeComponent( );
this.excelCode = targetExcelCode;
}

The next step is to create an instance of the Windows Forms class and
call the Show method. In this example, you are
going to do this when the workbook first opens the new document:

protected void ThisWorkbook_Open( )
{
GetCustomer inputForm = new GetCustomer (this);
inputForm.Show( );
}

At this point, you should be able to build and test your code. Excel
should start and open your blank form. Sure, it
doesn't do much yet, but you should run it now to
make sure that all of the pieces are falling together.

11.4.3. Gather Data with the Windows Forms

Now that you have the form opening up when the
user starts a new instance of the Excel document, you need to add
code to the form's Load event to populate the combo
boxes with data from a database. First, to make life a little easier,
in your Windows Forms class, you need to add references to the data
namespaces listed here:

using System.Data;
using System.Data.SqlClient;

Next, create a method to handle the form's Load
event. The easiest way to do this is to double-click anywhere on the
form (as long as it is not a control), and you'll be
placed in the code editor for this method. (You may need to change
the connection string, highlighted in bold, to suit your SQL Server
or MSDE installation.)

private void GetCustomer_Load(object sender, System.EventArgs e)
{
//Get the customers and order years from the database
SqlConnection conn = new SqlConnection("data source=localhost; 
initial catalog=Northwind;integrated security=true");
SqlDataAdapter da = new SqlDataAdapter("select CustomerID, 
CompanyName from customers", conn);
DataSet ds = new DataSet( );
da.Fill(ds, "Customers");
da.SelectCommand.CommandText = "select distinct 
datepart(\"Year\", orderdate) as [Year] from orders";
da.Fill(ds, "Years");
//Using databinding, fill the combo boxes with data
cboCustomer.DisplayMember = "CompanyName";
cboCustomer.ValueMember = "CustomerID";
cboCustomer.DataSource = ds.Tables["Customers"];
cboYear.DisplayMember = "Year";
cboYear.ValueMember = "Year";
cboYear.DataSource = ds.Tables["Years"];
}

This method connects to the SQL Server using a data adapter. In this
example, a DataSet is used in order to take
advantage of the .NET data-binding capabilities to fill the combo
boxes. Then the control properties are set to map the data fields to
show the friendly text (CompanyName) and make the
CustomerID available for later.

Once the user makes a selection of the customer and a year, you will
need to use the selections to query the database for the order counts
by month and then output the results to the Excel workbook:

private void cmdOK_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("data source=localhost; 
initial catalog=Northwind;integrated security=true");
SqlCommand cmd = new SqlCommand(
"select datepart(\"Month\", Orderdate) 
as [Month], count(orderid) as OrderCount
from orders 
where customerid = @CustID 
and datepart(\"Year\", OrderDate) = @Year 
group by customerid, datepart(\"Month\", Orderdate)", conn);
cmd.Parameters.Add("@CustID", cboCustomer.SelectedValue);
cmd.Parameters.Add("@Year", cboYear.SelectedValue); 
SqlDataReader dr;
conn.Open( );
dr = cmd.ExecuteReader( );
//initialize array    
string[,] OrderCounts = new string[12,2] {
{"Jan", "0"},    {"Feb", "0"},{"Mar", "0"},{"Apr", "0"},
{"May", "0"},{"Jun", "0"},{"Jul", "0"},{"Aug", "0"},
{"Sep", "0"},{"Oct", "0"},{"Nov", "0"},{"Dec", "0"}
};
while (dr.Read( ))
{
//get Month number from first column
int Month = (int)dr["Month"];
//get OrderCount from second column and put in array
OrderCounts[Month - 1, 1] = dr["OrderCount"].ToString( );
}
dr.Close( );
conn.Close( );
//Output customers order data to Excel
for (int i = 0; i != 12; i++) 
{
for (int j = 0; j !=2; j++)
{
this.excelCode.EchoStringToCell(OrderCounts[i,j],
new int[2] {i+1,j+1});
}
}
}

In the method that handles the click event of the command button, you
are going to connect to the SQL server and retrieve the counts of
orders for a particular client for the selected year. Notice that by
using the SelectedValue property of the combo
boxes, you have the CustomerID field. Later, when
you are building the chart, you will use both the
Text property and the
SelectedValue property to label your work in the
Excel sheets.

There are a couple of ways that you could have handled the output of
the data for each month. Here, the use of an array, initialized to
the default values, allows you to control what is displayed for the
month names without writing too much Transact-SQL. Now all you need
to do is get SQL Server to return the rows where data exists. The
other option would be for SQL to return results for each month and
then write the data directly from the data reader to the Excel
worksheet.

Finally, to send the data to the Excel worksheet, you need to move
through the two dimensions of the array and call the
EchoStringToCell method that you will create in the next
section. This function will take a string value and an array for the
cell location in which to place the data. For example,
int[2] {1,1} sends the data to row one, column
one.

public void EchoStringToCell(string str, int[  ] Cell)
{
Excel.Worksheet sheet1 =
(Excel.Worksheet)this.ThisApplication.Sheets.get_Item(1);
((Excel.Range)sheet1.Cells[cell[0],cell[1]]).Value2 = str;
}

At this point, compile and test your application. Excel should start
and immediately display your Windows Forms. After entering data,
click the OK button, and your data should populate the Excel
spreadsheet.

11.4.4. Create a Chart in Excel

The last thing to do then is to create a
chart of the data that you read in from
the database. In the following code, you will pass the
CustomerID, CompanyName, and
Year that the user has selected. You will be using
this information to customize the layout of the chart page by
labeling the items appropriately.

In order to build the chart, first get a reference to the first
worksheet in the collection (where you have been putting all of the
data) and create a new chart sheet following this page by calling the
Add method of the Charts collection.

After you have created the chart page, call the chart wizard to build
the chart for you. Don't worry if there seem to be a
lot of parameters that aren't clear yet. The easiest
way to get familiar with the parameter options is to use the

Record Macro feature within Excel and
inspect the code that is generated.

public void CreateChart(
string CustomerID, string CompanyName, int Year) 
{
// Now create the chart.
Excel.Worksheet xlSheet = 
(Excel.Worksheet)this.ThisApplication.Sheets.get_Item(1);
Excel.Chart xlChart = (Excel.Chart)ThisWorkbook.Charts.Add
(Type.Missing, xlSheet, Type.Missing, Type.Missing);
Excel.Range cellRange = (Excel.Range)xlSheet.UsedRange;
xlChart.Name = CustomerID + "-" + Year.ToString( );
xlChart.ChartWizard(cellRange.CurrentRegion, 
Excel.Constants.xl3DBar, Type.Missing, 
Excel.XlRowCol.xlColumns, 1, Type.Missing, false, 
CompanyName + " Orders for " + Year.ToString( ),
Type.Missing, Type.Missing, Type.Missing);
// Apply some formatting to the chart title.
xlChart.ChartTitle.Font.Size = 16;
xlChart.ChartTitle.Shadow = true;
xlChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}

Now update the method behind your Get Data button to call this new
method and create the chart:

private void cmdOK_Click(object sender, System.EventArgs e)
{
//Data has already been loaded to the page, now create chart
this.excelCode.CreateChart((string)cboCustomer.SelectedValue,
(string)cboCustomer.Text, (int)cboYear.SelectedValue);
}

At this point, you can build and execute your application. Now, when
you click the Get Data button, the data will populate the Excel page
and then immediately create a chart of the data.

These hacks introduce the capabilities of the Visual Studio Tools for
Office and get you quickly up to speed with creating your own
solutions based on Microsoft Office System 2003. With the integration
of the .NET Framework, you can work in the language that is familiar
to you, take advantage of reusable components, and build on existing
Web Services to quickly build a line of business applications.

Brian Sherwin