Silverlight 4 - sharing data between Excel and Silverlight application

In my previous article Silverlight 4 - export to Excel, I demonstrated how you can export information from Silverlight grid to Excel application. In this article I am going to explain how you can share data between Excel and Silverlight application. You will see how the information modified in Excel application is getting reflected in Silverlight application.

1. Create new Silverlight Application

Create new Silverlight Application. I gave my sample application name "ExportToExcel" you may choose some different name.

2. Add Employee class

Add new class with the name Employee, derive it from INotifyPropertyChanged interface and implement PropertyChangedEventHandler event. Raise PropertyChanged event for both EmployeeName and Department properties when they are modified.

public class Employee : INotifyPropertyChanged

{

    private string emp_name;

    private string dept;

    public event PropertyChangedEventHandler PropertyChanged;

    public int EmployeeId { get; set; }

    public string EmployeeName

    {

        get { return emp_name; }

        set

        {

            emp_name = value;

            OnPropertyChanged("EmployeeName");

        }

    }

    public string Department

    {

        get { return dept; }

        set

        {

            dept = value;

            OnPropertyChanged("Department");

        }

    }

    protected void OnPropertyChanged(string propertyName)

    {

        if (PropertyChanged != null)

            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));

    }

}

Add GetEmployees method in MainPage.xaml.cs which will return ObservableCollection of type Employee.

private ObservableCollection<Employee> GetEmployees()

{

    return new ObservableCollection<Employee>{

      new Employee{ EmployeeName="Mike", EmployeeId=100, Department="CDT"},

      new Employee{ EmployeeName="Jason", EmployeeId=105, Department="CDT"},

      new Employee{ EmployeeName="Tony", EmployeeId=102, Department="Grandslam"},

      new Employee{ EmployeeName="Brain", EmployeeId=112, Department="ESP"},

      new Employee{ EmployeeName="Charls", EmployeeId=108, Department="Grandslam"},

      };

}

3. Design UI

Add DataGrid in MainPage.xaml which will show employee details returned from GetEmployees method created in step #2. Also add a button which will be used for export to excel.

<UserControl Name="EmployeeControl"

    xmlns:my="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" 

    x:Class="ExportToExcel.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:DesignHeight="350" d:DesignWidth="400">

    <Grid x:Name="LayoutRoot" Background="Transparent">

        <StackPanel>

            <Grid Margin="10,10,10,10">

                <Grid.RowDefinitions>

                    <RowDefinition Height="*"/>

                    <RowDefinition Height="10"/>

                    <RowDefinition Height="50"/>

                </Grid.RowDefinitions>

                <my:DataGrid AutoGenerateColumns="True"

                      Height="250"

                      IsReadOnly="True"

                      HorizontalAlignment="Center"

                      VerticalScrollBarVisibility="Auto"

                      Margin="5,5,5,5"

                      x:Name="EmployeeGrid"

                      VerticalAlignment="Top"

                      Width="400" ItemsSource="{Binding EmployeeCollection, ElementName=EmployeeControl, Mode=TwoWay}" />

                <Button Grid.Row="2" HorizontalAlignment="Center" Height="28" Click="btnExport_Click"

                    x:Name="btnExport"

                    Content="Export to Excel"

                    Width="100" />

            </Grid>

        </StackPanel>

    </Grid>

</UserControl>

4. Add reference to Microsoft.CSharp assembly

Add Microsoft.CSharp assembly reference in Silverlight project.

5. Declare delegate to attach it in SheetChange event

Declare delegate to attach it in SheetChange event of Excel application.

delegate void ExcelSheetChanged(dynamic excelSheet, dynamic args);

6. Add Export to Excel functionality 

Write code for Export to Excel in button click event. Here I have added code to get column header when records are exported to Excel, this was not available in my previous article.

You can see in below code I have attached ExcelSheetChanged delegate declared in step #5 to Excel application SheetChange event.

ExcelSheetChangedEventHandler method handles SheetChange event raised by Excel application. This method reads the changes and updates them to EmployeeCollection dependency property. As the grid has ItemSource property set to EmployeeCollection,  as soon as the EmployeeCollection property value is updated you can see updates in grid.

private void btnExport_Click(object sender, RoutedEventArgs e)

{

    excelApp = AutomationFactory.CreateObject("Excel.Application");

    excelApp.Visible = true;

    dynamic workbook = excelApp.workbooks;

    workbook.Add();

    dynamic sheet = excelApp.ActiveSheet;

    dynamic cell = null;

    int rowIndex = 2;

    for (int colIndex = 0; colIndex < EmployeeGrid.Columns.Count; colIndex++)

    {

        dynamic header = sheet.Cells[1, colIndex + 1];

        header.Value = EmployeeGrid.Columns[colIndex].Header;

        header.Font.Bold = true;

    }

    foreach (Employee emp in EmployeeGrid.ItemsSource)

    {

        cell = sheet.Cells[rowIndex, 1];

        cell.Value = emp.EmployeeId;

        cell = sheet.Cells[rowIndex, 2];

        cell.Value = emp.EmployeeName;

        cell = sheet.Cells[rowIndex, 3];

        cell.Value = emp.Department;

        rowIndex++;

    }

    excelApp.SheetChange += new ExcelSheetChanged(ExcelSheetChangedEventHandler);

}

private void ExcelSheetChangedEventHandler(dynamic sheet, dynamic rangeArgs)

{

    dynamic empNameColRange = sheet.Range("B2:B" + EmployeeCollection.Count);

    dynamic deptColRange = sheet.Range("C2:C" + EmployeeCollection.Count);

    for (int i = 0; i < EmployeeCollection.Count; i++)

    {

        EmployeeCollection[i].EmployeeName = empNameColRange.Item(i + 1).Value.ToString();

        EmployeeCollection[i].Department = deptColRange.Item(i + 1).Value.ToString();

    }

}

7. Setup Out of Browser mode

To setup OOB mode:

• Right click on Silverlight project and select properties.
• Check the "Enable running application out of the browser" check box.
  
  
  
• Click on "Out-of-Browser Settings..." button, from the OOB setting dialog select "Running elevated trust when running outside the browser" check box and click on ok.
  
  
  

8. Run application

Once OOB setting is completed run your application. Right click on application and install it in your system, once the application is installed it will be opened in OOB window.

Click on "Export to Excel" button to export all records into Excel application. You can see the records in Excel.

 

Now modify the EmployeeName and Department of Employee Id 105 in Excel. You can see in below screen the EmployeeName is modified to "Tom" and Department is modified to "PMO".

 

Using the COM API's you can do more complex operation like formula, export images, etc for Excel application. You can also communicate with other Microsoft Office applications like Word, Outlook, etc.