Click here to Skip to main content
Click here to Skip to main content
 
Add your own
alternative version

Silverlight 4: Interoperability with Excel using the COM Object

, 31 May 2010 CPOL
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.
Silverlight4_Interoperability_ExcelApp_Demo.zip
Silverlight4 Interoperability ExcelApp Demo
Silverlight4 Interoperability ExcelApp Demo.suo
Silverlight4.Interop.Excel.Demo.Web
bin
Silverlight4.Interop.Excel.Demo.Web.dll
Silverlight4.Interop.Excel.Demo.Web.pdb
ClientBin
Silverlight4.Interop.Excel.Demo.xap
Properties
Silverlight4.Interop.Excel.Demo.Web.csproj.user
Silverlight4.Interop.Excel.Demo
Bin
Debug
ar
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
bg
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
ca
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
cs
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
da
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
de
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
el
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
es
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
et
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
eu
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
fi
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
fr
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
he
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
hr
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
hu
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
id
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
it
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
ja
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
ko
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
lt
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
lv
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
Microsoft.CSharp.dll
ms
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
nl
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
no
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
pl
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
pt
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
pt-BR
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
ro
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
ru
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
Silverlight4.Interop.Excel.Demo.dll
Silverlight4.Interop.Excel.Demo.xap
sk
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
sl
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
sr-Cyrl-CS
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
sr-Latn-CS
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
sv
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.ComponentModel.DataAnnotations.dll
System.Windows.Controls.Data.dll
System.Windows.Controls.Data.Input.dll
System.Windows.Data.dll
th
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
tr
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
uk
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
vi
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
zh-Hans
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
zh-Hant
Microsoft.CSharp.resources.dll
System.ComponentModel.DataAnnotations.resources.dll
System.Windows.Controls.Data.Input.resources.dll
System.Windows.Controls.Data.resources.dll
System.Windows.Data.resources.dll
Properties
Silverlight4.Interop.Excel.Demo.csproj.user
using System.Collections.ObjectModel;
using System.Runtime.InteropServices.Automation;
using System.Windows;
using System.Windows.Controls;

namespace Silverlight4.Interop.Excel.Demo
{
    public partial class MainPage : UserControl
    {
        dynamic excel = null;
        bool newInstance = true;
        delegate void SheetChangedDelegate(dynamic excelSheet, dynamic rangeArgs);

        /// <summary>
        /// Gets or sets the customer collection.
        /// </summary>
        /// <value>The customer collection.</value>
        public ObservableCollection<Customer> CustomerCollection
        {
            get { return (ObservableCollection<Customer>)GetValue(CustomerCollectionProperty); }
            set { SetValue(CustomerCollectionProperty, value); }
        }

        public static readonly DependencyProperty CustomerCollectionProperty =
            DependencyProperty.Register("CustomerCollection", typeof(ObservableCollection<Customer>), typeof(MainPage), new PropertyMetadata(null));


        /// <summary>
        /// Initializes a new instance of the <see cref="MainPage"/> class.
        /// </summary>
        public MainPage()
        {
            InitializeComponent();

            if (App.Current.InstallState == InstallState.Installed)
            {
                if (App.Current.IsRunningOutOfBrowser)
                {
                    installButton.Visibility = Visibility.Collapsed;
                    rectangle1.Visibility = Visibility.Visible;
                    textBlock2.Visibility = Visibility.Visible;
                    App.Current.MainWindow.TopMost = true;
                }
                else
                {
                    installButton.IsEnabled = false;
                    rectangle1.Visibility = Visibility.Collapsed;
                    textBlock2.Visibility = Visibility.Collapsed;
                }
            }
            else
            {
                installButton.Visibility = Visibility.Visible;
                rectangle1.Visibility = Visibility.Collapsed;
                textBlock2.Visibility = Visibility.Collapsed;
            }

            CustomerCollection = CustomerDataProvider.LoadData();
        }

        /// <summary>
        /// Handles the Click event of the installButton control to initialize the installation of the OOB application
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Windows.RoutedEventArgs"/> instance containing the event data.</param>
        private void installButton_Click(object sender, RoutedEventArgs e)
        {
            App.Current.InstallStateChanged += (Current_InstallStateChanged);
            App.Current.Install();
        }

        void Current_InstallStateChanged(object sender, System.EventArgs e)
        {
            if (App.Current.InstallState == InstallState.Installed)
            {

                installButton.IsEnabled = false;
            }
        }

        /// <summary>
        /// Handles the Click event of the exportToExcelButton control to export the content of the datagrid to a new excel sheet
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Windows.RoutedEventArgs"/> instance containing the event data.</param>
        private void exportToExcelButton_Click(object sender, RoutedEventArgs e)
        {
            LaunchExcel(sender, e);
        }

        /// <summary>
        /// Handles the MouseLeftButtonDown event of the rectangle1 control to close the OOB application
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Windows.Input.MouseButtonEventArgs"/> instance containing the event data.</param>
        private void rectangle1_MouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            App.Current.MainWindow.Close();
        }

        /// <summary>
        /// Launches the excel to populate the sheet with the data from the OOB application
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.RoutedEventArgs"/> instance containing the event data.</param>
        private void LaunchExcel(object sender, RoutedEventArgs e)
        {
            // create an instance of excel application & make it visible to the others
            excel = AutomationFactory.CreateObject("Excel.Application");
            excel.Visible = true;

            // add a workbook to the instance 
            dynamic workbook = excel.workbooks;
            workbook.Add();

            // get the active sheet from the workbook
            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++;
            }

            // for the first time register the sheet change notification event
            if (newInstance)
            {
                App.Current.MainWindow.Closing += (MainWindow_Closing);
                excel.SheetChange += new SheetChangedDelegate(SheetChangedEventHandler);
                newInstance = false;
            }
        }

        /// <summary>
        /// Handles the Closing event of the MainWindow control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.ComponentModel.ClosingEventArgs"/> instance containing the event data.</param>
        void MainWindow_Closing(object sender, System.ComponentModel.ClosingEventArgs e)
        {
            if (excel != null)
            {
                excel.SheetChange -= new SheetChangedDelegate(SheetChangedEventHandler);
            }
        }

        /// <summary>
        /// Raises when the Sheet gets the change notification
        /// </summary>
        /// <param name="excelSheet">The excel sheet.</param>
        /// <param name="rangeArgs">The range args.</param>
        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 first column
                CustomerCollection[i].Name = col2range.Item(i + 1).Value.ToString();
            }
        }

        private void border1_MouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            App.Current.MainWindow.DragMove();
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

Kunal Chowdhury (@kunal2383)
Team Leader
India India
Kunal Chowdhury is a Microsoft "Windows Platform Development" MVP (Most Valuable Professional), a Codeproject Mentor, Telerik MVP, Nokia Developer Champion, Speaker in various Microsoft events, Author, passionate Blogger and a Software Engineer 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 Store (WinRT) app development. He posts his findings, articles, tutorials in his technical blog and CodeProject.
 
Technical Blog: http://www.kunal-chowdhury.com
Facebook: http://facebook.com/blog.kunal
Twitter : http://twitter.com/kunal2383
Follow on   Twitter   Google+   LinkedIn

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 31 May 2010
Article Copyright 2010 by Kunal Chowdhury (@kunal2383)
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid