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

  • Article

Summary:   This article is the second article in a two-part series. Learn how to add data to a Microsoft Office Excel 2007 worksheet using automation to format cells as well as create charts and send data for pushpins on the map and export charts images. (43 printed pages)

Imtiyaz Mubarak, 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

  • Scenario Overview

  • Create the Sales Report and Excel Charts

  • Add Pushpins to the Virtual Earth Map and Customize the InfoBox

  • Display the Sales Details from InfoBox of the Shape

  • Running the Application

  • Conclusion

  • About the Author

  • Additional Resources

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

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

Scenario Overview

For a description of the business scenario, see Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 1 of 2).

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

  • Create a Microsoft 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.

This article, the second article in the two-part series, describes how to:

  • Add data to an Office Excel 2007 worksheet using automation to format cells

  • Create charts and send data for pushpins on the map.

  • Create an Office Excel chart for each row and then export the chart as an image.

  • Customize the Virtual Earth InfoBox (also called a pop-up).

The data is taken from an Office Access database and added to a new Office Excel worksheet.

The new VEShape class gives you numerous methods to customize the shape object and to create and access drawing elements on the map. You can also customize the InfoBox for the shape using the Virtual Earth Application Programming Interface (API).

Create the Sales Report and Excel Charts

NoteNote

In this solution, we use the Constant.cs file to declare constants and variables. For more information about Constant.cs, download the 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio.

In Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 1 of 2), you defined constants in the Constant.cs file. This part of the solution uses additional constants.

To edit the Constant.cs class

  1. In Microsoft Visual Studio 2005, open the ExcelMapAddIn project created in Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 1 of 2).

  2. Open the Constant.cs file and replace all contents with the following code.

    using System;
using System.Collections.Generic;
using System.Text;

namespace ExcelMapAddIn
{
    class Constants
    {
        private Constants() { }

        // 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;
        }

        // Sets table style of the range.
        internal sealed class XLTableStyle
        {
            private XLTableStyle() { }
            internal const string STYLE_GREEN = "TableStyleMedium4";
            internal const string STYLE_BLUE = "TableStyleMedium2";
            internal const string STYLE_ORANGE = "TableStyleMedium7";
        }

        // Sets report title on the worksheet.
        internal sealed class ReportTitle
        {
            private ReportTitle() { }
            internal const string SALES_TERRITORY = "Sales by Territory";
            internal const string SALES_PERSON = "Sales by Sales Persons";
            internal const string SALES_STORE = "Sales by Stores";
        }

        // Sets column title for the various reports.
        internal sealed class ColumnTitle
        {
            private ColumnTitle() { }
            internal const string TERRITORY = "Territory";
            internal const string SALES_PERSON = "Sales Person";
            internal const string SALES_STORE = "Store Name";
            internal const string SRNO = "SR";
            internal const string SALES_LAST = "Sales 2006";
            internal const string SALES_YTD = "Sales 2007";
            internal const string SALES_LAST_SHORT = "F06";
            internal const string SALES_YTD_SHORT = "F07";
        }


        // Sets chart style for the chart. 
        internal sealed class XLChartStyle
        {
            private XLChartStyle() { }
            internal const int TERRITORY_PIE = 19;
            internal const int TERRITORY_COLUMN = 35;
            internal const int PERSON_COLUMN = 35;
            internal const int STORE_COLUMN = 35;
            internal const int INFOBOX_COLUMN = 45;

        }

        // Sets range style.
        internal sealed class XLRangeStyle
        {
            private XLRangeStyle() { }
            internal const string CURRENCY = "Currency";
            internal const string TOTAL = "Total";
        }

        // 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";
        }

        // Data columns of the Sales Territory table.
        internal sealed class SalesTerritoryDataColumn
        {
            private SalesTerritoryDataColumn() { }

            internal const string NAME = "TerritoryName";
            internal const string SALESYTD = "SalesYTD";
            internal const string SALESLAST = "SalesLastYear";
            internal const string LATITUDE = "Latitude";
            internal const string LONGITUDE = "Longitude";
            internal const string PERSON = "Person";
        }

        // Data columns of the Sales Store table.
        internal sealed class SalesStoreDataColumn
        {
            private SalesStoreDataColumn() { }

            internal const string NAME = "StoreName";
            internal const string SALESYTD = "SalesYTD";
            internal const string SALESLAST = "SalesLastYear";
            internal const string LATITUDE = "Latitude";
            internal const string LONGITUDE = "Longitude"; 
            internal const string SQUARE_FEET = "SquareFeet";
            internal const string YEAR_OPENED = "YearOpened";
            internal const string TOTAL_EMPLOYEE = "NoOfEmployee";
            internal const string MANAGER = "Manager";
            internal const string ADDRESS = "Address";
            internal const string CITY = "City";
        }

        internal sealed class SalesReport
        {
            private SalesReport() { }

            internal const string SALES_PERSON = "SalesPerson";
            internal const string SALES_TERRITORY = "SalesTerritory";
            internal const string SALES_STORE = "SalesStore";
        }

        // Chart dimensions
        internal sealed class ChartDimension
        {
            private ChartDimension() { }
            internal const int LEFT = 0;
            internal const int TOP = 1;
            internal const int WIDTH = 2;
            internal const int HEIGHT = 3;
        }


        // Data queries
        internal sealed class SalesDataQuery
        {
            private SalesDataQuery() { }
            internal const string SALES_TERRITORY = "SELECT Territory.* FROM Territory " + 
"ORDER BY Territory.TerritoryName;";
            internal const string SALES_PERSON = "SELECT SalesPerson.* FROM SalesPerson " +
               " ORDER BY SalesPerson.PersonName;";
            internal const string SALES_STORE = "SELECT Store.* FROM Store ORDER BY Store.StoreName;";
        }

        // InfoBox HTML for the sales reports
        internal sealed class InfoBoxHTML
        {
            private InfoBoxHTML() { }

            internal const string SALES_TERRITORY = "<tr><td><b>Territory:&nbsp;&nbsp;</b>" +
               "{0}<br><b>Email:&nbsp;&nbsp;</b>{1}<br>" +
               "<b>Country Region:&nbsp;&nbsp;</b>US<br><b>Sales 2006:&nbsp;&nbsp;</b>" + 
"{2}<br><b>Sales 2007:&nbsp;&nbsp;</b>{3}</td></tr>";

            internal const string SALES_PERSON = "<tr><td><b>Sales Person:&nbsp;&nbsp;</b>{0}" +
                "<br><b>Email:&nbsp;&nbsp;</b>{1}<br>" + 
                "<b>Country Region:&nbsp;&nbsp;</b>US<br><b>Sales 2006:&nbsp;&nbsp;</b>" +
                "{2}<br><b>Sales 2007:&nbsp;&nbsp;</b>{3}</td></tr>";

            internal const string SALES_STORE = "<tr><td><b>Sales Store:&nbsp;&nbsp;</b>{0}" +
                "<br><b>Manager:&nbsp;&nbsp;</b>{1}<br>" + 
                "<b>Country Region:&nbsp;&nbsp;</b>US<br><b>Sales 2006:&nbsp;&nbsp;</b>" +
                "{2}<br><b>Sales 2007:&nbsp;&nbsp;</b>{3}</td></tr>";
        }


        // 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";
        }

        // 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 SALES_RIBBON_XML = "ExcelMapAddIn.SalesRibbon.xml";
            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 SHEET_HEADER_IMAGE_PATH = @"Images\CONTOSO-LOGO_BIG.png";
            internal const string MAP_CONTROL_HTML_PATH = @"HTML\MapControl.html";
            internal const string IMAGE_FOLDER_PATH = @"Images\";
        }
    }
}

Adding Custom Images

The solution uses custom images to display the report heading and salesperson details. Next, you want to add an image folder.

To add custom images

  1. In the solution, at solution\debug\bin\, create a folder named Images and copy all images to this folder.

  2. Download images from 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio. You can find custom images in the Images folder or you can supply your own images.

Setting the Header Formatting

The following code demonstrates how you add an image to the shapes collection in the worksheet and set the report title to the specific Office Excel cell. You can add images to the worksheet using the AddPicture method (Shapes collection) and specify the height, width, and locations (upper, left) on the worksheet (see Figure 1).

To set header formatting for the report

  1. Open the SalesRibbon.cs file and add the following variables at the class level.

    double[] STColumnChartDim = { 15, 210, 315, 165 };
double[] STPieChartDim = { 15, 210, 315, 165 };

double[] SPColumnChartDim = { 350, 90, 425, 285 };
double[] SPPieChartDim = { (48.43 * 10), 50, 315, 165 };

int _rowStart;
int _rowEnd;

  2. Add the SetExcelSheetHeader method to the SalesRibbon class.

    // Sets the worksheet header (adds image and report title on the worksheet).

private void SetExcelSheetHeader(Excel.Worksheet currentSheet, string reportTitle)
{
    try
    {
        // Gets the path of the report header image.
        string headerImagePath = GetAssemblyPath() +
           Constants.AddInProperty.SHEET_HEADER_IMAGE_PATH;

        // Adds an image to the worksheet. 
        currentSheet.Shapes.AddPicture(headerImagePath, 
            Microsoft.Office.Core.MsoTriState.msoFalse,
            Microsoft.Office.Core.MsoTriState.msoTrue, 8, 10, 167, 66);

        // Sets the report title and aligns it to the right.
        currentSheet.Cells[6, 5] = reportTitle;
        ((Excel.Range)currentSheet.Cells[6, 4]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

    Figure 1. Picture with report title added to the worksheet

    Picture with report title

  3. In SalesRibbon.cs, add methods to set the column width, add a subtotal row, and format the range.

    // Sets the column header for the report.

void SetColumnHeaderAndWidth(Excel.Worksheet currentSheet, string reportType, int rowIndex)
{
    try
    {
        // Sets the column width.
        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.FIRST_COLUMN]).
           EntireColumn.ColumnWidth = 2;
        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME]).
            EntireColumn.EntireColumn.ColumnWidth = 24;
        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST]).
            EntireColumn.ColumnWidth = 18;
        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD]).
            EntireColumn.ColumnWidth = 18;

        // Hides the Serial no column.
        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.SRNO]).
            EntireColumn.Hidden = true;

        // Sets the values for the row.
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.SRNO] = Constants.ColumnTitle.SRNO;
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST] = 
           Constants.ColumnTitle.SALES_LAST_SHORT;
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD] = 
           Constants.ColumnTitle.SALES_YTD_SHORT;

        switch (reportType)
        {
            case Constants.SalesReport.SALES_PERSON:
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = 
                   Constants.ColumnTitle.SALES_PERSON;
                break;

            case Constants.SalesReport.SALES_TERRITORY:
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = 
                   Constants.ColumnTitle.TERRITORY;
                break;
            case Constants.SalesReport.SALES_STORE:
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = 
                   Constants.ColumnTitle.SALES_STORE;
                break;
        }
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}


// Adds the subtotal for the specific range.
private void AddSubTotalRow(Excel.Worksheet currentSheet, int rowIndex, int rowCount)
{
    try
    {
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = "Total";

        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST])
           .FormulaR1C1 = "=SUBTOTAL(9,R[-" + rowCount.ToString() + "]C:R[-1]C)";

        ((Excel.Range)currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD])
            .FormulaR1C1 = "=SUBTOTAL(9,R[-" + rowCount.ToString() + "]C:R[-1]C)";

        Excel.Range rng = GetWorksheetRange(currentSheet, rowIndex, Constants.ColumnIndex.NAME, 
         rowIndex, Constants.ColumnIndex.SALESYTD);
        rng.Style = Constants.XLRangeStyle.CURRENCY;
        rng.Style = Constants.XLRangeStyle.TOTAL;
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

