Skip to main content

How to Bind a Spreadsheet to a List of Objects

  • 5 minutes to read

This example demonstrates how to bind a list of custom objects to a worksheet range, display and edit the object data in worksheet cells.

Custom Object

A custom object is a weather report defined as illustrated below:

View Example

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
}

Data Source

A data source is a collection of the WeatherReport object and can be one of the following types:

Converter

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.

View Example

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

Options

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.

Binding

Call the WorksheetDataBindingCollection.BindToDataSource method for the specified worksheet range, with the specified options and the data source.

View Example

private void BindWeatherReportToRange(object weatherDatasource, CellRange bindingRange) {
    Worksheet sheet = spreadsheetControl1.Document.Worksheets[0];

    // Check for range conflicts.
    var dataBindingConflicts = sheet.DataBindings.
        Where(d => (d.Range.RightColumnIndex >= bindingRange.LeftColumnIndex) || (d.Range.BottomRowIndex >= bindingRange.TopRowIndex)) ;
    if (dataBindingConflicts.Count() > 0) {
        MessageBox.Show("Cannot bind the range to data.\r\nThe worksheet contains other binding ranges which may conflict.", "Range Conflict");
        return;
    }

    // Specify the binding options.
    ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
    dsOptions.ImportHeaders = true;
    dsOptions.CellValueConverter = new MyWeatherConverter();
    dsOptions.SkipHiddenRows = true;

    // Bind the data source to the worksheet range.
    WorksheetDataBinding sheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, bindingRange, dsOptions);

    // Adjust the column width.
    sheetDataBinding.Range.AutoFitColumns();
}

Result

The resulting data binding range in the sample project looks as shown in the picture below:

BindToDataExampleResult

Import Data

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.