Importing an Excel File into a Silverlight DataGrid in XML Format

FileInfo info = dlg.File;
StatusText.Text = info.Name;

// open the stream for reading

Stream s = info.OpenRead();

StreamReader reader = new StreamReader(s);

var xml = reader.ReadToEnd();
return xml;
}

We could actually take this further, by uploading the file via an http handler mechanism in ASP.NET and process the file on the server so that everyone looking at the URL address could see the uploaded data, but this is outside the scope of this article. If you would like to know how to upload a file to the server in Silverlight, check out Nipun's article File Upload in Silvelight.

Using Linq to XML to pull out Data

Silverlight unfortunately does not support the XmlDocument that the .NET framework contains. Silverlight does however, support Linq for XML making it easy to manipulate XML data inside your Silverlight application. To support Linq to XML you can add it as a reference assembly from the Silverlight SDK Client Directory (e.g. C:\Program Files (x86)\Microsoft SDKs\Silverlight\v3.0\Libraries\Client). The assembly is called System.Xml.Linq.dll.

Once you've added it to your project, you can use the power of LINQ to extract information from the XML Excel Spreadsheet read into memory. Our first step is parse the xml string into an XDocument (which is a bit different than the XmlDocument in the Windows .NET Framework you might be used to).

Listing 3 - Parsing the xml string into an XDocument

var doc = XDocument.Parse(xml);

In our program, we first want to extract the column headers from the XML to form the columns of our data grid. This is accomplished by reading the data of the first row of the spreadsheet. We'll use the DataGridTextColumn class to populate our DataGrid with columns that we know will contain text. We will then bind our column to a ValueConverter that will be able to extract data into a cell based on an array position.

Listing 4 - Creating the columns of our data grid from the spreadsheet data

static ObservableCollection<List<string>> items = new ObservableCollection<List<string>>();

private void SetupDataGridFromSpreadsheetColumnNames(XDocument doc)
{
// get a list of column names
var columnNames = doc.Descendants().Where(x => x.Name.LocalName == "Row").First().Descendants().Where(y => y.Name.LocalName == "Data").Select(q => q.Value).ToList();

int count = 0;
// create the columns in the datagrid and set the bindings to use
// a value converter that can process the array of strings
foreach (var name in columnNames)
{
var column = new DataGridTextColumn() { Header = name };
dataGrid1.Columns.Add(column);
column.Binding = new Binding() { Converter = (IValueConverter)this.Resources["arrayIndexToValueConverter"], ConverterParameter = count };
count++;
}
// set the data source of the data grid
dataGrid1.ItemsSource = items;
}

The data source of the DataGrid is an ObservableCollection. Each item of the Observable collection is an array of strings containing a row of data in the spreadsheet. To extract the rows of data, we'll again use Linq to pull out a list of strings from each row inside the XML.

Listing 5 - Extract the Data from the XML file into the Data Grid

private static void PopulateExcelDataToDataGrid(XDocument doc)
{
var rows = doc.Descendants().Where(x => x.Name.LocalName == "Row");

int rowCount = 0;
foreach (var row in rows)
{
// skip the data in the first row since it is the header
if (rowCount > 0)
{
var data = row.Descendants().Where(y => y.Name.LocalName == "Data").Select(q => q.Value).ToList();
items.Add(data);
}

rowCount++;
}
}

Not e that the Linq statement uses the LocalName to match the Row tag in order to get a list of rows. It then uses the LocalName to match the Data tag in each row in order to get a list of string data in each row. Each list of row data is added to the items ObservableCollection that is bound to the rows in the data grid.

Using the ValueConverter to pull data from a List into each Data Grid Cell

The ValueConverter provides us with a means of extracting the data from each index in the arraylist contained in each item of the ObservableCollection. We simply bind each column of the DataGrid to the same value converter, but we pass the index of the current column as a parameter. This way, when the grid is notified to populate a particular cell in a grid column, it will ask for the correct index of the string in the array.

Listing 6 - The Value Converter Bound to the DataGrid Column

public class ArrayIndexToValueConverter : IValueConverter
{

#region IValueConverter Members

public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
var index = (int)parameter;
var list = value as List<string>;
if (index >= list.Count) return "";
return list[index];
}

public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
throw new NotImplementedException();
}

#endregion
}

Conclusion

As long as your spreadsheet information is in an XML format, it's easy to figure out a way to import it into any application you can think of. A Silverlight application is a great choice for bringing Excel into the web because of it's equivalent rich UI experience. In this article we have illustrated how to save Excel in XML format, and how to import the XML into your Silverlight application into a DataGrid. If you start to experiment with the XML representation of XML, you'll notice style information describing your cells, so you could take this application further and apply the styles to your data grid cells. Anyway, enjoy experimenting with Excel XML, it will certainly excel-erate your ability to transfer information from office applications into applications on the world wide web.