Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 1 of 2)

Summary: Develop and implement a business intelligence application quickly with Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System, Virtual Earth Map Control 6.0, and Microsoft Office Excel 2007. (28 printed pages)

Imtiyaz Mubarak, Advaiya, Inc.

Jeff House, Advaiya, Inc.

May 2008

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Virtual Earth, Microsoft Virtual Earth Map Control 6.0

Contents

  • Imtiyaz Mubarak, Advaiya, Inchttp://www.advaiya.com/. Jeff House, Advaiya, Inchttp://www.advaiya.com/.May 2008Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Virtual Earth, Microsoft Virtual Earth Map Control 6.0ContentsIntroductionScenario OverviewDisplaying the Virtual Earth Map ControlCreating the Excel 2007 Add-in ProjectCreating the User Control for the Task PaneCustomizing the RibbonDisplaying the Map Control in the Task PaneAdding Data to an Excel WorksheetDisplaying Pushpins on the MapRunning the ApplicationConclusionAbout the AuthorsAdditional ResourcesDownload sample files: 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studiohttp://code.msdn.microsoft.com/VSTOVirtualEarthXL.

  • Scenario Overview

  • Displaying the Virtual Earth Map Control

  • Creating the Excel 2007 Add-in Project

  • Creating the User Control for the Task Pane

  • Customizing the Ribbon

  • Displaying the Map Control in the Task Pane

  • Adding Data to an Excel Worksheet

  • Displaying Pushpins on the Map

  • Running the Application

  • Conclusion

  • About the Authors

  • Additional Resources

Download sample files: 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio.

Scenario Overview

In this solution, you target the custom task pane to provide users with a rich, context-aware user interface (UI). Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 2 of 2) discusses how to further customize the map, use Automation in Microsoft Office Excel 2007 for formatting, and charting to provide better data visualization.

The Virtual Earth platform delivers an immersive experience in which users can easily discover, search, and visualize business location data. By integrating location intelligence into your application, you can enhance user experience by providing convenient, locally relevant information in a visual, easy-to-understand interface. The Virtual Earth platform is often used for building Web-enabled mapping solutions for real estate searches, travel portals, and fleet/asset tracking, but you can use the comprehensive mapping and geocoding capabilities of Virtual Earth to display any location data in a Web interface. For more information about Virtual Earth extensibility, see the Virtual Earth Interactive SDK.

The client applications in the 2007 Office system, such as Microsoft Office Outlook 2007, Microsoft Office Word 2007, and Office Excel, provide a platform for the development of Office Business Applications and support a variety of customizations to add the exact features that users need. In these applications, the custom task pane can host controls, such as lists, buttons, and text boxes, to present information from other sources for reference or integration within documents. In addition, you can extend the new Office Fluent Ribbon interface to expose line-of-business logic and data. The custom task pane provides customizable functionality for business users.

Business Scenario

Contoso Ltd is a fictitious sales and marketing company, which sells household products in different territories through several sales stores, managed by sales person. The sales account data is centrally maintained by the company’s corporate office. Management needs an advanced reporting tool that provides facility to analyze sales data on geographic maps, so that information on various sales criteria such as Sales by Territory, Salespeople and Sales Stores can be easily visualized.

This solution demonstrates how you can use Visual Studio 2005 Tools for Office System Second Edition and Virtual Earth Map Control 6.0 together to create customized applications with rich, informative user experiences.

The first article in this two-part series demonstrates how to:

  • Create a Virtual Earth control.

  • Create a Microsoft Office Excel 2007 add-in project using Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System

  • Get the data from a Microsoft Office Access database to create Virtual Earth Shapes (Pushpins) on the Virtual Earth map.

Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 2 of 2) shows how to customize the shape and the InfoBox (or pop-up or bubble). In addition, we use automation in Office Excel 2007 for formatting and charting to provide better data visualization.

Displaying the Virtual Earth Map Control

Because the Virtual Earth platform is built for Web browser-enabled applications, the first step is to create an HTML file to display the Virtual Earth map control. You can use any text editor to create an HTML file like Notepad or Microsoft Visual Studio.

