Reading Excel File in Silverlight 4.0 - COM Programming

Posted by: Mahesh Sabnis , on 9/20/2010, in Category Silverlight 2, 3, 4 and 5
Views: 48755
Abstract: 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.
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:
image_4
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:
image_1
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.
image_2
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 above code uses ‘dynamic’ keyword introduced in C# 4.0. This performs late-binding. Here ‘OpenFileDialog’ class is used to show the open file dialog of the OS when the button is clicked.
 
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:
 
image_3
 
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.

Absolutely Awesome Book on C# and .NET

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 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 eBook 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 .NET Standard and the upcoming C# 8.0 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!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years 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). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Derek Hans on Tuesday, September 28, 2010 2:24 AM
Nice tutorial Mahesh. You help me to improve my skill in silverlight. :-) I have hosted my site with http://www.asphostportal.com for a years. Everyhing looks fine and I like their support. Just info...
Comment posted by Mahesh Sabnis on Tuesday, November 23, 2010 2:14 AM
Hi Derek Hans
  Thanks a lot.
Mahesh Sabnis
Comment posted by Michael on Wednesday, December 1, 2010 2:58 PM
Hi Mahesh, thanks for a great article. I just have 1 concern. I have users who store files outside of the my documents region. The Dialog (as you know) cannot read the fullName, only the name. When you created this article, did you also run into this issue? If yes, have you subsequently tried to find any workarounds?
Comment posted by Mohammed.Shola on Saturday, May 21, 2011 10:57 PM
I really liked your article on reading Excel into Silverlight. Please do you have any code on filtering a datagrid and grouping by certain colums or fields within a datagrid? Now that I can read an Excel file into a SL Application, I want to be able to filter,group and total certain columns wihin the Datagrid based on certain values within the Datagrid. Your help will be most appreciated. Thanks again. Regards Mohammed Shola. (PS: I am a newbie in Silverlight)
Comment posted by Admin on Sunday, May 22, 2011 8:45 PM
@Mohammed: You can read an article for filtering over here http://www.devcurry.com/2011/05/silverlight-4-datagrid-filtering-using.html
Comment posted by Jayant N Pande on Tuesday, July 5, 2011 11:51 AM
It really helped me in reading excel file using OOB in Silverlight. Thank you very much.
Comment posted by Mahesh Sabnis on Saturday, July 16, 2011 11:06 PM
Hi Jayant,
  Thanks a lot. The COM capability provides lots of benefits for developing LOB applications using Silverlight. Typically for users those who wants to make use of SL OOB applications instead of other desktop apps it is really benificial.
Thanks
Regards
Mahesh Sabnis
Comment posted by sam on Thursday, August 11, 2011 9:41 AM
Hi, thank you for sharing this.
i have a small problem, each time i chose to open an excel file i get the following exception
(Exception from HRESULT: 0x800A03EC) at the following line:
dynamic objExcelWorkBook =
                    objExcel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
                    + "\\" + fileName);

Can you please help regarding this?
Thank you
Comment posted by ramesh on Friday, September 23, 2011 4:00 AM
I would like to read the whole excel data and bind it to datagrid.
How can we achieve it
Comment posted by Polly on Thursday, September 29, 2011 4:17 AM
Good Article, thanks.
I find a component which can realize to read Excel on Silverlight simply. So I want to introduce to you, Spire.XLS.
More information: http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html
Comment posted by Anil kumar on Wednesday, December 28, 2011 12:04 PM
HI Dear I am getting error "No object was found registered for specified ProgID"
line no "dynamic objExcel = AutomationFactory.CreateObject("Excel.Application");"

plz help me

Comment posted by Rakesh Chandra Gubbal on Monday, January 23, 2012 10:27 PM
Hi Mahesh. Thanks for you article. I have a requirement to read the excel file(.xls/.xlsx) or CSV from windows 7 machine. I need to read the data from those files and place it in byte[] to send it to WCF service from silverlight. Please help me...
Comment posted by Snehal on Thursday, April 5, 2012 5:41 AM
Hi,
I like your article. Its a good article. Very helpful.
Thanks.
Comment posted by made on Thursday, June 14, 2012 5:33 AM
Hi,
really nice work.
I got an error though: "Object reference not set to an instance of an object." at the "Iterate through Cells" part. What is the problem there?
Thanks in advance
Comment posted by Sanaullah on Thursday, May 30, 2013 8:44 AM
please tell me what will help me if i want read and write the .xlsx file for windows phone 7??????
means i want to save my data from .xlsx file to my windows phone 7!
in this case what mechanisum will i use???
Comment posted by Tanuit on Thursday, August 22, 2013 9:18 PM
Hi Mahesh ,
I make but error
Comment posted by Deepa on Tuesday, March 4, 2014 1:43 AM
HI,

Thanks a lot. Its works perfectly.

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook