Importing an Excel File into a Silverlight DataGrid in XML Format

Figure 1 - Excel to Silverlight

Introduction

Microsoft Excel is a wonderful tool which allows you to capture and analyze all kind of data, but one of the great features of Excel, is its ability to do simple data entry, whether you are a programmer or not. What if you could click a button and have your Excel data displayed inside a grid in the browser? Then anyone could see your data, if they are on a Mac or PC, and they don't even need to own Excel to see it. This article will illustrate that moving Excel Documents into Silverlight is a snap if your Excel spreadsheet is saved in an XML file.

Saving your spreadsheet in XML

To save your Excel spreadsheet as XML, first open up your Excel spreadsheet and go to the file menu (the little round circle in the upper left hand corner with the logo in it). Click Save As and choose XML spreadsheet 2003 (*.xml). This is what you will see if you are using Office 2007. Choose the directory you want to save the spreadsheet and click the Save button.



Figure 2 - Saving your Worksheet in XML

Your xml file will have an XML representation of your spreadsheet as you might expect, with nodes containing workbook, worksheet, rows, and cells. You can see that all the data is contained within the cell structure much like the programmable Excel COM interface you might use to extract data from Excel.

Listing 1 - Excel Worksheet saved in XML

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="74.25"/>
   <Column ss:Width="84.75"/>
   <Column ss:Index="4" ss:Width="130.5"/>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Address</Data></Cell>
    <Cell><Data ss:Type="String">State</Data></Cell>
    <Cell><Data ss:Type="String">How did you hear about us</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Mahesh Chand</Data></Cell>
    <Cell><Data ss:Type="String">40W Maple Drive</Data></Cell>
    <Cell><Data ss:Type="String">PA</Data></Cell>
    <Cell><Data ss:Type="String">Web</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Mike Gold</Data></Cell>
    <Cell><Data ss:Type="String">100 Lamar Drive</Data></Cell>
    <Cell><Data ss:Type="String">TX</Data></Cell>
    <Cell><Data ss:Type="String">Google Search</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Bill Robins</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">20 Oak Blvd</Data></Cell>
    <Cell><Data ss:Type="String">CA</Data></Cell>
    <Cell><Data ss:Type="String">a Friend</Data></Cell>
   </Row>
  </Table>

Importing the XML into Silverlight

So how do we get this data from our computer into a table on the browser? Sliverlight provides us with some mechanisms for extracting the data out of a file into client memory. We can use the OpenFileDialog to read the xml data for further processing.

Listing 2 - Reading in the XML file

private string ReadUserXMLFile()
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Multiselect = false;
            dlg.Filter = "Excel XML Files (*.xml)|*.xml";
            bool bResult = (bool)dlg.ShowDialog();
            if (!bResult)
                return "";

            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.