Export (0) Print
Expand All

Displaying Open XML Spreadsheet Tables in the Browser Using Silverlight

Office 2007

Summary: Learn how to use a Microsoft Silverlight application to display data from Open XML spreadsheet tables. (20 printed pages)

Office Visual How To

Applies to:2007 Microsoft Office system, Microsoft Office Excel 2007, Visual Studio 2008

Joel Krist, iSoftStone

December 2009

Overview

This Visual How To shows how to create a Microsoft Silverlight UserControl and an associated ASP.NET Web application that users can manipulate to select a table that is defined in a pre-existing Excel 2007 workbook on the server. The UserControl uses the Silverlight Grid control to display the contents of the selected table.

Because the Open XML Software Development Kit (SDK) for Microsoft Office cannot be used directly from a Silverlight application, the Silverlight control provides its own Open XML package-handling routines to access the XML in the workbook parts and uses LINQ to XML to query and retrieve the table contents. The sample code displays simple table cell values only and does not attempt to apply cell or table formatting.

See It

Video startup screen

Watch the Video

Length: 14:13 | Size: 19.30 MB | Type: WMV file

Code It | Read It | Explore It

Download the sample code

This Visual How To presents a solution that creates a Silverlight 3 application that displays worksheet table cell values. The application provides a series of cascading list boxes that lets the user select a workbook, a worksheet in the workbook, and ultimately a table in the worksheet. The application then displays the contents of the selected table in a grid. The following figure shows the user interface of the application.

Figure 1. Silverlight Application UI

Silverlight Application UI

 

To illustrate how to create the solution under discussion, this section walks through the following steps:

  1. Creating a Silverlight Application solution in Visual Studio 2008.

  2. Adding a reference to the System.Xml.Linq assembly.

  3. Adding the sample code to the solution.

    1. Adding the XAML for the Silverlight UserControl user interface.

    2. Adding the event handling code to the Silverlight UserControl code-behind file.

    3. Adding the LINQ to XML related code.

    4. Modifying the size of the Silverlight plug-in on the host page.

Creating a Silverlight Application in Visual Studio 2008

To use Visual Studio 2008 to build Silverlight applications, you must install the Silverlight development tools. For more information about how to install the Silverlight development tools for Visual Studio, see Get Started Building Silverlight 3 Applications.

To Create a Silverlight Application in Visual Studio 2008

  1. Start Microsoft Visual Studio 2008.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box select the Visual C# Silverlight type in the Project types pane.

  4. Select Silverlight Application in the Templates pane.

  5. Name the project DisplayTables.

    Figure 2. Creating the Solution

    Creating the Solution

     

  6. Click OK to open the New Silverlight Application dialog box.

  7. Select the option to Host the Silverlight application in a new Web site and choose ASP.NET Web Application project for the New Web project type.

    Figure 3. Choosing the Hosting Options

    Choosing the Hosting Options

     

  8. Click OK to create the solution.

Adding a Reference to the System.Xml.Linq Assembly

The sample code uses the functionality that the System.Xml.Linq namespace provides.

To add a reference to the System.Xml.Linq Assembly

  1. On the Project menu in Visual Studio, click Add Reference to open the Add Reference dialog box.

  2. Select the .NET tab, scroll down to System.Xml.Linq, select it, and then click OK.

    Figure 4. Add Reference to System.Xml.Linq

    Add Reference to System.Xml.Linq

Adding the Sample Code to the Solution

Adding the sample code to the solution only takes a couple of steps.

To add the XAML for the Silverlight UserControl user interface

  1. In Visual Studio, open the MainPage.xaml file that is located in the DisplayTables project of the DisplayTables solution.

  2. Replace all the contents of the MainPage.xaml file with the following markup.

