Exercise 4: Export Data to Excel, Word, and more

As mentioned above, enabling Elevated Privileges opens up a world of possibilities. One feature is the AutomationFactory. This enables you to call COM components from Silverlight. In our application we are going to enable the user to export their Bookmarks into Excel, Word, or e-mail them with Outlook.

1. In MainPage.xaml uncomment lines 128-152. This is the ExportPanel that contains four buttons as seen below.

   XAML

   <Border x:Name="ExportPanel" BorderThickness="0,2,0,0" BorderBrush="#FF666666" Margin="0,2,0,0" Grid.Row="1" Height="57" > <Border.Background> <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0"> <GradientStop Color="#FFDADADA"/> <GradientStop Color="#FFA5A5A5" Offset="1"/> </LinearGradientBrush> </Border.Background> <Grid HorizontalAlignment="Center"> <TextBlock TextWrapping="Wrap" Text="EXPORT BOOKMARKS TO" d:LayoutOverrides="Height" Foreground="#FF181818" HorizontalAlignment="Center" Margin="2,2,0,0" TextOptions.TextHintingMode="Animated" VerticalAlignment="Top" /> <StackPanel HorizontalAlignment="Left" Orientation="Horizontal" VerticalAlignment="Bottom" Height="35" Margin="2,0,0,2"> <Button x:Name="ExcelButton" Margin="0,0,5,0"> <Image Source="Images/Excel.png" Stretch="Fill"/> </Button> <Button x:Name="WordButton" Margin="0,0,5,0"> <Image Source="Images/Word.png" Stretch="Fill"/> </Button> <Button x:Name="OutlookButton" Margin="0,0,5,0"> <Image Source="Images/Outlook.png" Stretch="Fill"/> </Button> <Button x:Name="PowerPointButton" Margin="0"> <Image Source="Images/notepad.png" Stretch="Fill"/> </Button> </StackPanel> </Grid> </Border>
   Figure 1

   Export Panel

2. The ExportPanel contains four buttons: one for exporting to Excel, Word, creating an e-mail with Outlook, and a button to open Notepad. In MainPage.xaml.cs we’ll add Click event handlers for all four buttons. First, in the constructor, register the Click events for each button:

   C#

   // Register Click events for the Export buttons this.ExcelButton.Click += new RoutedEventHandler(ExcelButton_Click); this.WordButton.Click += new RoutedEventHandler(WordButton_Click); this.OutlookButton.Click += new RoutedEventHandler(OutlookButton_Click); this.PowerPointButton.Click += new RoutedEventHandler(PowerPointButton_Click);

   Visual Basic

   'Register Click events for the Export buttons AddHandler ExcelButton.Click, AddressOf ExcelButton_Click AddHandler WordButton.Click, AddressOf WordButton_Click AddHandler OutlookButton.Click, AddressOf OutlookButton_Click AddHandler PowerPointButton.Click, AddressOf PowerPointButton_Click

3. For exporting to Excel copy and paste the below event handler into MainPage.xaml.cs. Here we’re using the AutomationFactory to create an instance of Excel. Then we construct a worksheet.

   C#

   void ExcelButton_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 cell = null; int i = 1; // Populate the excel sheet foreach (var item in (LayoutRoot.DataContext as Bookmarks).Sites) { cell = sheet.Cells[i, 1]; cell.Value = item.Title; cell = sheet.Cells[i, 2]; cell.Value = item.Uri; cell.ColumnWidth = 100; i++; } }

   Visual Basic

   Private Sub ExcelButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) Dim excel As Object = AutomationFactory.CreateObject("Excel.Application") excel.Visible = True Dim workbook As Object = excel.workbooks workbook.Add() Dim sheet As Object = excel.ActiveSheet Dim cell As Object = Nothing Dim i As Integer = 1 ' Populate the excel sheet For Each item In (TryCast(LayoutRoot.DataContext, Bookmarks)).Sites cell = sheet.Cells(i, 1) cell.Value = item.Title cell = sheet.Cells(i, 2) cell.Value = item.Uri cell.ColumnWidth = 100 i += 1 Next item End Sub
   Figure 2

   Instance of Excel Page

4. For exporting to Word copy and paste the below event handler into MainPage.xaml.cs. Here we’re using the AutomationFactory to create an instance of Word. Then we construct a write data to the newly created page in Word. The interesting piece about this call to word is the last line where the SaveAs method is called. Calling this method saves the word doc to your Documents folder.

   C#

   void WordButton_Click(object sender, RoutedEventArgs e) { if (AutomationFactory.IsAvailable) { dynamic word = AutomationFactory.CreateObject("Word.Application"); word.Visible = true; word.Documents.Add(); word.Selection.TypeText("Bookmarks"); word.Selection.TypeText(string.Format("Exported at: {0}", DateTime.Today.ToShortDateString())); word.Selection.TypeParagraph(); foreach (var item in (LayoutRoot.DataContext as Bookmarks).Sites) { word.Selection.TypeText(string.Format("{0} \vURL: {1}\v", item.Title, item.Uri)); } word.ActiveDocument.SaveAs("BookmarksFromSL"); } }

   Visual Basic

   Private Sub WordButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) If AutomationFactory.IsAvailable Then Dim word As Object = AutomationFactory.CreateObject("Word.Application") word.Visible = True word.Documents.Add() word.Selection.TypeText("Bookmarks") word.Selection.TypeText(String.Format("Exported at: {0}",Date.Today.ToShortDateString())) word.Selection.TypeParagraph() For Each item In (TryCast(LayoutRoot.DataContext, Bookmarks)).Sites word.Selection.TypeText(String.Format("{0} " & vbVerticalTab & "URL: {1}" & vbVerticalTab, item.Title, item.Uri)) Next item word.ActiveDocument.SaveAs("BookmarksFromSL") End If End Sub
   Figure 3

   Instance of Word Page

5. For calling to Outlook copy and paste the below event handler into MainPage.xaml.cs. Here we’re using the AutomationFactory to create an instance of Outlook and then construct an e-mail.

   C#

   void OutlookButton_Click(object sender, RoutedEventArgs e) { if (AutomationFactory.IsAvailable) { dynamic outlook = AutomationFactory.CreateObject("Outlook.Application"); dynamic mail = outlook.CreateItem(0); mail.To = "me@myname.com"; mail.Subject = "My Bookmarks"; StringBuilder sb = new StringBuilder(); foreach (var item in (LayoutRoot.DataContext as Bookmarks).Sites) { sb.Append(string.Format("{0} \vURL: {1}\v\v", item.Title, item.Uri)); } mail.Body = sb.ToString(); mail.Display(); } }

   Visual Basic

   Private Sub OutlookButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) If AutomationFactory.IsAvailable Then Dim outlook As Object = AutomationFactory.CreateObject("Outlook.Application") Dim mail As Object = outlook.CreateItem(0) mail.To = "me@myname.com" mail.Subject = "My Bookmarks" Dim sb As New StringBuilder() For Each item In (TryCast(LayoutRoot.DataContext, Bookmarks)).Sites sb.Append(String.Format("{0} " & vbVerticalTab & "URL: {1}" & vbVerticalTab & vbVerticalTab, item.Title, item.Uri)) Next item mail.Body = sb.ToString() mail.Display() End If End Sub
   Figure 4

   Instance of Outlook Page

6. Finally, the last button doesn’t necessarily export the Bookmarks, rather it open Notepad. This is more or less to demonstrate a call to a non Office product. Using the WScript.Shell you could call a number of other external programs. So here’s your freebie. Enjoy, but make sure to play nice when working with Elevated Privileges and Out-of-Browser.

   C#

   void PowerPointButton_Click(object sender, RoutedEventArgs e) { if (AutomationFactory.IsAvailable) { dynamic cmd = AutomationFactory.CreateObject("WScript.Shell"); cmd.Run(@"c:\windows\notepad.exe", 1, true); } }

   Visual Basic

   Private Sub PowerPointButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) If AutomationFactory.IsAvailable Then Dim cmd As Object = AutomationFactory.CreateObject("WScript.Shell") cmd.Run("c:\windows\notepad.exe", 1, True) End If End Sub
   Figure 5

   Open Notepad