Exercise 2: Importing Excel data

As a developer it’s imperative to know how to work with tools that businesses rely on. Microsoft Excel is a tool frequently requested to have applications integrated with. With the out-of-browser feature in Silverlight, you’re able to call Excel methods, which include working with data contained inside.

In this exercise you’ll use the AutomationFactory to open an instance of Excel, open the first Worksheet, and loop through the content. The starter solution is already configured to make use of the AutomationFactory, however if you’re working on your own solution, you must first ensure the application has elevated trusted enabled. You can toggle this setting from the Out-of-browser settings in the Project’s Properties panel.

1. Add the below code to the Drop event handler in DataWidget.xaml.cs or DataWidget.xaml.vb The code:
   1. gets a handle on the first file, files[0],
   2. opens Excel using AutomationFactory,
   3. loads the Excel document from MyDocuments (notice the use of Environment.SpecialFolder.MyDocuments),
   4. And loads the active sheet.

   C#

   // Get the first dropped file var fi = files[0]; // Create the Excel object dynamic excel = AutomationFactory.CreateObject("Excel.Application"); // Open the excel document. Must be located in "My Documents" dynamic excelWorkBook = excel.Workbooks.Open(string.Format("{0}\\{1}", Environment.GetFolderPath( Environment.SpecialFolder.MyDocuments),fi.Name)); // Read the Worksheet dynamic activeWorkSheet = excelWorkBook.ActiveSheet();

   Visual Basic

   Dim fi = files(0) Dim excel As Object Try ' Check to see if Excel is already running excel = AutomationFactory.GetObject("Excel.Application") Catch excel = AutomationFactory.CreateObject("Excel.Application") End Try ' Open the excel document Dim excelWorkBook As Object = excel.Workbooks.Open(String.Format("{0}\\{1}", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), fi.Name)) ' Read the Worksheet Dim activeWorkSheet As Object = excelWorkBook.ActiveSheet()
2. At this point the active worksheet is open and you can access the data. Add the below code immediately following the above block to loop through content of the worksheet.

   C#

   // Cells to Read dynamic cell1, cell2; // Iterate through Cells for (int count = 3; count < 30; count++) { cell1 = activeWorkSheet.Cells[count, 1]; cell2 = activeWorkSheet.Cells[count, 2]; Data.Add(new YearValueData() { Year = cell1.Value, Value = cell2.Value }); }

   Visual Basic

   ' Cells to Read Dim cell1, cell2 As Object ' Iterate through Cells For count As Integer = 3 To 29 cell1 = activeWorkSheet.Cells(count, 1) cell2 = activeWorkSheet.Cells(count, 2) Data.Add(New YearValueData() With {.Year = cell1.Value, .Value = cell2.Value}) Next count

3. In the Excel sheet, at position A1 (as seen in Figure 2), is the name of the Worksheet. The following code gets that value (Cells[1,1]) and assigns the value to the Text property of Title TextBlock.

   C#

   // Title is a TextBlock in XAML, this sets the value Title.Text = activeWorkSheet.Cells[1, 1].Value;

   Visual Basic

   'Title is a TextBlock in XAML, this sets the value Title.Text = activeWorkSheet.Cells(1, 1).Value
   Figure 1

4. Add the below code to clean up the Excel object. This will close the Excel process and prevent any potential file locks

   C#

   // Close the workbook excelWorkBook.Close(); // Close the Excel process excel.Quit();

   Visual Basic

   ' Close the workbook excelWorkBook.Close() ' Close the Excel process excel.Quit()
5. Finally, now the data is loaded into the local variable Data, existing DataGrid and Chart’s ItemsSource can be set.

   C#

   // Populate the DataGrid ExcelDataGrid.ItemsSource = this.Data; // Create LineSeries LineSeries lineSeries = new LineSeries(); lineSeries.ItemsSource = this.Data; lineSeries.IndependentValueBinding = new Binding("Year"); lineSeries.DependentValueBinding = new Binding("Value"); this.Chart.Series.Add(lineSeries);

   Visual Basic

   ' Populate the DataGrid ExcelDataGrid.ItemsSource = Me.Data ' Create LineSeries Dim lineSeries As New LineSeries() lineSeries.ItemsSource = Me.Data lineSeries.IndependentValueBinding = New Binding("Year") lineSeries.DependentValueBinding = New Binding("Value") Me.Chart.Series.Add(lineSeries)

6. Putting this all together, here is what the ImportPanel’s Dropped event handler should look like.

   C#

   void ImportPanel_Drop(object sender, DragEventArgs e) { if (e.Data != null) { FileInfo[] files = e.Data.GetData(DataFormats.FileDrop) as FileInfo[]; if (AutomationFactory.IsAvailable) { // Get the first file var fi = files[0]; // Create the Excel object dynamic excel = AutomationFactory.CreateObject("Excel.Application"); // Open the excel document. Must be located in "My Documents" dynamic excelWorkBook = excel.Workbooks.Open(string.Format("{0}\\{1}", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),fi.Name)); // Read the Worksheet dynamic activeWorkSheet = excelWorkBook.ActiveSheet(); // Cells to Read dynamic cell1, cell2; // Iterate through Cells for (int count = 3; count < 30; count++) { cell1 = activeWorkSheet.Cells[count, 1]; cell2 = activeWorkSheet.Cells[count, 2]; Data.Add(new YearValueData() { Year = cell1.Value, Value = cell2.Value }); } // Title is a TextBlock in XAML, this sets the value Title.Text = activeWorkSheet.Cells[1, 1].Value; // Close the workbook excelWorkBook.Close(); // Close the Excel process excel.Quit(); // Populate the DataGrid ExcelDataGrid.ItemsSource = this.Data; // Create LineSeries LineSeries lineSeries = new LineSeries(); lineSeries.ItemsSource = this.Data; lineSeries.IndependentValueBinding = new Binding("Year"); lineSeries.DependentValueBinding = new Binding("Value"); this.Chart.Series.Add(lineSeries); } } }

   Visual Basic

   Private Sub ImportPanel_Drop(ByVal sender As Object, ByVal e As DragEventArgs) If e.Data IsNot Nothing Then Dim files() As FileInfo = TryCast(e.Data.GetData(DataFormats.FileDrop), FileInfo()) Dim fi = files(0) Dim excel As Object Try ' Check to see if Excel is already running excel = AutomationFactory.GetObject("Excel.Application") Catch excel = AutomationFactory.CreateObject("Excel.Application") End Try ' Open the excel document Dim excelWorkBook As Object = excel.Workbooks.Open(String.Format("{0}\\{1}", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), fi.Name)) ' Read the Worksheet Dim activeWorkSheet As Object = excelWorkBook.ActiveSheet() ' Cells to Read Dim cell1, cell2 As Object ' Iterate through Cells For count As Integer = 3 To 29 cell1 = activeWorkSheet.Cells(count, 1) cell2 = activeWorkSheet.Cells(count, 2) Data.Add(New YearValueData() With {.Year = cell1.Value, .Value = cell2.Value}) Next count 'Title is a TextBlock in XAML, this sets the value Title.Text = activeWorkSheet.Cells(1, 1).Value ' Close the workbook excelWorkBook.Close() ' Close the Excel process excel.Quit() ' Populate the DataGrid ExcelDataGrid.ItemsSource = Me.Data ' Create LineSeries Dim lineSeries As New LineSeries() lineSeries.ItemsSource = Me.Data lineSeries.IndependentValueBinding = New Binding("Year") lineSeries.DependentValueBinding = New Binding("Value") Me.Chart.Series.Add(lineSeries) End If End Sub
   Figure 2

   Note:

   Working with object via AutomationFactory, can be challenging. Unfortunately there is no intellisence helping out, so you must rely on documentation. Here are the coding documents that will help when working with Excel.

   Excel 2007 Developer Reference https://msdn.microsoft.com/en-us/library/bb149067(v=office.12).aspx

   Excel 2010 Developer Reference https://msdn.microsoft.com/en-us/library/ff846370.aspx