![importsil1.gif]()
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.
![importsil2.gif]()
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.