There have been several new features in Silverlight 4.0 which we have been discussing in the previous articles. One of the Silverlight 4.0 features you should know is COM programming capabilities. Using this feature in a Silverlight 4 application, you can Read and Write data to an Excel file. In this article, I will demonstrate the COM programming feature for reading data from Excel worksheet and displaying it in Silverlight 4 application.
Before we start, in the ‘MyDocuments’ folder, create an Excel file of the name ‘Population.xlsx’ with following data:
Creating Silverlight 4.0 client application
Step 1: Start VS2010 and Create a new Silverlight 4.0 application, name it as ‘SLIV4_Reading_Excel_File’.
Step 2: To read the local disk file contents and the files from the ‘MyDocuments’ folder, the Silverlight must be running in an Out-of-Browser mode with elevated rights. Right click on the Silverlight project and select properties. Check the ‘Enable running application out of browser’ as shown below:
After checking the checkbox, click on ‘Out-Of-Browser Settings...’ button. The following window will be displayed as shown below. Check the checkbox ‘Require elevated trust when running outside the browser’. This will make the ‘MyDocument’ folder accessible to the Silverlight 4 application.
Step 3: Open MainPage.Xaml and add mark up for a ‘DataGrid’ and ‘Button’ as shown below:
<sdk:DataGrid AutoGenerateColumns="False"
Height="263" HorizontalAlignment="Left"
Margin="30,66,0,0" Name="dgExcelData"
VerticalAlignment="Top" Width="274">
<sdk:DataGrid.Columns>
<sdk:DataGridTextColumn Binding="{Binding StateName}" Header="StateName"
Width="137"></sdk:DataGridTextColumn>
<sdk:DataGridTextColumn Binding="{Binding Population}" Header="Population" Width="*">
</sdk:DataGridTextColumn>
</sdk:DataGrid.Columns>
</sdk:DataGrid>
<Button Content="Load Data From Excel"
Height="23" HorizontalAlignment="Left"
Margin="30,12,0,0" Name="btnLoadData"
VerticalAlignment="Top" Width="274" Click="btnLoadData_Click" />
Note: Ignore the events listed here as I will be explaining it later.
Step 4: Add the following class in the Silverlight project. This class is used to store data from the Excel sheet.
C#
public class PopulationClass
{
public string StateName { get; set; }
public double Population { get; set; }
}
VB.NET (Converted Code)
Public Class PopulationClass
Public Property StateName() As String
Public Property Population() As Double
End Class
Step 5: Open MainPage.Xaml.cs and declare the following object at class level: also add the reference for ‘MiCrosft.CSharp’ assembly in the Silverlight project. This is used for making use of ‘AutomationFactory’ class. This class is used to create an object of ‘Excel’ application.
C#
ObservableCollection<PopulationClass> populationData;
VB.NET (Converted Code)
Dim populationData As ObservableCollection(Of PopulationClass)
Step 6: In the loaded event declare following object:
C#
private void UserControl_Loaded(object sender, RoutedEventArgs e)
{
populationData = new ObservableCollection<PopulationClass>();
}
VB.NET (Converted Code)
Private Sub UserControl_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
populationData = New ObservableCollection(Of PopulationClass)()
End Sub
Step 7: Write the following code in ‘Load Data From Excel’ button.
C#
private void btnLoadData_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog flDialog = new OpenFileDialog();
flDialog.Filter = "Excel Files(*.xlsx)|*.xlsx";
bool res = (bool)flDialog.ShowDialog();
if (res)
{
FileInfo fs = flDialog.File;
string fileName = fs.Name;
#region Reading Data From Excel File
dynamic objExcel = AutomationFactory.CreateObject("Excel.Application");
//Open the Workbook Here
dynamic objExcelWorkBook =
objExcel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
+ "\\" + fileName);
//Read the Worksheet
dynamic objActiveWorkSheet = objExcelWorkBook.ActiveSheet();
//Cells to Read
dynamic objCell_1,objCell_2;
//Iterate through Cells
for (int count = 2; count < 17; count++)
{
objCell_1 = objActiveWorkSheet.Cells[count, 1];
objCell_2 = objActiveWorkSheet.Cells[count, 2];
populationData.Add
(
new PopulationClass()
{
StateName = objCell_1.Value,
Population = objCell_2.Value
}
);
}
dgExcelData.ItemsSource = populationData;
#endregion
}
}
VB.NET (Converted Code)
Private Sub btnLoadData_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
Dim flDialog As New OpenFileDialog()
flDialog.Filter = "Excel Files(*.xlsx)|*.xlsx"
Dim res As Boolean = CBool(flDialog.ShowDialog())
If res Then
Dim fs As FileInfo = flDialog.File
Dim fileName As String = fs.Name
' #Region "Reading Data From Excel File"
Dim objExcel As Object = AutomationFactory.CreateObject("Excel.Application")
'Open the Workbook Here
Dim objExcelWorkBook As Object = objExcel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\" & fileName)
'Read the Worksheet
Dim objActiveWorkSheet As Object = objExcelWorkBook.ActiveSheet()
'Cells to Read
Dim objCell_1, objCell_2 As Object
'Iterate through Cells
For count As Integer = 2 To 16
objCell_1 = objActiveWorkSheet.Cells(count, 1)
objCell_2 = objActiveWorkSheet.Cells(count, 2)
populationData.Add (New PopulationClass() With {.StateName = objCell_1.Value, .Population = objCell_2.Value})
Next count
dgExcelData.ItemsSource = populationData
' #End Region
End If
End Sub
The code ‘Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments)’ is used to read the ‘MyDocuments’ folder on the OS. This requires Silverlight application running in ‘out of browser’ with elevated rights, which we configured in Step 2. ’ The ‘for’ loop starts from counter ‘2’ because the data from excel file is present starting from the second row.
Step 8: Run the application, click on the ‘Load Data From Excel’, it will show the ‘Open File Dialog’, select the ‘Population.xlsx’ and click on the ‘Open’ button, the following result will be displayed:
Conclusion: With Silverlight 4.0, developers are provided the facility to easily interact with COM based applications.
The entire source code of this article can be downloaded over here
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @
maheshdotnet or connect with him on
LinkedIn