DevLost

This article is available also on SilverlightShow:
http://www.silverlightshow.net/items/How-to-extend-Bing-Maps-Silverlight-with-an-elevation-profile-graph-ndash-Part-1.aspx

Introduction

One of the things I found missing in the current Bing Maps product is the possibility to create an elevation surface profile of routes. Perhaps this feature may not seem much on demand but actually affects more people than expected. Think for example about sports events like marathons and cycling races: to see a preview of the elevation profile of the trail would be of great benefit to the participants. But even if you're just simple hikers you might want to know what is the difference in level of your walking or bicycle trip to better understand the effort that it would entail.
So why not try to create this feature from scratch using the Bing Maps Silverlight Control? In this first part we will see how to extend the Bing Maps Silverlight Control by adding new commands to the navigation bar, how to create a route based on a start and end address or by clicking directly on the map and how to obtain elevation data of the route. In the second part we will briefly deal with details about the implementation via threads of the elevation data retrieval and we will show how to add some interactivity to the Chart control of the Silverlight toolkit.
I assume that the reader is already familiar with the basics related to the use of Bing Maps in Silverlight. If not so, I would like to suggest reading the first chapters of the SDK guide or at least the "Getting started" paragraph of the following interesting article already published on SilverlightShow.
In the second part a link to the live demo and to the source code will be made available; in the meantime you can watch a video here of an early beta.

The User Interface

What I wanted to do was to add this type of functionality to the Bing Maps Silverlight Control integrated with the existing interface. With this aim in mind I added 2 items to the navigation bar of the Bing Maps Silverlight Control as you can see on the following image, where the items “Route Profile“ and “Arbitrary Profile” have been added.

A click on “Route Profile” item opens a popup window which allows choosing 2 options:

• Building the route by inserting pushpins on the map
• Building the route providing a start address and an end address

With the first option you can put a series of pushpins on the map with a simple click of the mouse on the map; a double click starts the route calculation and, after that, the generation of the elevation profile graph. The second option considers first the geocoding of the start and end address of our route and then proceeds in the same way as the first option.
A click on “Arbitrary Profile”, instead, allows you to draw polylines directly on the map; even here a double click of the mouse triggers the generation of the route and the subsequent creation of the elevation profile graph.

The graph offers some kind of interactivity in the way that you can select and move 2 cursors over the points of the profile and see some conclusive data like the average elevation with regard to the portion of the profile between the two cursors.

Main points of interest

An interesting element is the description of how it was possible to add new commands to the navigation bar of the Bing Maps Silverlight Control without giving the impression of a last minute addition but of a natural integration. Another point which might be of interest for the reader is the retrieval of the elevation data and the subsequent creation of the profile graph; during the process the user is allowed to interact with the map thanks to the use of threads. Finally, the possibility to interact with the Chart control can offer some ideas for further improvements.

How to How to add items to the navigation bar of the Bing Maps Silverlight Control

The first thing I thought of doing to achieve this objective was to understand how the navigation bar was made. Obviously to better understand that I needed to see the XAML code which generates the navigation bar; but, how to get the XAML code? The setup of the Bing Maps Silverlight Control SDK (which can be downloaded at the following link ) installs two DLL files on your pc : “Microsoft.Maps.MapControl.dll” and “Microsoft.Maps.MapControl.Common.dll”. They contain all the assemblies needed in order to use the Silverlight Bing Maps Control in your applications. While I was looking inside the installation folders in order to find an inspiration at some point I remembered reading somewhere on the web that one could just open the DLL in a text editor and see the XAML code in plaintext. Indeed, if you open the "Microsoft.Maps.MapControl.dll" file and scroll down the content, at a certain point you see a large portion of XAML code and there is where you have to investigate. If you look at the code below which is the part describing how it is organized the superior portion of the navigation bar:

 <StackPanel Grid.Row="0" Grid.Column="3" Orientation="Horizontal" x:Name="HorizontalPanel">

     <StackPanel Orientation="Horizontal" x:Name="HorizontalLeftPanel">

         <navigation:CommandRadioButton x:Name="RoadStyleButton"  />

         <navigation:CommandRadioButton x:Name="AerialStyleButton"  />

     </StackPanel>

     <navigation:CommandSeparator />

     <StackPanel Orientation="Horizontal" x:Name="HorizontalRightPanel">

         <navigation:CommandToggleButton x:Name="LabelsButton" />

     </StackPanel>

 </StackPanel>

