Adding Drag-and-Drop Functionality using the .NET Framework and Visual Studio 2005 Tools for Office Second Edition

Article
07/09/2014

Summary: Learn how to use the Microsoft .NET Framework and Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System to add drag-and-drop functionality to your custom .NET Framework application. (14 printed pages)

Albert Raiani, Senior Software Developer, twentysix New York

September 2007

Applies to: 2007 Microsoft Office System, Microsoft .NET Framework 3.0, Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (also known as "Visual Studio 2005 Tools for Office Second Edition")

Contents

Overview
Dragging Data from a Text Editor onto an Excel Worksheet
Creating a Windows Forms Application to Supply Data
Triggering Custom Code Inside Excel
Testing the Application
Conclusion
Additional Resources

Overview

When working in Microsoft Office Excel 2007, you may want to trigger custom code when you drag data from an external source onto a worksheet. Office Excel 2007 has intrinsic functionality that recognizes data types and simple formula strings when you drag them onto a worksheet. However, Excel does not have event handlers that respond specifically to a drag-and-drop operation. This article describes how to capture Excel event handlers to simulate executing custom code when you drag data onto a worksheet.

Dragging Data from a Text Editor onto an Excel Worksheet

To help you understand this process better, I first provide simple examples that demonstrate the drag-and-drop functionality. The procedures in this section show you what happens when you drag data from Microsoft WordPad, a text editor, onto an Excel worksheet. You see how Excel transforms string data into rows and columns and how Excel transforms strings into formulas inside the worksheet.

Dragging Simple Strings

The following procedure shows what happens when you drag text from Microsoft WordPad onto an Excel worksheet.

To drag text from WordPad onto an Excel worksheet

Start Excel.
Click a cell in Sheet1 to activate the worksheet.
Start WordPad. To start WordPad, click Start, and then click Run. Type WordPad and press ENTER. Alternatively, click Start, point to All Programs, point to Accessories, and then click WordPad.
Type Hello in WordPad and then select the text.
Press and hold the CTRL key.
Drag the selected text into a cell in your Excel worksheet. The mouse pointer changes to a rectangle behind a plus sign (+) to indicate that the worksheet can accept the data.

Figure 1. After dragging text onto Excel

Dragging Two-Dimensional Data onto an Excel Worksheet

When you drag two-dimensional string data onto an Excel worksheet, it is automatically formatted into columns and rows. This automatic formatting occurs when your string data is delimited properly. String data that is delimited by TAB characters converts into columns; string data that is delimited by carriage return/line feed (CR/LF) characters converts into rows.

The following procedure shows what happens when you drag two-dimensional string data onto an Excel worksheet.

To drag two-dimensional string data onto an Excel worksheet

Switch to Excel.
Click a cell in Sheet1 to activate the worksheet.
Switch to WordPad.
Type 1, 2, and 3, separated by TAB characters.
Press ENTER to insert a CR/LF character.
Type 3, 4, and 5, separated by TAB characters.
Select both lines of text, press and hold the CTRL key, and drag the text onto the Excel worksheet. Notice that Excel draws a border around three columns and two rows to indicate the cells that the text occupies in the worksheet.

Figure 2. After dragging the string data onto Excel

Note

Although the data that you drag from WordPad onto Excel is string data, Excel recognizes that the data is numeric and converts it so that you can perform arithmetic operations on it.

Dragging Formulas onto an Excel Worksheet

The following procedure shows you how to drag a formula from WordPad onto an Excel worksheet.

To drag a formula onto an Excel worksheet

Switch to Excel.
Click a cell in Sheet1 to activate the worksheet.
Switch to WordPad and type the formula =PI().
Select the formula and drag it into a cell in Excel. Excel converts the text to a formula and displays the value of pi: 3.141593.

The following procedure shows you how to drag a formula and associated values from WordPad onto an Excel worksheet.

To drag a formula and associated values onto an Excel worksheet

Switch to WordPad.
Type 100, 200, 300, and separate each number with a CR/LF character.
Press ENTER.
Type the formula =Sum(A1:A3) in the fourth line.
Select all four lines and drag them into cell A1 of the worksheet. Excel formats the fourth line as a formula and displays the sum of the other cells.

