This example demonstrates how to bind a list of custom objects to a worksheet range, display and edit the object data in worksheet cells.
A custom object is a weather report defined as illustrated below:
| C#:WeatherReport.cs |
public class WeatherReport {
[DisplayName("Date")]
public DateTime Date { get; set; }
[DisplayName("Weather Condition")]
public Weather Weather { get; set; }
[DisplayName("Max and Min Temperature")]
public List<HourlyReport> HourlyReport { get; set; }
}
public class HourlyReport {
public int Hour { get; set; }
public int Temperature { get; set; }
}
public enum Weather {
Sunny,
Cloudy,
Windy,
Gloomy,
Foggy,
Misty,
Rainy,
Undefined
}
|
| VB:WeatherReport.vb |
Public Class WeatherReport
<DisplayName("Date")> _
Public Property [Date]() As Date
<DisplayName("Weather Condition")> _
Public Property Weather() As Weather
<DisplayName("Max and Min Temperature")> _
Public Property HourlyReport() As List(Of HourlyReport)
End Class
Public Class HourlyReport
Public Property Hour() As Integer
Public Property Temperature() As Integer
End Class
Public Enum Weather
Sunny
Cloudy
Windy
Gloomy
Foggy
Misty
Rainy
Undefined
End Enum
|
A data source is a collection of the WeatherReport object and can be one of the following types:
- System.Collections.Generic.List<T>
- System.ComponentModel.IBindingList
- System.Collections.ObjectModel.ReadOnlyCollection<T>
Create a MyConverter that implements the IBindingRangeValueConverter interface and converts custom objects of the Weather and List<HourlyReport> types to cell values for display. The converter is also responsible for converting cell values back to store them as custom data types.
This code snippet contains the code of the converter which provides the
IBindingRangeValueConverter.TryConvertFromObject method used to convert fields of a custom WeatherReport object for proper display in a worksheet and the
IBindingRangeValueConverter.ConvertToObject method for storing cell values in the fields of a custom object.
| C#:MyConverter.cs |
public class MyWeatherConverter : IBindingRangeValueConverter {
public object ConvertToObject(CellValue value, Type requiredType, int columnIndex) {
if (requiredType == typeof(DateTime))
return value.DateTimeValue;
if (requiredType == typeof(Weather)) {
if (requiredType == typeof(Weather)) {
Weather w;
if (Enum.TryParse(value.TextValue, out w)) return w;
return Weather.Undefined;
}
else
return value.TextValue;
}
if (requiredType == typeof(List<HourlyReport>))
return new List<HourlyReport>();
return value.TextValue;
}
public CellValue TryConvertFromObject(object value) {
if (value is DateTime) {
return ((DateTime)value).ToString("MMM-dd");
}
if (value is Weather) {
return value.ToString();
}
if (value is List<HourlyReport>) {
var hourly = (List<HourlyReport>)value;
if (hourly.Count == 0) return "Undefined";
var high = hourly
.OrderByDescending(p => p.Temperature)
.FirstOrDefault()
.Temperature;
var low = hourly
.OrderBy(p => p.Temperature)
.FirstOrDefault()
.Temperature;
return String.Format("High - {0}, Low - {1}", high, low);
}
return CellValue.TryCreateFromObject(value);
}
}
|
| C#:WeatherReport.cs |
public class WeatherReport {
[DisplayName("Date")]
public DateTime Date { get; set; }
[DisplayName("Weather Condition")]
public Weather Weather { get; set; }
[DisplayName("Max and Min Temperature")]
public List<HourlyReport> HourlyReport { get; set; }
}
public class HourlyReport {
public int Hour { get; set; }
public int Temperature { get; set; }
}
public enum Weather {
Sunny,
Cloudy,
Windy,
Gloomy,
Foggy,
Misty,
Rainy,
Undefined
}
|
| VB:MyConverter.vb |
Public Class MyWeatherConverter
Implements IBindingRangeValueConverter
Public Function ConvertToObject(ByVal value As CellValue, ByVal requiredType As Type, ByVal columnIndex As Integer) As Object Implements IBindingRangeValueConverter.ConvertToObject
If requiredType Is GetType(Date) Then
Return value.DateTimeValue
End If
If requiredType Is GetType(Weather) Then
If requiredType Is GetType(Weather) Then
Dim w As Weather = Nothing
If System.Enum.TryParse(value.TextValue, w) Then
Return w
End If
Return Weather.Undefined
Else
Return value.TextValue
End If
End If
If requiredType Is GetType(List(Of HourlyReport)) Then
Return New List(Of HourlyReport)()
End If
Return value.TextValue
End Function
Public Function TryConvertFromObject(ByVal value As Object) As CellValue Implements IBindingRangeValueConverter.TryConvertFromObject
If TypeOf value Is Date Then
Return DirectCast(value, Date).ToString("MMM-dd")
End If
If TypeOf value Is Weather Then
Return value.ToString()
End If
If TypeOf value Is List(Of HourlyReport) Then
Dim hourly = DirectCast(value, List(Of HourlyReport))
If hourly.Count = 0 Then
Return "Undefined"
End If
Dim high = hourly.OrderByDescending(Function(p) p.Temperature).FirstOrDefault().Temperature
Dim low = hourly.OrderBy(Function(p) p.Temperature).FirstOrDefault().Temperature
Return String.Format("High - {0}, Low - {1}", high, low)
End If
Return CellValue.TryCreateFromObject(value)
End Function
End Class
|
| VB:WeatherReport.vb |
Public Class WeatherReport
<DisplayName("Date")> _
Public Property [Date]() As Date
<DisplayName("Weather Condition")> _
Public Property Weather() As Weather
<DisplayName("Max and Min Temperature")> _
Public Property HourlyReport() As List(Of HourlyReport)
End Class
Public Class HourlyReport
Public Property Hour() As Integer
Public Property Temperature() As Integer
End Class
Public Enum Weather
Sunny
Cloudy
Windy
Gloomy
Foggy
Misty
Rainy
Undefined
End Enum
|
Create a new instance of the ExternalDataSourceOptions object. Set the ExternalDataSourceOptions.ImportHeaders option to true to show Display Name attributes as headers in the binding range. Use the DataSourceOptionsBase.CellValueConverter property to use MyConverter instead of the default converter. Specify other options if needed.
Call the WorksheetDataBindingCollection.BindToDataSource method for the specified worksheet range, with the specified options and the data source.
| C#:MainWindow.xaml.cs |
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;
Worksheet sheet = spreadsheetControl1.Document.Worksheets[0];
WorksheetDataBinding sheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, 2, 1, dsOptions);
|
| VB:MainWindow.xaml.vb |
Dim dsOptions As New ExternalDataSourceOptions()
dsOptions.ImportHeaders = True
dsOptions.CellValueConverter = New MyWeatherConverter()
dsOptions.SkipHiddenRows = True
Dim sheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
Dim sheetDataBinding As WorksheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, 2, 1, dsOptions)
|
The resulting data binding range in the sample project looks as shown in the picture below:
To import data into a worksheet, you can bind a read-only data source and subsequently remove the binding using the WorksheetDataBindingCollection.Remove or WorksheetDataBindingCollection.Clear method. Another way to import data in a worksheet is the WorksheetExtensions.Import method as described in the How to: Import Data to a Worksheet document.