<UserControl x:Class="DisplayTables.MainPage"
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
 xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
 xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
 mc:Ignorable="d" d:DesignWidth="600" d:DesignHeight="800">
  <Grid x:Name="LayoutRoot"
        VerticalAlignment="Top"
        HorizontalAlignment="Left">
    <Grid.ColumnDefinitions>
      <ColumnDefinition></ColumnDefinition>
    </Grid.ColumnDefinitions>

    <Grid.RowDefinitions>
      <RowDefinition></RowDefinition>
      <RowDefinition></RowDefinition>
    </Grid.RowDefinitions>

    <Grid Grid.Row="0">
      <Grid.ColumnDefinitions>
        <ColumnDefinition></ColumnDefinition>
        <ColumnDefinition></ColumnDefinition>
        <ColumnDefinition></ColumnDefinition>
      </Grid.ColumnDefinitions>

      <Grid.RowDefinitions>
        <RowDefinition></RowDefinition>
        <RowDefinition></RowDefinition>
        <RowDefinition></RowDefinition>
      </Grid.RowDefinitions>

      <Border Grid.Row="0" Grid.Column="0" Height="100">
        <TextBlock VerticalAlignment="Bottom">Workbooks:</TextBlock>
      </Border>

      <Border Grid.Row="0" Grid.Column="1" Height="100">
        <TextBlock VerticalAlignment="Bottom">Worksheets:</TextBlock>
      </Border>

      <Border Grid.Row="0" Grid.Column="2" Height="100">
        <TextBlock VerticalAlignment="Bottom">Tables:</TextBlock>
      </Border>

      <Border Grid.Row="1" Grid.Column="0">
        <ListBox x:Name="WorkbooksList"
                 SelectionChanged="workbooksList_SelectionChanged"
                 Visibility="Visible" >
          <ListBoxItem>
            <TextBlock>Test1.xlsx</TextBlock>
          </ListBoxItem>
          <ListBoxItem>
            <TextBlock>Test2.xlsx</TextBlock>
          </ListBoxItem>
        </ListBox>
      </Border>

      <Border Grid.Row="1" Grid.Column="1">
        <ListBox x:Name="WorksheetsList"
                 SelectionChanged="worksheetsList_SelectionChanged"
                 Visibility="Visible"></ListBox>
      </Border>

      <Border Grid.Row="1" Grid.Column="2">
        <ListBox x:Name="TablesList"
                 SelectionChanged="tablesList_SelectionChanged"
                 Visibility="Visible"></ListBox>
      </Border>
    </Grid>

    <Border Grid.Row="1">
      <ScrollViewer x:Name="TableScrollViewer" Height="400" Width="600"
                    VerticalScrollBarVisibility="Auto" 
                    HorizontalScrollBarVisibility="Auto">
        <Grid x:Name="ExcelTableGrid" Visibility="Visible"></Grid>
      </ScrollViewer>
    </Border>
  </Grid>
</UserControl>

The XAML code uses hard-coded values for the <TextBlock> elements that display the names of the workbooks that are available on the server. To change the names of the available workbooks, modify the <TextBlock> elements, adding or removing <ListBoxItem> elements as needed.

To add the event-handling code to the Silverlight UserControl code-behind file

  1. In Visual Studio, open the MainPage.xaml.cs file that is in the DisplayTables project of the DisplayTables solution. To open MainPage.xaml.cs, use the Visual Studio Solution Explorer, which displays the file as a child of the MainPage.xaml file. Or, right-click MainPage.xaml.cs in the MainPage.xaml file in the Visual Studio editor and then select View Code.

  2. Replace all the contents of the MainPage.xaml.cs file with the following code.

using System;
using System.Collections.Generic;
using System.Windows.Controls;
using System.IO;
using System.Windows.Browser;
using System.Net;

namespace DisplayTables
{
  public partial class MainPage : UserControl
  {
    Dictionary<string, Worksheet> worksheetDictionary;
    Dictionary<string, Table> tableDictionary;

    public MainPage()
    {
      InitializeComponent();

      // Allocate dictionaries to hold worksheets and tables by name.
      worksheetDictionary = new Dictionary<string, Worksheet>();
      tableDictionary = new Dictionary<string, Table>();
    }