The previous examples are simple but give you an idea of the intrinsic functionality available in Excel when you drag values from external sources. By using the Microsoft .NET Framework, you can extend this functionality so that Excel responds to external data and executes your custom code.

For example, you can create a custom application that provides real-time data feeds into an Excel worksheet. Excel can then respond to the feeds by running custom procedures. By using the .NET Framework, you can create your own functions and use them as wrappers around the real-time data functions of Excel.

Note
For more information about real-time data functions, search for the term real-time data in Microsoft Office Excel Help.

Creating a Windows Forms Application to Supply Data

The earlier examples demonstrate how Excel handles text that is dragged from an existing application. Remembering those examples, I next explore how you can extend this functionality by using an Excel add-in created with the .NET Framework. First, you create a Microsoft Windows Forms application that contains the data that you drag onto an Excel worksheet. Then you create custom code that replaces the default behavior of Excel.

Creating the Windows Forms Application

The following procedure shows you how to create a Windows Forms application that consists of a form and a ListBox control.

To create the Windows Forms application

Start Microsoft Visual Studio 2005.
On the File menu, point to New, and then click Project. The New Project dialog box appears.
Click Visual C# under Project Types, and then select Windows Application.
In the Name box, type CsExcelDragDropExample and click OK.

The application opens and you see a blank form.
On the View menu, click Toolbox to display the Toolbox pane.
Click All Windows Forms and drag a ListBox control onto the form.
In the Properties pane, scroll down to SelectionMode and select MultiExtended from the drop-down list box to the right.
In the Properties pane, scroll down to Items and click the ellipsis button (...) to the right.
In the String Collection Editor dialog box, type AAPL, MSFT, and ORCL, each on a separate line, and click OK.

Figure 3. The completed form

Creating the Mouse Event Handler

The following procedure shows you how to create an event handler for mouse events.

To create an event handler for the mouse

Click the ListBox control.
In the Properties pane, click the Events button, which is shaped like a lightning bolt. The events for the ListBox control appear.
Scroll down to the MouseMove event and double-click it. This moves the cursor to the code window and creates the listBox1_MouseMove event handler procedure.

Replace the code in the listBox1_MouseMove event handler with the following code.

private void listBox1_MouseMove(object sender, MouseEventArgs e)
{
   if (e.Button == MouseButtons.Left)
   {
      if (this.listBox1.SelectedItems.Count > 0)
     {
         StringBuilder sbDragData = new StringBuilder();
         int itemCount = this.listBox1.SelectedItems.Count;

         for(int i = 0; i < itemCount; i++)
         {
             String symbol = (String)(this.listBox1.SelectedItems[i]);
             if (i < itemCount - 1)
             {
                sbDragData.AppendFormat("{0}\n", symbol);
             }
             else
             {
                sbDragData.Append(symbol);
             }
         }
         this.listBox1.DoDragDrop(sbDragData.ToString(), 
         DragDropEffects.Copy);
      }
   }
}

Press F5 to run the application and display the form.
Switch to Excel and click a cell to activate Sheet1.
Left-click an item in the list box and drag it onto the Excel worksheet. When you move the cursor over the worksheet, the cursor indicates that the destination is valid.

Next, select multiple items from the list box and drag them to the worksheet. Excel inserts the corresponding cells into Sheet1.

Note
To drag multiple list items, select an item from the list, press and hold the CTRL key, and then select another item. After you select the last item, release the CTRL key, but keep the mouse button pressed. Then drag the items onto your Excel worksheet.

Figure 4. After dragging data onto Excel

After dragging data onto Excel

Triggering Custom Code Inside Excel

After you create the Windows Forms application to supply the data, you next create the Excel add-in that triggers custom code when you drag the data onto the worksheet. Specifically, the code simulates a stock price lookup when you drag stock symbols from the Windows Forms application that you created earlier. The custom code places the opening price and the current price of the stock into adjacent cells.

You create the Excel add-in by using Microsoft Visual Studio 2005 Tools for Office Second Edition. For more information about how to download Visual Studio Tools for Office Second Edition for free see Visual Studio 2005 Tools for Office Second Edition.

Creating the Excel Add-In

The following procedures show you how to create the Excel add-in that triggers a simulated stock price look up when you drag data onto a worksheet.

