CellValueToColumnTypeConverter.Convert(Cell, CellValue, Type, out Object) Method

Converts a worksheet cell value to a value that will be written in the DataTable.

Namespace: DevExpress.Spreadsheet.Export

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

NuGet Package: DevExpress.Spreadsheet.Core

NuGet Packages: DevExpress.Spreadsheet.Core, DevExpress.WindowsDesktop.Spreadsheet.Core

Declaration

C#

public virtual ConversionResult Convert(
    Cell cell,
    CellValue value,
    Type destinationType,
    out object result
)

VB.NET

Public Overridable Function Convert(
    cell As Cell,
    value As CellValue,
    destinationType As Type,
    ByRef result As Object
) As ConversionResult

Parameters

Name	Type	Description
cell	Cell	A Cell object that is the worksheet cell whose value is converted.
value	CellValue	A CellValue object that is the value to be converted.
destinationType	Type	A Type object that indicates the column data type to which a cell value is converted.
result	Object	An object that is the result of conversion - a value that will be written in a DataTable.

Returns

Type	Description
ConversionResult	A ConversionResult enumeration member that indicates whether the conversion is successful.

Remarks

Supported destination data types (DataTable column data types) are listed below.

• Boolean
• Byte
• Char
• DateTime
• Decimal
• Double
• Guid
• Int16
• Int32
• Int64
• SByte
• Single
• String
• TimeSpan
• UInt16
• UInt32
• UInt64
• Byte[]

If the conversion is successful, the Convert method returns the ConversionResult.Success value.

Note

If an attempt to convert a value failed, the Convert method returns the ConversionResult.Error. Then, the DataTableExporter.CellValueConversionError event is raised. If the CellValueConversionError event is not handled, an exception is thrown.

View Example

Form1.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
        private void barButtonItemUseCustomConverter_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
            CellRange range = worksheet.Tables[0].Range;

            // Create a data table with column names obtained from the first row in a range.
            // Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
            DataTable dataTable = worksheet.CreateDataTable(range, true);
            // Change the data type of the "As Of" column to text.
            dataTable.Columns["As Of"].DataType = System.Type.GetType("System.String");
            // Create the exporter that obtains data from the specified range and populates the specified data table. 
            DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
            // Handle value conversion errors.
            exporter.CellValueConversionError += exporter_CellValueConversionError;

            // Specify a custom converter for the "As Of" column.
            DateTimeToStringConverter toDateStringConverter = new DateTimeToStringConverter();
            exporter.Options.CustomConverters.Add("As Of", toDateStringConverter);
            // Set the export value for empty cell.
            toDateStringConverter.EmptyCellValue = "N/A";
            // Specify that empty cells and cells with errors should be processed.
            exporter.Options.ConvertEmptyCells = true;
            exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;

            // Perform the export.
            exporter.Export();

            // A custom method that displays the resulting data table.
            ShowResult(dataTable);
        }

        // A custom converter that converts DateTime values to "Month-Year" text strings.
        class DateTimeToStringConverter : ICellValueToColumnTypeConverter
        {
            public bool SkipErrorValues { get; set; }
            public CellValue EmptyCellValue { get; set; }

            public ConversionResult Convert(Cell readOnlyCell, CellValue cellValue, Type dataColumnType, out object result)
            {
                result = DBNull.Value; 
                ConversionResult converted = ConversionResult.Success;
                if (cellValue.IsEmpty) {
                    result = EmptyCellValue;
                    return converted;
                }
                if (cellValue.IsError) {
                    // You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
                    //return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
                    result = "N/A";
                    return ConversionResult.Success;
                }
                result =  String.Format("{0:MMMM-yyyy}",cellValue.DateTimeValue);
                return converted;
            }
        }
        void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e)
        {
            MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
            e.DataTableValue = null;
            e.Action = DataTableExporterAction.Continue;
        }

Form1.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export
        Private Sub barButtonItemUseCustomConverter_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barButtonItemUseCustomConverter.ItemClick
            Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
            Dim range As CellRange = worksheet.Tables(0).Range

            ' Create a data table with column names obtained from the first row in a range.
            ' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
            Dim dataTable As DataTable = worksheet.CreateDataTable(range, True)
            ' Change the data type of the "As Of" column to text.
            dataTable.Columns("As Of").DataType = System.Type.GetType("System.String")
            ' Create the exporter that obtains data from the specified range and populates the specified data table. 
            Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, True)
            ' Handle value conversion errors.
            AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError

            ' Specify a custom converter for the "As Of" column.
            Dim toDateStringConverter As New DateTimeToStringConverter()
            exporter.Options.CustomConverters.Add("As Of", toDateStringConverter)
            ' Set the export value for empty cell.
            toDateStringConverter.EmptyCellValue = "N/A"
            ' Specify that empty cells and cells with errors should be processed.
            exporter.Options.ConvertEmptyCells = True
            exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = False

            ' Perform the export.
            exporter.Export()

            ' A custom method that displays the resulting data table.
            ShowResult(dataTable)
        End Sub

        ' A custom converter that converts DateTime values to "Month-Year" text strings.
        Private Class DateTimeToStringConverter
            Implements ICellValueToColumnTypeConverter

            Public Property SkipErrorValues() As Boolean
            Public Property EmptyCellValue() As CellValue Implements ICellValueToColumnTypeConverter.EmptyCellValue

            Public Function Convert(ByVal readOnlyCell As Cell, ByVal cellValue As CellValue, ByVal dataColumnType As Type, <System.Runtime.InteropServices.Out()> ByRef result As Object) As ConversionResult Implements ICellValueToColumnTypeConverter.Convert
                result = DBNull.Value
                Dim converted As ConversionResult = ConversionResult.Success
                If cellValue.IsEmpty Then
                    result = EmptyCellValue
                    Return converted
                End If
                If cellValue.IsError Then
                    ' You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
                    'return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
                    result = "N/A"
                    Return ConversionResult.Success
                End If
                result = String.Format("{0:MMMM-yyyy}", cellValue.DateTimeValue)
                Return converted
            End Function
        End Class
        Private Sub exporter_CellValueConversionError(ByVal sender As Object, ByVal e As CellValueConversionErrorEventArgs)
            MessageBox.Show("Error in cell " & e.Cell.GetReferenceA1())
            e.DataTableValue = Nothing
            e.Action = DataTableExporterAction.Continue
        End Sub

Implements

Convert(Cell, CellValue, Type, out Object)