// Sets the style and formatting for the specific range.
private void SetRangeStyleAndFormatting(Excel.Worksheet currentSheet, 
   Excel.Range rng, string listName, string tableStyle)
{
    try
    {
        rng.Style = Constants.XLRangeStyle.CURRENCY;

        currentSheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange,
            rng, missing, Excel.XlYesNoGuess.xlYes, missing).Name = listName;
        currentSheet.ListObjects[listName].TableStyle = tableStyle;
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

// Gets the worksheet range.
private Excel.Range GetWorksheetRange(Excel.Worksheet currentSheet, int startRow, int startCol, 
   int endRow, int endCol)
{

    Excel.Range retVal = null;
    try
    {
        retVal = currentSheet.get_Range(currentSheet.Cells[startRow, startCol], 
           currentSheet.Cells[endRow, endCol]);
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }

    return retVal;
}

Add Charts to the Worksheet

Office Excel 2007 provides an enhanced charting engine for all Microsoft Office applications. Excel 2007 charts provide new formatting objects based on the OfficeArt object model. The code below shows how you can add the chart to the worksheet and customize the chart.

The solution creates column and pie charts for the reports. The pie chart is displayed only in the Sales by Territory report (see Figures 2 and 3).

Figure 2. Sample column chart

Sample column chart

Figure 3. Sample pie chart

Sample pie chart

To add charts

  1. Add the following code to the SalesRibbon.cs class for the column chart.

    // Adds the column chart to the worksheet.

private void AddColumnChart(Excel.Worksheet currentSheet, Excel.Range sourceRange,
    Excel.Range seriesRange, double[] chartDimension, string chartTitle, int chartStyle)
{
    try
    {
        // Gets the chart object.
        Excel.ChartObjects chartObj = 
(Excel.ChartObjects)currentSheet.ChartObjects(missing);

        // Adds a chart to the chart object.
        Excel.Chart chart = (Excel.Chart)chartObj.Add(
            chartDimension[Constants.ChartDimension.LEFT],
            chartDimension
            [Constants.ChartDimension.TOP], chartDimension
            [Constants.ChartDimension.WIDTH],
            chartDimension[Constants.ChartDimension.HEIGHT]).Chart;

        // Sets the source data range for the chart.
        chart.SetSourceData(sourceRange, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns);
        // Sets the chart type.
        chart.ChartType = Excel.XlChartType.xlColumnClustered;

        //Sets the series name.
        ((Excel.Series)chart.SeriesCollection(1)).Name = Constants.ColumnTitle.SALES_LAST;
        ((Excel.Series)chart.SeriesCollection(2)).Name = Constants.ColumnTitle.SALES_YTD;

        // Sets chart formatting and specifies legend position, chart title position and axis value.
        chart.ChartStyle = chartStyle;
        chart.ClearToMatchStyle();
        chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementLegendBottom);
        chart.SetElement(Microsoft.Office.Core.MsoChartElementType.
        msoElementPrimaryValueAxisThousands);

        // Shows the chart title.
        chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
        chart.ChartTitle.Text = chartTitle;
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

  2. Then, add the following code to the SalesRibbon.cs class for the pie chart. Remember that the pie chart will be displayed in the Sales by Territory report only.

    // Adds the pie chart to the worksheet.
private void AddPieChart(Excel.Worksheet currentWorksheet, Excel.Range sourceDataRange,
   double[] chartDimension, string chartTitle)
{
    try
    {
        // Gets the chart object.
        Excel.ChartObjects chartObj = (Excel.ChartObjects)currentWorksheet.
          ChartObjects(missing);

        // Adds a chart to the chart object and 
        // sets the chart dimensions (height, width, top, and left).
        Excel.Chart chart = (Excel.Chart)chartObj.Add(chartDimension[Constants.ChartDimension.LEFT],
            chartDimension[Constants.ChartDimension.TOP], 
            chartDimension[Constants.ChartDimension.WIDTH],
            chartDimension[Constants.ChartDimension.HEIGHT]).Chart;

        // Sets the source data range.
        chart.SetSourceData(sourceDataRange, missing); 

        // Sets the chart type (pie).
        chart.ChartType = Excel.XlChartType.xlPie;

        // Sets the chart style.
        chart.ChartStyle = Constants.XLChartStyle.TERRITORY_PIE ;
        chart.ClearToMatchStyle();

        // Sets the chart title above the chart.
        chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);

        // Sets the chart title.
        chart.ChartTitle.Text = chartTitle; 
        chart.ApplyLayout(1, missing);

        // Gets the series of the chart.
        Excel.Series series = (Excel.Series)chart.SeriesCollection(1); 
        Excel.Point pn = (Excel.Point)series.Points(1);

        // Sets the 3D effect for the point.
        pn.Has3DEffect = true; 
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

Create the Chart for the InfoBox

To display a chart in the InfoBox for the pushpin, create an Office Excel chart. This Office Excel chart displays the sales figures of the salesperson or the store for the sales years 2006 and 2007. According to the Microsoft Virtual Earth Software Developers Kit (SDK), the Virtual Earth pushpin can display plain text or custom HTML in the InfoBox. You can provide your own custom HTML for the InfoBox that includes formatting, styles, and images. You can then export the chart object to display the chart as an image in the shape’s InfoBox on the map (see Figure 4).

Figure 4. Chart for InfoBox

Chart for InfoBox

Note

This chart is used frequently in this solution. Instead of creating a new chart for each row, just create the chart with the appropriate properties and method and omit the source data. You can specify source data later.

NoteNote

This chart is used frequently in this solution. Instead of creating a new chart for each row, just create the chart with the appropriate properties and method and omit the source data. You can specify source data later.

To create the InfoBox chart

  • To create the InfoBox chart, add the following code to SalesRibbon.cs.

    // Adds the InfoBox chart to the worksheet.

private Excel.Chart AddInfoBoxChart(Excel.Worksheet currentWorksheet)
{
    Excel.Chart infoBoxChart = null;

    try
    {
        // Gets the chart object from the Excel worksheet.
        Excel.ChartObjects chartObj = (Excel.ChartObjects)currentWorksheet.ChartObjects(missing);
        // Adds a chart to the chart object.
        infoBoxChart = (Excel.Chart)chartObj.Add(50, 500, 125, 100).Chart;
        // Sets the chart type (column clustered).
        infoBoxChart.ChartType = Excel.XlChartType.xlColumnClustered;

        // Sets the chart's plot property.
        infoBoxChart.PlotBy = Microsoft.Office.Interop.Excel.XlRowCol.xlRows;
        // Sets the chart style.
        infoBoxChart.ChartStyle = Constants.XLChartStyle.INFOBOX_COLUMN;
        // Hides the legends for the chart.
        infoBoxChart.HasLegend = false;
        infoBoxChart.ClearToMatchStyle();

        // Sets rounded corners for the chart.
        ((Excel.ChartObject)infoBoxChart.Parent).RoundedCorners = true;

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

    return infoBoxChart;
}

Set the InfoBox Chart Source Data and Export It as an Image

After you create the InfoBox chart and set the appropriate properties, you need to define the source data range for the chart, and then export the chart as an image file to the specific location and file type.

Add the following code to SalesRibbon.cs.

// Sets the source data for the specific chart and exports it as an image file.

private void SetInfoBoxChartSourceDataAndExport(Excel.Chart chart, Excel.Range sourceDataRange,
   Excel.Range seriesRange, string imageFileName)
{
    try
    {
        // Sets the source data range for the chart.
        chart.SetSourceData(sourceDataRange, missing);

        // Sets the X-Values for the series collection.
        ((Excel.Series)chart.SeriesCollection(1)).XValues = seriesRange;

        //Sets the axis value for the chart to thousands.
        chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisThousands);

        // Exports the chart.
        chart.Export(imageFileName, "gif", missing);

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

Replace the Ribbon Callback Method

This section replaces the Ribbon callback method with a method to add data for sales reports and then adds charts and formats cells.

To replace the Ribbon call-back method

  1. Replace the Ribbon call-back method with the following code in SalesRibbon.cs

    public void SalesAction(Office.IRibbonControl control)
{
    switch (control.Id)
    {
        case Constants.RibbonButtonID.SALES_TERRITORY:
            CreateReport(Constants.SalesReport.SALES_TERRITORY, Constants.ReportTitle.SALES_TERRITORY,
                Constants.SalesDataQuery.SALES_TERRITORY, Constants.XLTableStyle.STYLE_BLUE,
                Constants.XLChartStyle.TERRITORY_COLUMN);
            break;

        case Constants.RibbonButtonID.SALES_STORE:
            CreateReport(Constants.SalesReport.SALES_STORE, Constants.ReportTitle.SALES_STORE,
                Constants.SalesDataQuery.SALES_STORE, Constants.XLTableStyle.STYLE_BLUE,
                Constants.XLChartStyle.STORE_COLUMN);
            break;

        case Constants.RibbonButtonID.SALES_PERSON:
            CreateReport(Constants.SalesReport.SALES_PERSON, Constants.ReportTitle.SALES_PERSON,
                Constants.SalesDataQuery.SALES_PERSON, Constants.XLTableStyle.STYLE_BLUE,
                Constants.XLChartStyle.PERSON_COLUMN);
            break;

        case Constants.RibbonButtonID.SHOW_MAP:
            TogglePane(true);
            break;
    }
}

  2. Locate the CreateReport method in SalesRibbon.cs and replace it with the following code. The code calls a method to add data for sales reports and then adds charts and formats cells.

    // Adds the report to the worksheet.
public void CreateReport(string reportType, string reportTitle, string dataQuery, 
    string tableStyle, int columnChartStyle)
{
    try
    {
        // Clears all data from the map.
        mapUserControl.ClearMap();

        // Adds a new worksheet.
        Excel.Worksheet salesWS = AddExcelWorkSheet();

        // Sets the report header.
        SetExcelSheetHeader(salesWS, reportTitle);

        // Gets the data table.
        DataTable dataTable = GetDataTable(dataQuery);

        int rowIndex = 7;

        // Sets column width.
        SetColumnHeaderAndWidth(salesWS, reportType, rowIndex);

        // Sets the chart series range.
        Excel.Range chartSeriesRange = salesWS.get_Range(salesWS.Cells
            [rowIndex, Constants.ColumnIndex.SALESLAST],
            salesWS.Cells[rowIndex, Constants.ColumnIndex.SALESYTD]);

        // Adds the InfoBox chart.
        Excel.Chart infoBoxChart = AddInfoBoxChart(salesWS);

        _rowStart = rowIndex + 1;

        AddReportDataToSheet(salesWS, chartSeriesRange, reportType, dataTable, 
            infoBoxChart, ref rowIndex);

        // Sets the column header titles.
        salesWS.Cells[_rowStart - 1, Constants.ColumnIndex.SALESLAST] = 
        Constants.ColumnTitle.SALES_LAST;
        salesWS.Cells[_rowStart - 1, Constants.ColumnIndex.SALESYTD] = Constants.ColumnTitle.SALES_YTD;

        _rowEnd = rowIndex;

        rowIndex++;

        // Adds the subtotal for the table.
        AddSubTotalRow(salesWS, rowIndex, (_rowEnd - _rowStart) + 1);

        Excel.Range sourceDataRange = GetWorksheetRange(salesWS, _rowStart - 1, 
            Constants.ColumnIndex.NAME,
            _rowEnd, Constants.ColumnIndex.SALESYTD);

        // Sets the table style and formatting.
        SetRangeStyleAndFormatting(salesWS, sourceDataRange, reportType, tableStyle);

        // Adds the column bar chart to the worksheet.
        AddColumnChart(salesWS, sourceDataRange, chartSeriesRange, SPColumnChartDim,
            reportTitle, columnChartStyle);

        if (reportType == Constants.SalesReport.SALES_TERRITORY)
        {
            AddPieChart(salesWS, sourceDataRange, STPieChartDim, reportTitle);
        }

        // Makes the worksheet visible.
        salesWS.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
        salesWS.Activate();


        // Sets the map view to include all of the points.
        mapUserControl.SetMapView();
        //mapUserControl.AssignDetailTable(dataTable);

        // Adds an event for the worksheet selection change.
        //salesWS.SelectionChange += new Microsoft.Office.Interop.Excel.
        // DocEvents_SelectionChangeEventHandler(Worksheet_SelectionChange);
    }

    catch (Exception ex)
    {
        DisplayException(ex.Message);

    }
}

  3. Add the following methods to SalesRibbon.cs to generate sales reports for Sales by Territory, Sales by Sales Person, and Sales by Store.

    // Adds various reports to the worksheet.
void AddReportDataToSheet(Excel.Worksheet currentSheet, Excel.Range chartSeriesRange, 
     string reportType, DataTable dataTable, Excel.Chart infoBoxChart, ref int rowIndex)
{

    try
    {
        switch (reportType)
        {
            case Constants.SalesReport.SALES_TERRITORY:
                AddTerritoryData(currentSheet, chartSeriesRange, dataTable, infoBoxChart, ref rowIndex);
                break;
            case Constants.SalesReport.SALES_PERSON:
                AddSalesPersonData(currentSheet, chartSeriesRange, dataTable, infoBoxChart, 
                ref rowIndex);
                break;
            case Constants.SalesReport.SALES_STORE:
                AdSalesStoreData(currentSheet, chartSeriesRange, dataTable, infoBoxChart,
                ref rowIndex);
                break;
        }
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }

}


// Adds the data for the Sales Territory report.

void AddTerritoryData(Excel.Worksheet currentSheet, Excel.Range chartSeriesRange, 
    DataTable dataTable, Excel.Chart infoBoxChart, ref int rowIndex)
{

    int index = 0;
    foreach (DataRow dr in dataTable.Rows)
    {
        rowIndex++;
        index++;

        // Gets the values for the columns.
        string territoryName = dr[Constants.SalesTerritoryDataColumn.NAME].ToString();
        string salesYTD = dr[Constants.SalesTerritoryDataColumn.SALESYTD].ToString();
        string salesLastYear = dr[Constants.SalesTerritoryDataColumn.SALESLAST].ToString();
        string latitude = dr[Constants.SalesTerritoryDataColumn.LATITUDE].ToString();
        string longitude = dr[Constants.SalesTerritoryDataColumn.LONGITUDE].ToString();
        string territoryPerson = dr[Constants.SalesTerritoryDataColumn.PERSON].ToString();

        // Adds data to the worksheet, cell by cell.
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.SRNO] = index;
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = territoryName;
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST] = salesLastYear;
        currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD] = salesYTD;

        // Gets the source data range for the chart.
        Excel.Range sourceDataRange = currentSheet.get_Range(currentSheet.Cells
          [rowIndex, Constants.ColumnIndex.SALESLAST],
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD]);

        // Gets the graph image name and path for the InfoBox.
        string graphImageFileName = GetChartImageFilePath(rowIndex);

        // Sets the chart source data and exports the data as an image for the InfoBox.
        SetInfoBoxChartSourceDataAndExport(infoBoxChart, sourceDataRange, chartSeriesRange,
          graphImageFileName);

        // Replaces placeholders in the HTML string for the InfoBox.
        string htmlTable = string.Format(Constants.InfoBoxHTML.SALES_TERRITORY, territoryName,
            territoryPerson, salesLastYear, salesYTD);

        // Adds pushpins to the map.
        mapUserControl.AddPushPinOnMap(latitude, longitude, GetImageURLPath(graphImageFileName),
            htmlTable, Constants.SalesReport.SALES_TERRITORY);

    }
}


// Adds data for the Sales Territory report.
void AddSalesPersonData(Excel.Worksheet currentSheet, Excel.Range chartSeriesRange, DataTable dataTable,
    Excel.Chart infoBoxChart, ref int rowIndex)
{
    int index = 0;

    try
    {
        foreach (DataRow dr in dataTable.Rows)
        {
            rowIndex++;

            index++;

            // Gets 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();
            string emailAddress = dr[Constants.SalesPersonDataColumn.EMAIL].ToString();


            // Adds data to the worksheet, cell by cell.
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SRNO] = index;
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = salesPerson;
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST] = salesLastYear;
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD] = salesYTD;

            // Gets the data range for the row chart.
            Excel.Range sourceDataRange = currentSheet.get_Range(currentSheet.Cells
                [rowIndex, Constants.ColumnIndex.SALESLAST],
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD]);

            // Gets the graph image name and path for the InfoBox.
            string graphImageFileName = GetChartImageFilePath(rowIndex);

            // Sets the chart source data and exports the data as an image for the InfoBox.
            SetInfoBoxChartSourceDataAndExport(infoBoxChart, sourceDataRange, 
               chartSeriesRange, graphImageFileName);

            // Replaces placeholders in the HTML string for the InfoBox.
            string htmlTable = string.Format(Constants.InfoBoxHTML.SALES_PERSON, salesPerson,
                emailAddress, salesLastYear, salesYTD);

            // Adds pushpins to the map.
            mapUserControl.AddPushPinOnMap(latitude, longitude, GetImageURLPath(graphImageFileName),
                htmlTable, Constants.SalesReport.SALES_PERSON);
        }
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }

}

// Adds data for the Sales by Store report.
void AdSalesStoreData(Excel.Worksheet currentSheet, Excel.Range chartSeriesRange, DataTable dataTable,
    Excel.Chart infoBoxChart, ref int rowIndex)
{
    int index = 0;

    try
    {
        foreach (DataRow dr in dataTable.Rows)
        {
            rowIndex++;

            index++;

            // Gets the values for the columns.
            string storeName = dr[Constants.SalesStoreDataColumn.NAME].ToString();
            string salesLastYear = "$" + dr[Constants.SalesStoreDataColumn.SALESLAST].ToString();
            string salesYTD = "$" + dr[Constants.SalesStoreDataColumn.SALESYTD].ToString();
            string latitude = dr[Constants.SalesStoreDataColumn.LATITUDE].ToString();
            string longitude = dr[Constants.SalesStoreDataColumn.LONGITUDE].ToString();
            string manager = dr[Constants.SalesStoreDataColumn.MANAGER].ToString();

            // Adds data to the worksheet, cell by cell.
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SRNO] = index;
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.NAME] = storeName;
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESLAST] = salesLastYear;
            currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD] = salesYTD;

            // Gets the source data range for the InfoBox chart.
            Excel.Range sourceDataRange = currentSheet.get_Range(currentSheet.Cells
                [rowIndex, Constants.ColumnIndex.SALESLAST],
                currentSheet.Cells[rowIndex, Constants.ColumnIndex.SALESYTD]);

            // Gets the graph image name and path for the InfoBox.
            string graphImageFileName = GetChartImageFilePath(rowIndex);

            // Sets the chart source data and export the data as an image for the InfoBox.
            SetInfoBoxChartSourceDataAndExport(infoBoxChart, sourceDataRange,
                chartSeriesRange, graphImageFileName);

            // Replaces placeholders in the HTML string for the InfoBox.
            string htmlTable = string.Format(Constants.InfoBoxHTML.SALES_STORE, storeName,
                manager, salesLastYear, salesYTD);

            // Adds pushpins to the map.
            mapUserControl.AddPushPinOnMap(latitude, longitude, GetImageURLPath(graphImageFileName),
                htmlTable, Constants.SalesReport.SALES_STORE);
        }
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }

}


