Visual Studio Tools for Office 2007: How the Ribbon Helps Make Your Document a Dynamic Application

Parse this code to see what it does. At the beginning of the function, create a new SQLCommand to fetch data from the Customers table. Define the command text in a SQL statement and then create a DataReader to execute the SQLCommand using the ExecuteReader() method. Because you are not making any changes to the table, it is better to get this data in a read-only format; this also improves performance. Now, you need to populate the drop-down list box in VSTO, so you use the DataReader to iterate through the records and then populate the drop-down list box in a loop. Note that you do not need to look for the beginning and end of the records because this is handled by the DataReader.

Finally, you need to write code for the clicked event of the button. The code is found below.

private void RCustomerLookup_Load(object sender,
   RibbonUIEventArgs e)
{
   //we load the data from the database.
   GetCustomerNames();
}

private void button1_Click(object sender,
   RibbonControlEventArgs e)
{
   Microsoft.Office.Interop.Excel.Worksheet activeWorksheet;
   //Get the active worksheet
   activeWorksheet =
      DispOrderAddin.Globals.ThisAddIn.GetActiveWorksheet();

   activeWorksheet.Cells.Clear();
   int row = 10;

   //we extract the id of the employee
   String selempid   = Custddlb.SelectedItem.Tag.ToString();
   String selempname = Custddlb.SelectedItem.Label;

   //we display the data from the sales order in the Excel sheet;
   //the query for this is here
   SqlCommand sqlcmdOrders = new SqlCommand();
   sqlcmdOrders.CommandText = "select A.contactid, A.salesorderid,
      B.firstname + ' ' + B.middlename + ' ' +
      B.lastname as CustName, " +
      "A.subTotal, A.taxamt, A.freight, A.totaldue " +
      "from sales.salesorderheader A, person.contact B " +
      "where A.contactid = B.contactid " +
      "and A.contactid = '" + selempid + "'" +
      " order by 3";

   sqlcmdOrders.Connection = sqlconn;

   SqlDataReader OrdersReader = sqlcmdOrders.ExecuteReader();

   //layout of the data on the Excel sheet
   //Column headers
   activeWorksheet.Cells[4, 2] = "Customer ID";
   activeWorksheet.Cells[5, 2] = "Customer Name";

   activeWorksheet.Cells[9, 3] = "S. No";
   activeWorksheet.Cells[9, 4] = "Sales Order ID";
   activeWorksheet.Cells[9, 5] = "Amount";
   activeWorksheet.Cells[9, 6] = "Tax";
   activeWorksheet.Cells[9, 7] = "Freight";
   activeWorksheet.Cells[9, 8] = "Total";

   int currentrow = 1;

   while (OrdersReader.Read())
   {
      activeWorksheet.Cells[4, 3] = selempid;
      activeWorksheet.Cells[5, 3] = selempname;

      activeWorksheet.Cells[row, 3] = currentrow;
      activeWorksheet.Cells[row, 4] = OrdersReader.GetValue(1);
      activeWorksheet.Cells[row, 5] = OrdersReader.GetValue(3);
      activeWorksheet.Cells[row, 6] = OrdersReader.GetValue(4);
      activeWorksheet.Cells[row, 7] = OrdersReader.GetValue(5);
      activeWorksheet.Cells[row, 8] = OrdersReader.GetValue(6);

      row++;
      currentrow++;

   }

   OrdersReader.Close();

   activeWorksheet.Cells.Select();
activeWorksheet.Cells.EntireColumn.AutoFit();
}

Parse this code and see what it does. If you look carefully, you will see that it is similar to what you did earlier with the DataReader except that now you are displaying the data in particular cells of the Excel file instead of populating other controls.

Now, when the user runs the application, he will get the desired output, as shown in Figure 8.

Click here for a larger image.

Figure 8: The final details of the orders for the selected customer.

Page 4 of 5



This article was originally published on July 11, 2008