Click here to Skip to main content
15,860,972 members
Articles / Desktop Programming / XAML

Silverlight 4: Interoperability with Excel using the COM Object

Rate me:
Please Sign up or sign in to vote.
5.00/5 (22 votes)
31 May 2010CPOL7 min read 193.9K   61   54
Silverlight 4 has the feature to talk with Excel or any other application using the COM API. In this article, I will guide you to understand this feature step-by-step.

Table of Contents

Introduction

Silverlight 4 has the capability of accessing the COM object using the COM API. You can access any program installed in your PC using those APIs in Silverlight. You can open Notepad, Word, Excel or Outlook from a Silverlight application. Here I will demonstrate using the step-by-step tutorial on opening an Microsoft Excel book followed by data sharing between the Silverlight application and the Excel Sheet. Here, we will use a DataGrid which will load some customer information. Then we will pass the data to the Excel Sheet and then we will modify the data in the external application (i.e. inside the Excel sheet). You will see that the modified data will reflect automatically to the Silverlight Application.

See the below figure that we are going to demonstrate:

image002.png

Prerequisite

To develop this simple application, you need the following tools installed in your development environment:

  • Microsoft Visual Studio 2010
  • Silverlight 4 Tools for Visual Studio 2010

Remember that the Silverlight 4 applications can be developed only in Visual Studio 2010. Hence, if you have Visual Studio 2008 installed in your PC, you can install Visual Studio 2010 side-by-side for exploring Silverlight 4.

Getting Started

If your development environment is ready, then we can proceed towards creating a new Silverlight Application project. At the end of this part, we will be able to run our first Silverlight application inside the browser.

  1. Open your Visual Studio 2010 IDE
  2. Select File > New Project or just press CTRL + SHIFT + N to open up the New Project dialog
  3. Expand the “Visual C#” node and then go to sub node “Silverlight”
  4. Select “Silverlight Application” in the right pane
  5. Select proper location to store your application (let’s say, “D:\Sample Apps\”)
  6. Now enter a proper name for your project (call it as: “Silverlight4.Interop.Excel.Demo”)
  7. Select the .NET Framework version from the combo box at the top (I am using .NET Framework 4.0)
  8. Click OK to continue
  9. In the next dialog, make sure that “Host the Silverlight application in a new Web site” option is selected
  10. Choose “Silverlight 4” as the Silverlight Version and hit OK

Wait for a while, Visual Studio will now create the Silverlight solution for you to use which will contain a Silverlight Project and one Web Application Project to host your Silverlight application. In your Silverlight project, you will find a “MainPage.xaml” & an “App.xaml” file which are already created for you by the IDE Template.

Once you are done with setting up the project, you need to add an Assembly Reference to the Silverlight project. We will use the “dynamic” keyword, and for this we need to add the “Microsoft.CSharp” assembly reference.

  1. Right click on the “Reference” folder inside the Silverlight project and click on the “Add Reference” menu item from the context menu.

    image004.png

  2. This will open up the “Add Reference” dialog on the screen. Scroll the window to find the assembly named “Microsoft.CSharp” from the .NET tab. Select it and click “OK”. This will add the Microsoft.CSharp assembly reference into your Silverlight project. Once added, your project will support dynamic variable declaration.

    image006.png

Configuring Out-of-Browser Settings

Once you are done with setting up your Silverlight project, we are ready to implement custom Out-of-Browser Window for our application. You can read the complete article on Creating Silverlight 4 Custom Out-of-Browser window from CodeProject.

Once you design your out-of-browser Window, go to the properties of the Silverlight project. From the Silverlight pane, be sure that you are using “Silverlight 4” as target version. Now select the “Enable running application out of browser” which will make the “Out-of-Browser Settings…” button enabled. Click on it for more settings.

image008.png

From the Settings dialog window, select “Show install menu” which will create a Menu Item inside the Silverlight context menu. Once you run your application and right click on the application, you will see an “Install” menu item on it. I will come to this section later.

Now, check the “Require elevated trust when running outside the browser” as mentioned below and choose “Window Style” as “No Border”. This will make the default Chrome Window visibility to collapsed and if you run OOB, you will not see any Window border by default. Once you are done with these settings, click ok to save the configurations. You can also change the “Window Title”, “Size” and other options available there.

image010.png

Basic Design for Out-of-Browser Application

Now we will design our MainPage.xaml file with a DataGrid which will fetch Customer details from the DataProvider and generate the columns for it. We will make the DataGrid as Readonly, so that we can't directly edit the data inside it.

XML
<sdk:DataGrid AutoGenerateColumns="True"
        Height="295"
        IsReadOnly="True"
        HorizontalAlignment="Left"
        VerticalScrollBarVisibility="Auto"
        Margin="0,30,0,0"
        x:Name="customerDataGrid"
        VerticalAlignment="Top"
        Width="576" 
        ItemsSource="{Binding CustomerCollection, 
		ElementName=userControl, Mode=TwoWay}" />