// Gets the URL for the InfoBox image.
private string GetImageURLPath(string filePath)
{
    string returnValue = "";
    try
    {
        if (filePath.Trim().Length != 0)
            returnValue = "file:///" + filePath.Replace(@"\", "/");
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }

    return returnValue;
}

// Gets the temporary image file name for the InfoBox chart.
private string GetChartImageFilePath(int imageIndex)
{
    string retVal = "";
    try
    {
        string _temporaryFolderPath = System.IO.Path.GetTempPath();
        retVal = _temporaryFolderPath + "ST_" + imageIndex.ToString() + ".gif";
    }

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

    return retVal;

}

Figure 5. Sales report and chart displayed on the worksheet

Sales report and chart displayed on worksheet

Set Custom Icons that Use Virtual Earth Shapes

In Part 1: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 1 of 2), you added a pushpin to the map using a Shape object. This section describes how you can customize the shape icon and the InfoBox for the shape.

To set a custom icon for the shape, you can pass an image URL or custom HTML to the SetCustomIcon method of the Shape object. You can also create a text overlay for your custom image by using HTML with an <img> tag. This overlay can show incremental numbers or letters on a group of pins that are displayed on the same custom image.

NoteNote

Although you can set numbers or letters on a group of shapes that are displayed on the same custom image, this solution shows incremental numbers for each shape. For more details about using numbered pushpins, see Blog: Virtual Earth API: Creating Numbered Pushpins.

