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 4 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:
1: public interface IFileDropFormat
2: {
3: string GetFilename();
4: List<string> GetListSubItems();
5: IEnumerable<IDictionary> GetData(string itemSelected);
6: }
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:
1: public IEnumerable<IDictionary> GetData(string itemSelected)
2: {
3: int worksheetIdex = GetWorksheetIndex(itemSelected);
4:
5: if (worksheetIdex <= 0)
6: yield break;
7:
8: XElement wsSelectedElement = GetWorksheet(worksheetIdex);
9: if (wsSelectedElement == null)
10: yield break;
11:
12: IEnumerable<XElement> rowsExcel = from row in wsSelectedElement.Descendants(XLSXReader.excelNamespace + "row")
13: select row ;
14:
15: if (rowsExcel == null)
16: yield break;
17:
18: foreach (XElement row in rowsExcel)
19: {
20: var dict = new Dictionary<string, object>();
21: IEnumerable<XElement> cellsRow = row.Elements(XLSXReader.excelNamespace + "c");
22: foreach (XElement cell in cellsRow)
23: {
24: if (cell.HasElements == true)
25: {
26: string cellValue = cell.Element(XLSXReader.excelNamespace + "v").Value;
27: if (cell.Attribute("t") != null)
28: {
29: if (cell.Attribute("t").Value == "s")
30: {
31: cellValue = sharedStrings[Convert.ToInt32(cellValue)];
32: }
33: }
34:
35: dict[cell.Attribute("r").Value.Substring(0, 1)] = cellValue as Object;
36: }
37: }
38: yield return dict;
39: }
40: }
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.
1: public void UpdateMainGrid()
2: {
3: if (MainGridElement == null)
4: return;
5:
6: IEnumerable<IDictionary> datasource = FileDropped.GetData(ItemSelected);
7:
8: MainGridElement.Columns.Clear();
9:
10: if (datasource.Count() == 0)
11: {
12: MainGridElement.Columns.Add(
13: new DataGridTextColumn
14: {
15: Header = "There are no items!",
16: Binding = new Binding("")
17: });
18: MainGridElement.ItemsSource = "";
19:
20: return;
21: }
22:
23: // create columns
24: IDictionary firstRow = null;
25:
26: try{
27: firstRow = datasource.First();
28: }
29: catch{
30: return;
31: }
32:
33: foreach (DictionaryEntry pair in firstRow)
34: {
35: MainGridElement.Columns.Add(
36: new DataGridTextColumn
37: {
38: Header = pair.Value,
39: Binding = new Binding(pair.Key.ToString())
40: });
41: }
42: // we assume that the first column contains the headers of the columns
43: MainGridElement.ItemsSource = datasource.Skip(1).ToDataSource();
44: }
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:
1: public static void ManageDrop(object sender, RoutedEventArgs e)
2: {
3: DragEventArgs dr = e as DragEventArgs;
4: string objectName = GetSenderName(sender);
5:
6: if (dr.Data == null)
7: return;
8:
9: IDataObject dataObject = dr.Data as IDataObject;
10: FileInfo[] files = dataObject.GetData(DataFormats.FileDrop) as FileInfo[];
11:
12: foreach (FileInfo file in files)
13: {
14: // open Excel file
15: if (file.Name.EndsWith("xlsx"))
16: {
17: XLSXReader xlsxReader = new XLSXReader(file);
18:
19: List<string> subItems = xlsxReader.GetListSubItems();
20:
21: if (droppedFiles == null)
22: droppedFiles = new Dictionary<string, IFileDropFormat>();
23: if (droppedFiles.ContainsKey(file.Name) == false)
24: droppedFiles.Add(file.Name, xlsxReader);
25: }
26: }
27: MainPage myPage = App.Current.RootVisual as MainPage;
28: foreach (KeyValuePair<string, IFileDropFormat> keyValuePair in droppedFiles)
29: {
30: if (myPage != null)
31: {
32: myPage.dfvFilesList.AddItems(keyValuePair.Value.GetListSubItems(), keyValuePair.Key);
33: }
34: }
35: }
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.
1: <!-- Datagrid with drag&drop support -->
2: <ControlTemplate TargetType="dfv:dfvDatagrid">
3: <my3:DataGridDragDropTarget x:Name="MainGridDragDropElement" HorizontalAlignment="Stretch">
4: <my2:DataGrid x:Name="MainGridElement" AllowDrop="True" HorizontalScrollBarVisibility="Auto" VerticalScrollBarVisibility="Visible" AutoGenerateColumns="False" HorizontalAlignment="Stretch"/>
5: </my3:DataGridDragDropTarget>
6: </ControlTemplate>
7:
8: <!-- Chart with drag&drop support -->
9: <toolkitDD:DataPointSeriesDragDropTarget x:Name="MainChartDragDropElement" AllowDrop="true" HorizontalContentAlignment="Stretch" VerticalContentAlignment="Stretch">
10: <toolkitDD:Chart AllowDrop="True" x:Name="MainChartElement"/>
11: </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:
1: private void MainChartDragDrop_doDrop(object sender, Microsoft.Windows.DragEventArgs e)
2: {
3: if (e.Data == null)
4: return;
5:
6: object drArgs = e.Data.GetData("System.Windows.Controls.ItemDragEventArgs");
7:
8: if (drArgs == null)
9: return;
10:
11: ItemDragEventArgs dataDropped = drArgs as ItemDragEventArgs;
12:
13: if (dataDropped == null)
14: return;
15:
16: SelectionCollection selectedData = dataDropped.Data as SelectionCollection;
17:
18: if (selectedData == null)
19: return;
20:
21: BuildGraphFromDrop(selectedData);
22: }
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.
1: private void BuildGraphFromDrop(SelectionCollection droppedData)
2: {
3: IEnumerator<Selection> ieSel = droppedData.GetEnumerator();
4:
5: // let's get the number of series (or columns) included in the data dropped
6: ieSel.MoveNext();
7: PropertyInfo[] propInfo = ieSel.Current.Item.GetType().GetProperties();
8: int columnsNumber = propInfo.Count();
9:
10: for (int i = 0; i < columnsNumber - 1; i++)
11: _dynItemsSource.Add(new ObservableCollection<Pair>());
12:
13: // reset any data linked to the chart
14: foreach (ObservableCollection<Pair> pairCollection in _dynItemsSource)
15: pairCollection.Clear();
16:
17: object _first = double.NaN;
18: object _second = double.NaN;
19: double firstMin, firstMax, secondMin, secondMax;
20: firstMin = firstMax = secondMin = secondMax = double.NaN;
21:
22: // let's navigate through each item of the SelectionCollection (which represents a row in the datagrid)
23: // and fill the observable collection created just before
24: foreach (Selection sel in droppedData)
25: {
26: for (int i = 0; i < columnsNumber; i++)
27: {
28: object cellvalue = sel.Item.GetType().GetProperties()[i].GetValue(sel.Item, null);
29: double dblValue;
30: if (ComparerHelper.TryConvertToDouble(cellvalue, out dblValue))
31: {
32: if (i == 0) // we assume the first column as the independent column
33: {
34: _first = dblValue;
35: firstMin = double.IsNaN(firstMin) ? dblValue : firstMin;
36: firstMin = firstMin > dblValue ? dblValue : firstMin;
37: firstMax = double.IsNaN(firstMax) ? dblValue : firstMax;
38: firstMax = firstMax < dblValue ? dblValue : firstMax;
39: }
40: else
41: {
42: _second = dblValue;
43:
44: secondMin = double.IsNaN(secondMin) ? dblValue : secondMin;
45: secondMin = secondMin > dblValue ? dblValue : secondMin;
46: secondMax = double.IsNaN(secondMax) ? dblValue : secondMax;
47: secondMax = secondMax < dblValue ? dblValue : secondMax;
48:
49: Pair newPairValue = new Pair { First = _first, Second = _second };
50: _dynItemsSource[i - 1].Add(newPairValue);
51: }
52: }
53: else
54: {
55: _first = double.NaN;
56: }
57:
58: }
59: }
60:
61: // Add Axes
62: MainChartElement.Axes.Clear();
63:
64: double rangeX = Math.Abs(firstMax - firstMin);
65: double rangeY = Math.Abs(secondMax - secondMin);
66: IAxis myAxisX = new LinearAxis
67: {
68: Orientation = AxisOrientation.X,
69: Minimum = firstMin - rangeX / 10,
70: Maximum = firstMax + rangeX / 10,
71: ShowGridLines = true
72: };
73: MainChartElement.Axes.Add(myAxisX);
74:
75: IAxis myAxisY = new LinearAxis
76: {
77: Orientation = AxisOrientation.Y,
78: Minimum = secondMin - rangeY / 10,
79: Maximum = secondMax + rangeY / 10,
80: ShowGridLines = true
81: };
82: MainChartElement.Axes.Add(myAxisY);
83:
84: MainChartElement.Series.Clear();
85: int seriesNum = 0;
86: foreach (ObservableCollection<Pair> newPairCollection in _dynItemsSource)
87: {
88: LineSeries mySerie = new LineSeries();
89: mySerie.Name = "Serie" + seriesNum.ToString();
90: mySerie.Title = "";
91: seriesNum += 1;
92:
93: mySerie.ItemsSource = newPairCollection;
94: mySerie.IndependentValueBinding = new Binding("First");
95: mySerie.DependentValueBinding = new Binding("Second");
96:
97: //
98: MainChartElement.Series.Add(mySerie);
99:
100: MainChartElement.Title = FileDropped.GetFilename();
101: MainChartElement.LegendTitle = "Legend";
102: }
103:
104: }
As you may see, first we determine the number of columns included in the selection and then we cycle on each Selection item of the SelectionCollection to populate a List of ObservableCollection objects (_dynItemsSource); this List is defined as follows:
private List<ObservableCollection<Pair>> _dynItemsSource;
The class Pair, which represents a couple of values (the dependent and independent values of a LineSerie) is defined as follow:
1: public class Pair : INotifyPropertyChanged, IComparable
2: {
3:
4: private object _first;
5: private object _second;
6: private static PairSortMethod _sortOrder;
7:
8: /// <summary>
9: /// Gets or sets the first value.
10: /// </summary>
11: public object First
12: {
13: get
14: {
15: return _first;
16: }
17: set
18: {
19: _first = value;
20: OnPropertyChanged("First");
21: }
22: }
23:
24: /// <summary>
25: /// Gets or sets the second value.
26: /// </summary>
27: public object Second
28: {
29: get
30: {
31: return _second;
32: }
33: set
34: {
35: _second = value;
36: OnPropertyChanged("Second");
37: }
38: }
39:
40: /// <summary>
41: /// Implements the INotifyPropertyChanged interface.
42: /// </summary>
43: public event PropertyChangedEventHandler PropertyChanged;
44:
45: /// <summary>
46: /// Fires the PropertyChanged event.
47: /// </summary>
48: /// <param name="propertyName">Name of the property that changed.</param>
49: private void OnPropertyChanged(string propertyName)
50: {
51: PropertyChangedEventHandler handler = PropertyChanged;
52: if (null != handler)
53: {
54: handler.Invoke(this, new PropertyChangedEventArgs(propertyName));
55: }
56: }
57:
58: public static PairSortMethod SortOrder
59: {
60: get { return _sortOrder; }
61: set { _sortOrder = value; }
62: }
63:
64: int IComparable.CompareTo(object obj)
65: {
66: if (obj is Pair)
67: {
68: Pair p2 = (Pair)obj;
69:
70:
71: return ((Double)First).CompareTo(p2.First) + ComparerHelper.PairComparison(Second, p2.Second);
72: }
73: else
74: throw new ArgumentException("Object is not a Pair.");
75: }
76: }
It is important to underline that the items in the SelectionCollection are objects of a type generated at runtime ( TempType objects) and need to be converted where possible to double values; this is why we try to convert them (with TryConvertToDouble) before the insertion.
In other words, on the basis of the SelectionCollection returned, we build a List of ObservableCollection objects which we then link to the Chart control. In detail, we build as many LineSeries objects as the number of ObservableCollection objects, we link the ItemsSource property of each LineSerie to an ObservableCollection of Pair and indicate that the independent values and dependent values of each LineSerie coincide with elements “First” and “Second” of the class Pair. Behind the scene here we are making assuming that the first column of our Datagrid (and obviously of our worksheet)contains the independent values and all the other columns consist of dependent values. This is indeed a simplification, but it shouldn’t be too difficult to add some kind of interactivity to allow the user to choose a column different from the independent column. Limited to a single LineSerie this can be done by using the panel on the left of the Chart control.
The Chart is finally completed by adding the Axes and all the LineSeries instantiated.
Zooming on the Chart
To add the zoom functionality to the Chart control of the Silverlight Toolkit I first inserted the Chart in a Canvas Control. The main reason for that is the possibility to use the local coordinate system of the Canvas to draw a semitransparent rectangle when the user chooses the option “zoom window” from the contextual menu. This rectangle gives guidance to the user in terms of the size of the window he wants.
The core of the mechanism of zooming is common to the tree zoom options: basically we change the minimum and maximum value of the LinearAxis along X and Y and then the Chart changes its scale accordingly. The hardest part is to understand that we must make two conversions between three coordinate systems. The first coordinate system is the one used by the method GetPlotAreaCoordinate(…) of the Chart; the second is the one of the UIElement used to obtain the mouse coordinate with GetPositionUIElement …); finally the third refers to the values of data series. So, as you can see below, we first need to transform the coordinates of the point clicked from the coordinate system of the LineSeries (the UIEement we chose as reference) to the one used by GetPlotAreaCoordinate(…) and this is, by the way, a simple translation of axes. Then we have to calculate the scale factor between pixels and data values and perform a scale conversion.
1: private void ZoomWindowChart(MouseButtonEventArgs e)
2: {
3: LineSeries myLS = MainChartElement.Series[0] as LineSeries;
4: Point endPoint = e.GetPosition(myLS);
5:
6: LinearAxis xAxis = MainChartElement.Axes[0] as LinearAxis;
7: LinearAxis yAxis = MainChartElement.Axes[1] as LinearAxis;
8:
9: // values range of both axes
10: double rangeX = Math.Abs(Convert.ToDouble(xAxis.ActualMaximum - xAxis.ActualMinimum));
11: double rangeY = Math.Abs(Convert.ToDouble(yAxis.ActualMaximum - yAxis.ActualMinimum));
12:
13: // extent of the graph in pixel
14: double pixelExtentX = Math.Abs((xAxis.GetPlotAreaCoordinate(Math.Abs((double)xAxis.ActualMaximum))).Value);
15: double pixelExtentY = Math.Abs((yAxis.GetPlotAreaCoordinate(Math.Abs((double)yAxis.ActualMaximum))).Value);
16:
17: // calculate scale factor for both axes
18: double xScalePixelFactor = rangeX / pixelExtentX;
19: double yScalePixelFactor = rangeY / pixelExtentY;
20:
21: // calculate offset
22: double xOffset = xAxis.GetPlotAreaCoordinate(0.0).Value;
23: double yOffset = yAxis.GetPlotAreaCoordinate(0.0).Value;
24:
25: // new min & max values of the graph
26: xAxis.Minimum = (Math.Min(startPoint.X, endPoint.X) - xOffset) * xScalePixelFactor;
27: xAxis.Maximum = (Math.Max(startPoint.X, endPoint.X) - xOffset) * xScalePixelFactor;
28: yAxis.Minimum = (pixelExtentY - Math.Max(startPoint.Y, endPoint.Y) - yOffset) * yScalePixelFactor;
29: yAxis.Maximum = (pixelExtentY - Math.Min(startPoint.Y, endPoint.Y) - yOffset) * yScalePixelFactor;
30:
31: SelectionElement.Visibility = Visibility.Collapsed;
32:
33: }
Summary
In this article we have seen how to build a simple excel files viewer using the new features of Silverlight 4 and some controls included in the Silverlight toolkit. In particular, we have showed how to handle drag & drop of files between local folders and a Silverlight application and also drag & drop of data between two Silverlight controls (Datagrid and Chart). We have also showed how it is possible to easily extend control chart and add functionalities to the zoom. As I wrote at the beginning, the application is at an early stage and I am planning to improve it following the suggestions made by Jose Almoguera in the final part of his article, in particular with regards to the ExcelReader project and the possibility to convert it to Silverlight.