Silverlight 4 Communicating with Excel

When a Silverlight 4 application is running with Elevated Permissions you can access COM objects which can be quite handy.  For example you may want to open Outlook and send an email, or as in the example presented here open Excel and create a new worksheet



 


It is important to include the following namespace when wanting to use these features:

using System.Windows.Interop;
You will also need to add a reference to the Microsoft.CSharp.dll which can be found in:

Program Files/Microsoft SDKs/Silverlight/V4.0/Libraries/Client/

<UserControl x:Class="SilverlightExcel.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="300" d:DesignWidth="400">

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

        <Button Content="Create XLS" Width="100" Height="30" Click="Button_Click" />

    </Grid>

</UserControl>

 

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using System.Windows.Interop;

using System.Runtime.InteropServices.Automation;

namespace SilverlightExcel

{

    public partial class MainPage : UserControl

    {

        public MainPage()

        {

            InitializeComponent();

        }

        private void Button_Click(object sender, RoutedEventArgs e)

        {

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

            excel.Visible = true;

            dynamic workbook = excel.workbooks;

            workbook.Add();

            dynamic sheet = excel.ActiveSheet;

            dynamic range;

            range = sheet.Range("A1");

            range.Value = "Hello from Silverlight";

            range = sheet.Range("A2");

            range.Value = "100";

            range = sheet.Range("A3");

            range.Value = "50";

            range = sheet.Range("A4");

            range.Formula = "=@Sum(A2..A3)";

            range.Calculate();

        }

    }

}

Here we use the new C# 4.0 dynamic keyword to create and hold the Excel application object.  The following causes Excel to actually load and display:

excel.Visible = true;

Next we add a new Workbook and get a reference to the current Worksheet.  When we have our worksheet we are ready to populate the worksheet cells, for this example we set values in column A.  First we set the column header, then two values and the final cell is a formula to add the two values together.