Export Data Grid to Excel in Silverlight 4.0


One of the great feature in Silverlight 4.0 is  COM interoperability, now your Silverlight application can talk with Office applications using Com Automation Factory. This feature requires a trusted application (elevated permissions). In this article I am going to explain how you can export your data from datagrid to excel spread sheet as  OOB (Out of browser) application using C# 4.0 magic keyword called “dynamic“.

You can Download the complete source code from here

Create a new Silverlgiht 4.0 project using VS 2010 and add Microsoft.CSharp as reference (for C# 4.0 dynamic keyword) and also the add the name space System.Runtime.InteropServices.Automation for  com interoperability

I am using a customer class with some getter and setter properties to bind the datagrid.

Customer Class


using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;
using System.Runtime.InteropServices.Automation;

 public class Customer
 {
 public string FirstName { get; set; }
 public string LastName { get; set; }
 public int Age { get; set; }
 public bool IsSubscribed { get; set; }
 }

Method for Binding customer list to the Data Grid

 public ExportToExcel()
 {
 InitializeComponent();
 BindDataGrid();
 }

 void BindDataGrid()
 {
 int count = 10;
 List<Customer> customers = new List<Customer>();
 for (int i = 0; i < count; i++)
 {
 customers.Add(new Customer()
 {
 FirstName = "First Name " + i,
 LastName = "Last Name " + i,
 Age = i + 10,
 IsSubscribed = (i % 2 == 0)
 });
 }
 dgCustomers.ItemsSource = customers;
 }

Method for exporting data from data grid to excel spread sheet

 void GenerateSpreadSheet(object sender, RoutedEventArgs e)
 {
 int rowIndex = 1;
 int coulmnIndex = 1;

 try
 {
 dynamic excel = AutomationFactory.CreateObject("Excel.Application");

 excel.workbooks.Add();

 dynamic sheet = excel.ActiveSheet;

 for (int i = 0; i < dgCustomers.Columns.Count; ++i)
 {
 dynamic headerCell = sheet.Cells[rowIndex, coulmnIndex + i];
 headerCell.Value = dgCustomers.Columns[i].Header;
 headerCell.Font.Bold = true;
 headerCell.Interior.Color = 0xFF00;
 }

 foreach (Customer customer in dgCustomers.ItemsSource)
 {
 rowIndex++;

 //column 1
 dynamic cellFirstName = sheet.Cells[rowIndex, 1];
 cellFirstName.Value = customer.FirstName;
 cellFirstName.Font.Color = 003399;

 //column 2
 dynamic cellLastName = sheet.Cells[rowIndex, 2];
 cellLastName.Value = customer.LastName;
 cellLastName.Font.Color = 003399;

 //column 3
 dynamic cellAge = sheet.Cells[rowIndex, 3];
 cellAge.Value = customer.Age;
 cellAge.Font.Color = 003399;

 //column 4
 dynamic cellSubscribed = sheet.Cells[rowIndex, 4];
 cellSubscribed.Value = customer.IsSubscribed ? "Yes" : "No";
 cellSubscribed.Font.Color = 003399;
 }
 excel.Visible = true;
 }
 catch (Exception ex)
 {
 MessageBox.Show("Error generating excel: " + ex.Message);
 }
 }

The xaml code creates a data grid and a button

<Grid x:Name="LayoutRoot" Background="White">
 <Grid.RowDefinitions>
 <RowDefinition Height="350"></RowDefinition>
 <RowDefinition Height="5"></RowDefinition>
 <RowDefinition Height="25"></RowDefinition>
 <RowDefinition Height="*"></RowDefinition>
 </Grid.RowDefinitions>
 <my:DataGrid x:Name="dgCustomers" Grid.Row="0" AutoGenerateColumns="True" HeadersVisibility="All"
 RowBackground="Azure" AlternatingRowBackground="LightSteelBlue" ColumnWidth="85" RowHeight="30">
 </my:DataGrid>
 <Button  Grid.Row="2" Content="Export To Excel" Height="25" Width="120" Name="btnExportExcel" Click="GenerateSpreadSheet"  />
 </Grid>

Out of Browser set up

It is really easy to make a Silverlight applicaton as run out of browser mode. Right click the Silverlight project, select properties and check the ENABLE RUNNING APPLICATION OUT OF THE BROWSER.

Elevated permissions (Trusted Application)

Right click Silverlight Project – > Select Properties – > Click Out-Of-Browser Settings

You can Download the complete source code from here

Hope this help and If you have any comments, please feel free to write your feedback.

Thanks
Deepu

Advertisements

29 thoughts on “Export Data Grid to Excel in Silverlight 4.0

  1. It is not work. Give me error on following line

    AutomationFactory.CreateObject(“Excel.Application”);

    1. Add Microsoft.CSharp as reference (for C# 4.0 dynamic keyword) and also the add the name space System.Runtime.InteropServices.Automation for com interoperability
      let me know if it works

      Thanks
      Deepu

  2. If we to use vb.net is there an equivalent to ‘dynamic’ keyword – I’ve tried using Object but with option strict this fails – any advice? Thanks

    1. Yes, It work only Desktop like a windows app because it uses com-Interoperability and this feature requires a trusted application (elevated permissions)

      Thanks
      Deepu

  3. Hi Deepu,
    First of all, thanks for sample code. Somehow, when I run this example, I am getting this error. Any idea what I could be missing.

    Error Generaitng Excel: The Operation is not supported in current context

    Thanks
    Jay

  4. Hi Deepu,

    Thanks for getting back to me.

    Actually I am running the app from a browser so I am looking for example that allows the export in browser scenario.

    Thanks
    Jay

  5. I don’t know is this is true, but is it possible, that this code will only work if Microsoft Office (with MS Excel) is installed on a computer on which I am trying to execute it?

  6. hi Deepu,
    I have just posted ExcelLite a C#/Silverlight library for Silverlight applications that can read and write MS Excel ( Excel 97-2003) files without COM interaction. You can manipulate MS Excel files totally on client side as this library is using Binary excel format to read and write data.
    Codeplex project http://excellite.codeplex.com/
    Code examples at my blog
    http://abubakar-dar.blogspot.com/2010/12/excellite-silverlight-library-for-ms.html

  7. mr deepu,,please help me,,,why i got the error like this…

    “Error 1 ‘my’ is an undeclared prefix. Line 22, position 10. D:\silverlight\exporttoexcel\exporttoexcel\MainPage.xaml 22 10 exporttoexcel”

  8. Thank you for your perfect codding. but the question is if we have multiple data grids, and plan to have them all in one excel file but in various worksheet with some specific worksheet name, then what is solution? thanks for your help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s