Read Excel in Silverlight

Before we start, in the ‘MyDocuments’ folder, create an Excel file of the name ‘Population.xlsx’ with following data:

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" />

 

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; }

}

 

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;

Step 6: In the loaded event declare following object:

C#

private void UserControl_Loaded(object sender, RoutedEventArgs e)

{

populationData = new ObservableCollection<PopulationClass>();

}

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

}

}

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:

Conclusion: With Silverlight 4.0, developers are provided the facility to easily interact with COM based applications