To display the default Virtual Earth Map control

  1. Open Notepad and copy the following HTML code.

    <html>
       <head>
          <title></title>
          <meta http-equiv="Content-Type" content="text/html; 
            charset=utf-8">
          <script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6">
          </script>
    
    <script>
    
              var map = null;
    
              function GetMap()
              {
                 map = new VEMap('myMap');
                 map.LoadMap();
                 map.Resize(GetWindowWidth(),GetWindowHeight());
                 map.HideDashboard();
              }            
    
              function setDivSize()
              {
                 map.Resize(GetWindowWidth(),GetWindowHeight());
    
              }
    
    </script>
       </head>
    
      <body onload="GetMap();" scroll="no" onresize="setDivSize()"
        style="height:100%; width:100%" >
            <div id='myMap' style="position: relative; width:100%; height:100%"></div>
      </body>   
    </html>
    
  2. Navigate to C:\Projects\VirtualEarthOBA land save it as MapControl.html.

If you open the MapControl.html file in the Internet browser, it displays a map similar to Figure 1.

Figure 1. Display a basic map in the Web browser

Display a basic map in the Web browser

The map is not displayed yet in Excel, but this demonstrates how to display a map in a Web page. This file is added to the project later, after which, it will display in a custom task pane.

Creating the Excel 2007 Add-in Project

Visual Studio Tools for Office contains project templates for document-level customizations and application-level add-ins.

Application-level add-ins consist of an assembly that is associated with a Microsoft Office application. Typically, the add-in runs when the associated application is started, although users can also load add-ins after the application is already running.

For more information about application-level Add-ins, see Getting Started Programming Application-Level Add-ins. For more information about how to create add-in projects, see How to: Create Visual Studio Tools for Office Projects.

To start, create an Excel 2007 add-in project in Visual Studio 2005 Tools for Office SE and name it ExcelMapAddIn as shown in Figure 2.

Figure 2. The Excel 2007 add-in project

Excel 2007 add-in project

To add HTML file to the solution

  1. In Solution Explorer, select the ExcelMapAddIn project, and then click Show All Files.

    Figure 3. Show All Files of the solution

    Show All Files of the solution

  2. Expand the bin folder, right-click the Debug folder, and then select Include In Project.

  3. Right-click the Debug folder, select Add->New Folder, and then name it HTML.

  4. Right-click the HTML folder and then select Add->Existing Item. This shows the file dialog box.

  5. Select the MapControl.html file and then click Add. This adds the html file to the solution.

Creating the User Control for the Task Pane

NoteNote

In this solution, we use the Constant.cs file for the constant and variables declaration.

Custom task panes are user interface panels that are typically docked to one side of a window in a Microsoft Office application. Custom task panes give you a way to create your own task pane and provide users with a familiar interface to access your solution's features. For more information about custom task panes see Custom Task Panes Overview and Creating Custom Task Panes in the 2007 Office System.

