Silverlight 4 - Export to Excel


Along with many other features a good feature introduced with Silverlight 4.0 is COM Interoperability. Now Silverlight application can talk to other applications using their API's. Your Silverlight application can access Word, Excel, Outlook, etc.

Silverlight uses COM Automation Factory to communicate with other applications. This feature works in Out of Browser Silverlight application with elevated permission on. In this article I am going to explain how you can export data from Silverlight to Excel.

1. Create new Silverlight Application

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

1.gif

2. Add Employee class

Add new item Employee.cs, the class name will be Employee.  This class will have EmployeeId, EmployeeName and Department properties.

public class Employee
{
    public int EmployeeId { get; set; }
    public string Department { get; set; }
    public string EmployeeName { get; set; }
}

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.

<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" />
        <Button Grid.Row="2" HorizontalAlignment="Center" Height="28" Click="btnExport_Click"
            x:Name="btnExport"
            Content="Export to Excel"
            Width="100" />
    </Grid>
</StackPanel>

4. Add reference to Microsoft.CSharp assembly

Add Microsoft.CSharp assembly reference in Silverlight project.

2.gif

5. Add Export to Excel functionality using dynamic keyword

Write code for Export to Excel in button click event. As I mentioned above Silverlight uses COM Automation Factory to talk to Office application, I am creating object of Excel application using Automation Factory. 

Also reading values from Grid and adding the values in cells of active excel sheet.

private void btnExport_Click(object sender, RoutedEventArgs e)
{
    dynamic excelApp;
    excelApp = AutomationFactory.CreateObject("Excel.Application");
    excelApp.Visible = true;
    dynamic workbook = excelApp.workbooks;
    workbook.Add();
    dynamic sheet = excelApp.ActiveSheet;
    dynamic cell = null;
    int index = 1;
    foreach (Employee emp in EmployeeGrid.ItemsSource)
    {
        cell = sheet.Cells[index, 1];
        cell.Value = emp.EmployeeName;
        cell = sheet.Cells[index, 2];
        cell.Value = emp.EmployeeId;
        cell = sheet.Cells[index, 3];
        cell.Value = emp.Department;
        index++;
    }
}

6. 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.

    3.gif
  • 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.

    4.gif
7. 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.

5.gif

Click on "Export to Excel" button. The Employee details are exported into Excel.

6.gif

In this article I covered how you can export Employee details into MS Office Excel application. In next article I will demonstrate how values can be shared between Silverlight and Excel application.