You see that there is a StackPanel (called “HorizontalPanel”) which contains two other StackPanels called respectively “HorizontalLeftPanel” and “HorizontalRightPanel”. Inside them, there are the buttons you are used to clicking when you interact with the Bing Maps Silverlight Control, i.e. “Road”, “Aerial” and “Labels”.

So, if you want to add two other commands as in the first image above, in principle you just have to create two instances of the type CommandRadioButton (which is in the assembly Microsoft.Maps.MapControl.Navigation) and add them to the “HorizontalPanel” as children controls. But the reality is somewhat more complicated than just described. In order to add these controls you have to wait for the completion of the initialization phase of some components. This phase, without going too much over details, can be regarded as made up of 2 steps. A first step where the ForegroundMap Control is loaded; this is the control which displays amongst other the map navigation bar on the map. A second step where the navigation bar is loaded. Only after these two phases you can add your controls. In practical terms first you have to add an EventHandler to the TemplateApplied EventHandler of the ForegroundMap Control. When the event is triggered it means that the NavigationBar has been instantiated and you can add another EventHandler this time to the TemplateApplied EventHandler of the NavigationBar. Once this last event is triggered you can add your controls to the NavigationBar. The portion of code below realizes what I have just described.

 ...

 private CommandRadioButton routeBtn = new CommandRadioButton();

 private CommandRadioButton freeProfileBtn = new CommandRadioButton();

 ...

 public MainPage()

 {

     InitializeComponent();

     ...

     ElevationChartMap.MapForeground.TemplateApplied += new EventHandler(MapForeground_TemplateApplied);

     ...

 }

 void MapForeground_TemplateApplied(object sender, EventArgs e)

 {

     ElevationChartMap.MapForeground.NavigationBar.TemplateApplied += new EventHandler(NavigationBar_TemplateApplied);

 }

 void NavigationBar_TemplateApplied(object sender, EventArgs e)

 {

      NavigationBar navControl = ElevationChartMap.MapForeground.NavigationBar;

      navControl.HorizontalPanel.Children.Add(routeBtn);

      navControl.HorizontalPanel.Children.Add(freeProfileBtn);

 }

Creating the route with the “Route Profile” option

For the creation of the route either giving start and end address or inserting some markers on the map I used part of the code samples available on Keith Kinnan’s Blog and presented during the PDC09. I don’t want to repeat what has been already very well explained at the links above so I will stick to a brief description.
First of all, you have to create two service references, the Geocode service and the Route service:

https://staging.dev.virtualearth.net/webservices/v1/routeservice/routeservice.svc?wsdl

https://staging.dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl

Don’t forget the final postfix “?wsdl” which is missing in the related paragraph (“Calculating a Route Using Bing Maps Web Services “) of the SDK Guide version 1.01.
With the first Service you obtain the coordinates of the user location inputs; these coordinates can be then provided to the Route Service to calculate the route. Since Silverlight uses an asynchronous model all the requests to the Services above have to be asynchronous. For instance, after you inserted the start and end address of your route, two asynchronous calls to the Geocode Service are made. To catch them, an EventHandler to the GeocodeCompleted event has been added. Inside this EventHandler we keep track of how many times it was crossed; if it has been twice it means that both start and end address have been elaborated and then it is possible to make the request to the Route Service, where another EventHandler has been added to intercept the CalculateRouteCompleted event. In this EventHandler the result of the calculation is checked and if it is Ok the Polyline representing the route is drawn. In particular an object of type MapPolyline is instantiated and the points in geographic coordinates resulting from the calculation are added to its "Locations" property. This object is then added to a MapLayer which represents a layer over the map and allows to position UI elements according to geographic coordinates. The code below illustrates what I have described.

 double dist = e.Result.Result.Summary.Distance;

 Color routeColor = Colors.Blue;

 SolidColorBrush routeBrush = new SolidColorBrush(routeColor);

 //Add the Polyline for the route

 MapPolyline routeLine = new MapPolyline();

 routeLine.Locations = new LocationCollection();

 routeLine.Stroke = routeBrush;

 routeLine.Opacity = 0.65;

 routeLine.StrokeThickness = 5.0;

 foreach (Location loc in e.Result.Result.RoutePath.Points)

 {

     routeLine.Locations.Add(loc);

 }

 profileLayer.Children.Add(routeLine);