To add a constant class

  1. On the Project menu, click Add New Item.

  2. In the Add New Item dialog box, select Class.

  3. Change the name of the new class file to Constant.cs, and then click Add.

  4. Replace the Constants.cs code with the following code.

    using System;
    using System.Collections.Generic;
    using System.Text;
    
    namespace ExcelMapAddIn
    {
        class Constants
        {
            private Constants() { }
    
            // This is for the worksheet column Index.
            internal sealed class ColumnIndex
            {
                private ColumnIndex() { }
                internal const int FIRST_COLUMN = 1;
                internal const int SRNO = 2;
                internal const int NAME = 3;
                internal const int SALESLAST = 4;
                internal const int SALESYTD = 5;
            }
    
            // These are data columns of the Sales Person table.
            internal sealed class SalesPersonDataColumn
            {
                private SalesPersonDataColumn() { }
    
                internal const string NAME = "PersonName";
                internal const string SALESYTD = "SalesYTD";
                internal const string SALESLAST = "SalesLastYear";
                internal const string LATITUDE = "Latitude";
                internal const string LONGITUDE = "Longitude";
                internal const string EMAIL = "EmailAddress";
                internal const string JOB_TITLE = "JobTitle";
                internal const string ADDRESS = "Address";
                internal const string CITY = "City";
            }
    
    
            // Now come data queries.
            internal sealed class SalesDataQuery
            {
                private SalesDataQuery() { }
                internal const string SALES_PERSON = "SELECT SalesPerson.* 
                FROM SalesPerson ORDER BY SalesPerson.PersonName;";
            }
    
    
            // The Ribbon button ID is defined in ribbon.xml.
            internal sealed class RibbonButtonID
            {
                private RibbonButtonID() { }
                internal const string SALES_TERRITORY = "SalesTerritory";
                internal const string SALES_PERSON = "SalesPerson";
                internal const string SALES_STORE = "SalesStore";
                internal const string SHOW_MAP = "ShowMap";
            }
    
            // Set general settings.
            internal sealed class AddInProperty
            {
                private AddInProperty() { }
    
                internal const string ADDIN_NAME = "Excel VE";
                internal const string SALES_SHEET_NAME = "SalesAnalysis";
                internal const string MAPUSERCONTROL_NAME = "Map Control";
                internal const string DATABASE_PATH = @"Data\Sales.accdb";
                internal const string CONNECTION_STRING = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source={0};";
                internal const string MAP_CONTROL_HTML_PATH = @"HTML\MapControl.html";
            }
        }
    }
    
NoteNote

Custom task panes are user interface panels that are typically docked to one side of a window in a Microsoft Office application. Custom task panes give you a way to create your own task pane and provide users with a familiar interface to access your solution's features. For more information about custom task panes see Custom Task Panes Overview and Creating Custom Task Panes in the 2007 Office System.

NoteNote

The add-in uses an Access database for sales data and geocode information (latitude and longitude). You can find the sample database and image files for the picture box in the 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio.

Next, create a custom task pane using a user control. The user control hosts the map and associated user interface.

To add a user control

  1. On the Project menu, click Add New Item.

  2. In the Add New Item dialog box, select User Control.

  3. Change the name of the new user control to MapUserControl.cs, and then click Add.

To add controls to the form designer

  1. If the designer is not already displayed, in Solution Explorer, right-click MapUserControl.cs, and then select View Designer.

  2. On the View menu, click Toolbox.

  3. On the Common Controls tab of the toolbox, add the controls in Table 1 for map control, and then set their properties.

    Table 1. Windows Forms controls for the custom control

    Control Type

    Name Property

    Text Property

    Anchor Property

    WebBrowser

    webBrowserMap

    None

    Top, Bottom, Left, Right

    TrackBar

    zoomTrackBar

    None

    Bottom, Left

    Radio Button

    rdRoad

    Road

    Bottom, Left

    Radio Button

    rdAerial

    Aerial

    Bottom, Left

    Radio Button

    rdHybrid

    Hybrid

    Bottom, Left

    Label

    lblZoom

    Zoom Map

    Bottom, Left

    Label

    lblMapStyle

    Map Style

    Bottom, Left

    Picture Box

    pbImage

    None

    Top, Left

  4. In the rdRoad option button control, set the Checked property to True.

  5. In the webBrowserMap control, set the Dock property to None.

  6. For the TrackBar object, set the following properties.

    Table 2. Property values for TrackBar controls

    Property

    Value

    Maximum

    19

    Minimum

    1

    Value

    3

  7. Lay out the controls as shown in figure 4.

    Figure 4. User control with Windows control

    User control with Windows control

  8. The WebBrowser control lets you view Web content inside your application. It permits two-way communication between your Windows Forms client applications code and your Web page scripting code through the ObjectForScripting and Document properties. In this solution, the WebBrowser control hosts the MapControl.html file we developed previously.