To set a custom icon

  1. First, create custom CSS styles for a pin to set the background image, position, and font styles.

  2. Add the following CSS code to the MapControl.html file.

    <style type="text/css" >

.customPinStyle{ position:relative; background:url('map.pin.gif') no-repeat 0 0;
 height:30px; width:25px; top:-15px; color:#fff; text-align:center;
 font: bold 12px Arial; cursor: pointer;}

.customPinStyle .text{ position: relative; top: 2px; }

</style>

We will use the SetCustomIcon method in the AddPushpinOnMap method to set the pin icon to use custom HTML/CSS. In this example, the pin icon consists of two styled <div> tags using the styles defined previously and the text "1". You use the outer <div> to style the pushpin image, and the inner <div> to style the pushpin text.

Add Pushpins to the Virtual Earth Map and Customize the InfoBox

The Virtual Earth API provides a feature-rich class called VEShape class that represents all pushpins, polylines, and polygons. Each VEShape object can store HTML content for an InfoBox, which will be shown when the user moves the mouse over the shape. If you do not want to use the default InfoBox style, you can clear it and use a custom CSS.

Customize the InfoBox for the Shape

To customize the InfoBox for the shape, disable the default pop-up style using the ClearInfoBoxStyles method of the VEMap object. Then, define a custom CSS in the following steps.

Open MapControl.html and add the following line at the end of the GetMap method.

map.ClearInfoBoxStyles();

This method clears the default InfoBox styles that give the Virtual Earth default InfoBox its look and feel. A few basic style components remain to get you started. By clearing the default styles, you can customize your InfoBox by referring to CSS classes within your custom HTML.

To customize the InfoBox, add the following CSS code between <style> tags in the MapControl.html file.

/* Styles that apply to the InfoBox's containing element whether it
 has no beak, a rightBeak, or a leftBeak */ 
.customInfoBox-noBeak, 
.customInfoBox-with-rightBeak, 
.customInfoBox-with-leftBeak 
{
   position: absolute;
}
/* Offsets the body for the drop shadow and sets the body 
background and border (developers can change these settings) */
.customInfoBox-body
{
   position: relative;
   top: 0px;
   left: -2px;
   bottom : 0px;
   padding: 0px;
   border: 0px solid #000;
   background-color:transparent;
   width:50px;
   height:250px;
}

/* Applies a default drop shadow after the default 
Virtual Earth InfoBox styles are cleared */ 
.customInfoBox-shadow 
{
   position: relative;
   background-color:transparent;
}

/* Sections of the InfoBox that a developer could style if desired,
 but have no style when the default Virtual Earth InfoBox styles
 are cleared */ 
.customInfoBox-previewArea, 
.customInfoBox-actionsBackground 
{ 

}
/* Sections of the InfoBox that should not be visible when the 
default Virtual Earth InfoBox styles are cleared */ 
.customInfoBox-actions, 
.customInfoBox-paddingHack 
{
   display: none;
}

.customInfoBox-beak,
.customInfoBox-progressAnimation
{
    visibility: hidden;
}

To use something other than the default plain text in your InfoBox, you can provide custom HTML by using the details parameter of the VEShape.SetDescription method. The content of the details parameter is wrapped in a <div> tag.

Add the following HTML string to MapControl.html.

// HTML string for Shape Description to display infobox.
var descriptionHTML = "<div id='customPopup' style='width: 199px;height:295px' class='DivBG'>" + 
    "<table style='background-color:transparent;width:100%;' border='0' cellpadding='0' " +
    "cellspacing='0' class='TableBG' ><tr valign='top' ><td valign='top' align='center' >" + 
    "<img alt='' src='{0}' /><br/>" + 
    "<table style='width: 98%;text-align:left;height:95px;vertical-align:top;color:#000000' >" + 
    "<tr valign='top'>{1}</tr></table></td></tr><tr valign='top' ><td align='center' >" + 
    "<a href='#' onClick='javascript:ShowDetails({2},{3});' >" + 
    "<img alt='' src='view.details.png' style='border-width:0px' /></a></td></tr></table></div>";

This custom string consists of four placeholders that are replaced with the parameter values that come from the map control host, as shown in Table 1.

Table 1. Placeholders and their values for MapControl.html

Placeholder

Parameter value

‘{0}’

Chart image file

‘{1}’

Shape details (also HTML) such as the sales person’s information

‘{2}’

Shape index

‘{3}’

Report type

Locate the AddPushPinOnMap method in MapControl.html and replace it with the following code. The code adds the pushpin to the map and sets the custom icon and custom HTML for the InfoBox.

// An array to store shapes IDs. 
var shapeIDs = new Array; 
var pinID = 0;

function AddPushPinOnMap(lat, lng, imagePath, htmlTable, reportType)
{
        pinID++;
        
        loc = new VELatLong(parseFloat(lat),parseFloat(lng));
        
        // Stores the location in an array.
        locs.push(loc);            
        
        // Creates the shape object (type is pushpin).
        var shape = new VEShape(VEShapeType.Pushpin, loc ); 

        // Sets a custom icon for the pushpin.
        shape.SetCustomIcon("<div class='customPinStyle'><div class='text' >" + pinID + "</div></div>"); 
        shape.SetTitle('');
        
        // Formats the HTML string for the InfoBox description.
        var shapeDescription = descriptionHTML.replace('{0}', imagePath);
        shapeDescription = shapeDescription.replace('{1}', htmlTable);
        shapeDescription = shapeDescription.replace('{2}', pinID -1);
        shapeDescription = shapeDescription.replace('{3}', "\"" + reportType + "\"");
        
        shape.SetDescription(shapeDescription);
        // Adds the shape to the map.
        map.AddShape(shape);                      

        // Stores the shape's ID in the array.
        var shapeID = new String(shape.GetID());
        shapeIDs.push(shapeID);
}

Open MapUserControl.cs and replace the AddPushpinOnMap method with the following code.

public void AddPushPinOnMap(string latitude, string longitude, string imagePath,
    string htmlTable, string reportType)
{
    // Invokes the AddPushPinOnMap method.
    webBrowserMap.Document.InvokeScript("AddPushPinOnMap", new object[] { latitude, longitude, imagePath, htmlTable, reportType });
}

Add Pushpin and InfoBox

This solution uses custom images to customize pushpins and their InfoBoxes. Download the solution for images and copy the following images to the bin\debug\HTML folder.

  • Info.bg.gif

  • Map.pin.gif

  • View.details.png

Figure 6. Pushpins displayed on the map

Pushpins displayed on map

Figure 7. Customized InfoBox

Customized Infobox

Code the Cell Selection Change Event and Display the InfoBox on the Map

You can display the shape’s InfoBox by resting the pointer over the shape. To provide this functionality when users change the cell on the Office Excel worksheet, you can use an Office Excel event handler for the SelectionChange event.

To specify event behavior

  1. Remove commenting from the following lines in the CreateReport method in SalesRibbon.cs.

    salesWS.SelectionChange += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler
   (Worksheet_SelectionChange);

  2. Add the method for the SelectionChange event handler in SalesRibbon.cs. This method passes the row index number to the custom method.

    public void Worksheet_SelectionChange(Microsoft.Office.Interop.Excel.Range Target)
{
    try
    {
        // Gets the row index.
        Excel.Range rng = (Excel.Range)Target.Worksheet.Cells[Target.Row, 2];

        if (rng.FormulaR1C1.ToString() != string.Empty) 
        {
            int rowIndex = Convert.ToInt32(rng.FormulaR1C1.ToString());

            if (rowIndex > 0)
                mapUserControl.ShowPopupInfoBox(rowIndex);
        }
    }
    catch (Exception ex)
    {
        DisplayException(ex.Message);
    }
}

  3. Add the following method to MapUserControl.cs. This method calls the ShowPopupInfoBox method in MapControl.html and passes the integer value of the shape index to display the InfoBox.

    // Invokes the JavaScript method for the display InfoBox of the shape.