“Arbitrary Profile” option: how to allow the user to draw lines directly on the map

The “Arbitrary Profile” option gives the user the chance to draw a completely arbitrary polyline with subsequent mouse clicks; you can either follow the profile of a road or click on whatever you want. A double click ends the drawing mode and launches the generation of the elevation profile graph. To do that two EventHandlers have been added to the Map Control in order to intercept the mouse click and double click on the map. In case of a single click, the ViewportPoint of the MouseEventArgs object is evaluated and the result is added to the Locations Collection of a MapPolyline previously added as a child UI element to the same MapLayer used for route showing. 

 ...

 private MapPolyline arbitraryProfilePolyline;

 ...

 void ElevationChartMap_MouseClick(object sender, MapMouseEventArgs e)

   {

      if (myProfileModeMap.EnableArbitraryProfileMode == true)

      {

          arbitraryProfilePolyline.Locations.Add(ElevationChartMap.ViewportPointToLocation(e.ViewportPoint));

      }

      ...

Summary

In this first part we have explored some “internals” of the navigation bar of the Bing Maps Silverlight Control and we have given some advice on how to create routes using Geocoding and Route Services and on how to draw lines on the Map and obtain the geographics coordinates of its points. In the next article we will see how to obtain elevation data for the routes and the arbitrary paths we created and how to customize the Chart Control of the Silverlight Toolkit for the needs of this specific application. You will be also able to try a live demo and download the source code.

This article is available also on SilverlightShow:
http://www.silverlightshow.net/items/An-Excel-file-Viewer-in-Silverlight-4.aspx

Introduction

Let’s imagine a scenario in which you have a series of Excel files (.xlsx) on your computer but you don’t have any Microsoft Office installed. What can we do if we want to examine the content of these files?
In this article we will describe a small Silverlight application allowing you to open and view these files and also create line series graphs. I used this exercise as laboratory to familiarize myself with some of the new features of Silverlight like, for instance, drag&drop of files from local folders to the application, drag&drop between controls in the application, right click event handling, theming and so on. I also used the Chart control from the Silverlight toolkit and I extended it by adding zoom +, zoom – and the zoom window functionality. Obviously this application is not intended as a complete and stable product. In fact, it is limited to the Excel files, which are not too big and complicated, and it is at an early stage, not bug free - nevertheless I hope that it can be a decent example capable of showing what can we do with Silverlight 4 and a good starting point for someone who wants to extend it and improve it.
Here you can watch a video showing how to use the Silverlight application and here you can try it by yourself. Here you can download the source code.

The User Interface

The UI is very simple and it is made of two columns; the tight column on the left contains a TreeView Control and the larger column in the centre contains a Tab Control. You can interact with the interface at the beginning by simply dragging the Excel files from your local folder to the TreeView. Consequently, the TreeView will be populated with a series of items and sub-items corresponding respectively to the name of the files and the name of their sheets. A click on one of these items or sub-items causes the creation of a Tab Control on the central area with a number of tabs equalling the number of sheets in the xlsx file selected.

A DataGrid overtopped by a Chart and an Expander Control are put on each tab. The DataGrid is filled up with the data contained in the sheet selected, assuming that the first row contains the headers of the columns (I recognize this is a very rough simplification). At this stage you can either build a graph using the options contained in the Expander Control (i.e. choosing the independent column and the dependent column and then pressing the button “Build”) or drag & drop items of the rows selected from the Datagrid to the Chart. Actually, this last operation is a bit tricky due to the standard behaviour of the Datagrid: you have to click on the first row of the selection you want and, keeping pressed the shift key, click again on the final row of your selection and finally without releasing the left mouse button drag the selection just created and drop it on top of the Chart. The Chart will be populated with all the data suitable found in the selection.
The Chart Control has been extended with some additional functionalities; a right click on the Chart area shows a contextual menu with the already mentioned features (zoom +, zoom – and zoom window ) ; the icon cursor changes accordingly to your choice.

Main points of interest

Surely the first problem that I had to solve was the reading of the excel file, then the way in which the data can be put in the Datagrid. Other interesting points are how to manage the drag & drop operations between local folders and the Silverlight application and between controls inside the application. Finally, 2 difficult points were, on the one hand, the transformation of the data dragged from the Datagrid to an observable Collection compatible with the Chart control and, on the other hand, the implementation of the zooming functionalities.

How to read the Excel (*.xlsx) files

A good starting point is a series of great articles from rmaclean’s blog which couldn’t explain better how to work with the xlsx Excel 2007 native file format. Essentially a .xlsx file is composed of a zip file and a series of xml files containing information on what is inside the zip. You can refer to these articles for further investigation. I based my implementation of these hints starting with a public Interface which exposes the following methods:

public interface IFileDropFormat
      {
          string GetFilename();
          List<string> GetListSubItems();
          IEnumerable<IDictionary> GetData(string itemSelected);
      }

The first method does not need any explanation, the second instead (GetListSubItems() ) let’s presume that the generic file dropped can be composed of subitems and the third suggests that we should be able to get an Ienumerable<IDictionary> of the data in the file for each sub-item considered. The structure of an xlsx file follows this scheme: each sheet is a sub-item containing data.
Upon this Interface I built a class (XLSXReader) to read the Excel files. The method GetData(…) is implemented as follows:

public IEnumerable<IDictionary> GetData(string itemSelected)
  {
      int worksheetIdex = GetWorksheetIndex(itemSelected);
  
      if (worksheetIdex <= 0)
          yield break;
 
      XElement wsSelectedElement = GetWorksheet(worksheetIdex);
      if (wsSelectedElement == null)
          yield break;
      IEnumerable<XElement> rowsExcel = from row in wsSelectedElement.Descendants(XLSXReader.excelNamespace + "row")
                                       select row  ;
      if (rowsExcel == null)
          yield break;
  
      foreach (XElement row in rowsExcel)
      {
          var dict = new Dictionary<string, object>();
          IEnumerable<XElement> cellsRow = row.Elements(XLSXReader.excelNamespace + "c");
          foreach (XElement cell in cellsRow)
          {
              if (cell.HasElements == true)
              {
                  string cellValue = cell.Element(XLSXReader.excelNamespace + "v").Value;
                  if (cell.Attribute("t") != null)
                  {
                      if (cell.Attribute("t").Value == "s")
                      {
                          cellValue = sharedStrings[Convert.ToInt32(cellValue)];
                      }
                  }
  
                  dict[cell.Attribute("r").Value.Substring(0, 1)] = cellValue as Object;
              }
          }
          yield return dict;
      }        
  }

In the code portion above, firstly we obtain an XElement representing the worksheet selected and then we extract an IEnumerable(rowsExcel ) in order to walk all the rows in the worksheet and create the IEnumerable<IDictionary> requested.

Binding to Datagrid

To bind the IEnumerable<Dictionary> to the Datagrid I drew inspiration from this article which suggests using the class provided by Vladimir Bodurow in his blog. Essentially this class transforms each dictionary key into a property of anonymous typed object.

In the following portion of code we proceed adding the columns to the Datagrid assuming (as we already outlined in the introduction) that the first row of data in the Excel file contains the header columns name; then we transform the IEnumerable<IDictionary> to a datasource using the class above mentioned and skipping the first item since it has already been used for the headers. The datasource is finally bound to the ItemSource property to the Datasource.

public void UpdateMainGrid()
  {
      if (MainGridElement == null)
          return;
  
      IEnumerable<IDictionary> datasource = FileDropped.GetData(ItemSelected);
  
      MainGridElement.Columns.Clear();
  
      if (datasource.Count() == 0)
      {
          MainGridElement.Columns.Add(
              new DataGridTextColumn
              {
                  Header = "There are no items!",
                  Binding = new Binding("")
              });
          MainGridElement.ItemsSource = "";
  
          return;
      }
  
      // create columns
      IDictionary firstRow = null;
  
      try{
          firstRow = datasource.First();
      }
      catch{
          return;
      }
  
      foreach (DictionaryEntry pair in firstRow)
      {
          MainGridElement.Columns.Add(
              new DataGridTextColumn
              {
                  Header = pair.Value,
                  Binding = new Binding(pair.Key.ToString())
              });
      }
      // we assume that the first column contains the headers of the columns
      MainGridElement.ItemsSource = datasource.Skip(1).ToDataSource();
  }

Drag & Drop operations

The first Drag & Drop considered is between a local folder and the TreeView Control; first of all we need to set to true the AllowDrop property of the TreeView and then manage the event concerned. This is carried out by the Method ManageDrop(..) here below:

public static void ManageDrop(object sender, RoutedEventArgs e)
  {
      DragEventArgs dr = e as DragEventArgs;
      string objectName = GetSenderName(sender);
  
      if (dr.Data == null)
          return;
  
      IDataObject dataObject = dr.Data as IDataObject;
      FileInfo[] files = dataObject.GetData(DataFormats.FileDrop) as FileInfo[];
  
      foreach (FileInfo file in files)
      {
          // open Excel file
          if (file.Name.EndsWith("xlsx"))
          {
              XLSXReader xlsxReader = new XLSXReader(file);
  
              List<string> subItems = xlsxReader.GetListSubItems();
             
              if (droppedFiles == null)
                  droppedFiles = new Dictionary<string, IFileDropFormat>();
              if (droppedFiles.ContainsKey(file.Name) == false)
                  droppedFiles.Add(file.Name, xlsxReader);
          }
      }
      MainPage myPage = App.Current.RootVisual as MainPage;
      foreach (KeyValuePair<string, IFileDropFormat> keyValuePair in droppedFiles)
      {
          if (myPage != null)
          {
              myPage.dfvFilesList.AddItems(keyValuePair.Value.GetListSubItems(), keyValuePair.Key);
         }
      }
  }

The second drag & drop between the Datagrid and the Chart Control is more interesting and the obstacles that we had to face were pretty challenging. Firstly in the generic.xaml file of the main project from one side and in the generic.xaml of the library containing the extended version of the Chart, we surrounded the two controls with respectively a DataGridDragDropTarget and a DataPointSeriesDragDropTarget control. The two controls are available in the Silverlight Toolkit and add all the necessary support to this kind of tasks.

<!-- Datagrid with drag&drop support -->
  <ControlTemplate TargetType="dfv:dfvDatagrid">
       <my3:DataGridDragDropTarget x:Name="MainGridDragDropElement" HorizontalAlignment="Stretch">
           <my2:DataGrid x:Name="MainGridElement"  AllowDrop="True" HorizontalScrollBarVisibility="Auto" VerticalScrollBarVisibility="Visible" AutoGenerateColumns="False"  HorizontalAlignment="Stretch"/>
       </my3:DataGridDragDropTarget>
   </ControlTemplate>
  
  <!-- Chart with drag&drop support -->
  <toolkitDD:DataPointSeriesDragDropTarget x:Name="MainChartDragDropElement" AllowDrop="true" HorizontalContentAlignment="Stretch" VerticalContentAlignment="Stretch">
       <toolkitDD:Chart AllowDrop="True"   x:Name="MainChartElement"/>
  </toolkitDD:DataPointSeriesDragDropTarget>

Then, obviously we had to set to true the property AllowDrop on both the controls. In the codebehind we added the MainChartDragDrop_doDrop event to the DragEventHandler of the DataPointSeriesDragDropTarget control:

private void MainChartDragDrop_doDrop(object sender, Microsoft.Windows.DragEventArgs e)
  {
      if (e.Data == null)
          return;
  
      object drArgs = e.Data.GetData("System.Windows.Controls.ItemDragEventArgs");
  
      if (drArgs == null)
          return;
  
      ItemDragEventArgs dataDropped = drArgs as ItemDragEventArgs;
  
      if (dataDropped == null)
          return;
   
      SelectionCollection selectedData = dataDropped.Data as SelectionCollection;
  
      if (selectedData == null)
          return;
  
      BuildGraphFromDrop(selectedData);
  }

With this method we make a series of steps in order to come to a Collection of objects of type SelectionCollection. A SelectionCollection is actually the way in which the DragDropTarget controls store the objects selected. What we need to do now is transform this SelectionCollection into a ObservableCollection linked to the Chart. This is done in the BuildGraphFromDrop(…) method.