    private void workbooksList_SelectionChanged(object sender,
      SelectionChangedEventArgs e)
    {
      // Get the name of the selected workbook.
      ListBoxItem listboxItem = e.AddedItems[0] as ListBoxItem;
      TextBlock textBlock = listboxItem.Content as TextBlock;
      string WorkbookName = textBlock.Text;

      // Clear the worksheet and table listboxes/dictionaries
      // and the table cell Grid control.
      WorksheetsList.Items.Clear();
      TablesList.Items.Clear();

      worksheetDictionary.Clear();
      tableDictionary.Clear();

      ExcelTableGrid.Children.Clear();

      if (e.AddedItems.Count > 0)
      {
        // Get the absolute path of the page that hosts 
        // the Silverlight plug-in.
        string path = HtmlPage.Document.DocumentUri.AbsolutePath;
        path = path.Substring(0, path.LastIndexOf("/") + 1);

        // Build the full URL of the folder that contains
        // the current page.
        string appPath = string.Concat("http://",
          HtmlPage.Document.DocumentUri.Host, ":",
          HtmlPage.Document.DocumentUri.Port, path);

        // Begin an async request for the selected workbook.
        // The following code specifies that the selected workbook
        // is located in the "Sample Workbooks" folder, which is located 
        // below the folder that contains the current page.
        //
        // Change the value of the sampleWorkbookFolder variable to
        // specify a different relative folder for the
        // sample workbooks.
        string sampleWorkbookFolder = "Sample Workbooks/";

        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(
          new Uri(appPath + sampleWorkbookFolder + WorkbookName));

        request.BeginGetResponse(new AsyncCallback(ResponseCallback),
          request);
      }
    }

    private void worksheetsList_SelectionChanged(object sender,
      SelectionChangedEventArgs e)
    {
      // Clear table listbox/dictionary
      // and the table cell Grid control.
      TablesList.Items.Clear();
      tableDictionary.Clear();
      ExcelTableGrid.Children.Clear();

      if (e.AddedItems.Count > 0)
      {
        // Get the selected worksheet object from the dictionary
        // by its name.
        Worksheet selectedWorksheet =
          worksheetDictionary[(string)(e.AddedItems[0])];

        // Iterate through the tables in the selected worksheet,
        // adding them to the table listbox and dictionary.
        foreach (Table table in selectedWorksheet.Tables())
        {
          TablesList.Items.Add(table.TableDisplayName);
          tableDictionary.Add(table.TableDisplayName, table);
        }
      }
    }

    private void tablesList_SelectionChanged(object sender,
      SelectionChangedEventArgs e)
    {
      // Clear the table cell Grid control.
      ExcelTableGrid.Children.Clear();

      if (e.AddedItems.Count > 0)
      {
        // Get the selected table object from the
        // dictionary by its name.
        Table table = tableDictionary[(string)(e.AddedItems[0])];

        int colNumber = 0;
        int rowNumber = 0;

        // Iterate through the columns in the table
        // adding ColumnDefinitions to the Grid for each column
        // in the table.
        foreach (TableColumn column in table.TableColumns())
        {
          ExcelTableGrid.ColumnDefinitions.Add(
            new ColumnDefinition());
        }

        // Iterate through each row in the table.
        foreach (TableRow tableRow in table.TableRows())
        {
          // Add a RowDefinition to the Grid for each row in the
          // table.
          ExcelTableGrid.RowDefinitions.Add(new RowDefinition());

          // Iterate through each cell in the table row.
          foreach (Cell cell in tableRow.Row.Cells())
          {
            // Create a read-only TextBox to display the
            // cell's contents.
            TextBox txtBox = new TextBox();
            txtBox.IsReadOnly = true;

            // Determine the type of content in the cell. If the
            // type is "s" it is a string, so get the cell's
            // content from the workbook's shared string table.
            // If it is not a string, get the value directly
            // from the cell.
            if (cell.Type == "s")
              txtBox.Text = cell.SharedString;
            else
              txtBox.Text = cell.Value;

            // Place the TextBox into the correct row/column
            // of the parent Grid.
            txtBox.SetValue(Grid.ColumnProperty, colNumber);
            txtBox.SetValue(Grid.RowProperty, rowNumber);
            ExcelTableGrid.Children.Add(txtBox);

            colNumber++;
          }

          colNumber = 0;
          rowNumber++;
        }
      }
    }

    private void ResponseCallback(IAsyncResult asyncResult)
    {
      // Handle the response to the async request initiated in
      // the workbooksList_SelectionChanged method.
      HttpWebRequest request =
        (HttpWebRequest)asyncResult.AsyncState;
      HttpWebResponse response =
        (HttpWebResponse)request.EndGetResponse(asyncResult);
      Stream content = response.GetResponseStream();

      // Instantiate a new Workbook object from the response stream.
      Workbook workbook = new Workbook(content);

      // Because this method is called on a different thread than the
      // UI thread that the worksheets ListBox is running on the
      // ListBox can't be updated directly from here. Use
      // Dispatcher.BeginInvoke instead.
      this.Dispatcher.BeginInvoke(delegate()
      {
        // Iterate through each worksheet in the workbook
        // and add the worksheet to the ListBox and dictionary.
        foreach (Worksheet worksheet in workbook.WorkSheets())
        {
          WorksheetsList.Items.Add(worksheet.WorksheetName);
          worksheetDictionary.Add(worksheet.WorksheetName, worksheet);
        }
      });
    }
  }
}

The sample code specifies that the available workbooks on the server have been added to the folder named "Sample Workbooks,” which is located below the folder that contains the current page. Change the value of the sampleWorkbookFolder variable in the workbooksList_SelectionChanged method to specify a different relative folder for the available workbooks.

To add the LINQ to XML related code

  1. In Visual Studio, add a new C# code file to the DisplayTables project in the DisplayTables solution by doing the following:

    • Right-click the project in the Visual Studio Explorer, point to Add, and then click New Item.

  2. Name the new code file SpreadsheetML.cs and then click Add to add the file to the project.

    Figure 5. Add SpreadsheetML.cs to the DisplayTables Project

    Add SpreadsheetML.cs to the DisplayTables Project

     

  3. If you have not already done this, download the sample code, locate the SpreadsheetML.cs file in the DisplayTables folder, and open it in your favorite text editor. Copy all of the text in the file and paste it into the empty SpreadsheetML.cs file that you created in Visual Studio.

To modify the size of the Silverlight plug-in on the hosting ASP.NET page

  1. In Visual Studio, open the DisplayTablesTestPage.aspx file that is in the DisplayTables.Web project of the DisplayTables solution.

  2. Modify the width and height attributes of the object element for the Silverlight plug-in. Set the width attribute to 600px and the height attribute to 800px.

    Figure 6. Set Width and Height of Silverlight Plug-in

    Set Width and Height of Silverlight Plug-in

Before you build and run the solution, create a folder named Sample Workbooks in the file system under the DisplayTables.Web folder of the DisplayTables Visual Studio solution folders. Then create and save some Excel workbooks to the Sample Workbooks folder; make sure that each workbook that you save contains worksheets that have defined tables. Also, make sure that the names of the workbooks match the workbook names that are specified in MainPage.xaml.

Figure 7. Create Sample Workbooks Folder

Create Sample Workbooks Folder

 

In the Visual Studio Solution Explorer, set the DisplayTables.web project as the startup project and set the DisplayTablesTestPage.aspx as the home page.

Figure 8. Set StartUp Project and Start Page

Set StartUp Project and Start Page

 

Build and run the solution in Visual Studio by pressing CTRL+F5. When you do, the browser displays the DisplayTablesTestPage.aspx page that contains the Silverlight UserControl. Selecting a workbook displays the worksheets in the workbook. Selecting a worksheet displays the tables in the worksheet. Selecting a table displays the values in the table’s cells.

The sample code in this Visual How To shows how to create a Silverlight application that displays the contents of Excel worksheet tables. This section uses code examples from the Code It section to describe the approach to the solution.

Open XML Related Code

The Open XML SDK 2.0 for Microsoft Office provides strong support for working programmatically with Microsoft Office Word 2007, Microsoft Office Excel 2007, and Microsoft Office PowerPoint 2007 documents via the Open XML Formats. The typesafe classes included with the SDK provide a layer of abstraction between the developer and the Open XML Formats, simplifying the process of working with Office 2007 documents.

