DataSourceOptionsBase.CellValueConverter Property

Gets or sets the converter that transforms cell values into custom objects and back.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public IBindingRangeValueConverter CellValueConverter { get; set; }

VB.NET

Public Property CellValueConverter As IBindingRangeValueConverter

Property Value

Type	Description
IBindingRangeValueConverter	An object implementing the IBindingRangeValueConverter interface which provides methods for converting cell values into required data types and back.

Example

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

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

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
}

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

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

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the CellValueConverter property.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-bind-a-worksheet-to-a-generic-list-or-a-bindinglist-data-source/CS/DataBindingToListExample/Form1.cs#L50

dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;

wpf-spreadsheet-bind-a-worksheet-to-generic-list-or-bindinglist-data-source/CS/DataBindingToListExample/MainWindow.xaml.cs#L53

dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;

spreadsheet-document-api-use-worksheet-table-as-data-source/CS/SpreadsheetDocumentServerAsDataSourceExample/Form1.cs#L26

options.UseFirstRowAsHeader = true;
options.CellValueConverter = new MyPictureProvider(wb.Worksheets[0]);
options.DataSourceColumnTypeDetector = new MyColumnDetector();

VB.NET

how-to-bind-a-worksheet-to-a-generic-list-or-a-bindinglist-data-source/VB/DataBindingToListExample/Form1.vb#L53

dsOptions.ImportHeaders = True
dsOptions.CellValueConverter = New MyWeatherConverter()
dsOptions.SkipHiddenRows = True

wpf-spreadsheet-bind-a-worksheet-to-generic-list-or-bindinglist-data-source/VB/DataBindingToListExample/MainWindow.xaml.vb#L56

dsOptions.ImportHeaders = True
dsOptions.CellValueConverter = New MyWeatherConverter()
dsOptions.SkipHiddenRows = True

spreadsheet-document-api-use-worksheet-table-as-data-source/VB/SpreadsheetDocumentServerAsDataSourceExample/Form1.vb#L22

options.UseFirstRowAsHeader = True
options.CellValueConverter = New MyPictureProvider(wb.Worksheets(0))
options.DataSourceColumnTypeDetector = New MyColumnDetector()