To create the Excel add-in project

Switch to Visual Studio 2005. You should see the Windows Forms project that you created earlier.
On the File menu, point to New, and then click New Project. The New Project dialog box appears.
Expand Visual C# under Project Types, and then expand Office.
Select 2007 Add-ins or 2003 Add-ins, depending on your version of Excel.
Select Excel Add-In under Templates.
In the Name box, type StockPriceExcelAddInCs, and click OK. Visual Studio 2005 adds two new projects to your solution: the add-in project and a setup project that you use to install the add-in.

Figure 5. The Excel add-in project in Visual Studio 2005

Creating the Stock Quotes Class

After creating the add-in project, you create a class that simulates the retrieval of stock prices.

To create the stock quotes class

In Solution Explorer, right-click the StockPriceExcelAddInCs project node, point to Add and then click Class.
In the Add New Item dialog box, select Class, name the class Quote.cs, and click Add.

In the code window, replace the existing code with the following code.

Note
Do not overwrite the using statements.

namespace StockPriceExcelAddInCs
{
   class Quote
   {
      private static Random _random = null;
      private string _symbol = null;
      private double _lastPrice = 0;
      private double _openPrice = 0;
      private int _min = 0;
      private int _max = 0;

      public Quote(string symbol)
      {
         this._symbol = symbol;
         this._openPrice = this.GetRandomPrice();
         this._lastPrice = this.GetRandomPrice();
      }

      public string Symbol
      {
         get
         {
            return _symbol;
         }
      }

      public double OpenPrice
      {
         get
         {
            return _openPrice;
         }
      }

      public double LastPrice
      {
         get
         {
            return _lastPrice;
         }
      }

      private double GetRandomPrice()
      {
          switch (this._symbol)
          {
              case("MSFT"):
                   {
                      return GetRandomDouble(25, 31, 2);
                      break;
                   }
              case ("ORCL"):
                   {
                      return GetRandomDouble(18, 22, 2);
                      break;
                   }
              case ("AAPL"):
                   {
                      return GetRandomDouble(125, 135, 2);
                      break;
                   }
                default:
                   {
                      return GetRandomDouble(10, 50, 2);
                      break;
                   }
            }

      }

      private static double GetRandomDouble(int min, int max, int maxDecimalPlaces)
      {
          if (_random == null)
             _random = new Random();

            string zeros = new String(Convert.ToChar("0"), maxDecimalPlaces);
            int factor = Convert.ToInt32(string.Format("1{0}", zeros));
            int minValue = min * factor;
            int maxValue = max * factor;
            int randomValue = _random.Next(minValue, maxValue);
            double randomDouble = Convert.ToDouble(randomValue) / factor;
            return randomDouble;
      }

   }
}

In Solution Explorer, under the StockPriceExcelAddInCs node, with the Excel tree node expanded, double-click ThisAddIn.cs. You see the code window for the ThisAddIn object.
Create a class level variable to hold the unique key that helps identify the data that you drag onto the Excel worksheet. To create the variable, insert the following statement after the opening brace ({) of the public partial class ThisAddIn statement.
```
private string _myUniqueKey = "{fe1031bf-094a-4ee1-8483-6c3b27398a47}";
```

Next, insert the following two helper methods after the statement you just added.

private Quote[] CreateQuotesFromString(string dragData) 
{
   string droppedString = dragData.Remove(0, _myUniqueKey.Length);
   string[] stockSymbols = droppedString.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
   Quote[] quotes = new Quote[stockSymbols.Length];
   for(int i = 0; i < stockSymbols.Length; i++)
   {
      Quote quote = new Quote(stockSymbols[i]);
      quotes[i] = quote;
   }
   return quotes;
}

private void DoDropAction(string dragData, Excel.Range target)
{
   Quote[] quotes = CreateQuotesFromString(dragData);
   for(int i = 0; i < quotes.Length; i++)
   {
      double openPrice = quotes[i].OpenPrice;
      double lastPrice = quotes[i].LastPrice;
      string symbol = quotes[i].Symbol;

      target.get_Offset(i, 0).Value2 = symbol;

      Excel.Range openPriceCell = target.get_Offset(i, 1);
      openPriceCell.Value2 = openPrice;
      openPriceCell.NumberFormat = "$,0.00";

      Excel.Range lastPriceCell = target.get_Offset(i, 2);
      lastPriceCell.Value2 = quotes[i].LastPrice;
      lastPriceCell.NumberFormat = "$,0.00";

      switch(lastPrice.CompareTo(openPrice))
      {
         case 0:
         {
            lastPriceCell.Font.ColorIndex = 
            Excel.XlColorIndex.xlColorIndexAutomatic;
            break;
         }
         case 1:
         {
            lastPriceCell.Font.Color = -11489280;
            break;
         }
         case -1:
         {
            lastPriceCell.Font.Color = -16776961;
            break;
         }
      }
   }
}

Create the Application_SheetChange event handler. This event is triggered when the contents of a cell change. You use this event to intercept and examine the value of the data dragged onto the worksheet. If the data starts with the unique key that the code defined, then the code invokes the drop routine. In the ThisAddIn_Startup method, add the following line.
```
this.Application.SheetChange += new Excel.AppEvents_SheetChangeEventHandler(this.Application_SheetChange);
```

Add the event handler after the closing brace (}) of the ThisAddIn_Startup procedure.