To add code to the user control

  1. Open the code windows of MapUserControl.cs file.

  2. If the code window is not already displayed in Solution Explorer, right-click MapUserControl.cs, and then click View Code.

  3. Replace all content with the following code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;
    
    namespace ExcelMapAddIn
    {
        [ComVisible(true)]
        public partial class MapUserControl : UserControl
        {
    
            DataTable _dataTable = null;
            string _assemblyPath = "";
    
            public MapUserControl()
            {
                InitializeComponent();
            }
    
            public void LoadMapControl(string assemblyPath)
            {
                _assemblyPath = assemblyPath;
                // Set the ObjectForScripting property.
                webBrowserMap.ObjectForScripting = this;
                // Load the map control HTML file.
                webBrowserMap.Navigate(_assemblyPath + Constants.AddInProperty.MAP_CONTROL_HTML_PATH);
            }
        }
    }
    
    NoteNote

    In the previous code example, we set the ComVisible attribute to true to access the scripting object and modified the default constructor of the MapUserControl class. We use the Initiate method an instance of the MapUserControl object from another class and pass the string value of the assembly path to navigate the map control html file in the WebBrowser control.

  4. In Solution Explorer, right-click MapUserControl.cs, and then select View Designer.

  5. Add events for the TrackBar object and option buttons controls. Double-click the Trackbar and option buttons each control to generate the event methods for each control. Then, replace the automatically-generated event methods with the following code. Note how we use the Document property to access the script code in the HTML file.

    // Scroll method for the Zoom trackbar control.
    private void zoomTrackBar_Scroll(object sender, EventArgs e)
    {
        webBrowserMap.Document.InvokeScript("SetMapZoomLevel", new object[] { zoomTrackBar.Value });
    }
    
    // Adding the CheckedChanged Event method.
    private void rdRoad_CheckedChanged(object sender, EventArgs e)
    {
        webBrowserMap.Document.InvokeScript("SetMapStyle", new object[] { "r" });
    }
    
    // Adding the CheckedChanged Event method.
    private void rdAerial_CheckedChanged(object sender, EventArgs e)
    {
        webBrowserMap.Document.InvokeScript("SetMapStyle", new object[] { "a" });
    }
    
    // Adding the CheckedChanged Event method.
    private void rdHybrid_CheckedChanged(object sender, EventArgs e)
    {
        webBrowserMap.Document.InvokeScript("SetMapStyle", new object[] { "h" });
    }
    
  6. Open the MapControl.html file, and then add the following functions to the <script> element. You can call these functions through the user control. These methods handle zoom level and map style functionalities of Virtual Earth.

    function SetMapZoomLevel(zoomLevel)
    {
        map.SetZoomLevel(zoomLevel);
    }
    
    function SetMapStyle(mapStyle)
    {
        map.SetMapStyle(mapStyle);
    }
    

Customizing the Ribbon

The Ribbon is part of the new Microsoft Office Fluent user interface (UI) for Office Access 2007, Office Excel 2007, Office Outlook 2007, and Office Word 2007. The Office Fluent UI is a departure from the system of menus, toolbars, and dialog boxes that were part of earlier releases of Microsoft Office. For more information about customizing the Office Fluent UI, see Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3).

In this solution, users can use the Ribbon to create buttons for the following reports:

  • Sales Territory

  • Sales Store

  • Sales by Sales Person

  • Show Map

NoteNote

Sales Territory and Sales Store buttons are not used in the first article of this series.

To customize the Ribbon

  1. On the Project menu, click Add New Item as shown in Figure 5.

    Figure 5. Insert a Ribbon file to the add-in

    Insert a Ribbon file to the add-in

  2. In the Add New Item dialog box, select Ribbon support.

  3. Change the name of the new Ribbon to SalesRibbon.cs, and then click Add.

    Figure 6. Add Ribbon support file to the project

    Add Ribbon support file to the project

  4. The SalesRibbon.cs file opens in the designer. An XML file named SalesRibbon.xml is added to your project.

  5. In the SalesRibbon.cs file, locate the namespace ExcelMapAddIn statement, and then replace the USING statements with the following:

    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.IO;
    using System.Text;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;
    using System.Data;
    using System.Data.OleDb;
    using Microsoft.Office.Tools;
    using Office = Microsoft.Office.Core;
    using Excel = Microsoft.Office.Interop.Excel;
    
  6. Add the following fields and the constructor to the SalesRibbon class.

    [ComVisible(true)]
    public class SalesRibbon : Office.IRibbonExtensibility
    {
    
        #region Variable Declaration
    
        private Office.IRibbonUI ribbon;
    
        object missing = System.Reflection.Missing.Value;
    
        Excel.Application _excelApplication = null;
    
        MapUserControl mapUserControl = null;
    
        OleDbConnection _dbConnection = null;
    
        #endregion
    
        #region Constructor
        public SalesRibbon()
        {
    
            // Initiate the map user control.
            mapUserControl = new MapUserControl(GetAssemblyPath());
            // Load the map html file in the map user control (Web Browser Control).
            mapUserControl.LoadMapControl();
    
            // Initiate the Excel Application object.
             _excelApplication = (Excel.Application)Globals.ThisAddIn.Application;
        }
        #endregion
    
       // Gets the assembly's physical path.
       private string GetAssemblyPath()
       {
          string retVal = "";
          try
          {
            retVal = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
          }
    
         catch (Exception ex)
         {
            DisplayException(ex.Message);
         }
    
         return retVal;
       }
    
    
        // To display a message box containing an error. 
        private void DisplayException(string exceptionMessage)
        {
    
           string title = Constants.AddInProperty.ADDIN_NAME;
    
           try
           {
              MessageBox.Show(exceptionMessage, title);
           }
           catch (Exception err)
           {
              Debug.WriteLine(err.Message, title);
              throw err;
           }
        }
    
  7. Locate the code comments that start with TODO:, and uncomment the definition of the partial ThisAddIn class. This code enables the Microsoft Office application to discover and load your custom Ribbon UI. For more information, see Ribbon Extensibility Overview.

  8. After you uncomment the code, it should resemble the following example.

    public partial class ThisAddIn
    {
        private SalesRibbon ribbon;
    
        protected override object RequestService(Guid serviceGuid)
        {
            if (serviceGuid == typeof(Office.IRibbonExtensibility).GUID)
            {
                if (ribbon == null)
                    ribbon = new SalesRibbon();
                return ribbon;
            }
    
            return base.RequestService(serviceGuid);
        }
    }
    

To add buttons to the Add-Ins tab

  1. In Solution Explorer, right-click SalesRibbon.xml, and then click Open.

  2. Replace the contents of the tab element with the following XML. This XML changes the label of the default control group. It also adds four new buttons with the following labels: Sales Territory, Sales Store, Sales by Sales Person, and Show Map.

          <tab idMso="TabAddIns">
            <group id="SalesGroup" label="Sales Report">
              <button id="SalesTerritory" size="large" label="Sales Territory" 
                 screentip="Get the sales by territory report" onAction="SalesAction" 
                 imageMso="ShowTimeZones" />
              <button id="SalesStore" size="large" label="Sales Store" screentip=
                 "Get the sales by store report" onAction="SalesAction" imageMso="OmsNewTextMessage" />
              <button id="SalesPerson" size="large" label="Sales by Sales Person" 
                 screentip="Get the sales by person report" onAction="SalesAction" 
                 imageMso="FileCreateDocumentWorkspace" />
              <button id="ShowMap" size="large" label="Show Map" screentip=
                 "Display Virtual Earth Map" onAction="SalesAction" imageMso="ModuleInsert" />
            </group>
          </tab>
    

This XML markup results in a modified Fluent UI, as Figure 7 shows.

Figure 7. Sample of Office Fluent UI customization in Excel

Sample of Office Fluent UI customization in Excel

Displaying the Map Control in the Task Pane

To automate the document from the buttons, you must add onAction callback methods for the buttons to perform actions when the user clicks them.

To add callback methods for the buttons

  1. In Solution Explorer, right-click SalesRibbon.cs, and then click Open.

  2. Delete the OnToggleButton1 method. This method is automatically generated by Visual Studio Tools for Office. It is unnecessary for this solution.

  3. Add the following method to the SalesRibbon class. This is a callback method used for buttons that call their respective methods, identified by the control id. For now, we are only supporting a callback for the Show Map button. Others are added later in this example and in Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 2 of 2).

    public void SalesAction(Office.IRibbonControl control)
    {
        switch (control.Id)
        {
            case Constants.RibbonButtonID.SHOW_MAP:
                TogglePane(true);
                break;
    
        }
    }
    
  4. Create a region for CustomTaskPane methods in SalesRibbon.cs. The included region has methods to show or hide the custom task pane.

            #region CustomTaskPane Methods
            // <summary>
            // To show or hide the task pane.
            // </summary>
            // <param name="Visible">bool value to show or hide task pane</param>
            private void TogglePane(bool Visible)
            {
                CustomTaskPane pane = GetTaskPane(Constants.AddInProperty.MAPUSERCONTROL_NAME);
                if (Visible)
                {
                    if (pane == null)
                    {
                        pane = Globals.ThisAddIn.CustomTaskPanes.Add(mapUserControl, Constants.AddInProperty.MAPUSERCONTROL_NAME);
                    }
    
                    pane.Visible = true;
                }
                else
                {
                    if (pane != null)
                    {
                        pane.Visible = false;
                    }
                }
            }
    
            // <summary>
            // Returns the custom task pane for the specific title string.
            // </summary>
            // <param name="Title">The name of a custom task pane</param>
            // <returns></returns>
            private CustomTaskPane GetTaskPane(string Title)
            {
                try
                {
                    for (int i = 0; i < Globals.ThisAddIn.CustomTaskPanes.Count; i++)
                    {
                        if (Globals.ThisAddIn.CustomTaskPanes[i].Title == Title)
                            return Globals.ThisAddIn.CustomTaskPanes[i];
                    }
                }
    
                catch (Exception ex)
                {
                    DisplayException(ex.Message);
                }
                return null;
            }
    
            #endregion
    
  5. Build the solution, and then press F5 to run this solution.

  6. Click Show Map on the customized Ribbon. The task pane will be displayed in the document with the Virtual Earth Map. You can check the zoom and map style functionalities of the Virtual Earth Map through the Trackbar control and RadioButton control as shown in Figure 8.

    NoteNote

    When you build the solution, you might get validation (XHTML 1.0 Transitional) errors for the MapControl.html file with Build Succeeded status. You can safely ignore them.

    When loaded for the first time, the Virtual Earth Map may take a moment to display in the task pane.

    Figure 8. Virtual Earth map control displayed in the task pane

    Virtual Earth map control

