Posted by: Karlo Bartels | 2010/11/04

Excel Spatial Visualizer Add-In

Excel Spatial Visualizer

Inspired by the Excel Spatial Spreadsheet blog articles by Olivier Meyer, I decided to create an add-in that provides functionality to visualize spatial (geographic) data in Excel. The screenshot on the right displays the end result.

 

Spatial analysis

The aforementioned articles describe how to build an Excel add-in to perform spatial analysis in .NET. An Excel user could enter a formula like:

=PARSEANDLOCATE("One Microsoft Way, Redmond WA")

and Excel would in turn return the coordinates of that location

POINT(47.639747 -122.129731)

This allows an Excel user to easily transform addresses into coordinates which can be used as input for spatial analysis scenarios, like finding the region in which a certain product is most often sold.

Displaying spatial objects

To easily facilitate the process of displaying spatial objects in Excel, I created an Excel add-in that allows you to select a range of cells containing coordinates and show them on a map. To do this, the add-in accesses the Bing Maps SOAP Services. This is a set of programmable SOAP services that allow you to match addresses to a map (and much more). You will need a Bing Maps Application ID to access these services.

To demonstrate, let’s display Mediamarkt stores in The Netherlands on a map. First, open the Excel workbook containing the coordinates (which I geocoded by hand using the store addresses):

xl2010geoaddin-1

Select the type of map (road or aerial) and the type of marker on the Geo Visualizer ribbon and click the Launch Geo Visualizer button to display the Visualizer window.

xl2010geoaddin-2

Click the Range selection button to select the range that contains the coordinates.

xl2010geoaddin-3

Click the expand button on the range selection control to parse the coordinates and display the map with the selected coordinates. Note that you can use the slider on the right of the map to zoom in or out.

xl2010geoaddin-4

You can save the map to disc or copy it to the clipboard by right-clicking the map (or use the toolbar buttons to perform these tasks).

Routes and optional POIs

To display a route on the map, i.e. to draw a line which connects the individual coordinates, click the Draw route button on the ribbon and optionally select the route color and route width.

xl2010geoaddin-5

The Draw route option treats all coordinates in the selected range as both route markers and route segments. The screenshot below illustrates this.

xl2010geoaddin-6

In some situations (like the one shown above) this is not the desired output; simply add a third column to the coordinate range to specify how to treat the coordinate, either as a POI or as part of a route. Enter TRUE in the cell of the third column to treat that location as POI, or FALSE to treat it as part of a route. The next screenshot shows the proper output of my city tour in Paris.

xl2010geoaddin-7

Map options

The map settings dialog allows you to fine-tune the map and its POI.

xl2010geoaddin-8

Use the Offset X and Offset Y properties to correct the position of the POI markers. Due to the shape of the POI marker it sometimes looks like it is placed incorrectly. Change the value of the Offset X and Offset Y properties to correct the placement of the markers on the map in either horizontal or vertical direction.

The Map options section offers additional customization and usability options.

The code

The add-in was created by using the Excel 2010 Add-in template in Visual Studio, which I extended with my own classes and a Bing Maps Service Reference that retrieves the map images. The next image shows the solution structure in Class View.

xl2010geoaddin-9

Note that I used the VS.NET Refedit control in my add-in project which allows easy Excel range selection from a Windows Form.

The following classes are the workhorses of the application:

  • BingMaps – This class interacts with the Bing Maps SOAP Services. It creates the necessary WCF service clients, retrieves map data and adds points of interest and routes to the map image.
  • Map – This is an Excel Bing Maps wrapper class that abstracts the Bing Maps code away from the Excel add-in by providing a simple GetMap() method. Includes an Excel Range parser method that converts cells in an Excel Range object into Coordinate objects. This class also contains a local map cache.
  • Shared – Includes generic bitmap functions like downloading image data and loading byte arrays into Bitmap objects.
  • Visualizer – main Visualizer window.

The biggest challenge in programming this add-in was converting geographic coordinates (from degrees) into pixel coordinates (x,y). This is necessary to determine the proper location of the POI icons and route segments on the map Bitmap object. Refer to this Bing Maps Tile System article for an overview of the necessary formulas.

Based on that article, I created the LatLongToPixel method that accepts a coordinate, the size of the map image, the current zoom level and map center point. It returns a Point object that contains the pixel coordinate of the passed coordinate:

/// <summary>
/// Converts a Latitude/Longitude coordinate to a pixel 
    coordinate of a location based on the current map view.
/// </summary>
/// <param name="latlong">The coordinate to convert.</param>
/// <param name="view">A BestView object of the current view.</>
/// <param name="mapWidth">The width of the map image.</param>
/// <param name="mapHeight">The height of the map image.</param>
/// <returns>A Point object containing the pixel coordinate of 
    the input location.</returns>
 