Unfortunately, the Open XML SDK for Microsoft Office is not available for use directly from Silverlight applications. Silverlight applications are built against the Silverlight Common Language Runtime (CLR), which differs from the CLR that the Open XML SDK is built against. The result is that Silverlight applications can only reference assemblies that have been built against the Silverlight CLR. If you try to add a reference to the DocumentFormat.OpenXml.dll assembly that is provided with the Open XML SDK to a Silverlight project in Visual Studio, you get the following error message.

Figure 9. Trying to Add a Reference to DocumentFormat.OpenXml.dll

Error from Trying to Add a Reference

 

In addition to that limitation, the basic package handling functionality that is provided with the System.IO.Packaging namespace is not present with the Silverlight CLR. Because of these challenges, the code sample provides its own implementation of classes that enable the Silverlight application to work with Excel 2007 workbooks at the Open XML level.

The Open XML related code in this Visual How To is based on the code in the LtxOpenXml Namespace created by Eric White. Eric's implementation is based on the Open XML SDK for Microsoft Office, which it uses for package and document part handling.

This Visual How To uses a version of Eric's code that was modified to remove the dependence on the Open XML SDK for Microsoft Office. The classes in the SpreadsheetML.cs source file (in the DisplayTables Silverlight project) provide the Open XML related code. That code provides implementations of the following classes to enable the work with Excel 2007 tables:

  • Workbook

  • Worksheet

  • SharedStringTable

  • Table

  • Row

  • Cell

  • TableColumn

  • TableRow

  • TableCell

In addition, the LocalExtensions class provides a set of extensions.

The following section focuses on specific aspects of the Open XML related code in SpreadsheetML.cs; in particular, where it was modified from Eric White's version to remove the dependency on the Open XML SDK for Microsoft Office.

For more information about the implementation of the LtxOpenXml Namespace and the LINQ to XML constructs it uses, see Eric White's excellent series of blog posts.

The Workbook class provides an abstraction of an Excel 2007 workbook. It has a constructor that accepts a System.IO.Stream argument that is used to create a System.Xml.Linq.XDocument instance. The class uses this XDocument instance to access the workbook relationships and the XML of the main workbook part so that it can provide the collection of worksheets in the workbook.

public class Workbook
{
  public Stream FileStream;
  public XDocument XDocument { get; set; }
  public SharedStringTable SharedStringTable { get; set; }

  public Workbook(Stream fileStream)
  {
    this.FileStream = fileStream;

    using (StreamReader sr = new StreamReader(
      GetDocumentPartStream("xl/workbook.xml")))
    using (XmlReader xr = XmlReader.Create(sr))
      XDocument = XDocument.Load(xr);

    SharedStringTable = new SharedStringTable(this);
  }
  ...

The code uses the Workbook.GetDocumentPartStream method to get a Stream on the document part that is specified by the PartPath argument. The basic package handling functionality that this method provides removes the dependency on the System.IO.Packaging namespace and allows the Workbook class to access the Open XML for its document parts and perform LINQ to XML queries.

public Stream GetDocumentPartStream(string PartPath)
{
  Uri fileUri = new Uri(PartPath, UriKind.Relative);
  StreamResourceInfo info = new StreamResourceInfo(FileStream,
    null);
  StreamResourceInfo stream =
    System.Windows.Application.GetResourceStream(info, fileUri);

  if (stream != null)
    return stream.Stream;

  return null;
}

The other classes use the Workbook.GetDocumentPartStream implementation to get their own XDocument instances to provide access to Open XML content to query. The following code fragment shows the beginning of the declaration of the Worksheet class, followed by the implementation of the Worksheet.GetDocumentPartStream method.

public class Worksheet
{
  public Workbook Parent;
  public XDocument XDocument { get; set; }

  public string RelId { get; set; }
  public string WorksheetName { get; set; }
  public string SheetId { get; set; }
  public string PartPath { get; set; }

  public Worksheet(Workbook Workbook, string WorksheetName,
    string RelId, string SheetId)
  {
    this.Parent = Workbook;
    this.WorksheetName = WorksheetName;
    this.RelId = RelId;
    this.SheetId = SheetId;
    this.PartPath = "xl/" +
      Workbook.GetRelationshipTargetPath(RelId);

    using (StreamReader sr = new StreamReader(      GetDocumentPartStream(PartPath)))
    using (XmlReader xr = XmlReader.Create(sr))
      XDocument = XDocument.Load(xr);
  }
  ...
  public Stream GetDocumentPartStream(string PartPath)
  {
    return Parent.GetDocumentPartStream(PartPath);
  }
  ...

Silverlight UserControl XAML

The Silverlight control in this Visual How To implements a very simple user interface that lets a user display a specific table. The XAML for the UserControl is located in the MainPage.xaml file that is part of the DisplayTables project and uses a combination of Silverlight Grid, Border, TextBlock, and ListBox controls.

To simplify this Visual How To, the user interface uses hard-coded values for the list of workbooks that can be selected. As mentioned previously, the names of the workbooks displayed in this list box must match the names of the actual workbooks on the server. The following XAML fragment shows the declaration of the workbook list box.

<Border Grid.Row="1" Grid.Column="0">
<ListBox x:Name="WorkbooksList"
         SelectionChanged="workbooksList_SelectionChanged"
         Visibility="Visible" >
  <ListBoxItem>
    <TextBlock>Test1.xlsx</TextBlock>
  </ListBoxItem>
  <ListBoxItem>
    <TextBlock>Test2.xlsx</TextBlock>
  </ListBoxItem>
  <ListBoxItem>
    <TextBlock>Test3.xlsx</TextBlock>
  </ListBoxItem>
  <ListBoxItem>
    <TextBlock>Test4.xlsx</TextBlock>
  </ListBoxItem>
  <ListBoxItem>
    <TextBlock>Test5.xlsx</TextBlock>
  </ListBoxItem>
  <ListBoxItem>
    <TextBlock>Test6.xlsx</TextBlock>
  </ListBoxItem>
</ListBox>
</Border>

Silverlight UserControl Code-Behind

The code that downloads the workbook content from the server and that provides the list box ‘selection-changed’ event handling and table rendering functionality for the Silverlight UserControl is located in the MainPage.xaml.cs code file in the DisplayTables project. The code uses the Open XML related classes that are implemented in SpreadsheetML.cs to access the list of worksheets and tables for the list boxes in the user interface and to display the contents of the selected table.

The code uses the System.Net.HttpWebRequest class to perform an asynchronous download of the selected workbook from the server. The following code fragment shows the initiation of the asynchronous request that is performed when the user selects a workbook in the workbooks list box.

if (e.AddedItems.Count > 0)
{
  // Get the absolute path of the page that the 
  // Silverlight plug-in is hosted on.
  string path = HtmlPage.Document.DocumentUri.AbsolutePath;
  path = path.Substring(0, path.LastIndexOf("/") + 1);

  // Build the full URL of the folder the current page is
  // located in.
  string appPath = string.Concat("http://",
    HtmlPage.Document.DocumentUri.Host, ":",
    HtmlPage.Document.DocumentUri.Port, path);

  // Begin an async request for the selected workbook.
  // The following code specifies that the selected workbook
  // is located in the "Sample Workbooks" folder, which is located 
  // below the folder that contains the current page.
  //
  // Change the value of the sampleWorkbookFolder variable to
  // specify a different relative folder for the
  // the sample workbooks.
  string sampleWorkbookFolder = "Sample Workbooks/";

  HttpWebRequest request = (HttpWebRequest)WebRequest.Create(
    new Uri(appPath + sampleWorkbookFolder + WorkbookName));

  request.BeginGetResponse(new AsyncCallback(ResponseCallback),
    request);
}

The following code fragment shows the implementation of the method that is called when the asynchronous request completes. The code creates a Workbook instance from the response stream. It also uses Dispatcher.BeginInvoke to update the Silverlight application user interface. The code must do that because the call into the method occurs on a different thread than the UI thread that the Silverlight controls are running on.

private void ResponseCallback(IAsyncResult asyncResult)
{
  // Handle the response to the async request initiated in
  // the workbooksList_SelectionChanged method.
  HttpWebRequest request =
    (HttpWebRequest)asyncResult.AsyncState;
  HttpWebResponse response =
    (HttpWebResponse)request.EndGetResponse(asyncResult);
  Stream content = response.GetResponseStream();

  // Instantiate a new Workbook object from the response stream.
  Workbook workbook = new Workbook(content);

  // Because this method is called on a different thread than the
  // UI thread that the worksheets ListBox is running on the
  // ListBox can't be update directly from here. Use
  // Dispatcher.BeginInvoke instead.
  this.Dispatcher.BeginInvoke(delegate()
  {
    // Iterate through each worksheet in the workbook
    // and add the worksheet to the ListBox and dictionary.
    foreach (Worksheet worksheet in workbook.WorkSheets())
    {
      WorksheetsList.Items.Add(worksheet.WorksheetName);
      worksheetDictionary.Add(worksheet.WorksheetName, worksheet);
    }
  });
}

The following code fragment shows the event handler for the list box ‘selection-changed’ event of the worksheets list box. The code uses the Worksheet and Table classes to get the list of tables in the selected worksheet and to add them to the tables list box.

private void worksheetsList_SelectionChanged(object sender,
  SelectionChangedEventArgs e)
{
  // Clear table listbox/dictionary
  // and the table cell Grid control.
  TablesList.Items.Clear();
  tableDictionary.Clear();
  ExcelTableGrid.Children.Clear();

  if (e.AddedItems.Count > 0)
  {
    // Get the selected worksheet object from the dictionary
    // by its name.
    Worksheet selectedWorksheet =
      worksheetDictionary[(string)(e.AddedItems[0])];

    // Iterate through the tables in the selected worksheet,
    // adding them to the table listbox and dictionary.
    foreach (Table table in selectedWorksheet.Tables())
    {
      TablesList.Items.Add(table.TableDisplayName);
      tableDictionary.Add(table.TableDisplayName, table);
    }
  }
}

The following code fragment displays the table contents. The code uses the Table, TableColumn, TableRow, and Cell classes to display table cell contents in the Silverlight Grid control.

private void tablesList_SelectionChanged(object sender,
  SelectionChangedEventArgs e)
{
  // Clear the table cell Grid control.
  ExcelTableGrid.Children.Clear();

  if (e.AddedItems.Count > 0)
  {
    // Get the selected table object from the
    // dictionary by its name.
    Table table = tableDictionary[(string)(e.AddedItems[0])];

    int colNumber = 0;
    int rowNumber = 0;

    // Iterate through the columns in the table
    // adding ColumnDefinitions to the Grid for each column
    // in the table.
    foreach (TableColumn column in table.TableColumns())
    {
      ExcelTableGrid.ColumnDefinitions.Add(
        new ColumnDefinition());
    }

    // Iterate through each row in the table.
    foreach (TableRow tableRow in table.TableRows())
    {
      // Add a RowDefinition to the Grid for each row in the
      // table.
      ExcelTableGrid.RowDefinitions.Add(new RowDefinition());

      // Iterate through each cell in the table row.
      foreach (Cell cell in tableRow.Row.Cells())
      {
        // Create a read-only TextBox to display the
        // cell's contents.
        TextBox txtBox = new TextBox();
        txtBox.IsReadOnly = true;

    !!    // Determine the type of the cell's content. If the
        // type is "s" it is a string, so get the cell's
        // content from the workbook's shared string table.
        // If it is not a string, get the value directly
        // from the cell.
        if (cell.Type == "s")
          txtBox.Text = cell.SharedString;
        else
          txtBox.Text = cell.Value;

        // Place the TextBox into the correct row/column
        // of the parent Grid.
        txtBox.SetValue(Grid.ColumnProperty, colNumber);
        txtBox.SetValue(Grid.RowProperty, rowNumber);
        ExcelTableGrid.Children.Add(txtBox);

        colNumber++;
      }

      colNumber = 0;
      rowNumber++;
    }
  }
}

Show:
© 2014 Microsoft