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.