private Point LatLongToPixel(Location latlong, BestView view,
                             int mapWidth, int mapHeight)
{
  // Calcuate pixel coordinates of map center point
  double sinLatitudeCenter =
         Math.Sin(view.Center.Latitude * Math.PI / 180);
  double pixelXCenter = ((view.Center.Longitude + 180) / 360)
         * 256 * Math.Pow(2, view.ZoomLevel);
  double pixelYCenter = (0.5 - Math.Log((1 + sinLatitudeCenter)
         / (1 - sinLatitudeCenter)) / (4 * Math.PI))
         * 256 * Math.Pow(2, view.ZoomLevel);

  // Calculate pixel coordinate of location
  double sinLatitude =
         Math.Sin(latlong.Latitude * Math.PI / 180);
  double pixelX = ((latlong.Longitude + 180) / 360)
         * 256 * Math.Pow(2, view.ZoomLevel);
  double pixelY = (0.5 - Math.Log((1 + sinLatitude)
         / (1 - sinLatitude)) / (4 * Math.PI))
         * 256 * Math.Pow(2, view.ZoomLevel);

  // Calculate top left corner pixel coordiates of map image
  double topLeftPixelX = pixelXCenter - (mapWidth / 2);
  double topLeftPixelY = pixelYCenter - (mapHeight / 2);

  // Calculate relative pixel cooridnates of location
  double x = pixelX - topLeftPixelX;
  double y = pixelY - topLeftPixelY;
  return new Point((int)Math.Floor(x), (int)Math.Floor(y));
}

Getting started

Start by downloading the code (see below for download link) and build the solution. The add-in is written in C# (.NET version 4.0) and is intended to be used with Excel 2010. To build the code you should use Visual Studio 2010.

To install the add-in, double-click the BartelsOnline.GeoAddIn.vsto deployment manifest file. If the installation was successful, the Geo Visualizer ribbon will appear the next time you start Excel.

Click the Settings button on the Geo Visualizer ribbon and enter your Bing Maps Application ID in the Bing Maps section. Click OK and you are ready to start visualizing!

xl2010geoaddin-10

Download

Download source code (includes sample spatial data Excel workbook)

Advertisements

Responses

  1. […] out his blog/article here (source and examples […]

  2. Hi Karlo

    I am not a programmer and I do not know how to add-in your excel spatial visulizer add-in in to my excel 2010. I have already registered for bing maps application id. what are the applications that i need or a step by step method for me to get this add-in into my excel 2010.

    Hope to hear from you soon.

    Thanks.

    Kind regards
    Aaron

  3. Hi Aaron,
    Please read the section called “Getting started” at the bottom of the article for instructions on how to install the add-in.
    Regards,
    Karlo

  4. Hi Karlo

    I downloaded the source code and it sits in my documents file as a zip file. I opened it and looked for the geoaddin.vsto file. Is the manifest file in the v1.0/source/GeoAddIn.sln? When I click on it, it says that I must have Visual Studio 2010 but I do not have this software.
    I tried using Excel 2010 Add-In function but could not find the Add-In file.

    You mentioned that “To install the add-in, double-click the BartelsOnline.GeoAddIn.vsto deployment manifest file. If the installation was successful, the Geo Visualizer ribbon will appear the next time you start Excel” Where can i get the BartelsOnline.GeoAddIn.vsto manifest file? It looks like the installation was not successful as I did not see the ribbon.

    Any suggestions?

    Thanks.

    Kind regards
    Aaron

  5. Hi Karlo

    I managed to install the add-in already. I keep getting this error “Credentials are either invalid or unspecified” after I select the MediaMrkt coordinates based on the example that you have given.

    I have also keyed in my account Id. Any ideas?

    Thanks.

    Kind regards
    Aaron

  6. Hi Aaron, to be able to retrieve a map from Bing the code must validate you as a Bing Maps user. It does so by sending your unique Bing Maps key to the Bing service. The Bing service in turn checks the credentials to make sure they are valid. If that is not the case you receive an error message like the one you stated above. Please make sure you enter the Bing Maps key in the options dialog in exactly the same way you received it when applying for it. Make sure you enter all its characters. Also check for trailing spaces.
    Regards,
    Karlo

  7. Hi Karlo,

    This is simply amazing what you have done.
    I was wondering would it be possible to add names/labels for the points on map?

    Regards,
    Simon

  8. Sure, you could use custom pushpins or use the default Microsoft ones. You could extend the GetMap method to draw labels and other items through the System.Drawing namespace (DrawLine, DrawRectangle, DrawString, etc.)

  9. I am trying to install the add in in windows 7 and windows 8 with office 2010. When I run bartelsonline.geoaddin.vsto I receive the same error: Customized funcionality in this application will not work because the certificate used to sign the deployment manifest for Bartelsonline.geoaddin or its location is not trusted. Contact your administrator. Thanks for sharing and will you be so kind as to help me. A little late from the time you posted it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: