In my previous article
Silverlight 4 - export to Excel, I demonstrated how you can export information from Silverlight grid to Excel application. In this article I am going to explain how you can share data between Excel and Silverlight application. You will see how the information modified in Excel application is getting reflected in Silverlight application.
1. Create new Silverlight Application
Create new Silverlight Application. I gave my sample application name "ExportToExcel" you may choose some different name.
2. Add Employee class
Add new class with the name Employee, derive it from INotifyPropertyChanged interface and implement PropertyChangedEventHandler event. Raise PropertyChanged event for both EmployeeName and Department properties when they are modified.
public class
Employee : INotifyPropertyChanged
{
private
string emp_name;
private
string dept;
public event PropertyChangedEventHandler
PropertyChanged;
public int EmployeeId { get;
set; }
public string EmployeeName
{
get
{ return emp_name; }
set
{
emp_name = value;
OnPropertyChanged("EmployeeName");
}
}
public string Department
{
get
{ return dept; }
set
{
dept = value;
OnPropertyChanged("Department");
}
}
protected
void OnPropertyChanged(string
propertyName)
{
if
(PropertyChanged != null)
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
Add GetEmployees method in MainPage.xaml.cs which will return ObservableCollection of type Employee.
private ObservableCollection<Employee> GetEmployees()
{
return new ObservableCollection<Employee>{
new Employee{
EmployeeName="Mike",
EmployeeId=100, Department="CDT"},
new Employee{
EmployeeName="Jason",
EmployeeId=105, Department="CDT"},
new Employee{
EmployeeName="Tony",
EmployeeId=102, Department="Grandslam"},
new Employee{
EmployeeName="Brain",
EmployeeId=112, Department="ESP"},
new Employee{
EmployeeName="Charls",
EmployeeId=108, Department="Grandslam"},
};
}
3. Design UI
Add DataGrid in MainPage.xaml which will show employee details returned from GetEmployees method created in step #2. Also add a button which will be used for export to excel.
<UserControl Name="EmployeeControl"
xmlns:my="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"
x:Class="ExportToExcel.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
d:DesignHeight="350" d:DesignWidth="400">
<Grid x:Name="LayoutRoot"
Background="Transparent">
<StackPanel>
<Grid Margin="10,10,10,10">
<Grid.RowDefinitions>
<RowDefinition Height="*"/>
<RowDefinition Height="10"/>
<RowDefinition Height="50"/>
</Grid.RowDefinitions>
<my:DataGrid AutoGenerateColumns="True"
Height="250"
IsReadOnly="True"
HorizontalAlignment="Center"
VerticalScrollBarVisibility="Auto"
Margin="5,5,5,5"
x:Name="EmployeeGrid"
VerticalAlignment="Top"
Width="400" ItemsSource="{Binding EmployeeCollection, ElementName=EmployeeControl, Mode=TwoWay}" />
<Button Grid.Row="2" HorizontalAlignment="Center" Height="28" Click="btnExport_Click"
x:Name="btnExport"
Content="Export
to Excel"
Width="100"
/>
</Grid>
</StackPanel>
</Grid>
</UserControl>
4. Add reference to Microsoft.CSharp assembly
Add Microsoft.CSharp assembly reference in Silverlight project.
5. Declare delegate to attach it in SheetChange event
Declare delegate to attach it in SheetChange event of Excel application.
delegate void
ExcelSheetChanged(dynamic
excelSheet, dynamic args);
6. Add Export to Excel functionality
Write code for Export to Excel in button click event. Here I have added code to get column header when records are exported to Excel, this was not available in my previous article.
You can see in below code I have attached ExcelSheetChanged delegate declared in step #5 to Excel application SheetChange event.
ExcelSheetChangedEventHandler method handles SheetChange event raised by Excel application. This method reads the changes and updates them to EmployeeCollection dependency property. As the grid has ItemSource property set to EmployeeCollection, as soon as the EmployeeCollection property value is updated you can see updates in
grid.
private void
btnExport_Click(object sender, RoutedEventArgs e)
{
excelApp = AutomationFactory.CreateObject("Excel.Application");
excelApp.Visible = true;
dynamic
workbook = excelApp.workbooks;
workbook.Add();
dynamic
sheet = excelApp.ActiveSheet;
dynamic
cell = null;
int
rowIndex = 2;
for
(int colIndex = 0; colIndex <
EmployeeGrid.Columns.Count; colIndex++)
{
dynamic
header = sheet.Cells[1, colIndex + 1];
header.Value =
EmployeeGrid.Columns[colIndex].Header;
header.Font.Bold = true;
}
foreach
(Employee emp in
EmployeeGrid.ItemsSource)
{
cell = sheet.Cells[rowIndex,
1];
cell.Value = emp.EmployeeId;
cell = sheet.Cells[rowIndex,
2];
cell.Value = emp.EmployeeName;
cell = sheet.Cells[rowIndex,
3];
cell.Value = emp.Department;
rowIndex++;
}
excelApp.SheetChange += new ExcelSheetChanged(ExcelSheetChangedEventHandler);
}
private
void ExcelSheetChangedEventHandler(dynamic sheet, dynamic
rangeArgs)
{
dynamic
empNameColRange = sheet.Range("B2:B"
+ EmployeeCollection.Count);
dynamic
deptColRange = sheet.Range("C2:C"
+ EmployeeCollection.Count);
for
(int i = 0; i < EmployeeCollection.Count;
i++)
{
EmployeeCollection[i].EmployeeName
= empNameColRange.Item(i + 1).Value.ToString();
EmployeeCollection[i].Department = deptColRange.Item(i +
1).Value.ToString();
}
}
7. Setup Out of Browser mode
To setup OOB mode:
- Right click on Silverlight project and select properties.
- Check the "Enable running application out of the browser" check box.
![3.gif]()
- Click on "Out-of-Browser Settings..." button, from the OOB setting dialog select "Running elevated trust when running outside the browser" check box and click on ok.
![4.gif]()
8. Run application
Once OOB setting is completed run your application. Right click on application and install it in your system, once the application is installed it will be opened in OOB window.
Click on "Export to Excel" button to export all records into Excel application. You can see the records in Excel.
Now modify the EmployeeName and Department of Employee Id 105 in Excel. You can see in below screen the EmployeeName is modified to "Tom" and Department is modified to "PMO".
Using the COM API's you can do more complex operation like formula, export images, etc for Excel application. You can also communicate with other Microsoft Office applications like Word, Outlook, etc.