We will also set two different buttons in the Window. One for installing the application out of the browser window and the other to export the datagrid content to an Excel application. Here is the XAML code for the buttons:

XML
<Button Height="28"
        HorizontalAlignment="Left" Click="exportToExcelButton_Click"
        Margin="244,333,0,0"
        x:Name="exportToExcelButton"
        Content="Export To Excel"
        VerticalAlignment="Top"
        Width="109" />

<Button Height="28"
        HorizontalAlignment="Left" Click="installButton_Click"
        Margin="244,333,0,0"
        x:Name="installButton"
        Content="Install"
        VerticalAlignment="Top"
        Width="109" />

Once your design is ready and you are done with data binding with your datagrid by fetching the customer information from the CustomerDataProvider, we can run the application inside the browser Window. Once loaded with fetched data, our application will look like the below figure:

image012.png

Here, you will notice the “Install” button enabled at the bottom of the DataGrid. Click the button to install this application outside the browser as a standalone application, so that, you can launch it from desktop or startmenu. When you start the installation procedure, it will pop up the Security Warning dialog. Click “Install” to continue.

image014.png

This will install the application in your local drive and automatically launch it out-of-browser. Here you will see a bit different view. You will notice that the “Install” button is no more available now and a new button named “Export to Excel” has been added to the view.

The following code block is responsible for changing the look of the application in different view:

C#
if (App.Current.InstallState == InstallState.Installed)
{
    if (App.Current.IsRunningOutOfBrowser)
    {
        // write the code for out-of-browser window to make the
        // export to excel button visible
    }
    else
    {
        // write the code for the browser window to disable the install button
        // when the application is already installed
    }
}
else
{
    // write the code for the default view
}
image016.png

“Export to Excel” Event Implementation

  1. Let us now go for the code implementation for exporting data from the datagrid to the Excel application instance.
    First of all, we will create the instance of an Excel application and will set the visibility to true, so that, others can view it.
    C#
    excel = AutomationFactory.CreateObject("Excel.Application");
    excel.Visible = true;
  2. Now create a workbook for the instance of the opened Excel application:
    C#
    dynamic workbook = excel.workbooks;
    workbook.Add();
  3. Get the ActiveSheet from the Excel Workbook and iterate through each row and column of the datagrid collection and set the values to the Excel sheet.
    C#
    dynamic sheet = excel.ActiveSheet;
    dynamic cell = null;
    int i = 1;
     
    // iterate through the data source and populate the excel sheet
    foreach (Customer item in customerDataGrid.ItemsSource)
    {
        cell = sheet.Cells[i, 1];
        cell.Value = item.Name;
        cell.ColumnWidth = 50;
     
    
        cell = sheet.Cells[i, 2];
        cell.Value = item.ID;
     
        cell = sheet.Cells[i, 3];
        cell.Value = item.Age;
     
        i++;
    }
  4. For the first instance of the application, we will now register the SheetChange event notification to our application. This will fire the event when you modify the content of the sheet.
    C#
    if (newInstance)
    {
        App.Current.MainWindow.Closing += (MainWindow_Closing);
        excel.SheetChange += new SheetChangedDelegate(SheetChangedEventHandler);
        newInstance = false;
    }
  5. Now go for the SheetChange event implementation. Here we will get the excelSheet which we will first store as a local instance for further access to it. Then get the range of the items to update from the Excel app to our application and iterate through the items and update. Below is the code for the event implementation:
    C#
    private void SheetChangedEventHandler(dynamic excelSheet, dynamic rangeArgs)
    {
        // copy the excelsheet to a local instance for further processing
        dynamic sheet = excelSheet;
     
        // get the range of the items to update
        dynamic col2range = sheet.Range("A1:A" + CustomerCollection.Count);
     
        for (int i = 0; i < CustomerCollection.Count; i++)
        {
            // update each and every row of the datagrid with the updated column
            // the first column in our case
            CustomerCollection[i].Name = col2range.Item(i + 1).Value.ToString();
        }
    }

“Export to Excel” Demo

We are done writing the code. Now, it is time to demo it. We will now see our application to actually talk with the Microsoft Excel application. From our application, we will transfer the datagrid content to the Excel Sheet. Hence click on the “Export to Excel” button from your out-of-browser application. You will notice that one Excel sheet has been created and the data is populating in the sheet row by row.

image018.png

Let us drag our application on top of the Excel book to see the live demo. You can now see the Silverlight application on top of Excel while editing the sheet.

image020.png

Now start editing any Row of the Excel sheet. In our example, we will use the first column for editing purposes because our code checks only the first column.

C#
// get the range of the items to update
dynamic col2range = sheet.Range("A1:A" + CustomerCollection.Count);
image022.png

Once you are done modifying the cell of the Excel sheet, just press Enter, so that it will come out from the edit mode. Hey what happened? Did you notice anything in our application? Yes right, in our application, the corresponding cell got updated with the modified cell content.

image024.png

What Next?

I think you now got the idea of Silverlight - Excel messaging through the COM API. This was a small demonstration of the new feature. You can now modify it for a bigger range of cell to update the Silverlight datagrid from Excel sheet. Be sure that it will only work for Silverlight trusted Out-of-Browser applications.

Please vote for this article and let me know if you have any feedback or suggestions, so that I can improve it for you.

History

  • Initial post (28-May-2010)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India

Kunal Chowdhury is a former Microsoft "Windows Platform Development" MVP (Most Valuable Professional, 2010 - 2018), a Codeproject Mentor, Speaker in various Microsoft events, Author, passionate Blogger and a Senior Technical Lead by profession.

He is currently working in an MNC located in India. He has a very good skill over XAML, C#, Silverlight, Windows Phone, WPF and Windows app development. He posts his findings, articles, tutorials in his technical blog (www.kunal-chowdhury.com) and CodeProject.


Books authored:


Connect with Kunal on:





Comments and Discussions

 
GeneralMy vote of 3 Pin
niemand21227-Feb-14 23:40
niemand21227-Feb-14 23:40 
QuestionExport to excel not working on MAC OS Pin
skumar1137-Nov-12 1:07
skumar1137-Nov-12 1:07 
GeneralNice Article Pin
vasanthamateti16-Sep-12 20:51
vasanthamateti16-Sep-12 20:51 
Generaldisplaying pictures in Excel Pin
Member 183522722-Mar-11 4:44
Member 183522722-Mar-11 4:44 
GeneralMy vote of 5 Pin
Member 183522722-Mar-11 4:43
Member 183522722-Mar-11 4:43 
GeneralMy vote of 5 Pin
Nick Polyak17-Feb-11 4:36
mvaNick Polyak17-Feb-11 4:36 
GeneralOpening word .doc or Excel .xls Pin
razvansovrea2-Feb-11 4:00
razvansovrea2-Feb-11 4:00 
GeneralHELP Pin
luweijun10-Dec-10 16:49
luweijun10-Dec-10 16:49 
GeneralRe: HELP Pin
Ali Taghvajou30-Dec-10 10:27
professionalAli Taghvajou30-Dec-10 10:27 
QuestionWord Document Guidance such as Excel one using Com Introp. In SL4 Pin
Ali Taghvajou2-Nov-10 10:56
professionalAli Taghvajou2-Nov-10 10:56 
AnswerRe: Word Document Guidance such as Excel one using Com Introp. In SL4 Pin
Kunal Chowdhury «IN»2-Nov-10 15:56
professionalKunal Chowdhury «IN»2-Nov-10 15:56 
GeneralRe: Word Document Guidance such as Excel one using Com Introp. In SL4 Pin
Ali Taghvajou30-Dec-10 10:35
professionalAli Taghvajou30-Dec-10 10:35 
GeneralMy vote of 5 - With a Question! Pin
Ali Taghvajou2-Nov-10 10:52
professionalAli Taghvajou2-Nov-10 10:52 
AnswerRe: My vote of 5 - With a Question! Pin
Kunal Chowdhury «IN»2-Nov-10 15:55
professionalKunal Chowdhury «IN»2-Nov-10 15:55 
GeneralUsing this approach for other COM DLLs Pin
Igor Kondrasovas27-Oct-10 3:36
professionalIgor Kondrasovas27-Oct-10 3:36 
AnswerRe: Using this approach for other COM DLLs Pin
Kunal Chowdhury «IN»2-Nov-10 15:52
professionalKunal Chowdhury «IN»2-Nov-10 15:52 
GeneralUnable to run demo app Pin
anilkrip25-Oct-10 22:56
anilkrip25-Oct-10 22:56 
GeneralRe: Unable to run demo app Pin
Kunal Chowdhury «IN»2-Nov-10 15:51
professionalKunal Chowdhury «IN»2-Nov-10 15:51 
QuestionCan Silverlight 4 and VS2008 be used as the development platform? Pin
Greg the Grip8-Jun-10 2:43
Greg the Grip8-Jun-10 2:43 
AnswerRe: Can Silverlight 4 and VS2008 be used as the development platform? Pin
Kunal Chowdhury «IN»8-Jun-10 5:39
professionalKunal Chowdhury «IN»8-Jun-10 5:39 
GeneralWow Pin
Abhishek Sur5-Jun-10 6:41
professionalAbhishek Sur5-Jun-10 6:41 
GeneralRe: Wow Pin
Kunal Chowdhury «IN»5-Jun-10 6:48
professionalKunal Chowdhury «IN»5-Jun-10 6:48 
QuestionCan be SL 4 used to host MS Office apps in html? Pin
R_PR3-Jun-10 5:21
R_PR3-Jun-10 5:21 
AnswerRe: Can be SL 4 used to host MS Office apps in html? Pin
Dewey3-Jun-10 12:31
Dewey3-Jun-10 12:31 
GeneralRe: Can be SL 4 used to host MS Office apps in html? Pin
R_PR3-Jun-10 22:40
R_PR3-Jun-10 22:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.