public void ShowPopupInfoBox(int rowIndex)
{
    webBrowserMap.Document.InvokeScript("ShowPopupInfoBox", new object[] { rowIndex });
}

To hide or show the InfoBox, copy the following code to the MapControl.html file.

function ShowPopupInfoBox(shapeIndex)
{
    map.HideInfoBox();
    var shapeID = new String(shapeIDs[shapeIndex - 1]);
   
    var shape = map.GetShapeByID(shapeID);
    
    map.ShowInfoBox(shape);
}

// Hides the InfoBox.
function HidePopupInfoBox()
{
    map.HideInfoBox();
}

Display the Sales Details from InfoBox of the Shape

To display the details form (when the user clicks the View Details image in the InfoBox), add an anchor and an image tag to the custom HTML string. When a user clicks the image, a custom JavaScript function calls the method on the host by using windows.external.

Here is a sample HTML string.

a href='#' onClick='javascript:ShowDetails({2},{3});' >
<img src='view.details.png' style='border-width:0px' /></a>
NoteNote

The shape index number replaces the placeholder {2}. The report type for displaying the details form replaces the placeholder {3}.

To invoke the host application method, add a JavaScript method to the HTML file.

function ShowDetails(index, reportType)
{
    window.external.ShowDetails(index, reportType);
}
NoteNote

This solution uses Microsoft Windows forms to display sales details. For more information about the details forms, download the sample files: 2007 Office Sample: Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio.

Next, create a Windows form to display sales person details.

To create a Windows form

  1. To add a user control, on the Project menu, click Add New Item.

  2. In the Add New Item dialog box, select Windows Form.

  3. Change the name of the new User Control to SalesPersonDetails.cs, and then click Add.

  4. To add controls to the form designer, if the designer is not already displayed, in Solution Explorer, right-click SalesPersonDetails.cs, and then select View Designer.

  5. On the View menu, click Toolbox.

  6. On the Common Controls tab of the toolbox, add the controls shown in Figure 8.

    Figure 8. Salesperson details

    Salesperson details

  7. Set the properties as described in Table 2.

    Table 2. Windows forms controls for the Sales Person details form

    Control type

    Name property

    Text property

    Access modifiers Property

    PictureBox

    pbImage

     

    Public

    Label

    lblName

     

    Public

    TextBox

    txtJobTitle

     

    Public

    TextBox

    txtPhone

     

    Public

    TextBox

    txtEmail

     

    Public

    TextBox

    txtAddress

     

    Public

    TextBox

    txtCity

     

    Public

    TextBox

    txtSalesYTD

     

    Public

    TextBox

    txtSalesLast

     

    Public

    Label

    lblJobTitle

    Job Title

    Private

    Label

    lblPhone

    Phone

    Private

    Label

    lblEmail

    Email

    Private

    Label

    lblAddress

    Address

    Private

    Label

    lblCity

    City

    Private

    Label

    lblSalesYTD

    Sales YTD

    Private

    Label

    lblSalesLast

    Sales Last

    Private

    Button

    btnClose

    Close

    Private

  8. To display details about the sales person, open MapUserControl.cs, and add the following code.

    DataTable _dataTable = null;

public void AssignDetailTable(DataTable dt)
{
    _dataTable = dt;
}

// Displays the details form.
public void ShowDetails(int dataRowIndex, string reportType)
{
    switch (reportType)
    {
        case Constants.SalesReport.SALES_PERSON:
            ShowSalesPersonDetails(dataRowIndex);
            break;
    }
}

// Displays the Sales Person details form.
public void ShowSalesPersonDetails(int dataRowIndex)
{
    DataRow dr = _dataTable.Rows[dataRowIndex];

    SalesPersonDetails salesPerson = new SalesPersonDetails();

    salesPerson.lblName.Text = dr["PersonName"].ToString();
    salesPerson.txtJobTitle.Text = dr["JobTitle"].ToString();
    salesPerson.txtPhone.Text = dr["Phone"].ToString();
    salesPerson.txtEmail.Text = dr["EmailAddress"].ToString();
    salesPerson.txtAddress.Text = dr["Address"].ToString();
    salesPerson.txtCity.Text = dr["City"].ToString();
    salesPerson.txtSalesYTD.Text = "$ " + dr["SalesYTD"].ToString();
    salesPerson.txtSalesLast.Text = "$ " + dr["SalesLastYear"].ToString();

    string imagePath = _assemblyPath + Constants.AddInProperty.IMAGE_FOLDER_PATH + dr["Image"].ToString();
    salesPerson.pbImage.Load(imagePath);

    salesPerson.Show();
    salesPerson.Activate();
}

  9. Open SalesRibbon.cs and remove the comments from the following line in the CreateReport method.

    mapUserControl.AssignDetailTable(dataTable);

Running the Application

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

To run the application

  1. Press F5 to build and run the 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.

  4. Move the mouse over the pushpin to get a customized InfoBox.

  5. Click View Details to display the Sales Person Details form.

Conclusion

Integrating Virtual Earth Maps and Excel 2007 Using Visual Studio 2005 Tools for Office Second Edition (Part 1 of 2) demonstrated how to use the Virtual Earth map control and display basic pushpins on the map in the custom task pane of the 2007 Microsoft Office Excel.

This article demonstrated how to format data on the Office Excel worksheet by using Excel automation, create Excel charts and customize pushpin shapes and InfoBoxes, and further enhance the application by using Virtual Earth 6.0 APIs.

About the Author

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.

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: