This example demonstrates the use of a List<T> and BindingList<T> objects as data sources to bind data to the worksheet range.
Implementation Details
Use the WorksheetDataBindingCollection.BindToDataSource method to bind data to the range, and the WorksheetDataBindingCollection.BindTableToDataSource method to bind data to the worksheet table.
The ExternalDataSourceOptions object specifies various data binding options. A custom converter with the IBindingRangeValueConverter interface converts weather data between the data source and a worksheet.
If the data source does not allow modification, the binding worksheet range also prevents modification.
Data binding error results in the WorksheetDataBinding.Error event and cancels data update. The event handler in this example displays a message containing the error type.
Files to Review
- Program.cs (VB: Program.vb)
- MyConverter.cs (VB: MyConverter.vb)
- WeatherReport.cs (VB: WeatherReport.vb)
Documentation
Does this example address your development requirements/objectives?
(you will be redirected to DevExpress.com to submit your response)
Example Code
SpreadsheetApiDataBinding/Program.cs(vb)
C#using DevExpress.Spreadsheet;
using System.Diagnostics;
namespace SpreadsheetApiDataBinding
{
internal class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.CreateNewDocument();
// Bind a first worksheet to a BindingList
BindWeatherReportToRange(MyWeatherReportSource.DataAsBindingList, workbook.Worksheets[0]);
// Bind a second worksheet to a List
workbook.Worksheets.Add();
BindWeatherReportToTable(MyWeatherReportSource.Data, workbook.Worksheets[1]);
// Bind a data source to the fixed table on the third worksheet
workbook.Worksheets.Add();
BindWeatherReportToFixedTable(MyWeatherReportSource.Data, workbook.Worksheets[2]);
workbook.Worksheets[0].DataBindings.Error += DataBindings_Error;
workbook.SaveDocument("DataBindings.xlsx");
Process.Start(new ProcessStartInfo("DataBindings.xlsx") { UseShellExecute = true });
}
private static void DataBindings_Error(object sender, DataBindingErrorEventArgs e)
{
Console.WriteLine(String.Format("Error at worksheet.Rows[{0}].\n The error is : {1}", e.RowIndex, e.ErrorType.ToString()), "Binding Error");
}
private static void BindWeatherReportToRange(object weatherDatasource, Worksheet worksheet)
{
// Check for range conflicts.
CellRange bindingRange = worksheet.Range["A1:C5"];
var dataBindingConflicts = worksheet.DataBindings.
Where(binding => (binding.Range.RightColumnIndex >= bindingRange.LeftColumnIndex)
|| (binding.Range.BottomRowIndex >= bindingRange.TopRowIndex));
if (dataBindingConflicts.Count() > 0)
{
Console.WriteLine("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 = worksheet.DataBindings.BindToDataSource(weatherDatasource, bindingRange, dsOptions);
// Adjust the column width.
sheetDataBinding.Range.AutoFitColumns();
}
private static void BindWeatherReportToTable(object weatherDatasource, Worksheet worksheet)
{
CellRange bindingRange = worksheet["A1:C5"];
// Remove all data bindings bound to the specified data source.
worksheet.DataBindings.Remove(weatherDatasource);
// Specify the binding options.
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;
// Create a table and bind the data source to the table.
try
{
WorksheetTableDataBinding sheetDataBinding = worksheet.DataBindings.BindTableToDataSource(weatherDatasource, bindingRange, dsOptions);
sheetDataBinding.Table.Style = worksheet.Workbook.TableStyles[BuiltInTableStyleId.TableStyleMedium14];
// Adjust the column width.
sheetDataBinding.Range.AutoFitColumns();
}
catch (Exception e)
{
Console.WriteLine(e.Message, "Binding Exception");
}
}
private static void BindWeatherReportToFixedTable(object weatherDatasource, Worksheet worksheet)
{
// Remove all data bindings bound to the specified data source.
worksheet.DataBindings.Remove(weatherDatasource);
CellRange bindingRange = worksheet["A1:C5"];
// Specify the binding options.
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;
// Create a table and bind the data source to the table.
try
{
Table boundTable = worksheet.Tables.Add(weatherDatasource, bindingRange, dsOptions);
boundTable.Style = worksheet.Workbook.TableStyles[BuiltInTableStyleId.TableStyleMedium15];
// Adjust the column width.
boundTable.Range.AutoFitColumns();
}
catch (Exception e)
{
Console.WriteLine(e.Message, "Binding Exception");
}
}
}
}
SpreadsheetApiDataBinding/MyConverter.cs(vb)
C#using DevExpress.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SpreadsheetApiDataBinding
{
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);
}
}
}
SpreadsheetApiDataBinding/WeatherReport.cs(vb)
C#using System.ComponentModel;
namespace SpreadsheetApiDataBinding
{
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
}
public static class MyWeatherReportSource
{
private static Random rand = new System.Random();
static List<WeatherReport> data;
static BindingList<WeatherReport> dataBindingList;
public static List<WeatherReport> Data
{
get
{
if (data == null)
{
data = GetReport();
}
return data;
}
}
public static BindingList<WeatherReport> DataAsBindingList
{
get
{
if (dataBindingList == null)
{
dataBindingList = new BindingList<WeatherReport>(Data);
}
return dataBindingList;
}
}
public static List<WeatherReport> GetReport()
{
var report = new List<WeatherReport>();
report.Add(new WeatherReport()
{
Date = DateTime.Today,
Weather = Weather.Rainy,
HourlyReport = GenerateRandomHourlyReport()
});
report.Add(new WeatherReport()
{
Date = DateTime.Today.AddDays(-1),
Weather = Weather.Cloudy,
HourlyReport = GenerateRandomHourlyReport()
});
report.Add(new WeatherReport()
{
Date = DateTime.Today.AddDays(-2),
Weather = Weather.Sunny,
HourlyReport = GenerateRandomHourlyReport()
});
report.Add(new WeatherReport()
{
Date = DateTime.Today.AddDays(-3),
Weather = Weather.Gloomy,
HourlyReport = GenerateRandomHourlyReport()
});
return report;
}
public static List<HourlyReport> GenerateRandomHourlyReport()
{
var report = new List<HourlyReport>();
for (int i = 0; i < 24; i++)
{
var hourlyReport = new HourlyReport();
hourlyReport.Hour = i;
hourlyReport.Temperature = rand.Next(30);
report.Add(hourlyReport);
}
return report;
}
public static void Reload()
{
data = GetReport();
}
}
}