private void Application_SheetChange(object sender, Excel.Range target)
{
   if (target.Cells.Count == 1)
   {
      string dragData = Convert.ToString(target.Value2);
      if (dragData.StartsWith(_myUniqueKey))
      {
         target.ClearContents();
         DoDropAction(dragData, target);
      }
   }
}

Tip
If you use the Application_SheetChange event handler instead of the Workbook_SheetChange or Sheet_Change event handlers, the Application_SheetChange event is triggered whenever the content of any cell changes and you do not have to track the active sheet.

Modifying Your Windows Forms Application Project

Next, you need to modify the Windows Forms application project that you created earlier so that it invokes the custom behavior of the add-in. For the add-in to work properly, the Windows Forms application must send the data in an expected format and prefix the data with a unique key. The following procedure shows you how to modify your Windows Forms application project so that it invokes the custom behavior of the add-in.

To modify your Windows Forms application project

In Solution Explorer expand the project CsExcelDragDropExample, select Form1.cs and press F7 to open the code window.
In the code window for Form1.cs, create the class level variable that holds the unique key. After the opening brace ({) of the public partial class Form1 : Form statement, insert the following code.
```
private string _myUniqueKey = "{fe1031bf-094a-4ee1-8483-6c3b27398a47}";
```

Next, change the following line in listBox1_MouseMove:

StringBuilder sbDragData = new StringBuilder();

to:

StringBuilder sbDragData = new StringBuilder(_myUniqueKey);

Change the line:

sbDragData.AppendFormat("{0}\n", symbol);

to:

sbDragData.AppendFormat("{0},", symbol);

Configuring the Solution to Start Both Projects

Before you run the application, you must configure the solution to start both projects.

To configure the solution to start both projects

Right-click the Solution 'StockPriceExcelAddInCs' node in Solution Explorer and click Set Startup Projects.
In the Solution 'StockPriceExcelAddInCs' Property Pages dialog box, select the Multiple startup projects option and set the action on both projects to Start.
Click OK.

Testing the Application

Next, test the application.

To test the application

Press F5 to start the application. The Windows Forms project starts and Excel launches automatically.
Drag all of the symbols to the worksheet using the CTRL key as described in step 8 of the procedure Creating the Mouse Event Handler.

When you drag the stock symbols from the list box to the worksheet, Excel populates the cells with the stock symbols and inserts random values to the right that represent the opening price and current price of the stock.

Figure 6. Dragging the data triggers the custom event
Close the Visual Studio 2005 project and the Excel workbook.

Conclusion

In this article, you built a simple Windows Forms application and a Visual Studio 2005 Tools for Office Second Edition add-in for Office Excel 2007 to perform a drag operation from your custom application to Office Excel 2007. Although you cannot directly interrupt a drag-and-drop sequence, you can use the .NET Framework APIs and the Office Excel 2007 object model to simulate drag-and-drop behavior. I encourage you to experiment with the code examples in this article and adapt them to your own applications.

Additional Resources

For more information, see the following resources: