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

How to Upload File in Silverlight ?

Step 1: First, create a Silverlight Web application in Visual Studio 2008. You will see your default Page.xaml.

Step 2: On Create Page.xaml, change your code by adding following Panel, Button, and TextBlock controls.

On buttin click event handler, I write code to call the OpenFileDialog that allows us to browse files and gives us the selected file name. Here is the code.

public void Button_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Multiselect = false;
dlg.Filter = "All files (*.*)|*.*|PNG Images (*.png)|*.png";

bool? retval = dlg.ShowDialog();

if (retval != null && retval == true)
{
UploadFile(dlg.File.Name, dlg.File.OpenRead());
StatusText.Text = dlg.File.Name;
}
else {
StatusText.Text = "No file selected...";
} }

As you can see from the above code, I call a method UploadFile by passing the selected file name from the OpenFileDialog. 

 

 

The UploadFile method looks like following. In this code, I use a WebClient class and a PushData method.

private void UploadFile(string fileName, Stream data)
{
UriBuilder ub = new UriBuilder("http://localhost:3840/receiver.ashx");
ub.Query = string.Format("filename={0}", fileName);

WebClient c = new WebClient();
c.OpenWriteCompleted += (sender, e) =>
{
PushData(data, e.Result);
e.Result.Close();
data.Close();
};
c.OpenWriteAsync(ub.Uri);
}

private void PushData(Stream input, Stream output)
{
byte[] buffer = new byte[4096];
int bytesRead;

while ((bytesRead = input.Read(buffer, 0, buffer.Length)) != 0)
{
output.Write(buffer, 0, bytesRead);
} }

Step 3: Add a new Generic Handler receiver.ashx.

Now let's add a class. Right click on the project and Add a new item by selecting Generic Handler in the right side templates as shown below.

 

And add the following code on the coe behind:

 

<%@ WebHandler Language="C#" Class="receiver" %>
using System;using System.Web;using System.IO;

public class receiver : IHttpHandler {

public void ProcessRequest (HttpContext context) {
string filename = context.Request.QueryString["filename"].ToString();

using (FileStream fs = File.Create(context.Server.MapPath("~/App_Data/" + filename)))
{
SaveFile(context.Request.InputStream, fs);
}
}

private void SaveFile(Stream stream, FileStream fs)
{
byte[] buffer = new byte[4096];
int bytesRead;
while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) != 0)
{
fs.Write(buffer, 0, bytesRead);
}
}

public bool IsReusable {
get {
return false;
}
}}

 

Step 4: Build and Run

 

That's all. You are done. Now just build and run your project.

When you click the Select File button, you will see Browse files dialog that lets you browse the files.

 

 

Note: You need to make sure your folder on the Web has write permissions to upload files.