Adding Data to an Excel Worksheet

This section describes how to add data to an Excel worksheet using automation and populate the map with Pushpins. The data is taken from an Access database and added to the new Excel worksheet.

NoteNote

This solution uses a sample Access database for the sales data. You have to add this database in the solution. To download a copy of the sample database, see 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio.

To add the sample database to the solution

  1. In Solution Explorer, select the ExcelMapAddIn project, and and then click Show All Files.

  2. Expand the bin folder, right-click the Debug folder, and then select Include In Project.

  3. Right-click the Debug folder, select Add->New Folder, and then name it Data.

  4. Right-click the Data folder, and then select Add->Existing Item. This shows the file dialog box.

  5. Select the Sales.accdb file. This adds the database file to the solution.

To add an Excel worksheet

  1. Use the Add method of the Sheets collection. The Add(Object, Object, Object, Object) method enables you to add a new worksheet to the collection of worksheets in the workbook.

  2. Add following code to the SalesRibbon class:

        //Adds a new worksheet for the report.
        private Excel.Worksheet AddExcelWorkSheet()
        {
            Excel.Worksheet newSheet = null;
            try
            {
                // Determines whether the worksheet exists or not.
                foreach (Excel.Worksheet ws in _excelApplication.ActiveWorkbook.Worksheets)
                {
                    if (ws.Name == Constants.AddInProperty.SALES_SHEET_NAME)
                    {
                        ws.Delete();
                        break;
                    }
                }
                // Adds a new sheet and sets the name.
                newSheet = (Excel.Worksheet)_excelApplication.Worksheets.Add(missing, missing, missing, missing);
                newSheet.Name = Constants.AddInProperty.SALES_SHEET_NAME;
                // Hides the sheet. 
                newSheet.Application.ActiveWindow.DisplayGridlines = false;
                // Hide the gridlines of the worksheet.
                newSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
            }
            catch (Exception ex)
            {
                DisplayException(ex.Message);
            }
            return newSheet;
        }
    

To Get the DataTable Object for the Report

The next step is to create the data helper region in SalesRibbon.cs. This region contains methods to get the data table from an Access database, returning a DataTable object based on the query string. When the GetDataTable method is called, it opens an OLE DB connection through the CreateDatabaseConnection method. The CreateDatabaseConnection method creates a new OleDbConnection for an Access database. By default, the sample data is stored at the assembly location for this solution.

#region Data Helper

// Returns the Dataset for the specific query string.
private DataTable GetDataTable(string queryString)
{
    DataTable dataTable = null;

    try
    {
        if (_dbConnection == null)
            CreateDatabaseConnection();

        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = _dbConnection;
        cmd.CommandText = queryString;
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        dataTable = ds.Tables[0];
    }

    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
    return dataTable;
}

// Creates a OleDbConnection for the specific Access Database.
private void CreateDatabaseConnection()
{
    try
    {
        string dbFilePath = _assemblyPath + Constants.AddInProperty.DATABASE_PATH; 
        string connectionString = String.Format(Constants.AddInProperty.CONNECTION_STRING, dbFilePath);
        _dbConnection = new OleDbConnection(connectionString);
        _dbConnection.Open();
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

#endregion

Displaying Pushpins on the Map

To create a simple pushpin on the Virtual Earth map, pass the VEShapeType.Pushpin type and a VELatLong instance to the VEShape constructor in JavaScript. Then, add it to the VEMap object with the AddShape call.

The syntax for creating a Pushpin object is as follows:

var shape1 = new VEShape(VEShapeType.Pushpin,new VELatLong
  (37.776, -122.426));
map.AddShape(shape1);

To display a pushpin on the map

  1. Add following code to MapControl.html. This method adds the shape to the map.

    var locs = new Array; // An array for store locations
    var loc;
    
    //To add the Pushpin object on the map.
    function AddPushPinOnMap(lat, lng, description)
    {
    
            loc = new VELatLong(parseFloat(lat),parseFloat(lng));
    
            // Store the location in an array.
            locs.push(loc);            
    
     var shape = new VEShape(VEShapeType.Pushpin, loc );
    
            // Set custom icon for the pushpin.
            shape.SetTitle('Sales Person');
            shape.SetDescription(description);
    
    // Add shape to the map.
            map.AddShape(shape);                      
    }
    
    // To set map view.
    function SetBestMapView()
    {
       map.SetMapView(locs);
    }
    
    function ClearMap()
    {
       // Remove all pushpins on the map.
       map.Clear();
       map.DeleteAllShapes();
    
       loc = null;
       locs = new Array; 
    }
    
  2. Add the following code to MapControl.cs. This method passes the Latitude property, Longitude property, and Description property to the AddPushPin method of the MapControl.html.

    // To invoke the JavaScript method for adding the pushpin on the Virtual Earth Map.
    public void AddPushPinOnMap(string latitude, string longitude, string description)
    {
        webBrowserMap.Document.InvokeScript("AddPushPinOnMap", new object[] { latitude, longitude, description });
    }
    
  3. Add the following code to MapControl.cs. Using this method, you can call the SetMapView method of the map. This method is useful when you want a map to cover a known collection of points, but you are not sure what the center point is or what zoom level is required to fit the entire region in your current map control.

    // To invoke JavaScript method for setting the best map view.
    public void SetMapView()
    {
        webBrowserMap.Document.InvokeScript("SetBestMapView");
    }
    
  4. In MapControl.cs, add the ClearMap method. This method invokes the JavaScript method to clear the map and delete all Pushpin instances.

    // To invoke JavaScript method for clearing the map data.
    public void ClearMap()
    {
        // Invoke the ClearMap method.
        webBrowserMap.Document.InvokeScript("ClearMap");
    }
    
  5. Add the following method in SalesRibbon.cs. This method adds the data to Excel worksheet and passes the Latitude property, Longitude property and other details to the map control. The purpose of this method is to add data to the sheet and pass the required parameter to add a basic shape to the map.

    // Add the report to the worksheet.
    public void CreateReport()
    {
        try
        {
            // Clear all data from the map.
            mapUserControl.ClearMap();
    
            // Add a new worksheet.
            Excel.Worksheet currentSheet = AddExcelWorkSheet();
    
            // Get the datatable.
            DataTable dataTable = GetDataTable(Constants.SalesDataQuery.SALES_PERSON);
    
            int rowIndex = 7;
            int index = 0;
    
            foreach (DataRow dr in dataTable.Rows)
            {
                rowIndex++;
    
                // Get the values for the columns.
                string salesPerson = dr[Constants.SalesPersonDataColumn.NAME].ToString();
                string salesLastYear = "$" + dr[Constants.SalesPersonDataColumn.SALESLAST].ToString();
                string salesYTD = "$" + dr[Constants.SalesPersonDataColumn.SALESYTD].ToString();
                string latitude = dr[Constants.SalesPersonDataColumn.LATITUDE].ToString();
                string longitude = dr[Constants.SalesPersonDataColumn.LONGITUDE].ToString();
    
                // Add data to the sheet, cell by cell.
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = salesPerson;
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST] = salesLastYear;
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD] = salesYTD;
    
                // Add Pushpin on the map.
                mapUserControl.AddPushPinOnMap(latitude, longitude, salesPerson);
            }
    
            // Make the sheet visible.
            currentSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
            currentSheet.Activate();
    
            // Sets the map view to include all of the points.
            mapUserControl.SetMapView();
    
        }
        catch (Exception ex)
        {
            DisplayException(ex.Message);
    
        }
    }
    
  6. Replace the SalesAction method with the following code in the SalesRibbon.cs.

    public void SalesAction(Office.IRibbonControl control)
    {
        switch (control.Id)
        {
            case Constants.RibbonButtonID.SHOW_MAP:
                TogglePane(true);
                break;
    
            case Constants.RibbonButtonID.SALES_PERSON:
                CreateReport();
                break;
        }
    }
    

    Figure 9. Display pushpins on the map

    Display pushpins on the map

Running the Application

You can now run the application to display the Virtual Earth map control in the task pane with pushpins.

To run the application

  1. Press F5 to build and run the Web application.

  2. Click Show Map.

  3. Click Sales by Sales Person.

    The application displays data on the sheet and the task pane with the map.

Conclusion

Now you can integrate Virtual Earth map control in custom task panes in the 2007 Office system by using Visual Studio Tools for Office 2005 SE. This article demonstrates only a small portion of the advanced functionality in the Virtual Earth map control. Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 2 of 2) shows how to customize the shape and InfoBox (or pop-up). In addition, we use automation in Office Excel 2007 for formatting and charting to provide better data visualization.

About the Authors

Imtiyaz Mubarak is a senior consultant with Advaiya, Inc. He has expertise with Microsoft technologies and in building solutions for Virtual Earth, Microsoft Office, Windows SharePoint Services, and Microsoft Office Project for clients.

Jeff House is a Software Architect with Advaiya, Inc. He has over 20 years of experience in software design and architecture, with a broad background including Microsoft technologies and mapping systems.

Advaiya, Inc. is a high-end technology and business strategy firm that provides services to help accelerate the adoption of emerging technology.

Additional Resources

For more information, see the following resources: