Display a Windows Form from Excel 2003





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.

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.

New Excel Workbook Project dialog


In the Microsoft Office Project Wizard, select the radio button to create a new document (Figure). 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.

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 and set the controls' properties according to Figure.

GetCustomer Windows Form


Windows Form controls and properties

Required controls

Properties

Label

Text: Customer Name:

ComboBox

Name: cboCustomer

Label

Text: Year

ComboBox

Name: cboYear

Button (Get Data)

Name: cmdOK


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.

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.

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


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows